SQL Server Technical Article

Writer: Michael Redman

Technical Reviewers: Sanjay Mishra, Juergen Thomas, Jimmy May, Burzin Patel, Glenn Berry (SQL Server MVP), Prem Mehra, Lindsey Allen, Thomas Kejser, Joseph Sack, Wanda He, Sharon Bjeletich

Published: November 2008

Applies to: SQL Server 2008 and SQL Server 2005

Summary: SQL Server 2005 implemented the concept of a database object schema. A schema is a distinct namespace to facilitate the separation, management, and ownership of database objects. It removed the tight coupling of database objects and owners to improve the security administration of database objects. Database object schemas offer functionality to control and help secure application objects within a database environment not available in previous versions of SQL Server.

Introduction

Microsoft SQL Server 2005 introduced the concept of database object schemas. Schemas are analogous to separate namespaces or containers used to store database objects. Security permissions apply to schemas, making them an important tool for separating and protecting database objects based on access rights. Schemas reduce the work required, and improve the flexibility, for security-related administration of a database.

This white paper discusses the opportunities for improvements in the security administration of a user database, and it outlines some best practices around using schemas to manage database objects in development and production databases. Specifically, it addresses three real-world scenarios:

• Protecting database objects from being altered by users without the knowledge of the database owner
• Preventing database base objects, independent software vendor (ISV) databases in particular, from ad hoc or incorrect user access leading to poor application performance
• Bringing related groups of objects (logical entities) together within one physical database to reduce physical database administrative overhead

Overview

In releases prior to SQL Server 2005, database object owners and users were the same things. SQL Server 2005 introduced the concept of database schemas and the separation between database objects and ownership by users. An object owned by a database user is no longer tied to that user. The object now belongs to a schema – a container that can hold many database objects. The schema owner may own one or many schemas. This concept creates opportunities to expose database objects within a database for consumption yet protect them from modification, direct access using poor query techniques, or removal by users other than the owner.

The ability to protect database objects in this way has many practical applications. One example of relevance is the protection of database objects in application development environments where developers and testers share the same set of database objects. Another example of protecting database objects is in ISV products such as Siebel or SAP. Unmanaged access by ad hoc queries or poorly tuned queries of the base objects will negatively impact the performance of the application. Additionally, the use of schemas can help to combine entities from separate applications or functional areas, or logical groups, into a single physical database.

What Is User Schema Separation?

Prior to SQL Server 2005, a database object (for example, a table) is owned by a user. That user could be DBO or any valid user account. That table is now directly linked to that user– the user cannot be deleted without removing the table or changing the owner of the table. The table can only ever be owned by one user.

User-schema separation, introduced in SQL Server 2005, means that the table is no longer owned by any user; it belongs to a schema. In turn, the schema is owned by a user.

A schema is separate entity within the database. It is created by using the CREATE SCHEMA statement. A schema can be owned by a user, a role, or a group (for more information about possible schema owners, see the “Principals” section in this document). A user executing CREATE SCHEMA can be the owner of the schema or it can allocate another user as the schema owner (with appropriate IMPERSONATE permissions). A schema only has one owner, but a user can own many schemas. Schema ownership is transferrable.

Database objects are created and contained within a schema. For example, when the following statement is executed, the table MyTable is created within the MySchema schema:

CREATE TABLE MySchema.MyTable (col1 int, col2 int)

This separation means objects and schemas can be created before users are added to the database. It also means a user can be dropped without specifically dropping the objects owned by that user.

Note: A schema cannot be dropped if it contains any objects. If a DROP SCHEMA statement is executed while it contains objects, the drop operation fails.

There are many advantages to user-schema separation, including:

• The matrix of permissions on schemas and the database objects within them is significantly more complex than in earlier releases. This allows much more control of access, and levels of access, for the administrator.
• Ownership of schemas and the database objects within them is transferable.
• Objects can be moved between schemas
• Multiple database users can share a single schema.
• A database user can be dropped without dropping objects in a corresponding schema.

Default Schema

Users can be defined with a default schema. The default schema is the first schema that is searched when it resolves the names of objects it references.

The default schema for a user can be defined by using the DEFAULT_SCHEMA option of CREATE USER or ALTER USER. If no default schema is defined for a user account, SQL Server will assume dbo is the default schema. It is important note that if the user is authenticated by SQL Server as a member of a group in the Windows operating system, no default schema will be associated with the user. If the user creates an object, a new schema will be created and named the same as the user, and the object will be associated with that user schema.

