Sql server Technical Article

Authorization

In addition to the enhanced protection for user data, structural information and metadata about a particular securable is now available only to principals that have permission to access the securable.

Furthermore, it is possible to create custom permission sets using a mechanism that allows one to define the security context under which stored procedures can run.

In addition, SQL Agent uses a flexible proxy scheme to allow job steps to run and access required resources. All these features make SQL Server more complex but far more secure.

Granular permissions

The set of fixed server and database roles is largely unchanged from SQL Server 2000, so you can still take advantage of those predefined bundles of permissions when users or applications require all or most of the defined permissions. Probably the biggest change is the addition of a public server role. But the principle of least privilege mandates that you not use a role that isn't a perfect fit for what the principal needs to do a job. Although it requires more work to discover and assign the permissions required for a principal, it can result in a far more secure database environment.

Principals and securables

• 
  Windows Domain login
  
• 
  Windows Local login
  
• 
  Windows group
  

• 
  SQL Server login
  
• 
  SQL Server login mapped to a Windows login
  
• 
  SQL Server login mapped to a certificate
  
• 
  SQL Server login mapped to an asymmetric key
  

• 
  Database user
  
• 
  Database user mapped to SQL Server login
  
• 
  Database user mapped to a Windows login
  
• 
  Database user mapped to a certificate
  
• 
  Database user mapped to an asymmetric key
  
• 
  Database role
  
• 
  Application role
  
• 
  Public role
  

The other part of authorization is the objects that can be secured through the granting or denying of permissions. Figure 4 lists the hierarchy of securable objects in SQL Server 2005. At the server level, you can secure network endpoints to control the communication channels into and out of the server, as well as databases, bindings, and roles and logins. At the database and schema level, virtually every object you can create is securable, including those that reside within a schema.

Figure 4   Securable objects hierarchy in SQL Server 2005

Roles and permissions

SELECT * FROM sys.fn_builtin_permissions(default)

Here are the new permission types in SQL Server 2005:

• 
  CONTROL. Confers owner-like permissions that effectively grant all defined permissions to the object and all objects in its scope, including the ability to grant other grantees any permissions. CONTROL SERVER grants the equivalent of sysadmin privileges.
  
• 
  ALTER. Confers permission to alter any of the properties of the securable objects except to change ownership. Inherently confers permissions to ALTER, CREATE, or DROP securable objects within the same scope. For example, granting ALTER permissions on a database includes permission to change its tables.
  
• 
  ALTER ANY . Confers permission to change any securable object of the type specified. For example, granting ALTER ANY ASSEMBLY allows changing any .NET assembly in the database, while at the server level granting ALTER ANY LOGIN lets the user change any login on that server.
  
• 
  IMPERSONATE ON . Confers permission to impersonate the specified user or login. As you'll see later in this article, this permission is necessary to switch execution contexts for stored procedures. You also need this permission when doing impersonating in a batch.
  
• 
  TAKE OWNERSHIP. Confers the permission to the grantee to take ownership of the securable, using the ALTER AUTHORIZATION statement.
  

Like earlier versions of SQL Server, activating an application role suspends other permissions for the duration that the role is active. However, new in SQL Server 2005, is the ability to unset an application role. Another difference between SQL Server 2000 and 2005 is that when activating an application role, the role also suspends any server privilege, including public. For example, if VIEW ANY DEFINITION is granted to public, the application role won’t honor it. This is most noticeable when accessing server-level metadata under an application role context.

