Last week we started the first of a three-part series on SQL Server database permissions and the permissions security model used by the database engine. Last week’s blog post covered the commands used in T-SQL to manage permissions, as well as the security scope within SQL Server. This week we look into how permissions behave they are applied at various points of an ownership chain, such as when a table is referenced in a view which is referenced in a stored procedure and so on. What are the actual permissions that get applied? Read on to find out.
As always, if you want to test these out and don’t have SQL Server handy, you can download a full-featured evaluation copy of SQL Server 2008 R2 from the TechNet Evaluation Center at http://technet.microsoft.com/en-ca/evalcenter/default.aspx (look under Server Products and Technologies | Release). If you are feeling adventurous and want to explore SQL Server 2012 RC0, you can download it from http://technet.microsoft.com/en-ca/evalcenter/hh225126.aspx. Give it a try!
SQL SERVER DATABASE ENGINE PERMISSION MODEL (PART 2)
Consider the following problem, how would you give someone access to parts of a table without giving them permissions on the table directly? Column level permissions or views can be used to project only the required columns; however, when a permission check is done, it is intuitive to check access to the view and the underlying base table.
In SQL Server, when an object is accessed through a chain, the owner of the object referenced and the calling object is checked. If both objects have the same owner, permissions on the referenced object is not evaluated. This is called ownership chaining. This idea is extended beyond just database objects to work across databases as well (called Cross-database ownership chaining).
Figure 1: Illustrating ownership chaining and cross-database ownership chaining in SQL Server
As illustrated in figure 1, when Alex queries view July2003 (owned by Mary), a permission check is done for view July2003. However, since view July2003 references view SalesXY (also owned by Mary), a permission check is not done. Similarly, moving down the permission chain, a permission check is not done for view InvoicesXZ. Since view InvoicesXZ references view AcctAgeXZ (owned by Sam), a permission check is done since there is a change in the object owners along the chain. Similarly, a permission check is done on the base table ExpenseXZ since the table is owned by Joe (different user than Sam).
THE 3 BASIC RULES OF OWNERSHIP CHAINING
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).
WHAT YOU SHOULD KEEP IN MIND WHEN USING OWNERSHIP CHAINING
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.
A SIMPLE T-SQL EXAMPLE ILLUSTRATING OWNERSHIP CHAINING
?CREATE DATABASE OwnershipChainedDB
/* Create users Mary and Alex */
CREATE USER Mary
CREATE USER Alex
/* Create a sensitive table and change owner to Mary */
CREATE TABLE SensitiveData
ALTER AUTHORIZATION ON SensitiveData TO Mary
/* Create procedure and change owner to Mary */
CREATE PROCEDURE SelectSensitiveData
SET NOCOUNT ON
ALTER AUTHORIZATION ON SelectSensitiveData TO Mary
/*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'