Security Concepts in SQL Server 2005 and SQL Server 2008

SQL Server 2005 introduced several new concepts that relate to the new schema objects.

Principals

When a schema is created, it is owned by a principal. A principal is any entity or object that has access to SQL Server resources. These are:

• Windows domain logins
• Windows local logins
• SQL Server logins
• Windows groups
• Database roles
• Server roles
• Application roles

Securables

A securable is any database entity or object that can be secured or managed with permissions. At the very highest level, this would be the server itself. Securables include databases and all associated objects. Some securables can be nested inside others. This creates a hierarchy referred to as the securable scope. The following table details the securables, and their scope, in SQL Server:

Table 1: SQL Server securable and scope

Permissions

Access to any securable is managed by permissions. Permissions are a set of defined levels of access to a securable and are applied individually or collectively to a securable. Permissions are granted to a principal. Common permissions to be granted are:

• CONTROL: Confers ownership-like capabilities on the grantee. The grantee effectively has all defined permissions on the securable.
• ALTER: Confers the ability to change the properties, except ownership, of a particular securable. The ALTER permission on a schema includes the ability to create, alter, and drop objects from the schema.
• ALTER ANY <Server Securable>: Confers the ability to create, alter, or drop individual instances of the server securable. For example, ALTER ANY LOGIN confers the ability to create, alter, or drop any login in the instance.
• ALTER ANY <Database Securable>: Confers the ability to CREATE, ALTER, or DROP individual instances of the database securable. For example, ALTER ANY SCHEMA confers the ability to create, alter, or drop any schema in the database.
• TAKE OWNERSHIP: Enables the grantee to take ownership of the securable on which it is granted.
• IMPERSONATE <Login>: Enables the grantee to impersonate the login.
• IMPERSONATE <User>: Enables the grantee to impersonate the user.
• CREATE <Server Securable>: Confers to the grantee the ability to create the server securable.
• CREATE <Database Securable>: Confers to the grantee the ability to create the database securable.
• CREATE <Schema-contained Securable>: Confers the ability to create the schema-contained securable. However, ALTER permission on the schema is required to create the securable in a particular schema.
• VIEW DEFINITION: Enables the grantee to access metadata.

For a complete list of permissions and the securables to which they can be granted, see SQL Server Books Online. There is also a very handy table-valued function called sys.fn_builtin_permissions that returns either all permissions or securables in a tabular format or for a specific securable. To return all permissions for all securables, execute the following statement:

SELECT * FROM sys.fn_builtin_permissions(DEFAULT)

Using Schemas in SQL Server

Schema functionality offers many benefits in a SQL Server environment. Combined with the appropriate level of user permissions, schemas can be a very effective object protection tool. Consider the scenario where multiple teams are working on a database application. The design team wants to maintain the integrity of the database tables but allow the developers to create other database objects such as stored procedures and views to suit the application.

This scenario is harmonious with the use of schemas to protect the database tables. The database application tables (securables) are created in a schema. This will allow access to the tables to be controlled by permissions. The schema could be the dbo schema or any other.

A second schema is created—called, say, Developer_Schema. The Developer_Schema will be the schema in which the developers will create all additional application related objects such as stored procedures, views, and functions.

Lastly, a database role called something like Developer_Role is created. Security principals (developers) are added to Developer_Role. Developer_Role is granted SELECT, REFERENCES, INSERT, UPDATE, DELETE, and (optionally) VIEW DEFINTION permissions on the dbo schema. Developer_Role is not granted ALTER or CONTROL permissions on the dbo schema. Developer_Role is granted ALTER and CONTROL permissions on Developer_Schema. This explicit set of permissions will allow the developer to perform any action on the database tables with the exception of altering or dropping the table in any way. The developers (principals) are configured with a default schema of Developer_Schema, allowing them to create objects in the Developer_Schema by default.

Lastly, the required permissions are assigned to Database_Role at a database level, for example, CREATE TABLE or DROP TABLE permissions. This is done using the GRANT statement.

Below is the code for the example above:

