SQL : Select data from a view and secure the table (Ownership Chains)

Introduction

If you want to hide tables and you want to let the user select the data with a view in SQL Server, you can apply Ownership Chains. From Technet we learn that Ownership Chains are multiple database objects that are sequentially accessed. The ownership chains are maintained by using the same owner in the referenced objects. For example, When a view that references a table has the same owner and you grant rights only to the view to another user than the owner, the data is still shown, though the user has not explicit rights on the referenced table.

In the example above you see three objects, Object1, Object2 and Object3. When an object is accessed (eg. Object2) the owner of the current object (eg. Object2) is compared with the owner of the calling object (eg. Object1). If they are equal, the permissions on the referenced object (eg. Object2) are not evaluated (!). The ownership chain remains unbroken. Suppose that the owner of Object3 has another owner, then the ownership chain is broken and an error will happen.

Ownership chain

So the basis for Ownership chaining is that all objects has the same owner and the owner controls the access of the objects. This gives an administrator a very flexible and robust security scheme. I've worked out an example to understand this principle better. Below an example of a view that references a table. Just an simple example to understand this concept.

In the script below I've tested the different scenario's for a thorough understanding of this concept. I've create a table (tblBla) and a view (vwBla) in a database (OwnershipChainingDB). The view vwBla references the table tblBla.

The following preparation steps are taken:

1. Create the necessary logins.
2. Create the database.
3. Create the table.
4. Create the view.
5. Setup the users.

-----------------------------------------------------------------------
-- 1. Create the necessary logins.
USE [master]
GO

DROP LOGIN [WIN-QTLB6QR5HC9\TestUserA]
GO

CREATE LOGIN [WIN-QTLB6QR5HC9\TestUserA] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

-----------------------------------------------------------------------
-- 2. Create database
USE master
GO 

DROP DATABASE [OwnershipChainingDB]
GO 

CREATE DATABASE [OwnershipChainingDB]
GO

-----------------------------------------------------------------------
-- 3. Create the table.

USE [OwnershipChainingDB]
GO

/*
DROP TABLE [dbo].[tblBla]
GO
*/

