SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


grant execute overrides user permissions


grant execute overrides user permissions

Author
Message
db042188
db042188
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28347 Visits: 39959
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

db042188
db042188
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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
db042188
db042188
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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.
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10125 Visits: 6367
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
When a question, really isn't a question - Jeff Smith
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


db042188
db042188
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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.
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28347 Visits: 39959
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

db042188
db042188
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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.
karthik babu
karthik babu
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1206 Visits: 842
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: :-)
William Rayer
William Rayer
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 454
Just an idea, what about adding triggers for INSERT, UPDATE and DELETE that just call raiserror and do a rollback, would this help?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search