--create a test database
CREATE DATABASE [SecurityTest]
GO
USE SecurityTest
GO
CREATE TABLE [dbo].[table1](
       [pkcol] [int] IDENTITY(1,1) NOT NULL,
       [col1] [int] NULL,
PRIMARY KEY CLUSTERED ([pkcol])
)
GO
--create test user login
CREATE LOGIN [User1] WITH PASSWORD=N'p@55w0rd'
GO
--create user in test database
CREATE USER [User1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA=[Developer_Schema]
GO
--create role
CREATE ROLE [Developer_Role] AUTHORIZATION [dbo]
GO
--create schema
CREATE SCHEMA [Developer_Schema] AUTHORIZATION [User1]
GO
--apply permissions to schemas
GRANT ALTER ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT CONTROL ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT SELECT ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT DELETE ON SCHEMA::[dbo] TO [Developer_Role]
GO
GRANT INSERT ON SCHEMA::[dbo] TO [Developer_Role]
GO
GRANT SELECT ON SCHEMA::[dbo] TO [Developer_Role]
GO
GRANT UPDATE ON SCHEMA::[dbo] TO [Developer_Role]
GO
GRANT REFERENCES ON SCHEMA::[dbo] TO [Developer_Role]
GO
--ensure role membership is correct
EXEC sp_addrolemember N'Developer_Role ', N'User1'
GO
--allow users to create tables in Developer_Schema
GRANT CREATE TABLE TO [Developer_Role]
GO
--Allow user to connect to database
GRANT CONNECT TO [User1]

So, in this scenario, the application database tables are protected from changes while the application can be enhanced by implementing other database objects such as stored procedures, views, and functions.

Note: This object protection does not apply to users who are members of the sysadmin server role. A member of the sysadmin server role has permission to undertake any activity within a SQL Server instance.

How Permissions Are Checked

A secured object has a four-part name (fully qualified name): server.database.schema.object. The object will also have permissions applied to it. Thus, the following checking is done on the object:

• Access is granted or denied directly on the object.
• Access is granted or denied on the schema containing the object.
• Access is granted or denied on the database containing the schema.
• Access is granted or denied on the server containing the database.

During the checks, DENY permissions are checked first and access is denied if it exists at any of the check levels above. Similarly, if no specific permission exists, access is denied.

Access is always granted to SA and to members of the sysadmin group.

When multiple objects are accessed sequentially, such as in a stored procedure or a view, that is known as chaining. Chaining allows the improvement in performance of checking object permissions. As a first step in the checking process, the object owner (schema) of the called object is compared to the owner of the calling object. If the owners are the same, the permissions of the called object are not checked. A chain of objects with the same owner is called an unbroken ownership chain.

In the scenario described above, views and stored procedures will normally access objects that are owned by different schemas. In this situation, the owner of the called object is compared with the owner of the calling object. If the owners are different, the full set of permissions is checked on the called object. This is referred to as a broken ownership chain.

How to Refer to Objects

It is always good practice to refer to database objects by a schema name and the object name, separated by a period (.). For a complete example, to SELECT records from the Employee table in the HumanResources schema of the current database would look like:

SELECT * FROM HumanResources.Employee

To reference an object located in a remote database, the fully qualified object name includes the server name and the database name. For example, to SELECT records from the Employee table in the HumanResources schema in the AdventureWorks database on MyServer would look like:

SELECT * FROM MyServer.AdventureWorks.HumanResources.Employee

In the scenario in the previous section, creating the base tables in the dbo schema will enable table objects to be referred to without an explicit schema name. This is because an object will be located by searching the default schema first, followed by the dbo schema. So:

SELECT * FROM Table1

Will assess the following statement first:

SELECT * FROM <defaultschema>.Table1

If it cannot find the object, the server will assess the following statement:

SELECT * FROM dbo.Table1.

The assessment process can be improved by using either the fully qualified name or the DEFAULT_SCHEMA option described earlier. By setting a value for DEFAULT_SCHEMA for the user, the server will check the DEFAULT_SCHEMA first, removing an unnecessary ownership checking process. This can improve performance considerably on heavily utilized systems.

For more information about the DEFAULT_SCHEMA and the performance considerations when not using fully qualified object names, see Upgrading to SQL Server 2005 and default schema setting.

Synonyms

A synonym is a database object that allows a user to create another name for some database objects. A synonym provides two advantages:

• It simplifies the process of naming a fully qualified database object by using an alternative, usually simpler, name.
• It abstracts the implementation of tables and schemas.

Using synonyms, the DBA can hide the schema name or the fully qualified object name from database consumers. This simplifies the implementation of schemas considerably—especially for those new to the concept of schemas. It also allows objects to be moved between schemas without impacting the application, by using the synonyms.

Synonyms can be used to bring together database objects managed by multiple schemas. For example, to reduce the complexity of knowing which schema owns the Employee and SalesPerson tables in the AdventureWorks database, synonyms provide an excellent way to abstract this implementation:

USE AdventureWorks
GO
--from the Sales schema
CREATE SYNONYM dbo.SalesPerson FOR Sales.SalesPerson
GO
--from the HumanResources schema
CREATE SYNONYM dbo.Employee FOR HumanResources.Employee
GO
--bring objects from different schemas ‘together’
SELECT * FROM
 dbo.SalesPerson INNER JOIN dbo.Employee
 ON dbo.SalesPerson.SalesPersonID = dbo.Employee.EmployeeID

A synonym belongs to a schema. A synonym can be created for the following objects:

• Table
• View
• Stored procedure
• Function
• CLR table-valued function
• CLR stored procedure
• CLS scalar function
• CLR aggregate function
• Extended stored procedure

A synonym is created using the following syntax:

CREATE SYNONYM NewSyn FOR Mydb.dbo.table1

A synonym can be consumed in the following statements:

• SELECT
• INSERT
• UPDATE
• DELETE
• EXECUTE
• Sub-selects

For example, the synonym created above can be used in the following way:

SELECT * from NewSyn

Object checks, including permissions, are applied to the synonym at run-time, so it is possible to create a synonym before the underlying objects exist. This is useful if you are trying to abstract the database structure.

The following permissions can be applied to the synonym:

• CONTROL
• DELETE
• EXECUTE
• INSERT
• SELECT
• UPDATE
• TAKE OWNERSHIP
• VIEW DEFINITION

Synonym permission checking is in line with the security ownership chaining concepts described above.

Controlling ISV Database Base Object Access

The SecurityTest scenario described in the “Using Schemas in SQL Server” section of this article demonstrates how to use database object schemas to protect base table objects from being altered or removed from the database schema.

ISV database schemas are complex, and in the case of applications such as Siebel and SAP, they are tuned for specific application access paths by using many customized indexes. Ad hoc access or alteration to the underlying base tables of these applications can severely impact performance of queries and the application itself.

Schemas can be used as a method to group the base tables of the application together. Developers can use an alternative schema to create custom objects, thus ensuring the integrity of the application base tables. While ISV applications do not usually implement schemas as a means to prevent user access, this approach provides an extra level of protection for the application base tables.

Administrators can take advantage of the functionality of schemas to provide the following database protection:

• Host the database objects in a schema protected by appropriate permissions to prevent the database objects from being dropped or altered. Using schemas in this way reduces the security administration required on the base objects.
• Prevent any direct access to tables from ad hoc queries by setting permissions on the schema to disallow SELECT access. Unmanaged SELECT statements on tables with many millions of rows are common causes of application performance issues in these types of applications. A solution to this issue may be to provide users with access to a schema containing stored procedures and so on that allow access to the data in an optimized approach to data access.

Managing Logical Entities in One Physical Database

Schemas provide the opportunity to simplify administration of security, backup/restore, and database management by allowing database objects, or entities, to be logically grouped together. This approach is particularly effective if those logical entities must remain synchronized in some way when consumed by an application. Using AdventureWorks as an example, there are conceptually five logical entities: HumanResources, Person, Production, Purchasing, and Sales. These entities could be stored as five separate physical databases or, using schemas, be combined as five logical entities in the one physical database. This reduces the administrative complexity of managing five databases in preference to a single database. Schemas help to manage the logical entity security separately from each other but still allow objects to work together where required.

Conclusion

The following best practices should be followed when you work with schemas in SQL Server 2005 and SQL Server 2008:

• Always refer to the objects using a fully qualified name. At the very least, use the schema name followed by the object name, separated by a period (.).
• Simplify the implementation of schemas in a database by using synonyms to abstract the schema ownership of objects.
• Use schemas to protect the base database object from being altered or removed from the database schema by users without sysadmin permissions. User permissions should be managed at the schema level.
• Use schemas to combine related, logical entities into one physical database to reduce administration overhead.

For More Information:

http://www.microsoft.com/sqlserver/: SQL Server Web site

http://technet.microsoft.com/en-us/sqlserver/: SQL Server TechCenter

http://msdn.microsoft.com/en-us/sqlserver/: SQL Server DevCenter 

Did this paper help you? Please give us your feedback. Tell us on a scale of 1 (poor) to 5 (excellent), how would you rate this paper and why have you given it this rating? For example:

• Are you rating it high due to having good examples, excellent screen shots, clear writing, or another reason?
• Are you rating it low due to poor examples, fuzzy screen shots, or unclear writing?

This feedback will help us improve the quality of white papers we release.

Send feedback.