Assign Minimal Permissions

Users should be permitted to perform just those operations required within the context of an application and nothing more. Restricting user permissions in this manner limits the potential for inappropriate data access and database actions.

With this in mind, the fixed server- and database-level roles are seldom appropriate for application users as membership in these roles (generally) permits broads set of actions. Instead, permissions should be applied directly to individual users or through membership in custom database roles to which a tailored set of permissions has been applied.

To limit permissions further still, users can be provided access to modules, e.g. stored procedures and functions, which access other database objects on their behalf.  Outside these modules, users do not necessarily have permission to access these objects directly. This pattern, accomplished through ownership chains, context switching, and module signing, provides for very carefully managed object access.

Ownership Chains

An ownership chain exists when an object such as a stored procedure or function references another object and those objects have the same owner.  Ownership chains can be of variable length and are intact where the ownership of the objects within the chain is continuous.

NOTE The concept of ownership changed with SQL Server 2005. In most database implementations, two objects have the same owner if they belong to the same schema while differing schemas and the objects they contain may have the same or differing owners. To learn more about ownership in SQL Server, please review the documentation associated with the ALTER AUTHORIZATION statement.

When a user is granted rights to engage an object at the top of the ownership chain, permissions on subsequently referenced objects are not checked so long as the chain remains unbroken.  For this reason, a user can be granted execute permissions on a stored procedure (at the top of the chain) that accesses data in a table with the same owner as the stored procedure but to which the user does not otherwise have access.

DEMONSTRATION To review a demonstration of ownership chains, check out this post.

Context Switching

In situations where an ownership chain is broken or otherwise a module needs to be executed under the permissions of a different user, the EXECUTE AS clause could be used.  Within functions and stored procedures, the EXECUTE AS clause supports the following qualifiers:

• CALLER – causes the module to execute under the context of the executing user
• SELF – causes the module to execute under the context of the user who created or last altered the module
• OWNER – causes the module to execute under the context of the module owner
• 'user name' – causes the module to execute under the context of the identified user

It’s important to note that IMPERSONATE permissions are required to create or alter a module specifying the EXECUTE AS SELF, EXECUTE AS OWNER, or EXECUTE AS 'user name' variants. 
If the user name specified in the EXECUTE AS 'user name' variant has implicit access to the database through a Windows group, CONTROL permissions on the database are also required.

DEMONSTRATION To review a demonstration of context switching, check out this post.

Module Signing

In situations where an ownership chain is broken or otherwise the module needs to be executed under permissions associated explicitly with the module, module signing can be employed.

Module signing is performed in four steps:

1. A certificate or asymmetric key is generated within the database,
2. A user is created from the certificate or asymmetric key,
3. Permissions are assigned to the user created in the previous step, and
4. The module, e.g. stored procedure or function, is signed using the ADD SIGNATURE statement.

To sign a module, ALTER permissions on the module are required as well as CONTROL permissions on the associated certificate or asymmetric key.

DEMONSTRATION To review a demonstration of module signing, check out this post.

A Note of Caution Regarding Context Switching & Module Signing

Context switching and module signing are great tools for providing controlled access to objects but database administrators should carefully review any scripts handed to them from developers to insure permissions are not being elevated through these mechanisms.  While developers do not typically have permission in production environments to alter objects let alone implement module signing or context switching, the DBAs to which they hand scripts (as part of a formal change control process) do.

A simple review of script code can identify EXECUTE AS clauses (and statements) and ADD SIGNATURE statements.  Once identified, the DBA should work with the appropriate developer(s) to thoroughly understand the permissions being assigned.