none
execute permission on a procedure allows selects without specific read permission

    Question

  • Does this behavior seem correct or am I not seeing some hidden security setting?

    I have a brand new login that I just mapped to a database. All that this user has is an execute permission on a certain stored procedure, nothing else. That stored procedure selects from a table in that database.

    When that user executes that procedure, he gets the data from the select statement, eventho he doesn't have read permission on that table. Is this the expected behavior or am I missing something? I thought the user also had to have read permission on that table.



    Tuesday, June 18, 2013 8:59 PM

Answers

  • Hallo Pertanyaan,

    yes - this is the expected behaviour if the owner of the objects / schema is always the same. To demonstrate this behaviour see the following example:

    USE db_demo;
    GO
    
    -- who am I = dbo
    SELECT USER_NAME();
    GO
    
    -- Create a dedicated schema
    CREATE SCHEMA foo AUTHORIZATION dbo;
    GO

    The above script creates a dedicated schema for the demonstratoin first. Please notice that I (I'm dbo) am the owner of the schema! Next is the creation of two dedicated objects and granting permissions to the procedure only.

    -- Now I create a table without any dedeicated permissions
    CREATE TABLE foo.foo
    (
    	id	int	not null	identity (1, 1),
    	col1 char(10) null
    );
    GO
    
    -- now i create a procedure which access the relation
    ALTER  PROC foo.proc_access_foo
    AS
    	SET NOCOUNT ON
    	SELECT * FROM foo.foo
    	SET NOCOUNT OFF
    GO

    As you can see from the above script there is a simple relation (which is in the schema [foo] which is owned by me "dbo". Furthermore the procedure - which is located in the same schema - access this relation. Both - relation an procedure - are members of the schema [foo] which is owned by me (dbo). So in this scenario the schema and all it's objects are owned by the dbo.  The next query will demonstrate this:

    -- who is the owner of each object
    SELECT	o.type,
    		o.name AS	object_name,
    		s.name AS	schema_name,
    		o.principal_id AS	object_principal,
    		s.principal_id AS	schema_principal,
    		USER_NAME(ISNULL(o.principal_id, s.principal_id))	AS	owner_name
    FROM	sys.objects o INNER JOIN sys.schemas s
    		ON (o.schema_id = s.schema_id)
    WHERE	s.name = 'foo';

    The above query returns all objects in the schema [foo] and it's owners. You will see that the principal_id of the objects are NULL. This is a normal behaviour if the "owner" of the schema is identically with the owner of the objects which are located in the schema!

    Next step is the demo. Therefore I create a security scenario as follows:

    • allow access to the procedure dbo.foo to ALL users of the database (role = public)
    • creation of a dedicated user (which is automatically member of the role public)
    • execute the procedure as this user
    -- now i authorize all users (not a good behaviour but for demo)
    -- to access this procedure
    GRANT EXECUTE on foo.proc_access_foo TO public
    GO
    
    -- now create a simple new user
    CREATE USER foo_user WITHOUT login;
    GO
    
    -- execute the proc as foo_user
    EXECUTE AS USER = 'foo_user'
    -- will work because EXECUTE is allowed
    EXEC foo.proc_access_foo;
    GO
    
    -- will fail because no SELECT permission granted
    SELECT * FROM foo.foo;
    GO
    REVERT;

    See the results...

    why the execution of the proc will work the SELECT will fail because any user is allowed to execute objects in the schema but has no dedicated SELECT permission for any object.

    This is a pretty smart security solution which works as implemented. Working with views / stored procedures should always the best practice instead of direct access to the relation itself. Unfortunateley 99% of all applications won't do it this way ;)


    Uwe Ricken

    MCM SQL Server 2008
    MCSE - SQL Server 2012
    MCSA - SQL Server 2012

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)


    Wednesday, June 19, 2013 1:41 AM
  • Hi,

    that's what I have written and linked to :)
    All clear than from your side?


    No, Uwe. You linked to the TRUSTWORTHY property - which can be a very big security risk, if used causually, and to certificate signing (which is a more secure way. GoodOldFashionLoverBoy found the third way, enable cross-database ownership chaining, which also can be a security risk, but not as big as the Trustworthy setting.

    For an indepth discussion of all three alterantives, see this article on my web site: http://www.sommarskog.se/grantperm.html


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

    Friday, June 21, 2013 12:19 PM

All replies

  • Hallo Pertanyaan,

    yes - this is the expected behaviour if the owner of the objects / schema is always the same. To demonstrate this behaviour see the following example:

    USE db_demo;
    GO
    
    -- who am I = dbo
    SELECT USER_NAME();
    GO
    
    -- Create a dedicated schema
    CREATE SCHEMA foo AUTHORIZATION dbo;
    GO

    The above script creates a dedicated schema for the demonstratoin first. Please notice that I (I'm dbo) am the owner of the schema! Next is the creation of two dedicated objects and granting permissions to the procedure only.

    -- Now I create a table without any dedeicated permissions
    CREATE TABLE foo.foo
    (
    	id	int	not null	identity (1, 1),
    	col1 char(10) null
    );
    GO
    
    -- now i create a procedure which access the relation
    ALTER  PROC foo.proc_access_foo
    AS
    	SET NOCOUNT ON
    	SELECT * FROM foo.foo
    	SET NOCOUNT OFF
    GO

    As you can see from the above script there is a simple relation (which is in the schema [foo] which is owned by me "dbo". Furthermore the procedure - which is located in the same schema - access this relation. Both - relation an procedure - are members of the schema [foo] which is owned by me (dbo). So in this scenario the schema and all it's objects are owned by the dbo.  The next query will demonstrate this:

    -- who is the owner of each object
    SELECT	o.type,
    		o.name AS	object_name,
    		s.name AS	schema_name,
    		o.principal_id AS	object_principal,
    		s.principal_id AS	schema_principal,
    		USER_NAME(ISNULL(o.principal_id, s.principal_id))	AS	owner_name
    FROM	sys.objects o INNER JOIN sys.schemas s
    		ON (o.schema_id = s.schema_id)
    WHERE	s.name = 'foo';

    The above query returns all objects in the schema [foo] and it's owners. You will see that the principal_id of the objects are NULL. This is a normal behaviour if the "owner" of the schema is identically with the owner of the objects which are located in the schema!

    Next step is the demo. Therefore I create a security scenario as follows:

    • allow access to the procedure dbo.foo to ALL users of the database (role = public)
    • creation of a dedicated user (which is automatically member of the role public)
    • execute the procedure as this user
    -- now i authorize all users (not a good behaviour but for demo)
    -- to access this procedure
    GRANT EXECUTE on foo.proc_access_foo TO public
    GO
    
    -- now create a simple new user
    CREATE USER foo_user WITHOUT login;
    GO
    
    -- execute the proc as foo_user
    EXECUTE AS USER = 'foo_user'
    -- will work because EXECUTE is allowed
    EXEC foo.proc_access_foo;
    GO
    
    -- will fail because no SELECT permission granted
    SELECT * FROM foo.foo;
    GO
    REVERT;

    See the results...

    why the execution of the proc will work the SELECT will fail because any user is allowed to execute objects in the schema but has no dedicated SELECT permission for any object.

    This is a pretty smart security solution which works as implemented. Working with views / stored procedures should always the best practice instead of direct access to the relation itself. Unfortunateley 99% of all applications won't do it this way ;)


    Uwe Ricken

    MCM SQL Server 2008
    MCSE - SQL Server 2012
    MCSA - SQL Server 2012

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)


    Wednesday, June 19, 2013 1:41 AM
  • You said at the start of your example, "this is the expected behaviour if the owner of the objects / schema is always the same". So, in your example, it isn't that the table and the procedure is in the same schema foo that makes it behave this way. Rather, it is because schema foo has the same owner, correct?


    How would I make this work with a procedure selecting from a table in another database? I guess the database that contains that table would have to have the same owner as the schema containing the procedure? But the owner of a database can only be a server login so I'm not sure how this would work.
    Wednesday, June 19, 2013 1:19 PM
  • Hallo Pertanyaan,

    your assumption is correct. Due to the fact that the "dbo" is owner of the schema and has created the object, both objects are from the same creator. The [principal_id] of the object will only be filled with the id of the owner if it differs from the owner of the schema!

    Concerning access to other databases this will not work.
    To access an object in a different database you have to activate TRUSTWORTHY and ...
    The user has to be a user in the other database and...
    has to have access to the object

    Another option would be the access to objects in different databases by a certificate!

    Trustworthy: http://msdn.microsoft.com/en-us/library/ms187861.aspx
    Access by certificate: http://msdn.microsoft.com/en-us/library/bb283630.aspx


    Uwe Ricken MCM - SQL Server 2008&lt;br/&gt; MCSE - SQL Server 2012&lt;br/&gt; MCSA - SQL Server 2012&lt;br/&gt; &lt;br/&gt; db Berater GmbH<br/> http://www.db-berater.de<br/> http://db-berater.blogspot.com

    Wednesday, June 19, 2013 2:39 PM
  • I did some more research on this and it turns out if you turn on cross database ownership chaining, then you can do this across databases. By default this is turned off.

    http://msdn.microsoft.com/en-us/library/aa905173(v=sql.80).aspx

    Wednesday, June 19, 2013 4:21 PM
  • Hi,

    that's what I have written and linked to :)
    All clear than from your side?


    Uwe Ricken MCM - SQL Server 2008 MCSE - SQL Server 2012 MCSA - SQL Server 2012 db Berater GmbH http://www.db-berater.de Blog (german only) http://db-berater.blogspot.com

    Wednesday, June 19, 2013 5:36 PM
  • Yes, all clear... all about ownership chain.
    Thursday, June 20, 2013 1:14 PM
  • Hi,

    that's what I have written and linked to :)
    All clear than from your side?


    No, Uwe. You linked to the TRUSTWORTHY property - which can be a very big security risk, if used causually, and to certificate signing (which is a more secure way. GoodOldFashionLoverBoy found the third way, enable cross-database ownership chaining, which also can be a security risk, but not as big as the Trustworthy setting.

    For an indepth discussion of all three alterantives, see this article on my web site: http://www.sommarskog.se/grantperm.html


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

    Friday, June 21, 2013 12:19 PM
  • Ups - thank you Erland for pointing to this failure.
    That's the problem when doing two things at the same time :)


    MCM SQL Server 2008 MCSE - SQL Server 2012 MCSA - SQL Server 2012 db Berater GmbH http://www-db-berater.de SQL Server Blog (german only)

    Friday, June 21, 2013 4:43 PM