SQL Server Security: The Internal Environment

Let’s now put aside the access control story and work within the DBMS. Once users are inside SQL Server, their objectives are to access the databases (and in the case of administrators, the DBMS). Your objectives as the DBA are to ensure that they access only the data and objects they are allowed to access. You also have to ensure that users do not destroy data, through either mala fide or bona fide actions. The mechanisms you have to secure data, at the object level, are very similar to the security mechanisms out on the file system or in the operating system.

As mentioned earlier, a database is an object that itself consists of objects…in much the same way a cell contains protoplasm and cytoplasm. We refer to this hierarchy of objects as an object chain, as illustrated by Figure 5–11. All objects in the chain inherit ownership from the parent object when they are created on the connection that first created the parent. Subsequent connections that can create objects become the owners of the new objects.

Figure 5–11: The object-ownership chain

What good are permissions if you don’t have users to use them? Figure 5–12 examines the access “tree” that towers above the object and its permissions. At the root of the tree, we have the objects, and the connection to it is via the permissions we have discussed. Roles or users have to go through the permissions to access the objects, as you now know. User objects are split into two entities: They can be individual users derived from several sources, or they can be “roles,” which are groups of individual user accounts. Users can also be given aliases, but well get to that information soon.

Figure 5–12: The Access tree starts at the object and extends up through permissions, users, and roles to the login

At the very top of the tree, you’ll find an entity called the login level, or logins. The login level controls access to the system itself. Access to the database is given by connecting the user account at the lower level with the login at the “server” level. The login, as you know, is associated with either a SQL Server nontrusted login or a security principal (trusted), a domain user account given to an external trusted Windows domain account, or an external nontrusted user that is assigned an internal SQL Server user ID account and password. Logins are managed interactively in Management Studio. They can also be managed programmatically, as is specifically discussed in Appendix.

Users

SQL Server users can be described as follows:

• Built-in users and system roles   When you first create an instance of SQL Server 2005, when you install it from scratch, the first user ID created is the System Administrator, or SA. This is the first user given login access and exists for access before any form of domain or network security exists. This user is created at the big bang of SQL Server 2005, when the instance is created and it needs a “first cause,” known for some time now as “SA.” SA has access to all of SQL Server.

  Several built-in system roles are also added (they are discussed shortly), and both domain and SQL Server users can use these roles. By the way, all members of your domain’s BUILTIN\Administrators group and the server’s local BUILTIN\Administrators group are members of the sysadmin system role, which gives any members of these groups trusted access at the same level as the SA.

• Database users   Database users are the database “connections” your logins access. If the user is nontrusted, the login will require an ID and password pair to match what is stored in the system’s user tables. Database user security is managed on the Users node of a database tree in Management Studio. You can also fully manage database users programmatically, using T-SQL (and of course, triggers and stored procedures also play a part in server and database security). To be clear on the difference between logins and users: Logins allow you to connect to SQL Server per se; users allow you to connect to databases, objects, and the DBMS per se.

• Guest user   The guest user requires special mention. The guest user is predefined in SQL Server when you first install the instance. It is automatically given access to the master database, albeit with no permissions that it could use to hang itself. The purpose of the guest user account is to allow any login to connect to a database, if you explicitly allow this, without having to demand “name, rank, and serial number” at every connection. The guest account is useful for casual access to the database, to look up data without risking data, and so forth.

If you add the guest account to a database, SQL Server will check a login attempt, as usual, to see if a valid user name or alias is assigned. If not, it will check to see if the guest has access to the database. If the guest has access, the nonauthenticated user will be admitted as a guest of the database owner. Allowing guests into the database is totally at the discretion of the database owner or system administrator.

Roles

To me, a security group is to a user account what a shoelace is to a shoe. If you lose the shoelace, you will either lose the shoe or lose your balance. Every domain I currently manage is put together using the building blocks of security and distribution groups. No user under my care is ever excluded from a group-even if I only have one user on the domain, that individual gets to be a member of a group.

