Why can "exec as user" bypass permission checking in a proc?

Question

I have a very specific question regarding a behaviour I am seeing but unable to figure it out. I will walk you through the question with code.

Start SSMS with a sysadmin user:

1. Create a new test database

create database test

2. Create a new login

create login newuser with password='newuser', check_policy=off

3. Create a user in the test database and map it to the login

create user newuser for login newuser

4. Create a new table and test it

create table dbo.newtable (id int identity(1, 1)) insert dbo.newtable default values select * from dbo.newtable

5. Execute as newuser

This step will prove that newuser has no SELECT permission on the table.

exec as user = 'NewUser' select suser_sname() as [suser_sname()], original_login() as [original_login()] select * from dbo.NewTable revert

Output:

Msg 229, Level 14, State 5, Line 3
The SELECT permission was denied on the object 'newtable', database 'test', schema 'dbo'.

Now, the next step is where the fun begins. If you wrap the above code snippet inside a proc, you will see that newuser can suddenly query the table.

6. Create a proc

create proc dbo.newproc as exec as user = 'newuser' select suser_sname() as [suser_sname()], original_login() as [original_login()] select * from dbo.newtable revert go -- Note I haven't granted any permissions to newuser whatsoever. exec dbo.newproc

Output:

suser_sname()  original_login()
------------------------------------
newuser        domain\louie.bao

id
--
1

Can anyone please explain to me how is it different in regards to permission checking executing the same code inside a proc?

EDIT

What's more troubling is even if you specifically issue a DENY, newuser still has access to dbo.newtable:

deny select on dbo.newtable to newuser exec dbo.newproc

I understand that ownership chaining will grant access to the proc caller (in this case me), but when I specifically wanted to exec as another user, I'd expect permissions to be checked on that user.

Answer 1

It's a matter of ownership chains.

See Books Online > Ownership Chains: https://technet.microsoft.com/en-us/library/ms188676(v=sql.105).aspx

First we can demonstrate how ownership chains work, without an EXECUTE AS inside the stored procedure, then we can see how it works with the EXECUTE AS.

Create two tables and insert data.

CREATE TABLE dbo.T1 (id int IDENTITY(1,1));
CREATE TABLE dbo.T2 (id int IDENTITY(1,1));

INSERT INTO dbo.T1 DEFAULT VALUES;
INSERT INTO dbo.T2 DEFAULT VALUES;

Create two users.

CREATE USER U1 WITHOUT LOGIN;
CREATE USER U2 WITHOUT LOGIN;

Change the ownership of table T2 to user U2.

ALTER AUTHORIZATION ON OBJECT::dbo.T2 TO U2;

Verify that we've changed the ownership.

SELECT name, principal_id
    FROM sys.tables
    WHERE name IN (N'T1', N'T2');

Demonstrate that user U1 does not have SELECT permission on either table T1 or T2.

EXECUTE AS USER = 'U1';
SELECT * FROM dbo.T1;
SELECT * FROM dbo.T2;
REVERT

Create two stored procedures and allow user U1 to execute both.

CREATE PROCEDURE dbo.P1
AS 
    SELECT * FROM dbo.T1;
GO  
CREATE PROCEDURE dbo.P2
AS 
    SELECT * FROM dbo.T2;
GO

GRANT EXECUTE ON dbo.P1 to U1;
GRANT EXECUTE ON dbo.P2 to U1;

Execute P1 as user U1. This works because there is an unbroken ownership chain. P1 and T1 have the same owner (which is the owner of schema dbo). In this case, permission checking inside the stored procedure is skipped.

EXECUTE AS USER = 'U1';
EXEC dbo.P1;
REVERT

Execute procedure P2 as user U1. This fails because the ownership chain is broken and therefore permissions inside the stored procedure are checked. P2 and T2 have different owners.

EXECUTE AS USER = 'U1';
EXEC dbo.P2;
REVERT

Next, demonstrate how stored procedures work when they contain EXECUTE AS.

Create two more stored procedures.

CREATE PROCEDURE dbo.P1A
AS 
    EXECUTE AS USER = 'U1';
    SELECT * FROM dbo.T1;
    REVERT
GO  
CREATE PROCEDURE dbo.P2A
AS 
    EXECUTE AS USER = 'U1';
    SELECT * FROM dbo.T2;
    REVERT
GO

Execute P1A and this works. Unbroken execution chain, so permissions inside the stored procedure aren't checked.

EXEC dbo.P1A;

Execute P2A and this fails. Broken execution chain, so permissions inside the stored procedure are checked.

EXEC dbo.P2A;

Note that there is an EXECUTE AS statement, which we are using in these examples. There is also an EXECUTE AS clause, which is used with stored procedures, functions, and triggers.

Books Online > EXECUTE AS (Transact-SQL): https://msdn.microsoft.com/en-us/library/ms181362.aspx

Books Online > EXECUTE AS Clause (Transact-SQL): https://msdn.microsoft.com/en-GB/library/ms188354.aspx

Answer 2

This is normal behaviour because of "ownership chaining"

Inside routines, permissions are not checked if the tables referenced have the same AUTHORIZATION as the stored proc. In this case, they both are in the "dbo" schema so permissions are not checked. This includes DENY permissions

create proc dbo.newproc2
with EXECUTE AS CALLER
as
select * from dbo.newtable
GO

GRANT EXEC ON dbo.newproc2 TO newuser
EXEC as user = 'newuser'
exec dbo.newproc2
REVERT


GO

DENY SELECT ON dbo.newtable TO newuser
exec dbo.newproc
GO
EXEC as user = 'newuser'
exec dbo.newproc2
REVERT
GO