January 21, 2013 at 9:30 am
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
January 21, 2013 at 9:38 am
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
January 21, 2013 at 10:15 am
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.
January 21, 2013 at 1:54 pm
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.
January 22, 2013 at 1:37 am
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.
January 22, 2013 at 7:58 am
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.
January 22, 2013 at 8:04 am
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
January 22, 2013 at 8:39 am
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.
October 17, 2016 at 6:49 am
Sorry for posting on three year old thread.
Just curious to know if there's any solution for a similar situation. I have an archive database in which the stored procedures should be executed but shouldn't insert\update\delete any records. It may give error if it gets to run the DML statements.
many thanks...
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
October 18, 2016 at 2:45 am
Just an idea, what about adding triggers for INSERT, UPDATE and DELETE that just call raiserror and do a rollback, would this help?
October 18, 2016 at 4:24 am
Is it truely an archive database, so you can just toggle it to read only mode, or is it somewhere in between?
You could add a file group, and move the clustered indexes to the new file group, and then make that tilegroup read only. That would prevent updates t9 those tables no matter the permissions or execute context.
Lowell
June 14, 2017 at 9:12 pm
This was removed by the editor as SPAM
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply