So, that title may be a little misleading to some. It is not meant as a blanket statement about all procedures and all permissions, but rather a specific situation that I encountered recently. I ran into a case where a user was able access data in a table, even though that user had been explicitly given DENY permission on that table. The data was returned from queries inside of stored procedures. This is something that I was not expecting, and I wanted to blog about it so that others would not be caught off guard by this “feature”. This post is especially for those of you dealing with sensitive data (think PHI, HIPPA, PCI, etc…), so LISTEN UP!
As it turns out, this is by design. According to this MSDN documentation for Managing Permissions with Stored Procedures in SQL Server:
Stored procedures take advantage of ownership chaining to provide access to data so that users do not need to have explicit permission to access database objects. An ownership chain exists when objects that access each other sequentially are owned by the same user. For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables. If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller’s permissions on other objects. Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.
Notice the last two statements. SQL Server only checks the user’s permission on the stored procedure itself, not the objects the procedure accesses. So you can DENY all permissions on a table, but grant EXECUTE on a stored procedure that accesses that table, and the user will get the data returned from the procedure.
Here is a quick example of what I am talking about:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
USE YourDB GO --create a test table CREATE TABLE dbo.heros ( ID TINYINT IDENTITY PRIMARY KEY ,Hero varchar(25) ) --insert some data INSERT INTO dbo.heros VALUES ('Batman') ,('Superman') ,('Homer Simpson') --create a procedure to get the data CREATE PROCEDURE dbo.getHeros AS BEGIN SELECT ID, Hero FROM dbo.heros END GO --create a test login CREATE LOGIN [zed] WITH PASSWORD=N'zed123' GO --set up the user's rights on the database CREATE USER [zed] FOR LOGIN [zed] WITH DEFAULT_SCHEMA=[dbo] GO DENY SELECT ON dbo.heros TO [zed] GO GRANT EXECUTE ON dbo.getHeros TO [zed] GO |
Now, if you log in to SSMS as the user “zed” we created, and run the following query:
|
1 2 3 4 |
SELECT ID, Hero FROM dbo.heros |
You will get the following error:
The SELECT permission was denied on the object ‘heros’
However, if you call the stored procedure that runs the exact same query, it will return the data from the table.
|
1 2 3 |
EXEC dbo.getHeros |
I understand the reasoning behind this, and I’ve used this myself with views before, although I was using multiple schemas and had to set up ownership chaining. I have known DBAs that only allowed access to the database via stored procedures and views, and direct table access was forbidden. Don’t get me wrong, this is a very useful security feature, I just did not realize it worked like this by default in stored procedures. I would have expected the DENY permission to have caused an error in the procedure, and have to explicitly code around it with EXECUTE AS OWNER or something like that.
The Bottom Line!
If you are working with sensitive data, be aware that your users may still be able to access that data via stored procedures, even if you have explicitly given DENY permission on your sensitive tables.