Notes on SQL

Introduction

Ownership chaining is one process that SQL Server uses to allow stored procedures access to tables where the user might not have permission. It has issues with stored procedures that have dynamic sql – EXECUTE AS is one solution.

Background

On occasions it might be necessary to control the data access for a specific user, without giving them access to the actual tables.

This is normally controlled via server roles, database roles and (at a more granular level) permissions against individual tables, procedures etc.

Generally, it is possible to give a user execute permission against a stored procedure and that stored procedure will have the required permissions against the tables, without giving permissions directly to the user. This is known as ownership chaining.

At times this simplified method is not possible and one way of coping with the exceptions, is to use EXECUTE AS.

Test Environment

A very basic environment – one simple table, two stored procedures (one with dynamic SQL), two users that have different access settings. We’ll then introduce a third user with the required permissions and no login.

Listing 1: Create database, table and first stored procedure

create database [SecurityTest] 
GO 

use [SecurityTest]; 
GO 

create table dbo.Products( 
ProductCode nvarchar(50) not null, 
ProductName nvarchar(100) not null, 
DateCreated datetimeoffset not null, 
RestrictedItem Bit not null default 0 
constraint [PK_Products] primary key clustered 
( 
ProductCode ASC 
)  
)on [PRIMARY] 
go 

Insert into dbo.Products(ProductCode, ProductName, DateCreated, RestrictedItem) 
values ('111-222-333', 'Test Product One', '01 Jan 2018', DEFAULT), 
('222-333-444', 'Test Product Two', '02 Feb 2018', DEFAULT), 
('333-XXX-555', 'Restricted Item 01', '03 Mar 2018', 1); 
go 

create procedure dbo.NonRestrictedProducts 
AS 
begin 
select ProductCode, ProductName, DateCreated 
from dbo.Products 
where RestrictedItem = 0; 
end 
go 

So, we have a very basic stored procedure, which only selects products that are not stored as restricted items.

Now to create two users. One will have full access to the table with the product data and the stored procedure, and the other will only have access to the stored procedure.

Listing 2: User accounts

/*Set the permissions*/ 
/*FullAccessUser has permissions to all tables and execute permission 
on the stored procedure*/ 

USE [master] 
GO 

CREATE LOGIN [FullAccessUser] WITH PASSWORD=N'FullAccessUser', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF 
GO 

USE [SecurityTest] 
GO 

CREATE USER [FullAccessUser] FOR LOGIN [FullAccessUser] 
GO 

ALTER ROLE [db_datareader] ADD MEMBER [FullAccessUser] 
GO 

ALTER ROLE [db_datawriter] ADD MEMBER [FullAccessUser] 

GO 

GRANT EXECUTE ON [dbo].[NonRestrictedProducts] TO [FullAccessUser] 
GO 


USE [master] 
GO 

CREATE LOGIN [RestrictedAccessUser] WITH PASSWORD=N'RestrictedAccessUser', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF 
GO 

USE [SecurityTest] 
GO 

CREATE USER [RestrictedAccessUser] FOR LOGIN [RestrictedAccessUser] 
GO 


/*RestrictedUser only has execute access on the stored procedure*/ 

GRANT EXECUTE ON [dbo].[NonRestrictedProducts] TO [RestrictedAccessUser] 
GO

Testing user permissions – ‘FullAccessUser’

Switch to ‘FullAccessUser’ and select all rows from the product table:

Listing 3: FullAccessUser queries the product table

EXECUTE AS USER = 'FullAccessUser'; 

SELECT SUSER_NAME(), USER_NAME(); 

SELECT * FROM dbo.Products;

Figure 1: Results from Listing 3

In the results above, the first result shows the user account that this code is being executed as. The second result shows the content of the Products table, because the account executing the query is a member of db_datareader.

We can also execute the stored procedure.

Listing 4: Execute the stored procedure

EXEC [dbo].[NonRestrictedProducts] 

REVERT; 

Figure 2: Results from Listing 4

The stored procedure executes as expected, extracting the information of those products where the RestrictedItem column is 0.

The REVERT command changes the context of the connection back to the original login.

Note: If you’re unsure if you have executed the REVERT command, you can execute it as many times as you wish – it does not raise an error if it is not required.

Testing user permissions – ‘RestictedAccessUser’

Switch to ‘RestrictedAccessUser’ and select all rows from the Product table:

Listing 5: RestrictedAccessUser queries the Product table

EXECUTE AS USER = 'RestrictedAccessUser'; 

SELECT SUSER_NAME(), USER_NAME(); 

select * from dbo.Products;

In this example the query returns the user details:

Figure 4: Result from query in Listing 5

And the attempt to query Products returns an error message:

Figure 5: Error message from Listing 5

Because this account was not given permissions that would allow access to this table, SQL Server has blocked the attempt.

However, because the user has been given execute permission on the stored procedure, the information that they are allowed to see (because the stored procedure was written with that in mind) can be returned to the user:

Listing 6: Execute the stored procedure

EXEC [dbo].[NonRestrictedProducts] 

REVERT;

Figure 6: Result from Listing 6

Now this restricted account can see the data, via the stored procedure. The stored procedure controls what data they can see.

The stored procedure can access the Product table and the user was assigned execute permission to that stored procedure. Even though the user does not have permission to select from the table, the stored procedure does.

Now with a dynamic SQL stored procedure

The situation is slightly different where a stored procedure contains dynamic SQL.

Listing 7: Dynamic SQL stored procedure

USE [SecurityTest] 
GO 

CREATE PROCEDURE [dbo].[NonRestrictedProducts_Dynamic]  
AS  

