Creating a Manageable Security Plan

In revamping the way SQL Server implements security in the 7.0 release, Microsoft created a flexible and powerful method for managing users' access both to the SQL Server system and to the databases. The changes also created confusion as administrators tried to upgrade from SQL Server 6.5 security. The confusion about the differences between SQL Server 7.0 and SQL Server 6.5 security led many administrators either to leave their security implementation alone or to inadvertently leave holes through which unauthorized users could get to the data. With the approach this article presents, you can build a security plan for SQL Server 7.0 (or SQL Server 2000) that is not only manageable and flexible but also verifiably secure.

Authentication Choices

In this article, I distinguish between the terms authentication and authorization. Authentication refers to verifying a user's identity; authorization refers to what a user is allowed to do. For this discussion, authentication occurs when the user logs in to SQL Server, and authorization occurs whenever a user attempts to access data or execute a command.

The first step in building a security plan is to decide how SQL Server will authenticate users. SQL Server authentication matches an account and password to a list stored in the Sysxlogins table of the Master database. Windows NT/2000 authentication asks a domain controller to validate the user's credentials. In general, always use Windows NT/2000 authentication for installations where the server has access to a domain controller. The domain controller can be either a Win2K or an NT server because in both cases SQL Server receives an access token, which is a special list built during authentication that contains the user's SID and a list of SIDs for the groups of which the user is a member. As I show later in this article, SQL Server assigns database access and permissions based on those SIDs. Note that how the OS builds the access token is unimportant. SQL Server uses the SIDs only in the access token, so whether you use SQL Server 2000, SQL Server 7.0, Win2K, or NT is unimportant. The result is the same.

The biggest benefit of using SQL Server authenticated logins is that they're simple to implement through Enterprise Manager. The major drawback is that SQL Server authenticated logins are local to a particular server, which means that they're difficult to manage in a multiserver environment. A lesser but still significant drawback is that with SQL Server authentication, you have to manage permissions on each database individually. If a user needs the same permissions in two databases, you must either assign the permissions manually or build a script to assign them. If you have a small number of users, say fewer than 25, and their permissions change infrequently, SQL Server authenticated logins might work for you. In almost all other cases (exceptions include an application that manages security directly), the administrative overhead in managing these logins outweighs their benefits.

Web Authentication

One situation that often breaks down even the best security plans is the use of SQL Server data in Web pages. The typical way to handle authentication in such a situation is to embed a SQL Server login and password in a Web-server-based program, such as an Active Server Pages (ASP) or Common Gateway Interface (CGI) script. The Web server takes responsibility for authenticating the user, then the program uses its own login (either the systems administrator—sa—account or a login account in the Sysadmin server role for convenience) to access the data for the user.

This arrangement has several drawbacks, the most significant of which are an inability to audit actions on the server, dependence on the Web program to authenticate users properly, and a lack of differentiation among users when SQL Server determines permissions. If you're using Microsoft IIS 5.0 or IIS 4.0, you have four options for authenticating users. The first option is to create an NT account for anonymous users for each site and virtual directory. All programs will then use that security context when they log in to SQL Server. By granting the anonymous NT account the appropriate permissions, you can improve auditing and authentication functionality.

The second option is to have each site use Basic authentication, in which users must type a valid account name and password into a dialog box before IIS permits them to view a page. IIS validates the login credentials against an NT security database that can reside on either the local server or a domain controller. When the user runs a program or script that accesses SQL Server, IIS sends the server the login credentials the user provided to view the page. If you use this option, remember that the transfer of the account and password between IIS and the browser usually isn't encrypted; therefore, you need to implement Secure Sockets Layer (SSL) on any Web site that uses Basic authentication.

If you use Microsoft Internet Explorer (IE) 5.0, IE 4.0, or IE 3.0, you have a third option. You can enable NT authentication on both Web sites and virtual directories. IE will send IIS the credentials the user logged on to the computer with, and IIS will use those credentials whenever that user tries to log in to SQL Server. In this simple way, you can authenticate users in a remote site's domain who log on to a domain that has a trust relationship with the domain the Web server uses.

Finally, if your users have personal digital certificates, you can map those certificates to NT accounts in the local domain. Based on the same technology that server digital certificates use, a personal certificate validates a user's identity and therefore can replace NT's Challenge/Response authentication algorithm. With this option, a user doesn't need to log on to a domain that trusts or even recognizes the domain where IIS resides. Both Netscape Navigator and IE will send certificate information automatically to IIS with every request for a page. IIS includes a tool that lets administrators map those credentials to an NT account, which means that the digital certificate replaces the usual login process of supplying an account name and password.

You have many options for authenticating users with NT accounts, even when the users connect to SQL Server across the Internet through IIS. Therefore, plan to use NT authentication as your principal means of authenticating users.

Users Travel in Groups

The next step in building the security plan is to identify which groups your users fall into. Typically, every organization or application has broad categories of users who need a specific type of access to data. Accounting applications usually have users who are data entry operators, data entry managers, report writers, accountants, auditors, and accounting managers. Each group needs a different kind of database access.

The easiest way to handle data access permissions for the different groups is to create a domain global group that matches each group of users. You can either create the groups separately for each application or create the groups to cover broad categories that apply throughout the organization. I prefer to create groups that are specific to each application so that I can know exactly what the members need to be able to do. Using the accounting example, you would create groups named Accounting Data Entry Operators, Accounting Data Entry Managers, and so on. Remember that for easy management, it pays to give your groups names that clearly relate to their purposes.

Besides application-specific groups, you need a few basic groups whose members manage your servers. As a rule, I recommend creating groups with names such as SQL Server Administrators, SQL Server Users, SQL Server Denied Users, SQL Server DB Creators, SQL Server Security Operators, SQL Server Database Security Operators, SQL Server Developers, and DB_Name Users (where DB_Name is the name of a database on the server). You can create other groups as you need them.

After you create the global groups, you can grant them access to SQL Server. First, create an NT authenticated login for SQL Server Users and grant login rights. Make Master the default database, and don't grant access to any other database or make the login account a member of any server role. Next, repeat the process with SQL Server Denied Users, but in this case, deny login access. After you've created these two groups, you have a simple way to permit and deny users access to the server. SQL Server lets users log in if any of the groups in the users' access token has appropriate permission; it rejects a login if any group is denied access, as does the No Access permission in NTFS.

To grant privileges to groups that don't have explicit login entries in the Sysxlogins system table, you must leave Enterprise Manager because it lets you choose only from a list of existing logins, not the entire list of groups in the domain. To access all the groups, open Query Analyzer, and use the system stored procedures sp_addsrvrolemember and sp_addrolemember to grant privileges. (For details about these stored procedures, see SQL Server Books Online—BOL.)

For the server operator groups, you use sp_addsrvrolemember to add each login to the appropriate server role. (For more information about server roles, see Michael D. Reilly's Certifiably SQL columns "Security and Permissions," January 10, 2000—online only—and "Login Security," January 2000.) The SQL Server Administrators login becomes a member of the Sysadmins role, SQL Server DB Creators becomes a member of the Dbcreator role, and SQL Server Security Operators becomes a member of the Securityadmin role. Remember that the second parameter of sp_addsrvrolemember requires the complete path for the account. For example, JoeS in the BigCo domain would be bigcojoes. (Note that if you want to use a local account, the path would be server_namejoes.)

To create users that exist in all new databases, change to the Model database. SQL Server automatically copies anything you do in Model into new databases, which can make your job easier. If you use Model correctly, you won't need to customize every new database after its creation. Using sp_addrolemember, add SQL Server Security Operators to db_securityadmin, and add SQL Server Developers to the db_owner role.

Notice that you still haven't granted any group or account access to the database. In fact, you can't grant database access through Enterprise Manager because the user interface lets you grant database access only to valid login accounts. SQL Server doesn't require an NT account to have access to the database before you can make the account a member of a database role or assign object permissions, but Enterprise Manager does. You can assign permissions to any NT account in the domain without granting that account database access as long as you use sp_addrolemember and not Enterprise Manager.

You're now finished with Model unless your users fall into broad categories across the organization as a whole. In that case, you can perform the following tasks in Model instead of the application-specific database.

Permitting Database Access

Inside a database, you can do something differently from what you've done so far with login authentication: You can assign permissions to roles instead of assigning them directly to a global group. This capability lets you add SQL Server authenticated logins to your security plan with little additional effort. Even if you never expect to use SQL Server login accounts, I still recommend assigning permissions to roles so that you're prepared in case your requirements change in the future.

After you create a database, use the stored procedure sp_grantdbaccess to grant the DB_Name Users group access to it. Note that no corresponding sp_denydbaccess stored procedure exists, so you can't deny access to a database in the same way you can deny access to the server. If you need to deny database access, create another global group named DB_Name Denied Users, grant it access to the database, and make it a member of the db_denydatareader and db_denydatawriter roles. Be careful about how you assign statement permissions because these roles limit only access to objects, not to Data Definition Language (DDL) statements.

As with logins, SQL Server lets a user access a database if any SID in the access token has an entry in the Sysusers system table. So, you can grant users access to a database through either their personal NT account SIDs or any NT group (or groups) of which they're members. To keep management simple, create one global group named DB_Name Users that has database access, and don't grant access to any other group. By doing so, you can add and remove database users simply by adding or removing membership in the global group.

Assigning Permissions

The last step in setting up your security plan is creating user-defined database roles and assigning permissions. The easiest way to handle this step is to create roles with names that match the names of the global groups. Using the accounting example, you would create roles named Accounting Data Entry Operators, Accounting Data Entry Managers, and so on. You could shorten the names because roles in the Accounting database will probably relate to accounting tasks. However, if the role names match the global group names, you will have less confusion and can more easily identify which groups belong in a role.

After you create the roles, you can assign permissions. For this step, just use the standard GRANT, REVOKE, and DENY statements. But be careful with the DENY permission because it takes precedence over all other permissions. SQL Server denies users access to an object if they are members of any role or group with the DENY permission. Also, if you're upgrading from SQL Server 6.5, remember that SQL Server 7.0 and SQL Server 6.5 assign and evaluate permissions completely differently.

Now, you can add any SQL Server authenticated logins you have. One of the most valuable features of user-defined database roles is that they can contain SQL Server logins as well as NT global groups, local groups, and individual accounts. User-defined roles are a universal container for all types of logins, which is the main reason to use them instead of assigning permissions directly to the global groups.

Notice that I'm suggesting that you use only two built-in database roles (db_securityadmin and db_owner). My reason is that built-in roles generally apply to the database as a whole, not to individual objects. For example, db_datareader grants SELECT permission on every object in the database. You could use db_datareader, then selectively deny SELECT permissions to individual users or groups, but that method creates an opportunity for you to forget to set the permissions for some users or objects. An easier, more straightforward, and less error-prone method is to create a user-defined role for a specific set of users and assign the permissions that those users will need only for the objects they need to access.

Simply Secure

SQL Server authenticated logins are simple to implement and easier than NT authenticated logins to code into programs, but they aren't easy to manage when the number of users exceeds 25, when you have multiple servers, when each user has access to multiple databases, or when the database has multiple administrators. Even in small installations in which the database administrator has other responsibilities, simple plans alleviate the problems of remembering each user's permissions and how he or she got them—a problem made more complex because SQL Server doesn't have a tool that shows users' effective permissions. The best recommendation, therefore, is to use NT authenticated logins and to manage database access with a carefully selected set of global groups and database roles.

Here are rules of thumb for keeping things simple:

With proper planning, you can handle all maintenance of access rights and permissions on the domain controller so that changes are reflected in all the servers you manage. You might have some different variations, but the basic steps I've outlined will help you to create a manageable security plan.