After authentication, it's time to think about what an authenticated login can do. In this area, SQL Server 2005 is more flexible than earlier versions. Permissions are now far more granular so that you can grant the specific permissions required rather than grant membership in a fixed role that probably carries with it more permissions than are necessary. You now have far more entities—securables—to which you can assign more granular permissions.
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.
One of the many ways that SQL Server 2005 is far more secure than earlier versions is the improved granularity of permissions. Previously, an administrator had to grant a user membership in a fixed server role or fixed database role to perform specific operations, but more often than not, those roles had permissions far too broad for simple tasks. The principle of least privilege requires that a user have only the minimum permissions to do a job, so assigning users to a broad role for narrow purposes violates this principle.
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
In SQL Server 2005 a principal is any individual, group, or process that can request access to a protected resource and be granted permission to access it. As in previous versions of SQL Server, a principal can be defined in Windows or can be based on a SQL Server login with no corresponding Windows principal. The following list shows the hierarchy of SQL Server 2005 principals—excluding the fixed server and database roles—and how logins and database users can be mapped to security objects. The scope of the influence of the principal depends on the scope of its definition, so that a Windows-level principal is more encompassing than a SQL Server-level principal, which is more encompassing than a database-level principal. Every database user automatically belongs to the fixed public role.
Windows Domain login
Windows Local login
SQL Server-level principals
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 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
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
For a sense of the number of permissions available in SQL Server you can invoke the fn_builtin_permissions system function:
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.
SQL Server 2005 still uses the familiar GRANT, DENY, and REVOKE scheme for assigning or refusing permissions on a securable object to a principal. The GRANT statement is expanded to cover all of the new permission options, such as the scope of the grant and whether the principal is able to grant the permission to other principals. Cross-database permissions are not allowed. To grant such permissions, you create a duplicate user in each database and separately assign each database's user the permission.
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.
Note The new, preferred alternative to application roles is to use execution context in code modules. For more information, see Execution Context in this paper.
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.
One benefit of the newly granular permission scheme is that metadata as well as data is now protected. In earlier versions of SQL Server, a user with any access to a database could see the metadata of all objects within the database, whether or not the user could access the data within it or execute a stored procedure.
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.
One of the best examples of the new and improved authorization model in SQL Server 2005 is SQL Agent. You can define various credentials often associated with Windows logins—linked to users with the necessary permissions to perform one or more SQL Agent steps. An SQL Agent proxy then links a credential with a job step to provide the necessary permissions.
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.
Note When you upgrade a server from SQL Server 2000, a single proxy account is created and all subsystems are assigned to that single proxy account so that existing jobs will continue to run. After upgrading, to create credentials and proxy accounts to implement a more secure, granular set of proxies to protect server resources.
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.
CREATE CREDENTIAL MyCredential WITH IDENTITY = 'MyDOMAIN\user1'
msdb..sp_add_proxy @proxy_name = 'MyProxy',
@credential_name = 'MyCredential'
msdb..sp_grant_login_to_proxy @login_name = 'MyLogin',
@proxy_name = 'MyProxy'
msdb..sp_grant_login_to_proxy @login_name = 'MyRole',
@proxy_name = 'MyProxy'
sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy',
@subsystem_name = 'ActiveScripting'
sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy',
@subsystem_name = 'CmdExec'
sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy',
@subsystem_name = 'ANALYSISQUERY'
sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy',
@subsystem_name = 'DTS'
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.
SQL Server has long supported the concept of ownership chaining as a way of ensuring that administrators and application developers have a way to check permissions upfront on the entry points to the database rather than being required to provision permissions on all objects accessed. As long as the user calling the module (stored procedure, function) or view had execute permissions on the module (or select permissions on the view) and the owner of the module (or view) was the owner of the objects accessed (an ownership chain), no permissions were checked on the underlying objects, and the caller received the data requested.
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.
Any time the user context changes using the EXECUTE AS option, the creator or alterer of the module must have IMPERSONATE permissions for the specified user. You cannot drop the specified user from the database until the execution context of all modules has been changed to other users.
SQL Server 2000 had no concept of a schema, which the ANSI SQL-99 specification defines as a collection of database objects owned by a single principal that forms a single namespace of objects. A schema is a container for database objects such as tables, views, stored procedures, functions, types, and triggers. It functions much as a namespace functions in the .NET Framework and XML, a way to group objects so that a database can reuse object names—such as allowing both dbo.Customer and Fred.Customer to exist in a single database—and to group objects under different owners.
Note You’ll need to switch to new catalog views like sys.database_sys.principals, sys.schemas, sys.objects, etc. The reason is that the old sysobjects system table did not “know” about schemas, and so was incapable of supporting U/S separation. Besides, the old catalog views are deprecated, so they’ll be dropped in a future version of SQL Server.
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:
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.
Note When using the ALTER AUTHORIZATION statement, it is possible to arrive in a state where YOU own a table in MY schema (or vice versa). This has some serious implications. For example, who owns the trigger on that table: me or you? The bottom line is that it can now be very tricky to discover the true owner of a schema-scoped object or type. There are two ways to get around this:
Use OBJECTPROPERTY(id, 'OwnerId') to discover the true owner of an object.
Use TYPEPROPERTY(type,'OwnerId') to discover the true owner of a type.
SQL Server 2005 can help save keystrokes with synonyms. You can create a synonym for any object using the two-, three-, or four-part full object name. SQL Server uses the synonym to access the defined object. In the following code, the History synonym represents the specified schema.table in the AdventureWorks database. The SELECT statement returns the contents of the EmployeeDepartmentHistory table.
CREATE SYNONYM History FOR HumanResources.EmployeeDepartmentHistory
SELECT * FROM History
Note The administrator or owner must grant permission on the synonym if someone else is to use it. GRANT SELECT on a synonym to a view or table or table-valued function. GRANT EXECUTE on a synonym to a procedure or scalar function, etc.
The History synonym could also have been defined for the complete, four-part name as in the following code:
CREATE SYNONYM History
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:
SELECT * FROM AdventureWorks..History
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.