BEGIN  

DECLARE @SQL NVARCHAR(200) = 
'SELECT ProductCode, ProductName, DateCreated  
FROM dbo.Products  
WHERE RestrictedItem = 0; ' 

EXECUTE sp_executesql @SQL; 

END  
GO  

GRANT EXECUTE ON [dbo].[NonRestrictedProducts_Dynamic] TO [FullAccessUser]  
GO  

GRANT EXECUTE ON [dbo].[NonRestrictedProducts_Dynamic] TO [RestrictedAccessUser]  
GO

The code in Listing 7 will produce the same results as the stored procedure created in Listing 1 – provided the permissions are correct.

Repeating the test with the dynamic SQL and the FullAccessUser:

Listing 8: FullAccessUser executing the dynamic sql stored procedure

EXECUTE AS USER = 'FullAccessUser';  

SELECT SUSER_NAME(), USER_NAME();  

EXEC [dbo].[NonRestrictedProducts_Dynamic]  

REVERT; 

Figure 7: Results from Listing 8

The results in Figure 7 match the results from Figure 2.

In the example from Listing 6, the ‘RestrictedAccessUser’ could also execute the orifginal stored procedure without error, even though their account did not have permission for the source table. Now try this against the dynamic sql code.

Listing 9: RestrictedAccessUser executing the dynamic sql stored procedure

EXECUTE AS USER = 'RestrictedAccessUser';  

SELECT SUSER_NAME(), USER_NAME();  

EXEC [dbo].[NonRestrictedProducts_Dynamic]  

REVERT; 

Figure 8: The result, showing the change of user

Figure 9: The result of the stored procedure execution

This time the attempt to execute the code resulted in the message “The SELECT permission was denied on the object ‘Products’, database ‘SecurityTest’, schema ‘dbo’.”

So, why the change in behaviour?

Stored Procedure Dependencies

If we right-click on the stored procedure ‘dbo.NonRestrictedProducts’, the objects that this stored procedure depends upon can be viewed.

Figure 10: NonRestrictedProducts dependencies

This shows that the stored procedure dbo.NonRestrictedProducts has a dependency on the table ‘Products’. This table is also within the dbo schema, so the access is allowed without any additional work (the owner is ‘dbo’ for both objects). Ownership chaining controls this aspect of the permissions between the objects.

The user ‘RestrictedAccessUser’ does not have permission to access the table ‘dbo.Products’ but because the stored procedure does, the stored procedure can be executed by that account successfully.

The situation is slightly different for the dynamic sql stored procedure.

Figure 11: NonRestrictedProducts_Dynamic dependencies

In this case, there is no dependency listed for other objects. SQL is unable to ascertain what objects are being referenced within the dynamic code.

So, when this code is executed it relies upon the permissions of the caller to dictate what can be accessed.

The user ‘FullAccessUser’ is part of the ‘db_datareader’ database role, which allows SELECT permissions to all tables. Execution of this stored procedure will use the permissions of the executing user and so the data can be accessed.

The user ‘RestrictedAccessUser’ does not have permissions to access any tables within the database, so the stored procedure cannot gain permission to access the table ‘dbo.Products’ by any path, so the execution fails.

EXECUTE AS

So, in this example we wish to give the user ‘RestrictedAccessUser’ the required settings to allow execution of the stored procedure ‘dbo.NonrestrictedProducts_Dynamic’ but without also assigning permission to the underlying table.

One solution is to create a user account that cannot be used as a login (so it cannot be used for unauthorised access), assign the required permissions to that use and then impersonate it within the required code.

Listing 10: Create a user with no associated login

CREATE USER PermittedUser WITHOUT LOGIN; 
GO 

GRANT SELECT ON [dbo].[Products] TO [PermittedUser] 
GO 

We now have a user that cannot be directly used, which has SELECT permission on the table ‘dbo.Products’.

To make use of this user within the stored procedure ‘dbo.NonrestrictedProducts_Dynamic’, we need to make a slight change to the stored procedure.

Listing 11: Altered dynamic stored procedure

ALTER PROCEDURE [dbo].[NonRestrictedProducts_Dynamic]  
WITH EXECUTE AS 'PermittedUser' 
AS  
BEGIN  

DECLARE @SQL NVARCHAR(200) = 
'SELECT ProductCode, ProductName, DateCreated  
FROM dbo.Products  
WHERE RestrictedItem = 0; ' 
EXECUTE sp_executesql @SQL; 

END 

Now we repeat the code that errored, in listing 9

Listing 12: Repeating the test in listing 9, with altered stored procedure (EXECUTE AS)

EXECUTE AS USER = 'RestrictedAccessUser';  

SELECT SUSER_NAME(), USER_NAME();  

EXEC [dbo].[NonRestrictedProducts_Dynamic]  

REVERT;  

Figure 12: Result from Listing 12

Although ‘RestrictedAccessUser’ still does not have the permission required to directly access ‘dbo.Products’, the stored procedure switched to the context of the account ‘PermittedUser’ and used the permissions allocated to that. Those permissions are only valid within the context of this stored procedure, so ‘RestrictedAccessUser’ is not able to use them elsewhere. As long as ‘RestrictedAccessUser’ does not have permission to write their own code, then this method is very effective.

Summary

When database user requires permissions that would normally be beyond what they should have, this is an effective way off assigning additional privileges for a very limited time, by creating an account that can be impersonated but not used to directly access a database.

References
Packaging permissions in stored procedures
The curse and blessing of dynamic sql
EXECUTE AS
Ownership chains