SQL Server 2012 : Authentication and Authorization (part 2) - User-Schema Separation,Execution Context

3. User-Schema Separation

Imagine that an employee in an organization who has created many tables and stored procedures leaves the company. As a database administrator, your task is to reassign ownership of all the objects that this former employee created and owned. Prior to SQL Server 2005, this was a tedious task. Since SQL Server 2005, the task is made a lot easier because of the schema feature.

SQL Server 2005 introduced a different concept of schemas than in earlier versions of SQL Server. In SQL Server 2000 (and earlier versions), database users and schemas were equivalent. Every database user was the owner of a schema that had the same name as the user. An owner of an object was effectively identical to the owner of the schema that contains the object. This one-to-one mapping behavior made it very tedious to reassign ownership.

As of SQL Server 2005, users and schemas are separate entities, and schemas are independent containers that can contain zero or more objects. Users can own one or more schemas and can be assigned a default schema. If a default schema is not specified, the user is defaulted to the dbo schema of the database. This default schema is used to resolve the names of securables that are referred to without using their fully qualified name. In SQL Server 2000, the location that is checked first is the schema owned by the calling database user, followed by the schema owned by dbo. Each user has a default schema, so when SQL Server resolves the names of securables, it checks the default schema.

You can thus transfer ownership of many securables, and a schema can contain securables owned by a database user other than the owner of the schema. This is important to note because if you leverage the old system views, such as sysobjects, the results returned in queries will not show up correctly. You must use a catalog view, such sys.objects, instead.

The CREATE SCHEMA statement is used to create a new schema. It can also be used to create tables and views within the new schema and to set GRANT, DENY, or REVOKE permissions on those objects, as shown in Example 3:

Example 3. Granting and denying permissions to a table in a schema.

USE master
GO

-- Create three server logins
CREATE LOGIN Rob WITH PASSWORD = 'jackpot_0'
CREATE LOGIN Tammie WITH PASSWORD = 'jackpot_0'
CREATE LOGIN Vince WITH PASSWORD = 'jackpot_0'
GO

-- Create a new database
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'MyDB')
 DROP DATABASE MyDB
GO
CREATE DATABASE MyDB
GO
USE MyDB
GO

-- Create three database users mapped to the logins
CREATE USER Rob FOR LOGIN Rob
CREATE USER Tammie FOR LOGIN Tammie
CREATE USER Vince FOR LOGIN Vince
GO

-- Create a schema owned by Rob
CREATE SCHEMA Sales AUTHORIZATION Rob
GO

-- Create a table in the schema owned by Rob
CREATE TABLE Sales.Leads (id int, name varchar(50), phone varchar(20))

-- Allow Tammie but not Vince to SELECT from the table in the schema owned by Rob
GRANT SELECT ON Sales.Leads TO Tammie
DENY SELECT ON Sales.Leads TO Vince

This example creates the Sales schema and assigns it to Rob. A Leads table is created within the Sales schema, and Tammie? is granted select access to the table, whereas Vince is denied select access.

For Tammie to access the table in versions of SQL Server prior to SQL Server 2005, she must use Rob for the schema name, like this:

SELECT * FROM Production.SalesReps.Rob.Leads

As of SQL Server 2005, she can use the Sales schema name instead, like this:

SELECT * FROM Production.SalesReps.Sales.Leads

If Rob ever quits or gets fired, all the sysadmin user has to do is transfer ownership of the Sales schema to Tammie by executing the following statement:

ALTER AUTHORIZATION ON SCHEMA::Sales TO Tammie

This abstraction of database users from schemas provides many benefits to developers and administrators, including the following:

• Multiple users can own a single schema through membership in roles or Windows groups. This extends familiar functionality and allows roles and groups to own objects.

• Dropping database users is greatly simplified.

• Dropping a database user does not require renaming objects that are contained by the schema of that user. Therefore, you do not have to revise and test applications that refer explicitly to schema-contained securables after dropping the user who created them.

• Multiple users can share one default schema for uniform name resolution.

• Shared default schemas allow developers to store shared objects in a schema that was created specifically for a particular application, instead of in the dbo schema.

• Permissions on schemas and schema-contained securables can be managed with a greater degree of detail than in earlier releases.

• Fully qualified object names have four parts: server.database.schema.object.

4. Execution Context

Granting and managing permissions to non-sysadmin users has always been an interesting challenge, especially when it comes to users who own stored procedures that access tables and other objects that they do not own. The code in Example 4 presents such a scenario, in which there are three logins: Login1, Login2, and Login3. Respectively, these logins are mapped to database users User1, User2, and User3 that own the schemas Schema1, Schema2, and Schema3.

Example 4. Controlling execution context with multiple database objects, owners, and permissions.

USE master
GO

-- Create 3 logins
CREATE LOGIN Login1 WITH PASSWORD = 'P@$$w0rd1'
CREATE LOGIN Login2 WITH PASSWORD = 'P@$$w0rd2'
CREATE LOGIN Login3 WITH PASSWORD = 'P@$$w0rd3'
GO

- Create a new database
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'MyDB')
 DROP DATABASE MyDB
GO
CREATE DATABASE MyDB
GO
USE MyDB
GO

-- Create 3 users mapped to the 3 logins
CREATE USER User3 FOR LOGIN Login3
CREATE USER User2 FOR LOGIN Login2
CREATE USER User1 FOR LOGIN Login1
GO

