none
Store Procedure Grant Execute Permission RRS feed

  • Question

  • Hi Experts,

    If I grant the execute permission on a store procedure to a windows user will it allow the user to also query the sys.tables or require some elevated permissions is needed.

    Say proc name usp_test contains (IF EXISTS ( SELECT 1 sys.tables WHERE Name = 'TableName' BEGIN 'Do Action' END).

    will this statement will be covered or not if execute permission is granted on a store procedure.

    Thanks 

    Priya

                                                                                                                                                                    

    Saturday, September 1, 2018 6:14 AM

Answers

  • No, EXECUTE permission is not sufficient. There is no ownership chaining for metadata. (So Visakh's answer is incorrect.) The user needs to have at least the permission VIEW DEFINITION on the table itself. The script below illustrates. The permission VIEW DEFINITION is not commonly used as such, but it is implied if you grant a user SELECT on a table. (But it can then be revoked or denied separately.)

    If you want to be sure that it works out, you can create a certificate and the sign the procedure with that certificate. You then create a user from the certificate and grant that user VIEW DEFINITION on the table. This is a technique that discuss in much more in this article on my web site: http://www.sommarskog.se/grantperm.html

    Here is the demo script:

    CREATE TABLE sometable (a int NOT NULL)
    go
    CREATE PROCEDURE testie AS
    IF EXISTS (SELECT * FROM sys.tables WHERE name = 'sometable')
       PRINT 'sometable exists'
    ELSE
       PRINT 'sometable does not exist'
    go
    EXEC testie
    go
    CREATE USER testuser WITHOUT LOGIN
    GRANT EXECUTE ON testie TO testuser
    go
    EXECUTE AS USER = 'testuser'
    go
    EXEC testie
    go
    REVERT
    go
    GRANT VIEW DEFINITION ON sometable TO testuser
    go
    EXECUTE AS USER = 'testuser'
    go
    EXEC testie
    go
    REVERT
    go
    DROP TABLE sometable
    DROP USER testuser
    DROP PROCEDURE testie


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, September 1, 2018 8:45 AM
  • Hi,

    If you grant execute permission on stored procedure then the granted user can fire the sys.tables query written inside the stored procedure no elevated permission is needed,however if the user have permission on  table of database then only the SELECT sys.tables query will give the result.

    Example here,

    1) created user with public access to Role and User Mappings

    2) created stored procedure in CSDPayroll db which includes sys.tables inside procedure code

    3) grant execute permission to tstUserGrant user.

    4) execute the stored procedure without giving access to csd_users table used in where condition in stored procedure

    5) given the Select permission to tstUserGrant user on csd_users table, and execute the same procedure and observed the result.

    HTH


    Hope this Helps!!!! Regards, Note: Please Mark As Answered if you satisfy with Reply.

    Saturday, September 1, 2018 7:30 AM
  • Say proc name usp_test contains (IF EXISTS ( SELECT 1 sys.tables WHERE Name = 'TableName' BEGIN 'Do Action' END).

    will this statement will be covered or not if execute permission is granted on a store procedure.                                                                                                                                        

    By default the calling user must have some permission on the object in order for meta-data to be visible via the SQL Server catalog views. This is detained in the documentation: https://docs.microsoft.com/en-us/sql/relational-databases/security/metadata-visibility-configuration. That permission may include VIEW DEFINITION as the documentation states:

    <Excerpt>

    To allow callers to view metadata, you can grant the callers VIEW DEFINITION permission at an appropriate scope: object level, database level or server level.

    </Excerpt>

    In the case of your hard-coded table name in the WHERE clause, you could just "GRANT VIEW DEFINITION ON OBJECT::dbo.TableName TO YourUserOrRole;" in addition to execute permission on usp_test. That would allow users to see the meta-data even without other permissions on the table. The consideration with that method is users could also see the table meta-data with ad-hoc queries outside of the proc code, which may or may not be acceptable depending on your use case. The module signing method Erland mentioned allows you to restrict the meta-data visibility (and other permissions) to only within the proc code.



    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, September 1, 2018 1:10 PM

All replies

  • Depends on the owner of the stored procedure. If owner has access to sys schema then no need of separate select access on sys.tables.

    Only difference is when you execute dynamic code from within the procedure

    Also see

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/managing-permissions-with-stored-procedures-in-sql-server


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Saturday, September 1, 2018 7:46 AM
    Saturday, September 1, 2018 7:21 AM
  • Hi,

    If you grant execute permission on stored procedure then the granted user can fire the sys.tables query written inside the stored procedure no elevated permission is needed,however if the user have permission on  table of database then only the SELECT sys.tables query will give the result.

    Example here,

    1) created user with public access to Role and User Mappings

    2) created stored procedure in CSDPayroll db which includes sys.tables inside procedure code

    3) grant execute permission to tstUserGrant user.

    4) execute the stored procedure without giving access to csd_users table used in where condition in stored procedure

    5) given the Select permission to tstUserGrant user on csd_users table, and execute the same procedure and observed the result.

    HTH


    Hope this Helps!!!! Regards, Note: Please Mark As Answered if you satisfy with Reply.

    Saturday, September 1, 2018 7:30 AM
  • No, EXECUTE permission is not sufficient. There is no ownership chaining for metadata. (So Visakh's answer is incorrect.) The user needs to have at least the permission VIEW DEFINITION on the table itself. The script below illustrates. The permission VIEW DEFINITION is not commonly used as such, but it is implied if you grant a user SELECT on a table. (But it can then be revoked or denied separately.)

    If you want to be sure that it works out, you can create a certificate and the sign the procedure with that certificate. You then create a user from the certificate and grant that user VIEW DEFINITION on the table. This is a technique that discuss in much more in this article on my web site: http://www.sommarskog.se/grantperm.html

    Here is the demo script:

    CREATE TABLE sometable (a int NOT NULL)
    go
    CREATE PROCEDURE testie AS
    IF EXISTS (SELECT * FROM sys.tables WHERE name = 'sometable')
       PRINT 'sometable exists'
    ELSE
       PRINT 'sometable does not exist'
    go
    EXEC testie
    go
    CREATE USER testuser WITHOUT LOGIN
    GRANT EXECUTE ON testie TO testuser
    go
    EXECUTE AS USER = 'testuser'
    go
    EXEC testie
    go
    REVERT
    go
    GRANT VIEW DEFINITION ON sometable TO testuser
    go
    EXECUTE AS USER = 'testuser'
    go
    EXEC testie
    go
    REVERT
    go
    DROP TABLE sometable
    DROP USER testuser
    DROP PROCEDURE testie


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, September 1, 2018 8:45 AM
  • Say proc name usp_test contains (IF EXISTS ( SELECT 1 sys.tables WHERE Name = 'TableName' BEGIN 'Do Action' END).

    will this statement will be covered or not if execute permission is granted on a store procedure.                                                                                                                                        

    By default the calling user must have some permission on the object in order for meta-data to be visible via the SQL Server catalog views. This is detained in the documentation: https://docs.microsoft.com/en-us/sql/relational-databases/security/metadata-visibility-configuration. That permission may include VIEW DEFINITION as the documentation states:

    <Excerpt>

    To allow callers to view metadata, you can grant the callers VIEW DEFINITION permission at an appropriate scope: object level, database level or server level.

    </Excerpt>

    In the case of your hard-coded table name in the WHERE clause, you could just "GRANT VIEW DEFINITION ON OBJECT::dbo.TableName TO YourUserOrRole;" in addition to execute permission on usp_test. That would allow users to see the meta-data even without other permissions on the table. The consideration with that method is users could also see the table meta-data with ad-hoc queries outside of the proc code, which may or may not be acceptable depending on your use case. The module signing method Erland mentioned allows you to restrict the meta-data visibility (and other permissions) to only within the proc code.



    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, September 1, 2018 1:10 PM