I was thus one of many Windows sysadmin/DBAs to be delighted to discover a similar concept established in SQL Server. Roles were added to SQL Server with version 7.0; they are very important to SQL Server 2005 management and count big time toward easing the cost of administration.

If you have worked with groups, then roles will not be too difficult to grasp. Collect the users that require the same services, access, and permissions for logins on databases and objects into a role. Then apply the object’s permissions to the role. The role members are then granted (or denied) access by role membership and not on an individual user basis. Whenever you have a new user that requires access, you can simply add the user to the role for it to inherit the same level of access as everyone else in the role.

When working with Windows groups, when I need to remove a security account’s access from a file or folder, I simply remove it from the group. Specifically denying a user access is not a common occurrence and is usually motivated by some ill intent on the part of the user. Denying access is also a costly management practice because you have to keep track of the actual account and the object that access has been denied to. And you also have to recall what level of access was denied on top of everything else.

With the exception of one role I will discuss, roles apply to database access and to the objects inside the database to which permissions must be set. Roles, like user groups in the OS, can be nested. So you can create a small role for a group of troublemakers and nest it inside a group of angels. If the troublemakers cause trouble, out they go without affecting the other kids. However, the best feature of a role, again like a user group, is that the user can be a member of more than one role, and that makes for some powerful access control and user management.

Roles are available as either predefined “house” roles or as user-defined roles. And they come in several flavors listed shortly. The term “user-defined” is really a misnomer because it is the DBA that will create these roles…or members of the db_owner or db_security predefined database roles discussed shortly.

The following lists the roles and the primary “roles” they play in the DBMS:

• Public role (built in)

• Server roles (built in)

• Database roles (built in)

• User-defined database application roles

• User-defined standard roles

Public Role

The public role is added to every database you create and is added to the system databases when you install SQL Server 2005. The public role works like the Everyone group in the OS environment with one exception: The Everyone group can be removed from objects to fully secure the system. The Everyone group to me is like the proverbial fly buzzing around your head. The Everyone group can be a real nuisance because every time you create a resource or establish a service, the Everyone group gets added. So I go to the parent and ensure that child objects do not inherit the Everyone group from it. Simple cutting the inheritance connection from the parent object, which forces you to add exactly the groups you need to the resource, does this, but with SQL Server it is not as simple as just going to the parent object and getting rid of “Everyone.”

Removing Public is not so easy to do with SQL Server. For starters, access to Public is given as soon as you create the database. Second, every user that gets permission to access the database gets added to the public role. The saving grace of the public role is that it is not a weak link in the security environment of SQL Server as Everyone is to the operating system or network. Instead, it is a convenient way of providing quick default access to users of the database. If you are worried about security, you should know that while the users are added to Public, they are not automatically handed the keys to the vault. You have to go into Public and set access permissions for each object the role can access.

In addition Public is a convenient means of providing all database users with a common set of permissions they should all have.

Server Roles

Server roles are fixed roles, predefined or built into SQL Server 2005. The server role is the only role that is applied outside of the database objects. The server role is a useful container that allows its members to administer the server and perform role-wide tasks. There are a number of server roles in SQL Server 2005, as defined in Table 5–1.

Table 5–1: Fixed Server Roles

Fixed Server Role	Duties
Sysadmin	Has the run of the ship; performs everything.
Serveradmin	Configures the server; shuts down the server. Users in this role can run server-related administration tasks such as DBCC FREEPROCCACHE, RECONFIGURE, and so on.
Setupadmin	Manages linked servers and startup procedures.
Securityadmin	Manages logins and can read error logs. Users can add members to this role and so on.
Processadmin	Manages processes on a SQL Server instance.
dbcreator	Creates, alters, and restores databases.
diskadmin	Manager of the disk files.
bulkadmin	Has the right to run BULK INSERT.

Database Roles

