locked
overriding behavior of grant execute RRS feed

  • Question

  • 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 objects that proc updates.

    Is there an elegant way to get the opposite effect?  Perhaps on the entire db?   Neither EXECUTE AS nor DENYDATAWRITER seem to change this behavior.

    Pls delete this post if it is considered a "cross post" with http://www.sqlservercentral.com/Forums/Topic1409609-1526-1.aspx .    I was having difficulty with IE8/MSDN and posted a similar question there.   Responses there seemed to dry up prematurely.    


    db042188

    • Moved by Tom Phillips Thursday, January 24, 2013 9:55 PM Security question
    Thursday, January 24, 2013 9:19 PM

Answers

  • It's about ownership chains.  You need to break the ownership chain by putting the read-only procs in a different schema. Like this 

    create table foo(id int)
    
    create user dev without login
    
    create schema dev authorization dev
    
    go
    create procedure dbo.p
    as
    delete from dbo.foo
    go
    
    create procedure dev.p
    as
    delete from dbo.foo
    
    go
    create user some_dev without login
    grant execute on schema::dbo to some_dev --just to test you don't actually wan this
    grant execute on schema::dev to some_dev
    grant select on schema::dbo to some_dev 
    
    go
    execute as user='some_dev'
    
        exec dbo.p --works
        exec dev.p --fails
    
    revert 

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by db042188 Friday, January 25, 2013 7:01 PM
    • Unmarked as answer by db042188 Friday, January 25, 2013 7:05 PM
    • Marked as answer by db042188 Monday, January 28, 2013 1:22 PM
    Friday, January 25, 2013 1:05 AM
  • from reading further in Erland's doc, dynamic sql rights are assesses independently because they arent considered part of the "chain".  In my case that would be a good thing because I'm hoping for the user's rights at the object level to override.

    Yes. In most cases, people do not want to grant access to the tables directly and only use stored procedures. Then they are bitten when they use dynamic SQL, because they are not aware of that the ownership chain is broken.

    The main body of the document shows how you can get over that with certificate signing. But since you want the users table rights to apply, you are already safe.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by db042188 Monday, January 28, 2013 1:22 PM
    Friday, January 25, 2013 10:38 PM