Granting a particular permission can convey the rights of other permissions by implication. The ALTER permission on a schema, for example, "covers" more granular and lower-level permissions that are "implied." Figure 5 displays the implied permissions for ALTER SCHEMA. SQL Server Books Online (http://msdn2.microsoft.com/en-us/library/ms130214.aspx) contains the Transact-SQL code for an ImplyingPermissions user-defined function that assembles the hierarchy list from the sys.fn_builtin_permissions catalog view and identifies the depth of each permission in the hierarchy. After adding ImplyingPermissions to the master database I executed this statement to produce Figure 5, passing in the object and permission type:

SELECT * FROM master.dbo.ImplyingPermissions('schema', 'alter')

ORDER BY height, rank

This is a great way to explore the permissions hierarchy in SQL Server 2005.

Figure 5   Hierarchy of implied permissions of ALTER SCHEMA

When you consider the number and types of principals available, the number of securable objects in the server and a typical database, and the sheer number of available permissions and the covered and implied permissions, it quickly becomes clear just how granular permissions can be in SQL Server 2005. Creating a database now requires a much more detailed analysis of its security needs and careful control of permissions on all objects. But this analysis is well worth it and using the new capabilities in SQL Server 2005 results in more secure databases.

Metadata security

SQL Server 2005 examines the permissions a principal has within the database and reveals the metadata of an object only if the principal is the owner or has some permission on the object. There is also a VIEW DEFINITION permission that can grant permission to view metadata information even without other permissions in the object.

This protection extends to the error messages returned from operations to access or update an object that the user has no access to. Rather than acknowledging that there is indeed a table named Address and give an attacker confirmation that she is on track, SQL Server returns an error message with alternate possibilities. For example, if a user with no permissions on any object in the database attempts to drop the Address table, the following error message is displayed:

Msg 3701, Level 14, State 20, Line 1

Cannot drop the table 'Address', because it does not exist or you do not have permission.

This way, an attacker gets no confirmation that an Address table actually exists. But someone debugging this problem still only has a limited number of possibilities to explore.

SQL Agent proxies

This provides a granular means of following the principle of least privilege: granting a job step the permissions it needs and no more. You can create as many proxies as you wish, associating each of them with one or more SQL Agent subsystems. This is in stark contrast to the all-powerful proxy account in SQL Server 2000 which let the user create job steps in any of the SQL Agent subsystems.

Figure 6 shows the Object Explorer in Management Studio with a list of subsystems available in SQL Agent. Each subsystem can have one or more proxies associated with it that grant the appropriate permissions for a job step. The one exception to this scheme is that Transact-SQL subsystems execute with the permissions of the module owner as they did in SQL Server 2000.

Figure 6   SQL Agent subsystems you can associate with proxies

Upon a fresh installation of SQL Server, only the System Administrator role has permissions to maintain SQL Agent jobs, and the management pane in the Management Studio Object Explorer is only available to sysadmins. SQL Server 2005 makes available a few other roles you can use to grant various levels of permissions. You can assign users to the SQLAgentUser, SQLAgentReaderRole, or SQLAgentOperator roles, each of which grants increasing levels of permission to create, manage, and run jobs, or the MaintenanceUser role, which has all the permissions of SQLAgentUser plus the ability to create maintenance plans.

Members of the sysadmin role, of course, can do anything they want in any of the subsystems. To grant any other user rights to use subsystems requires the creation of at least one proxy account, which can grant rights to one or more subsystems. Figure 7 shows how a proxy account, MyProxy, is assigned to multiple principals, here a user and a role. The proxy account uses a credential, which links it to an account, usually a domain account, with permissions in the operating system necessary to perform whatever tasks are required by the subsystem. Each proxy can have one or more subsystems associated with it that grant the principal the ability to run those subsystems.

Figure 7   SQL Agent proxy account for various subsystems

The following code shows the Transact-SQL code necessary to implement the scheme shown in Figure 7. It starts by creating a credential, a database object that provides the link to the operating system account with rights to perform the desired actions in the subsystems. Then it adds a proxy account, MyProxy, that is really just a friendly name for the credential. Next, it assigns the proxy to two principals, here a SQL Server login and a custom role. Finally it associates the proxy with each of the four SQL Agent subsystems.

<sup>CREATE CREDENTIAL MyCredential WITH IDENTITY = 'MyDOMAIN\user1'

GO

msdb..sp_add_proxy @proxy_name = 'MyProxy',

@credential_name = 'MyCredential'

GO

msdb..sp_grant_login_to_proxy @login_name = 'MyLogin',

@proxy_name = 'MyProxy'

GO

msdb..sp_grant_login_to_proxy @login_name = 'MyRole',

@proxy_name = 'MyProxy'

GO

sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy',

@subsystem_name = 'ActiveScripting'

GO

sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy',

@subsystem_name = 'CmdExec'

GO

sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy',

@subsystem_name = 'ANALYSISQUERY'

GO

sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy',

@subsystem_name = 'DTS'

GO</sup>

Management Studio provides full support for creating credentials and proxies as shown in Figure 8. This creates the same proxy as the previous code.

Figure 8   A new SQL Agent proxy in SQL Server Management Studio

A proxy is not a way to circumvent security in the operating system. If the credential used with a proxy doesn't have the permission in Windows, such as to write to a directory across the network, the proxy won't have it either. You can also use a proxy to grant limited execution rights to xp_cmdshell, since it is a favorite tool used by attackers to extend their reach into the network once they compromise a SQL Server computer. The proxy provides this protection because even if the principal has unlimited rights on the network—a domain administrator—any commands executed through the proxy have only the limited rights of the credential account.

Execution context

If the ownership chain was broken because the owner of the code didn't own the referenced object, permissions were checked against the caller's security context. If the caller had permission to access the object, the data was returned. If she didn't, an error would be raised.

Ownership chaining has some limitations—it applies only to data manipulation operations and not to dynamic SQL. Plus, if access were made to objects across ownership boundaries, ownership chaining would not be possible. Hence this upfront permissions checking behavior only worked for certain cases.

SQL Server 2005 introduces the ability to mark modules with an execution context, such that the statements within the module can execute as a particular user as apposed to the calling user. This way, while the calling user still needs permissions to execute the module, the permissions for statements within the module are checked against the execution context that the module was marked with. This behavior can be used to overcome some of the shortcomings of ownership chaining because it applies to all statements within the module. Administrators wanting to perform upfront permission checking can use the execution context to do that.

Now when you define user-defined functions (except inline table-valued), stored procedures, and triggers you can use the EXECUTE AS clause to specify which user's permissions SQL Server uses to validate access to objects and data referenced by the procedure:

CREATE PROCEDURE GetData(@Table varchar(40))

WITH EXECUTE AS 'User1'

SQL Server 2005 provides four EXECUTE AS options.

• 
  EXECUTE AS CALLER specifies that the code is executed in the security context of the caller of the module; no impersonation occurs. The caller must have access permissions on all of the objects referenced. But SQL Server only checks permissions for broken ownership chains, so if the owner of the code also owns the underlying objects, only the module's execute permission is checked. This is the default execution context for backward compatibility.
  
• 
  EXECUTE AS 'user_name' specifies that the code executes in the security context of the specified user. This is a great option if you don't want to rely on ownership chaining. Instead, you create a user with the necessary permissions to run the code and create custom permission sets.
  
• 
  EXECUTE AS SELF is a shortcut notation for specifying the security context of the user who is creating or altering the module. SQL Server internally saves the actual user name associated with the module rather than "SELF."
  
• 
  EXECUTE AS OWNER specifies that the security context is that of the current owner of the module at the time of module execution. If no owner is specified, the context of the containing schema's owner is used. This is a great option when you want to be able to change the module's owner without changing the module itself.
  

User/schema separation

How schemas worked in SQL Server 2000 is shown in the top portion of Figure 9. When an administrator created a user Alice in a database, SQL Server would automatically create a schema Alice that hid behind Alice the user. If Alice logged on to a server running SQL Server without database ownership and created Table1, the actual name of the table was Alice.Table1. The same held for other objects Alice created, such as Alice.StoredProcedure1 and Alice.View1. If Alice were a database owner or a sysadmin, the objects she created would be part of the dbo schema instead. Although we used to say that dbo owned the objects, it amounts to the same thing.

Figure 9   User/schema/objects in SQL Server 2000 and 2005

The problem with the unification of users and schemas in SQL Server 2000 arises when you need to change the ownership of objects, such as when Alice leaves the company and Lucinda takes over Alice's job. A system administrator would have to change ownership of all of the objects owned by Alice to Lucinda. More of a problem is that any Transact-SQL or client application code that referred to Alice.Table1 would have to be changed to Lucinda.Table1 after Lucinda took ownership of the table. Depending on the number of objects Alice owns and how many applications had the name embedded in them, this could be a major undertaking. Microsoft has long recommended that all database objects be owned by the built-in dbo to get around these problems. It was far easier to change a database's ownership than to change many objects and client applications.

Note   Don't be confused by the SQL Server 2000 CREATE SCHEMA statement. This was just an easy way to create tables and views owned by a particular user and to grant permissions. You could use the statement to name a schema's owner but not name the schema. The owner was still irrevocably linked to the schema with all the problems of changing ownership.

SQL Server 2005 cleans this up and implements the SQL-99 schema by separating the user from the schema as shown in the bottom part of Figure 9. When you create a new user Alice using the new CREATE USER DDL, SQL Server no longer automatically creates a schema with the same name. Instead you must explicitly create a schema and assign ownership of it to a user. Because all of the database objects shown are now contained in the Schema1 schema, which Alice initially owns, it becomes simple to change ownership of all the schema's objects by simply changing the ownership of the schema to Lucinda. Each user can also have a default schema assigned to it, so that any objects referenced by name without the schema reference are assumed to be in the default schema. In the bottom part of Figure 9, if Alice has Schema1 as her default schema, she can refer to the table as either Schema1.Table1 or simply as Table1. User Carol, who perhaps does not have a default schema associated with her user name, would have to refer to the table as Schema1.Table1. Any user without a default schema defined has dbo as the default.

Fully qualified object names in SQL Server 2005 have a four-part structure, similar to those in earlier versions of SQL Server:

server.database.schema.object

As in earlier versions, you can omit the server name if the object is on the same server as that where the code is running. You can omit the database name if the connection has the same database open, and you can omit the schema name if it is either the default schema for the current user or is owned by dbo, since that is the schema of last resort as SQL Server tries to disambiguate an object name.

Use the CREATE USER statement, instead of sp_adduser, to create new users. This system stored procedure is still around for backward compatibility and has been changed a bit to conform to the new separation of users from schemas. sp_adduser creates a schema with the same name as the new user name or the application role and assigns the schema as the default schema for the user, mimicking SQL Server 2000 behavior but providing a separate schema.

• 
  Use OBJECTPROPERTY(id, 'OwnerId') to discover the true owner of an object.
  
• 
  Use TYPEPROPERTY(type,'OwnerId') to discover the true owner of a type.
  

USE AdventureWorks

GO

CREATE SYNONYM History FOR HumanResources.EmployeeDepartmentHistory

The History synonym could also have been defined for the complete, four-part name as in the following code:

CREATE SYNONYM History

FOR MyServer.AdventureWorks.HumanResources.EmployeeDepartmentHistory

Using the full, four-part name like this allows the use of the synonym from another database context, assuming the current user has permissions to use the synonym and read the table:

USE pubs

Note too that if you don’t provide a schema name as part of the new synonym name, it will be part of the default schema.