CREATE TABLE [dbo].[tblBla](
 [bla] [nchar](10) NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[tblBla]
           ([bla])
     VALUES
           ('blaaaaaaa')
GO
-----------------------------------------------------------------------
-- 4. Create the view.
USE [OwnershipChainingDB]
GO

/*
DROP VIEW [dbo].[vwBla]
GO
*/

CREATE VIEW  [dbo].[vwBla] AS 
SELECT * FROM dbo.tblBla
GO

-----------------------------------------------------------------------
-- 5. Setup the users.
USE [OwnershipChainingDB]
GO

CREATE USER [WIN-QTLB6QR5HC9\TestUserA] FROM LOGIN [WIN-QTLB6QR5HC9\TestUserA]
GO

CREATE USER [WIN-QTLB6QR5HC9\TestUserB] FROM LOGIN [WIN-QTLB6QR5HC9\TestUserB]
GO

GRANT SELECT ON [vwBla] TO [WIN-QTLB6QR5HC9\TestUserA]
GO
 

The following testsituations are considerated :

1. Test the setup for TestUserA.
2. Test the setup for TestUserB,
3. Test what happens when we break the owner ship chain for TestUserA.
4. Test again what happens for TestUserB.
5. Change the ownership of the other object too.
6. GRANT SELECT for TestUserA

1. Test the setup for TestUserA
In this scenario I am testing the access of TestUserA to the view and the table.

-----------------------------------------------------------------------
-- 1. Test the setup for TestUserA
USE [OwnershipChainingDB]
GO

EXECUTE AS USER = 'WIN-QTLB6QR5HC9\TestUserA';
GO

SELECT * FROM dbo.[vwBla]
GO

SELECT * FROM dbo.[tblBla]
GO

SELECT User_name()

REVERT;
GO

The result is :

and

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'tblBla', database 'OwnershipChainingDB', schema 'dbo'.

So, TestUserA has rights to select the data via the view (and therefore the underlying table) but has not direct access rights to the table.


2. Test the setup for TestUserB.
Okay, what happens when TestUserB tries to access the view and the table:

-----------------------------------------------------------------------
-- 2. Test the setup for TestUserB

EXECUTE AS USER = 'WIN-QTLB6QR5HC9\TestUserB';
GO

SELECT * FROM dbo.[vwBla]
GO

SELECT * FROM dbo.[tblBla]
GO

SELECT User_name()

REVERT;
GO

SELECT User_name()

and this results in :

and

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'vwBla', database 'OwnershipChainingDB', schema 'dbo'.
Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'tblBla', database 'OwnershipChainingDB', schema 'dbo'.

This means that TestUserB does not have any rights on the view and no rights on the table.


3. Test what happens when we break the owner ship chain for TestUserA.
In this testsituation we have changed the owner of tblBla to TestUserB. So the ownership chain has broken.

USE [OwnershipChainingDB]
GO

ALTER AUTHORIZATION ON [tblBla] TO [WIN-QTLB6QR5HC9\TestUserB]
GO

EXECUTE AS USER = 'WIN-QTLB6QR5HC9\TestUserA';
GO

SELECT * FROM dbo.[vwBla]
GO

SELECT * FROM dbo.[tblBla]
GO

SELECT User_name()

REVERT;
GO

and this results in :

and

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'tblBla', database 'OwnershipChainingDB', schema 'dbo'.
Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'tblBla', database 'OwnershipChainingDB', schema 'dbo'.

The Ownership chain has broken. You can also check this with the script:

 USE [OwnershipChainingDB];
GO
SELECT 'OBJECT' AS entity_type
    ,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name
    ,name 
FROM sys.objects WHERE SCHEMA_NAME(schema_id) = 'dbo'

resulting in :

4. Test again what happens for TestUserB.

In this scenario we test the changed ownership of TestUserB. The owner of table tblBla has changed from TestUserA to TestUserB and let's see what happens to TestUserB.

-----------------------------------------------------------------------
-- 4. Test again the setup for TestUserB
EXECUTE AS USER = 'WIN-QTLB6QR5HC9\TestUserB';
GO

SELECT * FROM dbo.[vwBla]
GO

SELECT * FROM dbo.[tblBla]
GO

SELECT User_name()

REVERT;
GO

and this results in :

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'vwBla', database 'OwnershipChainingDB', schema 'dbo'.

In this scenario we see that the user TestUserB has no rights on the view.


5. Change the ownership of the other object (vwBla) too.
In this scenario we change the ownership of the view vwBla to TestUserB. Now the complete chain ownership has changed from TestUserA to TestUserB.

 -----------------------------------------------------------------------
-- 5. Test again the setup for TestUserB

ALTER AUTHORIZATION ON [vwBla] TO [WIN-QTLB6QR5HC9\TestUserB]
GO

EXECUTE AS USER = 'WIN-QTLB6QR5HC9\TestUserB';
GO

SELECT * FROM dbo.[vwBla]
GO

SELECT * FROM dbo.[tblBla]
GO

SELECT User_name()

REVERT;
GO

and this results in :

And the ownership is changed to TestUserB.

6.  GRANT SELECT for TestUserA
And now a final test for the user TestUserA and that is that we give GRANT SELECT rights on the view and not on the underlying table.

 -----------------------------------------------------------------------
-- 6. GRANT SELECT for TestUserA
USE [OwnershipChainingDB]
GO

GRANT SELECT ON [vwBla] TO [WIN-QTLB6QR5HC9\TestUserA]
GO

EXECUTE AS USER = 'WIN-QTLB6QR5HC9\TestUserA';
GO

SELECT * FROM dbo.[vwBla]
GO

SELECT * FROM dbo.[tblBla]
GO

SELECT User_name()

REVERT;
GO

And this results in :

And a SELECT permission error occurs, because TestUserA can retrieve data via the ownership chain (view, table) but not directly on the table.

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'tblBla', database 'OwnershipChainingDB', schema 'dbo'.

Conclusion

In this blogpost I've investigated different scenarios in order to understand the ownership chain better. I hope that you also enjoy reading this blogpost as I did investigating and writing about it.

Greetz,
Hennie