All replies

  • Granting a user the EXECUTE permission on a procedure doesn't change your INSERT/UPDATE/DELETE permission to a table or view.

    All permissions are aggregated, and a DENY overrides any grants. What are you trying to do?


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, January 24, 2013 10:05 PM
  • I just tired to accomplish what you were doing, 

    1.created a user with insert, delete,update and execute permissions on the database.
    2.created a stored prcodeure that updates a table.

    but I was able to update,insert, update and execute procedure without any issues. what does it say when you insert a row to the table. what does the error message say??

    Hope it Helps!!


    • Edited by Stan210 Thursday, January 24, 2013 10:11 PM
    Thursday, January 24, 2013 10:10 PM
  • What you see is the effect of ownership chaining. If you run a stored procedure owned by Jake, and it only accesses tables owned by Jake, all permissions checks are bypassed, as Jake have given you right to run the procedure.

    In most databases all objects are own by dbo, why this detail about the owner does not get much attention.

    It is not clear what you want to achieve, but if you want users permissions on the table to be in effect when they execute the procedure, change the owner of the procedure.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, January 24, 2013 10:28 PM
  • thx guys.  I thought our test was pretty thorough and at least one of the guys at the other post agrees with our findings.

    We want to grant execute on read only procs to certain developers and users.   We're afraid that if a programmer changes such a proc to update and the dba misses it and doesnt remove the grant executes, suddenly that developer or user will have update permissions in production on whatever object the proc now updates.

    What we did: 

    We made sure that one of my peers had no update permissions anywhere in a certain db and that he wasnt sysadmin.  We gave him two procs and a script.  One proc was a read only on an object and when he executed it under ssms, it ran fine.  The other proc did an update on an object but was added with a grant execute to his user id.  He ran it and it updated the object (a specific table) fine.

    The script was an update to the same table and it failed when he executed it.  The error was the familiar one about permissions but I dont have it verbatum at the moment.

    Following all of this we added a DenyDataWriter to the db specifically for him and as I recall on the entire db.  It changed nothing.

    What are you guys saying?


    db042188

    Friday, January 25, 2013 12:08 AM
  • It's about ownership chains.  You need to break the ownership chain by putting the read-only procs in a different schema. Like this 

    create table foo(id int)
    
    create user dev without login
    
    create schema dev authorization dev
    
    go
    create procedure dbo.p
    as
    delete from dbo.foo
    go
    
    create procedure dev.p
    as
    delete from dbo.foo
    
    go
    create user some_dev without login
    grant execute on schema::dbo to some_dev --just to test you don't actually wan this
    grant execute on schema::dev to some_dev
    grant select on schema::dbo to some_dev 
    
    go
    execute as user='some_dev'
    
        exec dbo.p --works
        exec dev.p --fails
    
    revert 

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by db042188 Friday, January 25, 2013 7:01 PM
    • Unmarked as answer by db042188 Friday, January 25, 2013 7:05 PM
    • Marked as answer by db042188 Monday, January 28, 2013 1:22 PM
    Friday, January 25, 2013 1:05 AM
  • We want to grant execute on read only procs to certain developers and users.   We're afraid that if a programmer changes such a proc to update and the dba misses it and doesnt remove the grant executes, suddenly that developer or user will have update permissions in production on whatever object the proc now updates.

    Then you should make sure that procedures and tables have different owners. The easiest may be to put the tables in a schema of their own, and make sure that the schema is not owned by dbo. When you create an object in a schema, by default, the schema owner becomes the owner of the new object, no matter who created the object.

    I have an article on my web site that discusses how you can grant permissions through stored procedure. I start off with describing ownership chaining, and that part may be of interest to you:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by db042188 Friday, January 25, 2013 7:01 PM
    • Unmarked as answer by db042188 Friday, January 25, 2013 7:05 PM
    Friday, January 25, 2013 8:30 AM
  • Thx David.  Your approach did not work.   Here is essentially what I did after reading your post and removing the grant executes and denydatawriter's from my peer's userid...

    1) I created a schema called rdOnly.  I did not include an AUTHORIZATION.

    2) I created proc rdOnly.[update]

    3) I created proc rdOnly.[readOnly]

    4) I granted execute on schema rdOnly to my peer's userid

    5) executing as my peer the original 2 procs and 2 new ones, the original 2 failed because of permissions and the two new ones worked.  But I didnt want rdOnly.[update] to work.  

    I'll try to digest Erland's solution next.  It looks to me like grant execute trumps everything and that is what I'm trying to override.  I have to admit, the concept of breaking the chain isnt meaning anything to me yet. 

     

    db042188

    Friday, January 25, 2013 6:00 PM
  • ok, I read part of Erland's doc.  Apparently David's AUTHORIZATION clause was there to break the chain between my giving my peer permission to execute a proc that I own, that also updates a table that I own.  So instead of trying to outsmart David, I followed the script again with an AUTHORIZATION to a user called rdONLY for the schema and all worked as it was supposed to.  

    Unfortunately, I need an approach for dynamic sql as well.   UDFs cannot do that.  So I'm limited to procs and I'll need to read Erland's doc further to see if he deals with that class of problem.  Maybe its already dealt with in this chaining thing.   I may try it before reading further.

    I did create another proc that updates the table by executing a command built in a varchar.  It failed which is good.  So now I'm wondering if the community feels this is too simple a test for the dynamic sql part of the question?  Maybe dynam sql introduces some holes I'm not thinking of.


    db042188



    • Edited by db042188 Friday, January 25, 2013 7:24 PM tried dynamic
    Friday, January 25, 2013 7:01 PM
  • from reading further in Erland's doc, dynamic sql rights are assesses independently because they arent considered part of the "chain".  In my case that would be a good thing because I'm hoping for the user's rights at the object level to override.

    Please let me know if I've got this right.  If yes, I think I will propose the whole idea to out dept as a compromise to what was previously a moratorium on any user being able to run read only procs against production data. 


    db042188

    Friday, January 25, 2013 10:03 PM
  • from reading further in Erland's doc, dynamic sql rights are assesses independently because they arent considered part of the "chain".  In my case that would be a good thing because I'm hoping for the user's rights at the object level to override.

    Yes. In most cases, people do not want to grant access to the tables directly and only use stored procedures. Then they are bitten when they use dynamic SQL, because they are not aware of that the ownership chain is broken.

    The main body of the document shows how you can get over that with certificate signing. But since you want the users table rights to apply, you are already safe.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by db042188 Monday, January 28, 2013 1:22 PM
    Friday, January 25, 2013 10:38 PM