Database roles are fixed roles that apply to the databases of an instance of SQL Server. There are nine fixed administrative database roles and one fixed user role in SQL Server 2005. The fixed user role is public, which we have already discussed. The administrative roles permit a wide range of database administrative chores, such as backing up and restoring, running DDL against the database, and database security. Table 5–2 lists the nine roles and their functions in a database.

Table 5–2: Fixed Database Roles

Fixed Database Role	Duties
db_owner	The big Kahuna: All activity in a database. This role essentially combines all the capabilities of the other database roles.
db_accessadmin	Adds and removes users and logins. This role allows its members to work with Windows users and groups.
db_datareader	Power to run SELECT on any user database. However, this role does not permit access to data editing functions.
db_datawriter	The converse of the preceding role. It has the power to run INSERT, DELETE, and UPDATE on any user database, but it does not permit the use of SELECT.
db_denydatareader	Can deny SELECT on any user database.
db_denydatawriter	Can prevent the change or deleting of data.
db_ddladmin	Can run DDL statements on a user database, but has no access to GRANT, REVOKE, or DENY (see Appendix).
db_securityadmin	Manages roles, membership, and permissions.
db_backupoperator	Performs backups, restores, DBCC, CHECKPOINT.

All databases can have the fixed database roles listed in Table 5–2, but membership is restricted to a database. For example, a member of db_ddladmin on the database SHOES1 is not automatically a member of the same role in SHOES2. This is referred to as the scope of the role.

You cannot nest predefined roles, not even in a user-defined standard role. If you want to let a DBA do both backup administration and login management, the user will have to be added to both roles.

Application Roles

The first question I asked when I encountered roles for the first time was “What about applications?” Surely an application can be a “login” without having to create a special account for the operator of the client machine. For example, you are asked to create a time-entry application to track work hours. Does this mean every employee in the company must be added to a role, possibly to Public? Which groups would you want the network administrator to create? What about change control policy, and so on? The answer lies in the application role.

This is how it works: When your application makes a connection to SQL Server, it calls the application role stored procedure sp_setapprole to request access. This SP takes two parameters in your statement, the name of the role and a password. The DBMS then works with the role in the context of a user on the database, and any permissions defined on objects in the database, in the application role, apply to the application. For example, if the application role only allows the application to select data, that’s all the application will be allowed to do. You will not be able to write a routine that “gate crashes” a role to write data.

Application roles are user-defined and can be secured with passwords. And of course, there are a number of ways to encrypt the password, as discussed earlier. Also, no users are associated with application roles. In the time-entry example, you could create one application role that allows users to enter information, and another one f or the HR managers that will allow their applications to access the information. You could also secure the application by requesting that a user provide the password required for advanced use of the application and pass the data to the SP password parameter for the application role. The application role is thus a very powerful tool, useful in a variety of client programming scenarios.

However, you should also understand that when an application makes use of application roles, any connection made to SQL Server in the context of a standard login is terminated. You have to close the connection to the application role and reconnect to the server if you require features or access in the context of a database user.

SQL Server also maintains any auditing you might have covering the user. So when you suspend user access and startup application access, auditing continues in the user context. In other words, SQL Server still knows who’s pulling the strings.

The application role is also an excellent means of protecting data from being attacked using SQL Server tools and DBA “thorns” like Microsoft Access. The Query Analyzer tool, from the SQL Server 2000 arsenal, for example, is extremely powerful, can connect to SQL Server 2005, and in the wrong hands can destroy a database that is not adequately protected. And Access’s grids do more than just run SELECT on data: one keypress and you can be in the table.

A downside of the application role, however, is that you have to hard-code a password into the client or force the user to provide one when attempting to connect to SQL Server. We are in the business of limiting the number of passwords, and the single sign-on benefit provided by Kerberos provides that very function thus, a second password might detract from that objective.

Standard Roles

Standard user-defined roles are containers you can create for a collection of users or groups from both trusted and nontrusted sources to provide access to a database. These roles are restricted to the database they were created in. The roles can also be used for nesting a number of roles. You can also use roles an alternative to groups from the Windows environment that you might not have access to or that may not yet exist. It will also be better to have the net work administrator create the groups you need and add them to your roles.

