Deny selection on a table is not effective inside the stored procedure
I use sql 2008 R2. My current goal is as a final defense to protect the database from harmful script injection to the stored procedure, assuming the hacker injects the script successfully. The background is this: As a system admin, I made a user login account “user1”, a table “tab1”, a stored procedure “sp1”, and a database role “r1”. I put user1 into r1 and the securables in r1 are: A) allow to execute sp1. B) Deny every permission (including the selection) on tab1. C) Add and save a code line “select * from dbo.tab1” to sp1. Now, I would like to know whether the role “r1” works properly or not: A) use Management studio to log on as user1. B) Execute sp1. The result is that all data from tab1 are returned without error. The role r1 already has the setting of denying user1 to make selection on tab1. Why this setting is not effective when running the stored procedure sp1, which performs selection on tab1?
Answer to your question is ownership chaining in the database. If your procedure has the same owner as the owner of the table, than the privileges are simply bypassed and results returned. So in case the user has DENY SELECT on table, but has EXECUTE on the procedure and the procedure has the same owner as the table, records will be returned without any error. If you want to have a situation that users are granted execute the stored procedures, but also you want the procedures checks the permissions on individual objects used inside the stored procedure (like SELECT on a table), than you can ensure that the stored procedure has different owner as the other objects. To achieve this, you can create a schema for example `[StoredProcs]`. Set the owner of the schema to `[public]`. Create all the procedures for accessing the objects inside this schema. Than grant for example EXECUTE on the schema or individual stored procedures. Once you will do this, the permissions will be applied. So in case the user does not have rights to select a table, procedure will throw an "The SELECT permission was denied on object...". In case you want some procedure to be always able to perform an action on particular object, you can specify the EXECUTE AS clause for such procedure to be always executed in the context of user with appropriate permission. For details see the [EXECUTE AS Clause (Transact-SQL)] especially the Remarks section, which describes the behavior of ownership chaining and ownership of objects. :
Don't you need to explicitly REVOKE permissions to execute the sp1 in order to prevent users from accessing it? As my befuddled mind has it, you can create defences against adhoc table queries by denying users access to the tables specifically, but by granting access only through stored procedures. So, by implication, even if you have denied access to a table, but allowed them access to a stored procedure that access that table, they've therefore got a mechanism to read that table, albeit indirectly. Sorry if this is a bit confused. I'm sure somebody will be along shortly to put that into comprehensible English.
I totally agree with Pavel. The best practice anyway is to specify the login/user with least privliges in the module itself. So whatever is to be done in the module, can be done only if that login/user has the permissions to do so. I have been involved in kind of same practice, where we had multiple users (IIRC, more than a hundred :) ) created without any LOGIN. We granted only required privileges to the users and used `EXEC AS 'USERNAME'` clause in the stored procedures to implement the least privlige security practice. This way we protect any unwanted behavior even if we are connected through webapp with elevated permissions likes of sa. Hope you got the idea and it will help your cause as well.