-- Create a corresponding schema for each of the 3 users
CREATE SCHEMA Schema3 AUTHORIZATION User3
GO
CREATE SCHEMA Schema2 AUTHORIZATION User2
GO
CREATE SCHEMA Schema1 AUTHORIZATION User1
GO

-- Let User3 create tables and let User2 create stored procedures
GRANT CREATE TABLE TO User3
GRANT CREATE PROCEDURE TO User2
GO

-- Impersonate Login3 (User3)
EXECUTE AS LOGIN = 'Login3'
GO

-- Create and populate a table in Schema3
CREATE TABLE Schema3.Region(RegionName nvarchar(50))
INSERT INTO Schema3.Region VALUES('East Coast'), ('West Coast') , ('Midwest')

-- Allow User2 to SELECT from the Schema3 table
GRANT SELECT ON Schema3.Region TO User2
GO
REVERT
GO

-- Impersonate Login2 (User2)
EXECUTE AS LOGIN = 'Login2'
GO

-- Create a stored procedure in Schema2 that selects from the table in Schema3
CREATE PROCEDURE Schema2.GetRegions AS
 SELECT * FROM Schema3.Region
-GO

-- Allow User1 to EXECUTE the Schema2 stored procedure
GRANT EXECUTE ON Schema2.GetRegions TO User1
GO
REVERT
GO

-- User1 cannot access the Schema3 table, even via a Schema2 stored
-- proc that they have permission to execute
EXECUTE AS LOGIN = 'Login1'
GO
SELECT * FROM Schema3.Region  -- fails
EXEC Schema2.GetRegions       -- fails
GO
REVERT
GO

-- Modify the Schema2 stored proc so that it always run in the context
-- of the owner, not the caller
EXECUTE AS LOGIN = 'Login2'
GO
ALTER PROCEDURE Schema2.GetRegions WITH EXECUTE AS OWNER AS
 SELECT * FROM Schema3.Region
GO
REVERT
GO

-- User1 still cannot access the Schema3 table directly, but now they can
-- indirectly via the Schema2 stored procedure
EXECUTE AS LOGIN = 'Login1'
GO
SELECT * FROM Schema3.Region  -- fails
EXEC Schema2.GetRegions       -- works!
GO
REVERT
GO

In this example, User3 owns the Schema3.Region table, and User2 owns the Schema2.GetRegions stored procedure that retrieves data from this table. This works because User2 has been granted SELECT permission on the table owned by User3. Now User1 needs to execute User2’s stored procedure. This is possible because User1 has been granted EXECUTE permission on the stored procedure, but will still fail because User1 wasn’t also granted SELECT permission on the underlying table that the stored procedure accesses. Prior to SQL Server 2005, there was no easy solution to this problem. Now multiply this requirement across an enterprise and you can appreciate the permission management nightmare that unfolds.

Fortunately, as of SQL Server 2005, it’s easy to change the execution context (a specialized form of impersonation) between different logins and database users, and thereby solve this issue. In our current example, User2 can simply change the execution context of the Schema2.GetRegions stored procedure by applying one of the various supported WITH EXECUTE AS clauses in an ALTER PROCEDURE statement:

• WITH EXECUTE AS CALLER Executes under the credentials of the caller, who requires permission to access all of the underlying objects used by the stored procedure. This is the same default behavior as in earlier versions of SQL Server.

• WITH EXECUTE AS SELF Executes under the credentials of the user who last modified the stored procedure.

• WITH EXECUTE AS <insert name of user> Executes under the credentials of the specified user. For this to work, the user creating or modifying the stored procedure must be granted the IMPERSONATE permission for the specified user.

• WITH EXECUTE AS OWNER Executes under the credentials of the login who owns the stored procedure. If the owner is changed after the object is created, the execution context is automatically mapped to the new owner.

As explained, without changing execution context, User1 can’t access the Schema3 table owned by User3—if an attempt is made, SQL Server will issue the following error message:

The SELECT permission was denied on the object 'Region', database 'MyDB', schema 'Schema3'
.

Prior to SQL Server 2005, you would have to give User1 some form of access against User3’s table in order to run User2’s stored procedure. But as of SQL Server 2005, User2 can simply change the execution context of the stored procedure so that User1 doesn’t need to be granted access to User3’s table. In Example 4, User2 changes the execution context to EXECUTE AS OWNER, which causes the stored procedure to be executed as User2 because User2 is the owner of the stored procedure.

Now when User1 executes User2’s stored procedure, the stored procedure executes under the credentials of User2 and can thus access User3’s table. This access is permitted only within the context of the stored procedure. Thus, User1 is able to execute User2’s stored procedure without having explicit access to the underlying table owned by User3.

Example 4 also demonstrates the EXECUTE AS statement, which lets you change the execution context of the current connection without having to close and reopen the connection. The user must either be a sysadmin or have IMPERSONATE permission on the login for this to work. Alternatively, you can use the EXECUTE AS statement to switch the user context for a database user. When used for switching the context on a database, the scope of impersonation is restricted to the current database. In a context switch to a database user, the server-level permissions of that user are not inherited. In addition, a user must either be a sysadmin or have IMPERSONATE permission on the user of the database.

Executing context switching is a powerful and efficient way to reduce the number of permissions to manage. For developers and administrators, it provides an easy way to test scripts and debug without having to log out and reconnect to SQL Server.