SQL Server

SQL Server security – (Part 7)

by Basit Aalishan Masood-Al-Farooq

SQL Server security – (Part 7)

Author: Basit A. Farooq

In this part, you’ll first learn how SQL Server manages ownership chaining and learned how to change an object’s owner. You changed the security context for a session. Then, you’ll learn how to manage security for CLR assemblies. Finally, you’ll learn when to use SAFE, EXTERNAL_ACCESS, and UNSAFE permissions sets.

Ownership chaining

Schemas have security implications. When one object accesses another object, it forms a chain, as shown in following figure shows ownership chaining example.

For example, suppose you have a function named Accounts.fn_GetSalary that accesses the Accounts.Salaries table. Because they’re both owned by Accounts, SQL Server checks permissions only on the Accounts.fn_GetSalary function. If the user has the EXECUTE permission on the Accounts.fn_GetSalary function, the function can access any objects the same owner.

On the other hand, if the Accounts.ufn_PayrollInfo function, which is owned by Accounts, tries to access the HumanResources.Employees table, which is owned by HumanResources, SQL Server checks to ensure that the user has permission to access both. When objects have different owners, as described here, the condition is referred to as a broken ownership chain.

By default, ownership is assigned automatically when you create objects based on the containing schema. When you create an object in a schema, the object is assigned the schema’s owner as its default owner.

Cross database ownership chaining

By default, ownership chaining is valid only within a specific database. However, you can enable cross-database ownership chaining for a single database by using the ALTER DATABASE statement and setting the DB_CHAINING option ON.

You can also enable cross-database ownership chaining for the entire server by checking cross database ownership chaining on the Security tab of Server Properties or by executing the sp_configure system stored procedure as follows:

— To allow advanced options to be changed.

EXEC sp_configure ‘show advanced options’, 1;

GO

— To update the currently configured value for advanced options.

RECONFIGURE;

GO

— To enable the feature.

EXEC sp_configure ‘cross db ownership chaining’, 1;

GO

— To update the currently configured value for this feature.

RECONFIGURE;

GO

It is generally not a good security practice to enable cross-database ownership chaining across all databases on a server instance. Cross-database ownership chaining makes it easy to assign permissions inadvertently to other databases without realizing that you have done so, for example. Microsoft strongly discourages enabling this option.

Object ownership

By default, objects in the schema container have the same owner as the schema. However, you can transfer the ownership to any database principal using the ALTER AUTHORIZATION statement. The ALTER AUTHORIZATION statement has the following syntax:

ALTER AUTHORIZATION

ON [<entity_type>::entity_name

TO {SCHEMA OWNER | principal_name}

For example, to change the ownership of the HumanResources.Employees table to Accounts, you execute the ALTER AUTHORIZATION statement as follows:

ALTER AUTHORIZATION

ON Object::Employees TO Accounts

You cannot change the ownership of following:

• Triggers
• Constraints
• Rules
• Defaults
• Statistics
• System objects
• Queues
• Indexed views
• Tables with indexed views

You must have the TAKE OWNERSHIP permission on the object you are changing. If you are assigning ownership to another user, you must have IMPERSONATE permission on that user. If you’re assigning ownership to a role, you need ALTER permission on the role.

Execution context

Tokens provide the security context and are used to authorize access attempts during the duration of the connection unless the security context is switched. When a user logs in to SQL Server, the user’s connection has the following tokens:

• Login token – Contains the login information used to check server-level permissions.
• User token – Contains the database user information for a specific database. A connection might have one or more user tokens.

Changing the login token

You can change the security context by using the EXECUTE AS statement. You must have IMPERSONATE permission on the login identified as login_name. You can change the login token for the session by using the following statement:

EXECUTE AS LOGIN = ‘login_name‘

When you change the login context, you can perform actions that require server-level permissions not granted to the account you used to log in—assuming the user you impersonate has the necessary permissions.

One use of this is a system administrator who has an account with standard permissions and one that belongs to the sysadmin role. The user can connect using the standard account but change the context when necessary by impersonating the higher-privileged account. This enables the user to avoid logging in as an administrator account and can minimize the number of login accounts you must manage. Rather than having both a standard and administrator account, you can assign a database administrator a standard account only for login. When administrator permissions are needed, the user runs the EXECUTE AS command.

Changing the user token

To change the user token to different database user, you execute the following:

EXECUTE AS USER = ‘user_name’

You must have IMPERSONATE permission on the database user. The justifications for impersonating a database user are the same as for impersonating server logins.

Using EXECUTE AS in a programmable object

You can use the EXECUTE AS clause within a CREATE FUNCTION, CREATE TRIGGER, or CREATE PROCEDURE statement to execute an object using a specific security context. When used in this manner, EXECUTE AS supports the following security contexts:

• CALLER – Executes in the context of the user who called the object.
• SELF – Executes in the context of the user who created the function, trigger, or stored procedure.
• OWNER – Executes in the context of the module owner. You cannot use this option when creating DDL triggers or if the module is owned by a role or group.
• ‘username’ – Executes in the context of a specific database user. When using the clause on a DDL trigger with server scope, you need to use ‘loginname’ instead.

Reverting to the previous original context

You can use the REVERT statement to revert the security context to the previous context. This returns the session security context to the original login and user, rather than leaving an open session with administrative (or other high-level) permissions.

CLR security

You can build CLR user-defined functions in languages such as Visual Basic .NET and C#.NET. When you use them, you should apply special security precautions. In this section, you’ll learn how to configure the appropriate access level for CLR user-defined functions.

CLR security overview

A CLR user-defined function is stored in a dynamic link library from which you have created an assembly. The assembly is a SQL Server object, and SQL Server checks permissions before executing it. A principal needs the EXECUTE permission on an assembly to run it.

A CLR assembly is subject to code access security restrictions. Administrators can use code access security to control the operations code components can perform on a computer.

Code access security

Code access security is implemented in tiers, as shown in following figure. Managed code (developed using .NET Framework languages) requests a specific level of access.

Before execution, the command processor checks security policy in the following order:

• Host policy
• User policy
• Machine policy

They must all allow the managed code the requested level of access for the code to load and execute.

You define the host policy when you create the assembly. An administrator sets user policy and machine policy using Software Restriction Policy in Windows Group Policy. The user policy applied is determined by the SQL Server service account.

Host policy permission sets

When you create the assembly using the CREATE ASSEMBLY statement, you select one of three permission sets. If you don’t specify a permission set, SQL Server uses the SAFE permission set. The permission sets are described in the following table:

Permission set	When to use
SAFE	Almost always, unless the component requires access, not supported by SAFE.
EXTERNAL_ACCESS	Component requires access to the file system, network, registry, environmental variables. Calling a Web service counts as network access.
UNSAFE	Component calls unmanaged code. Also allows component to access the file system, network, registry, and environmental variables.

Continue to Part-8…