Permissions

Microsoft introduced a new permissions system to SQL Server starting with version 7.0, its landmark product revision of the century and precursor to what we are now trying to drive. This system has been extended and further enhanced with SQL Server 2000 and 2005. It is based on the security model and the Windows NTFS permissions architecture, which we discussed earlier in this chapter. An “access control list” for a database object contains the names of users or groups of users that are granted access to an object, and the level of that access. A special “permission"-DENY-blocks a user or group (security principal) from accessing the object in a certain way.

Like its counterpart at the OS and NTFS levels, the user in SQL Server 2005 obtains the sum of permissions assigned to it at the individual and role levels (roles are discussed in a moment). In other words, if the user gets permission to select records in one role and in another role gets permission to insert and select records, the combined permissions are INSERT and SELECT. However, if another group on the same object denies SELECT, guess what?, the user is denied permission to SELECT, no matter that it is granted elsewhere. In Windows Server 2003 security, the object permissions work the same way. DENY is the most restrictive permission and overrides everything.

Inside the SQL Server DBMS, not all objects have permissions. Some objects by their nature or purpose cannot be shared or accessed, yet they all have some function to perform on data that is usually only called by its owner or an internal process. An example of such an object is an index on one end of the chain and the database at the other. No other users are allowed to access the objects until the owners authorize their access and set the access level.

A database object’s access is provided through a system of permissions. At the very top of the object chain, the database itself does not immediately make itself available to users. Before users can access a database, the database owner must first explicitly grant access. This is achieved by allowing groups of users to have access to the database, and permissions can be configured interactively through Management Studio or via several programmatic options. Three types of permissions can be applied to SQL Server objects. These are as follows:

• Object permissions

• Statement permissions

• Implied permissions

Object Permissions

The following is a list of objects that have permissions. If a user can access an object, it means that access to the object must have a system of permissions that govern its access. The following list shows all schema-scoped system objects that are included with SQL Server 2005. “Securables” are system objects contained in the schemas named sys or INFORMATION_SCHEMA. These are the objects that fall into the access-permissions category.

• Aggregate function (CLR)

• CHECK constraint

• DEFAULT (constraint or stand-alone)

• FOREIGN KEY constraint

• PRIMARY KEY constraint

• SQL Stored Procedure

• Assembly (CLR) stored procedure

• SQL scalar-function

• Assembly (CLR) scalar-function

• Assembly (CLR) table-valued function

• Rule (old-style, stand-alone)

• Replication-filter procedure

• Synonym

• Service queue

• Assembly (CLR) trigger

• SQL DML trigger

• SQL inline table-valued function

• SQL table-valued function

• Table (user-defined)

• UNIQUE constraint

• View

• Extended stored procedure

• Internal table

Statement Permissions

Statement permissions control access to the T-SQL statements that create database objects or that cause SQL Server to perform specific database-centric activities (such as execute, update, create, select). While a database is an object, SQL Server 2005 classifies the permissions that apply to it as statement permissions. Statement permissions also apply to stored procedures, functions, tables, views, rules, and so on. These permissions are accessible in the Database Properties, Permissions dialog box in Management Studio.

You will seldom need to provide users with these statement permissions. But if your enterprise maintains a large SQL Server implementation and you have several DBAs assigned to specific tasks, they will require the appropriate permission in order to carry out their duties. You can’t just give everyone access to SA anymore; that’s a no-no in SQL Server 2005.

Implied Permissions

The third class of permissions is called implied permissions. These apply to a wide range of activities and can only be made available to, implicitly, members of the predefined roles in the DBMS or the database owners. Such permissions allow the use, for example in the ALTER TABLE statement, of an implied permission that is inherited by the database owner by virtue of ownership.

A database owner, for example, also does not require permission to delete a table, add data, create indexes, and so on because these permissions are implied (inherited) through ownership.

