SQL Server 2005 is much more secure than earlier versions. The whole security architecture has been dramatically changed and many new features added. For example, permissions are more granular, more objects are securable, more sophisticated encryption has been added, SQL Server logins are as secure as Windows logins, and new schema objects have replaced object owners, but we will have a different focus in this chapter. I will describe the security architecture of the data layer and not cover all possible security details of SQL Server. The chapter starts with an introduction to the security architecture of SQL Server. It continues with an overview of statements and system stored procedures for implementing and managing security. Finally, it provides a few typical security architectures for custom applications.
Implementing security on SQL Server is not difficult, but you need to have a good understanding of its security architecture before you can define and implement an effective and manageable security solution. Let's start by describing basic concepts and terminology.
Authentication is the process of identifying a user or process. Authorization is the process of granting an authenticated user or process specific rights to access or modify resources.
Principals are users, groups, and processes that can ask for access to SQL Server resources. Principals have several common characteristics. Each principal has its own security identification number (SID). Principals could be sets of principals (such as Windows groups or database roles) or indivisible principals (such as local or domain logins). Each principal has a scope, and scopes are based on the level at which the principal is defined (see Table 19-1).
In the following sections, I will try to make these abstract principals easier to envision by referring often to them as "users". Just remember that a user, in this sense, can represent any of the principals shown in Table 19-1.
Windows–level Principals | SQL Server–level Principals | Database–level Principals |
---|---|---|
Local logins | Native SQL Server login | Database role |
Domain logins | SQL Server login linked with Windows login or group | Application role |
Local groups | SQL Server login linked with asymmetric key | Database user |
Domain groups | SQL Server login linked with certificate | Database user linked with Windows user or group Database user linked with asymmetric key Database user linked with certificate |
Securables are server or database objects that can be secured with permissions. They are divided by scope into three groups (see Table 19-2).
Server Scope Securables | Database Scope Securables | Schema Scope Securables |
---|---|---|
Logins | Users | Tables |
A user (a person or application) has to go through four levels of security before performing an action on a database object:
A user needs access to the client computer, operating system, and network on which the server is located. Usually, granting this access is the responsibility of technical support specialists or network administrators. However, in smaller environments, this responsibility may fall to a DBA or a developer instead.
This is the first level of security that pertains strictly to SQL Server. It allows a client to access a server. This security level is always the responsibility of database administrators.
The client has to authenticate itself (verify its identity) to the server before it is allowed to request additional resources. SQL Server supports Windows or SQL Server authentication to verify the identity of the client.
SQL Server authentication is based on logins and passwords that SQL Server stores in its master database. The client must supply them or it will be prompted for a login and password.
Windows authentication is based on Windows (network) domain or local logins. A client's connection to a server is associated with its Windows login. The client will not be prompted for logins and password. A behind-the-scenes process authenticates the client using its Windows login and SID.
Windows authentication is usually considered more secure and easier to maintain, partly because of the infrastructure work that Microsoft has done in Windows, and partly because of the availability of tools on the Internet that can read passwords from SQL Server logins. Windows authentication is easier to manage for both the user (who has to remember only one login and password combination and to type them just once to log in to the network) and the administrator (who can manage all logins and passwords centrally).
Note | Microsoft has made some improvements on SQL Server authentication in SQL Server 2005. For example, a SQL Server 2005 instance can he set to require strong passwords and to require password updates after a specified period of time. However, Microsoft still recommends using Windows authentication, saying that SQL Server authentication should he used only for legacy applications. |
SQL Server can be installed or configured in two authentication modes:
Windows Authentication Mode allows clients to authenticate themselves using only Windows authentication. Before SQL Server 7.0, it was called integrated security.
Mixed Authentication Mode supports both SQL Server and Windows authentication. Some users can log in with their network account while others (who may or may not have a Windows account) can log in using their SQL Server login.
Note | Early versions of SQL Server (before 7.0) could he installed in Standard security/mode, which supported only SQL Server authentication. |
Access to a server does not automatically provide a user with access to a database. An administrator has to assign a database to a login in one of the following manners:
The administrator creates a database user that corresponds to the login in each database to which the user needs access.
The administrator configures a database to treat a login/database user as a member of a database role. Such a user inherits all permissions from the role.
The administrator sets a login to use one of the default user accounts: guest or database owner (dbo).
Once access to a database has been granted, the user can see all database objects because the object definitions are stored in system tables to which every user has read access.
Permissions are the final level of SQL Server security. To have access to securables (resources) on SQL Server, a principal has to have permissions to perform actions on them.
SQL Server 2005 has a new object model with more granular permissions (than earlier versions) that are organized in a hierarchy. You can see a complete list if you execute the sys.fn_builtin_permissions function (see Figure 19-1). The result contains almost 200 individual permissions.
Figure 19-1: List of built-in permissions
Principals can be given Select, Update, Insert, and Delete permissions to schema-contained securables (such as tables and views), to schemas, and to databases. This access level means that the user can read, write, delete, or change data. Reference permission allows a principal to use a foreign key constraint to validate an entry using an object such as a column or a table. Execute permission is given on programmatic objects (such as stored procedures).
View Definition will allow a principal to get scripts for server and database object creation, while Alter permission will allow principals to modify (change, drop, or create) server or database objects. Database users that create objects become their owners (unless they are also already owners of the schema). Take Ownership permission allows other users to acquire them instead. Control includes all permissions (contained in Alter and Take Ownership permissions) and permission to grant others permissions on the object.
Alter Any permission applies to a collection of objects of a particular class. For example, Alter Any Database allows a user to alter all databases on the server.
The key to the table in Figure 19-1 is a combination of permission_name + class_descr (the name of the class of objects that the permission applies to). Some permissions have parents and a combination of fields; covering_permission_ name + class_descr points to them. For example, if a principal (user) has the Control permission over the Database class of objects, that permission also implies all (child) permissions such as View Definition and Alter for all Database class objects. You can see the complete hierarchy if you execute the following common table expression:
WITH Perms(class_desc, covering_permission_name, permission_name, level) AS ( -- anchor member (for first iteration) SELECT class_desc, covering_permission_name, permission_name, 0 level FROM sys.fn_builtin_permissions(default) WHERE covering_permission_name = '' UNION ALL -- recursive member (for later iterations) SELECT o.class_desc, o.covering_permission_name, o.permission_name, p.level + 1 level FROM sys.fn_builtin_permissions(default) o INNER JOIN Perms p ON o.covering_permission_name = p.permission_name and o.class_desc = p.class_desc ) SELECT s.class_desc, s.permission_name, s.covering_permission_name, s.level FROM Perms s INNER JOIN sys.fn_builtin_permissions(default) o ON o.permission_name = s.permission_name and o.class_desc = s.class_desc ORDER BY s.class_desc, s.level;
BOL contains the source code for the dbo.ImplyingPermissions user-defined function, which can be used to reveal covering permissions—permissions that "imply" the specified permission. For example, after you compile the function in the master database, you can use it to get a list of permissions of a higher level that can alter database objects such as stored procedures (see Figure 19-2).
Figure 19-2: Implying permissions
There is one more hierarchy implemented through parent_class_descr + parent_ covering_permission_name. This hierarchy ensures that, if you have permissions over a higher class of objects, you will also have appropriate permissions over their child objects. For example, if you have Control permission over Schema, it implies that you have Control permission over other objects in the schema (such as tables and views).
Principals can be granted permissions individually or as members of a role. Roles are the SQL Server equivalent to groups in a Windows domain.
In early versions of SQL Server, a user could belong to only one role (group). This restriction led to some pretty unrefined security solutions. A user can now be a member of many roles. Instead of managing large groups of users and their permissions, the idea is to grant permissions to a role and then assign users to it. Therefore, roles can be used to provide an efficient and sophisticated security model, managing access to the required securables (functionality and objects).
Server-level principals can become members of server roles to control server-scope securables (or lower). Server roles are fixed—it is not possible to change the permissions granted to them and it is not possible to create new server roles. The set of server roles is
sysadmin
securityadmin
serveradmin
setupadmin
processadmin
diskadmin
dbcreator
bulkadmin
In earlier versions of SQL Server, you could use the following stored procedures to get the list of permissions that is granted to the role:
exec sp_srvrolepermission 'processadmin'
Unfortunately, this stored procedure is not supported in SQL Server 2005. You can find a list of permissions granted to server roles in BOL in the "Permissions of Fixed Server Roles" section.
Database-level principals can become members of fixed database roles to manage database-scoped securables:
db_accessadmin
db_backupoperator
db datareader
db_datawriter db_ddladmin
db_denydatareader db_denydatawriter db_owner
db_securityadmin public
You can get the list of permissions assigned to each of these roles in BOL in the "Permissions of Fixed Database Roles" section.
The public role is special. Every user that is granted access to the database is automatically a member of the public role (and inherits the permissions granted to it).
You can create database roles and assign permissions to database- and schema-scoped securables. One user can be a member of zero, one, or more database roles (fixed or custom).
Microsoft recommends that you create database roles when there is no Windows group that can be used to manage permissions, or when you do not have permission to manage Windows groups. I believe that you should always have database roles. A Windows group or user to which you have assigned appropriate permissions could be deleted or used for something else, and that would require you to reassign individual permissions. If you have assigned them all to a single role (permissions are also grouped this way), it is much simpler to reassign them to the new user or group.
Sometimes you want to force your users to access a database only through a custom application. This scenario is quite typical when you are building business logic into middleware components. If your users (or their roles) have permissions to underlying objects, they can use generic tools such as Management Studio to access them directly. Microsoft has created application roles to prevent users from accessing underlying objects directly.
Application roles are custom roles defined on the database level that do not have members (principals), and that require a password to become active. When a connection string specifies an application role and provides the password, SQL Server disregards all user privileges and assigns the user only the privileges assigned to the application role.
Note | Application roles have a serious limitation in performing cross-database or cross-server operations. They are designed as database-level principals and they cannot have explicit permissions outside of the database in which they are created. An application role will get the permissions assigned to the guest user account (which is typically not much). |
Database objects such as stored procedures, views, and functions can reference other database objects such as tables, as well as other stored procedures, views, and functions. Underlying objects can also reference other database objects. You can envision the structure of objects linked this way as a tree, but SQL Server documentation refers to it (in the context of security) as a chain. SQL Server has to evaluate permissions that users have on each object individually and sequentially (that's why it's called a chain), before it allows the user to access them. This could be very complex and time-consuming, but fortunately there is a shortcut called an ownership chain that SQL Server uses. If all constituent objects belong to the same schema as the object that references them, SQL Server will not evaluate the permissions of the user to access individual (constituent) objects. However, if even one of them does not belong to the schema of the top object (that is, if the chain is broken), SQL Server will evaluate the user's permissions to access all underlying objects.
In SQL Server 2005, database objects are owned by schemas. Before SQL Server 2005, database objects were owned by owners such as dbo, and therefore, these chains were given the name ownership chains.
The main purpose of ownership chains is to simplify security management of database objects. It is typically sufficient to give users access to only top-level objects (such as stored procedures).
Database objects can also reference objects in other databases. In that case, ownership chains span multiple databases and they are called cross-database ownership chains. By default, cross-database ownership chains are not enabled on SQL Server. However, during the installation of the server, you can enable them for all databases on the server. Alternatively, you can enable cross-database ownership chains between specific databases.
Caution | Cross-database ownership chains are potentially dangerous and you should not enable them unless you need them. For example, say that there is a user John in a database Assets that has Select permission on the vlnventory view that belongs to dbo schema. The user also exists in database Finance but he is not given permissions to select data from table dbo.Transactions. If the user can modify the vlnventory view to read data from Finanace.dbo.Transactions, SQL Server will just verify that the schema of the view matches the schemas of all underlying objects and automatically give access to data in the table in the Finance database without checking permissions. |
I have demonstrated in earlier chapters how you can use the Execute As clause of stored procedures, user-defined functions (except inline table-valued UDFs), and triggers to control execution context. Basically, you can define procedures so that they are executed as if they were authenticated as a different user.
The Execute As Caller option is the default that is compatible with the behavior of earlier versions of SQL Server. The user needs to have permissions on the procedure and all objects it references.
The Execute As User = 'user' option is useful when a caller does not have permissions on underlying objects, and when those permissions are required (for example, when a procedure is dynamically assembling a batch, it is not sufficient to rely on permission to the procedure and the compatibility of schemas). The procedure will be executed in the context of 'user' (if he or she has adequate permissions).
The Execute As Self option will execute a procedure using the name of the user that has created or altered the procedure.
The Execute As Owner option specifies that the procedure will be executed as the owner of the object, or if the owner is not defined, in the context of the schema owner. The owner must be a singleton account—it cannot be a group or a role.
The Execute As statement (we described the Execute As clause in the previous section) changes execution context on the level of the session. It is possible to specify a different user or login to be impersonated on the level of a database or server respectively. In this case, permissions will be checked based on the security context of the specified user or login and all permissions of the original login will be ignored.
Note | If a session is set to impersonate a database user, any attempt to access objects outside of the database will fail (even distributed queries and the Use statement). |
Once set, the context of the session will be in effect until it is set again, until connection is dropped, or until the user invokes the Revert statement (which returns the execution context to the original login).