asked
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?
In reality, user1 is a login account used by a web app. It can only execute stored procedures by passing parameters. I have done validation on the web app to avoid SQL injection. But I would like to do one more step as a final defense as described above. You never know what script can be injected. So your suggestion of granting access only through stored procedures may not be a good idea.
Actually, using stored procedures (along with the security mapped out by @Thomas Ruston) is usually (at least in my opinion) the preferred way to prevent sql injections. This of course is assuming that you don't have dynamic SQL within the stored procedure. As Thomas mentions, you would need to explicitly REVOKE the permission you wish to block if you wish to have that "extra" step.
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)][1] especially the Remarks section, which describes the behavior of ownership chaining and ownership of objects. [1]:
http://msdn.microsoft.com/en-us/library/ms188354.aspx
Thanks to Pavel to point out the reason which is because of the ownership chaining. But your suggestion to make a new schema and put all stored procedures there may prevent illegal transactions, but you bring up new problems, which are to inhibit the legal transactions. Let me explain. Currently, all objects (table, stored procedure, role, etc) mentioned above are owned by dbo. The stored procedure does routine legal transactions (good things) onto the table. Doing good things of course has permission which is controlled by the database role. So, everybody is happy about that. But suddenly, the pass-in parameter injected with harmful script to do illegal transactions such as select data or delete all the rows from the table (bad things). Now, you suggested to make a new schema and put all stored procedures to the new schema and keep the table in dbo schema to avoid the bad things. It is fine. But it causes a new problem - the stored procedure cannot do good things anymore. Adding "execute as user = 'blabla'" to the stored procedure cannot solve the new problem. Stock
It is not exactly as you wrote. As for example @Usman mentioned, you use EXECUTE AS 'username', than you can have particular username with least privileges to do some task. Than such procedure can do only that particular task and not anything harmful. So there is no problem. Anyway it requires proper security planning.
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.
15 People are following this question.
