Ownership Chains in SQL Server 2005

When multiple database objects access each other sequentially, the sequence is known as a “chain.” Although such chains have no independent existence, when SQL Server is traversing the links in a chain, it evaluates user permissions on the constituent objects differently than it would if it were accessing them separately. These differences have important implications for managing security.

In the article, we will take a look at how ownership chains in SQL Server 2005 work. For demonstration purposes, we will use 3 users (Mary, John and Scott), and take a look at three different scenarios.

Four Different Scenarios

If you like, you can follow along with this article by running the following code in a test SQL Server database. This creates the three users we need for our scenario’s below.

sp_addlogin ‘Mary’,’mary123′,’AdventureWorks’

GO

USE AdventureWorks

GO

sp_grantdbaccess ‘Mary’

GO

sp_addrolemember ‘db_ddladmin’,’Mary’

Repeat the above code for two other users: John and Scott. (Replace Mary in the above code with John and Scott).

Scenario I

An unbroken ownership chain is one in which the owner of the calling object is also the owner of all the referenced objects. For example, Mary creates a stored procedure that references a table she owns.

She grants execute permissions on the stored procedure to another user, John. When John executes the stored procedure, SQL Server verifies that he (the caller) has permission to execute the stored procedure. Because John has permissions on the stored procedure and because the stored procedure and referenced table have the same owner, no additional permission checking is performed and the statement succeeds. In other words, when Mary granted permissions on the stored procedure to John, she indirectly granted permissions on the referenced table (which she also owns).

1.  Login as Mary and create a table and an SP.            

CREATE TABLE students                  –Creating table students

(rollno INT

,name VARCHAR(30)

,address VARCHAR(100))

GO

INSERT INTO students                     –Inserting records in students table

SELECT ’101′,’ABC’,’LA’ UNION

SELECT ’102′,’XYZ’,’LA’ UNION

SELECT ’103′,’PQR’,’LA’

GO

CREATE PROCEDURE stud_sp          –Creating SP stud_sp

AS

SELECT * FROM students

2. Grant Execute permission on SP ‘stud_sp’ to John
 

             GRANT EXECUTE ON stud_sp TO john

3.  Now login as John and execute the SP ‘stud_sp’.
            

             EXECUTE Mary.stud_sp           

Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |