Ownership chain in SQL Server November 26, 2013Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags: ownership chain, stored procedure
I have been delivering a certified course in MS SQL Server 2012 recently and I was asked a very interesting question about SQL Server stored procedures and ownership chain.
Security is a big thing in SQL Server and can be implemented in various levels.
Before we move on, we should define some basic terms that are very important when we talk about security in SQL Server.
The principal access data. It should be authenticated by SQL Server which basically means that the principal should “prove” that it is , whom it claims to be.
Principal is a user a group or an application granted permission to access securables.So what is a securable? Securable is an object to which access privileges are granted.
Privilege is an action that a principal is allowed to perform an a securable.
I want to show you an example where we want to deny a user to the actual table-data but grant him access through a view or a stored procedure.UserA is the owner of tableA and creates StoredProcA that gets data from tableA. Then if the UserB is granted permission to execute the StoredProcA even though he does not have direct access to tableA he will be able to get access (indirectly) to the table. This is the concept of ownership chaining.
I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.
I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)
USE master GO --Create a sample database CREATE DATABASE mysampledb; GO -- create a login CREATE LOGIN login1 WITH PASSWORD ='login1', CHECK_POLICY = OFF; USE mysampledb; go --Create a User for that login CREATE USER thelogin1 FOR LOGIN login1; --see the user that you are now (dbo) SELECT user --create table and populate it with sample data CREATE TABLE dbo.person (id INT IDENTITY (1,1), firstname varchar(40), lastname VARCHAR(50) ); go INSERT dbo.person VALUES ('nick','kenteris'),('maria','negri'),('apostolis','kaperis'),('kostas','gekas'); CREATE PROCEDURE dbo.getpersons AS SELECT id,firstname,lastname FROM dbo.person go --give permissions to execute the procedure to the thelogin1 user GRANT EXECUTE ON dbo.getpersons TO thelogin1 --change the current user as thelogin1 EXECUTE AS USER = 'thelogin1' --check if we are indeed thelogin1 user SELECT USER --fails SELECT * FROM dbo.person --works EXECUTE dbo.getpersons --get back to the dbo user REVERT; --see if the user is indeed dbo SELECT USER --we change the stored proc to use dynamic sql ALTER PROCEDURE dbo.getpersons AS EXEC('SELECT id,firstname,lastname FROM dbo.person') GO --change the current user as thelogin1 EXECUTE AS USER = 'thelogin1' --check to see if indeed the user is now thelogin1 SELECT USER --this will fail EXECUTE dbo.getpersons --get back to the dbo user REVERT; --check to see if we are dbo user SELECT user --this will work ALTER PROCEDURE dbo.getpersons WITH EXECUTE AS OWNER AS EXEC('SELECT id,firstname,lastname FROM dbo.person') GO EXECUTE AS USER = 'thelogin1' --check to see if we are dbo user SELECT USER --this will work EXECUTE dbo.getpersons
Let me explain what I am doing in this bit of code
- I am creating a database in line 6
- I create a login in line 11
- I create a user-thelogin1 for that login in line 17
- I create a new table in lines 23-30
- Then I populate the table – dbo.person in lines 32
- Then I create a procedure – dbo.getpersons in lines 34-36
- Then I grant permissions to the stored procedure to the user thelogin1
- Then I execute the statements like user – thelogin1
- If I try to access the table directly then I fail. That makes sense.
- Then I execute the stored procedure as user – thelogin1 in lines 55
- Then I revert back to the dbo user
- Then I make a change in lines 67-70 to the stored procedure. I use in the body of stored procedure dynamic sql.
- Then I change the user to thelogin1 in lines 74
- Then I execute the stored procedure in line 82 . This will fail.The dynamic SQL statement causes a break in the ownership chain.
- Then I revert back to line 85 as the dbo user
- In the lines 93-97 I change the stored procedure again (WITH EXECUTE AS OWNER)
- Then I change the user to thelogin1 in lines 99
- Finally, I execute the stored procedure again in lines 107 and it works
Hope it helps!!!