“The SQL Guy” Post # 8: SQL Server Database Engine Permission Model–Part 2

1. If you have access to a given object, and then reference another object through it (such as a view that accesses a table) where both objects have the same owner, permissions on the second object are not checked. If you have access to the first item, it's assumed you'll have access to the second.

2. If you have access to a given object, and then reference another object through it, but the second object does not have the same owner, the permissions on the second object are checked against the retrieving user's permissions. If the user doesn't have permissions that allow access to that second object, he can't read it.

3. All of this is instigated in the security context of the user who invokes the original object. If the user doesn't have access to that original object, nothing happens (which is just standard security practice).

1. Ownership chaining bypasses permission checks completely, which means that it also bypasses DENIES.

For example : If Joe denies Bob access to a table (owned by Joe) but grants him access to a view (owned by Joe) that queries that table, Bob will be able to query the table through the view since no permission check is done on the base table due to ownership chaining.

2. Un-intended ownership chaining can grant access incorrectly

It's often that objects are created as SYSADMINS resulting in the owner as dbo. Soon, a lot of objects are owned by dbo and because of ownership chaining, unintended access to objects can occur. You should always be mindful of ownership chaining when you are designing your database.

It is recommended not to enable cross database ownership chaining to prevent un-intended access. For this reason cross-database ownership chaining is turned off by default.

For example : In figure 1, Alex can use view July2003 in Database 1 to query the ProjectionsXY table in Database 2 if cross-database ownership chaining is turned on.

An alternative is using module signing as described here.

3. Schemas and ownership chaining

Ownership chaining permits bypassing permissions made by one object to another and schemas provide a way of grouping objects together under a single owner (the owner of the schema as in SQL Server 2005 and higher).

By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership.

What this means, from a security perspective, is that we need to check at both the object and the schema level for ownership and consider ownership chaining from there.

For schemas to be affective at separating access to objects, typically, they should be owned by different principals. Avoid all schemas in the database to be owned by dbo.

?CREATE DATABASE OwnershipChainedDB

GO

USE OwnershipChainedDB

GO

/* Create users Mary and Alex */

CREATE USER Mary

WITHOUT LOGIN

GO

CREATE USER Alex

WITHOUT LOGIN

GO

/* Create a sensitive table and change owner to Mary */

CREATE TABLE SensitiveData

(

    IntegerData INT

)

GO

ALTER AUTHORIZATION ON SensitiveData TO Mary

GO

/* Create procedure and change owner to Mary */

CREATE PROCEDURE SelectSensitiveData

AS

BEGIN

    SET NOCOUNT ON

    SELECT *

    FROM dbo.SensitiveData

END

GO

ALTER AUTHORIZATION ON SelectSensitiveData TO Mary

GO

/*Note that at this point Alex does not have any explicit grants on the SensitiveData table */

/*Now grant execute on the procedure to Alex*/

GRANT EXECUTE ON SelectSensitiveData TO Alex

/*Note that Alex can query data from the SensitiveData table through the procedure due to ownership chaining */

execute as user='alex'

exec SelectSensitiveData

revert;