The system administrator gets the full deck of implied permissions, from installation through complete management to the final removal of an instance.

Checking Permissions

SQL Server 2005 put its Windows Authentication trust to the test when a user attempts to log in. At the time of login, a trusted connection is established if the so-called security principal, a user or a group, arrives armed with an access token and SID information. When you create a login for a client derived from a domain security principal, the login is created and the SID for the security account is fetched from Active Directory or the NT Security Accounts Manager (SAM). You can also create a trusted account without the domain because the SID varbinary variable can store a NULL or a GUID.

Each time a user connects over the trusted connection, its SID is compared to the SID on file in the SQL Server sysxlogins table (visit the master database). If the SID does not compare, the connection is rejected. Do you remember what was discussed earlier in this chapter about the SID? If you know what goes on in the world of Windows security, you’ll remember that when you delete a user, computer, or group account, you also delete the SID. If you then create the exact same account, a new SID is generated because the old one was discarded when you nixed the earlier account.

The idea to use SIDs was born during the development of SQL Server 7.0, which replaced the old version 6.5 trusted mechanism that still uses access over Registry keys installed in the local host’s Registry, and we all know how secure that is. SQL Server would check if the user had access to the Registry key, and if access was proved, login was granted. SQL Security Manager (may it rest in peace) was used to insert the usernames into the old syslogins table. I am reminding you of this legacy because upgrading to SQL Server 2005 can be scary if your objective is to inherit extensive databases and configuration information you may have on the old platform. Before you upgrade, you must make a special effort to clean up your legacy installations, and the one area to start with is your user account and login information.

SIDs are stored along with the user login information in the sysusers and sysxlogins tables and are used to pair the logins with the associated user accounts. Each user ID is then stored in the sysobjects table to determine ownership and in the sysprotects table to apply permissions to the objects (in similar fashion to the ACLs out in the NTFS). Yet another table, systypes, is used to determine ownership of any user-defined types.

Every time a user connects to SQL Server and is given access, its connection information is recorded in a snapshot structure called the process status structure (PSS). The PSS is not persistent but is lost when the connection ends. However, every connection is recorded in its own PSS, so an application or user making multiple connections to SQL Server will have multiple PSS snapshots. The PSS in fact is very similar to the access token that is associated with the user every time it requires access to file system objects, as well as to the Kerberos tickets that are required to provide access to servers and services on a Windows Server 2003 domain.

When a user attempts to access a database, the server checks the sysusers tables for any negative credit that may have been placed there, in the form of specific access denials placed on the database objects. These are unlikely to occur, because users generally obtain access via the Public role discussed above. If all checks out and the user has database access, the DBMS then scans the sysmembers table to check on the role memberships of the user. This allows the DBMS to check if the user is constrained from access or denied service by virtue of group or role membership.

Once a user’s “persona” has been established by the server, object permissions are checked in the syspermissions table. Naturally the DENY permission information will be checked first; if it is present, the user will be blocked from accessing the respective resource on the level of access that has been denied. The combined access/denied scenario is cached to prevent system overhead every time the respective user attempts to access the same object.

SQL Server maintains a permissions cache that is kept up to date because permission status can change at any time. The permissions cache works like the procedure cache or the query plan cache. When permissions are changed, the DBMS increments a permissions counter that is compared against the value in the cache. If the value in the cache is lower than the counter value, the permissions are rescanned and the permissions cache is updated. There is a small overhead that is incurred at every permission rescan, but this is negligible because once a database model or design is settled, permissions are unlikely to be continually changed.

GUIDS for Nontrusted Users

SQL Server nontrusted users are assigned a 16-byte globally unique identifier in place of a SID (which will be absent when this user connects or when SQL Server is installed in an insecure environment). GUIDs are used in the same fashion as SIDs described previously are for trusted users. This allows the internal security architecture to remain the same for both trusted and nontrusted environments. In other words, all permissions-checking routines remain the same, no matter whether it is the SID or the GUID the security architecture revolves around.