Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

grant execute overrides user permissions Expand / Collapse
Author
Message
Posted Monday, January 21, 2013 9:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 9:13 PM
Points: 21, Visits: 69
Hi. It looks like granting a user permission to execute a proc overrides that user's insert/update/delete permissions they'd otherwise have on a table that the proc updates.

I was hoping for the opposite and wonder if there is an elegant way for the following to never occur...

proc x selects data from a table and we grant execute on that proc to user a.
IT changes the proc to do an update but forgets to remove the grant execute.
Now the user can update the table
Post #1409609
Posted Monday, January 21, 2013 9:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:23 PM
Points: 12,927, Visits: 32,330
db042188 (1/21/2013)
Hi. It looks like granting a user permission to execute a proc overrides that user's insert/update/delete permissions they'd otherwise have on a table that the proc updates.

I was hoping for the opposite and wonder if there is an elegant way for the following to never occur...

proc x selects data from a table and we grant execute on that proc to user a.
IT changes the proc to do an update but forgets to remove the grant execute.
Now the user can update the table


that is the expected behavior: if you grant EXECUTE to a proc, then as long as the objects affected all exist under the same owner(ownership chaining, the procedure can insert/update/delete from the table, even when the calling user has no access to those tables at all..

the idea is to give the ability to remove direct access to the underlying objects, and only grant permissions to specific, desired functionality, like a procedure.

in your case, since you stated the same procedure 's code changed from select to update, you'd want to drop adn recreate the procedure to remove the previous permissions on it, i think.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1409615
Posted Monday, January 21, 2013 10:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 9:13 PM
Points: 21, Visits: 69
thx. Any creative safeguard might help. For instance the documentation on EXECUTE AS looks to me like it will defer to permissions at the table level, not the proc level. Here is a link to some 2012 stuff that I think also applies to 2008. We run std 2008. I'll give this a whirl in the mean time.

http://technet.microsoft.com/en-us/library/ms188354.aspx
Post #1409636
Posted Monday, January 21, 2013 1:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 9:13 PM
Points: 21, Visits: 69
EXECUTE AS didnt seem to offer any kind of help. It seems to me that a person still needs the grant which overrides the credentials of the acct specified in the EXECUTE AS clause. Makes me wonder what the EXECUTE AS is for.

I'm thinking as a safeguard maybe some sort of naming convention that requires the the words READ and ONLY be in the proc name so dba can catch any attempt to put some sort of update etc in such a proc. Also, maybe a report for our auditors that identifies procs with these words in it that explicity contain any sort of update and/or execute another proc or udf.
Post #1409704
Posted Tuesday, January 22, 2013 1:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:10 AM
Points: 5,221, Visits: 5,119
Deny takes precedent over Grant, so if you only want users of the proc to select, then simply deny INSERT/UPDATE/DELETE on the tables the proc uses to stop them from changing the data.

You could look at the db level role db_denydatawriter which will prevent all Insert Update Delete statements.

But as Lowell said, drop and re-create will remove the existing permissions on the old proc and allow you to put in new permissions on the new proc.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1409860
Posted Tuesday, January 22, 2013 7:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 9:13 PM
Points: 21, Visits: 69
thx Anthony. That didnt seem to work. I see my peer is a member of the deny data writer role. That makes me believe I set that up correctly. And yet he can execute (successfully) the update proc to which all he has is execute granted.
Post #1410047
Posted Tuesday, January 22, 2013 8:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:23 PM
Points: 12,927, Visits: 32,330
yep if you are granted execute to a proc, even if you, the caller, are denied permission to the underlying tables, the proc will execute.

is this a real problem for you, that a programmer or DBA changes a procedure from SELECT to UPDATE?

it sounds more like an issue with getting with the programmer, who should be creating new procedures to handle the update.

How is he modifying an existing procedure without otherwise reviewing all the code that used to call the procedure and change how they are handled?.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1410051
Posted Tuesday, January 22, 2013 8:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 9:13 PM
Points: 21, Visits: 69
good questions. I'm in a situation (potentially) where an auditing firm is grading us on how well we comply with Sarbanes. Somehow (maybe indirectly) they've talked one of my peers into prohibiting general use of procs (even if they are read only) on data we can query anyway. They are ok if job and service accts execute procs. I suspect that they are somewhat naive but I'm starting to understand their concerns. I'm still against the decision mostly because of what dynamic sql and parameter passing features bring to the table when using procs in sql server.

I suspect I can come up with a silver bullet that would satisfy everybody.

I'm preparing a case against their decision but have struck out on two of the more promising sagfeguards.
Post #1410072
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse