jump to navigation

Ownership chain in SQL Server November 26, 2013

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags: ,
1 comment so far

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!!!

Advertisements

Stored procedure compilation and caching July 17, 2012

Posted by fofo in general .net, Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags: , ,
1 comment so far

In this post I will explain when the actual caching (the stored procedure is placed in the procedure cache ) takes place.

Many people think that the execution plan of a stored procedure is cached in the procedure cache when the stored procedure is created. That is a mistake.

You can use any edition of SQL Server of 2012/2008/2005 you want for this example. SQL Server Express edition will do just fine.Download the Express edition, here .

In my case I will use Developer edition of 2012 for these example.

I connect to the SQL Server instance.

Type (copy and paste but make sure you review it) and execute the code below


USE AdventureWorks2012
GO

DBCC FREEPROCCACHE
GO

CREATE PROCEDURE PeopleinSeattle
AS
SELECT [AddressID]
,[AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
FROM [AdventureWorks2012].[Person].[Address]
WHERE [City] = 'Seattle'

SELECT cpl.objtype AS PlanType,
OBJECT_NAME(stl.objectid,stl.dbid) AS ObjectName,
cpl.refcounts AS ReferenceCounts,
cpl.usecounts AS UseCounts,
stl.text AS SQLBatch,
qpl.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cpl
CROSS APPLY sys.dm_exec_query_plan(cpl.plan_handle) AS qpl
CROSS APPLY sys.dm_exec_sql_text(cpl.plan_handle) AS stl;
GO

Let me explain what I do with the code above. I clear the procedure cache in line 4.Do not do that in a production environment.

Then in lines 8-17 I create a stored procedure.In lines 19-28 I use 1 DMV and 2 DMFs to get information from the procedure cache.

I get information such as the Plan Type,Use counts(how many times the plan was used),the actual query and its query plan.

When I execute lines 8-17 I get the result you see in the picture below.

It is clear that there is no stored procedure in the cache.Let’s execute the stored procedure and run the code with the DMO (DMV+DMFs) again


exec PeopleinSeattle

SELECT cpl.objtype AS PlanType,
OBJECT_NAME(stl.objectid,stl.dbid) AS ObjectName,
cpl.refcounts AS ReferenceCounts,
cpl.usecounts AS UseCounts,
stl.text AS SQLBatch,
qpl.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cpl
CROSS APPLY sys.dm_exec_query_plan(cpl.plan_handle) AS qpl
CROSS APPLY sys.dm_exec_sql_text(cpl.plan_handle) AS stl;
GO

The result follows in the picture below

Only when the stored procedure is executed the Query optimiser generates a plan, compiles it and places it in the procedure cache.No plan is generated when the stored procedure is created.

Execute the code below


exec PeopleinSeattle
GO 40

SELECT cpl.objtype AS PlanType,
OBJECT_NAME(stl.objectid,stl.dbid) AS ObjectName,
cpl.refcounts AS ReferenceCounts,
cpl.usecounts AS UseCounts,
stl.text AS SQLBatch,
qpl.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cpl
CROSS APPLY sys.dm_exec_query_plan(cpl.plan_handle) AS qpl
CROSS APPLY sys.dm_exec_sql_text(cpl.plan_handle) AS stl;
GO

We execute the stored procedure 40 times and we check again to see the contents of the procedure cache.

Have a look at the picture below to see the results

Now we can see that the compiled plan that was compiled and placed in the procedure cache for this particular stored procedure will be used every time this procedure is executed.That is great performance boost.

That is one of the main advantages of stored procedures.

Hope it helps!!!