Securing SQL Server Tables

In "Access Granted," December 2000, I discuss securing access to SQL Server systems by using login accounts, which let users log in to SQL Server but don't necessarily provide access to the individual databases on the server. In this article, I review some database security principles and demonstrate simple techniques for securing the tables within a database on a SQL Server machine. You can use both user accounts and fixed database roles with login accounts to control access to SQL Server database tables. First, I briefly explain important design considerations for database access. Then, I describe techniques for manipulating the Microsoft Access project UI to control security for the tables within a database.

User Accounts

A login account lets a user access a database server; a user account identifies a user within a database. Any login can have multiple user accounts—one for each database on the SQL Server machine that the login account can access. The collection of user accounts within a database defines the users who can access that database.

Some important relationships exist between logins and two special user accounts. The Database Owner (dbo) user account is the default owner of a database. Every database must have a dbo user. You can't drop the dbo user, which has all permissions for all objects within the database, including permission to create new user accounts. The database creator holds dbo user status unless database ownership changes. Also, any login, such as sa, that belongs to the System Administrators fixed server role is automatically a dbo user for all databases on a server.

The guest user account is an optional user account. All logins—even if they don't have a specific user account—can access a database through the guest account if it exists on the specific database. If the database has no guest user account, a login attempting to connect to a database will fail unless the login has a user account explicitly defined for it. By default, the guest user account has no permissions relating to user-defined database objects, such as tables. You can use the Access project UI to create the guest user account. And you can assign database access permission to the guest account just as you do to other user-defined accounts.

Fixed Database Roles

SQL Server has nine fixed database roles, which define standard kinds of database access. When you assign a user to a fixed database role, you give that user a predefined cluster of permissions. From their memberships in fixed database roles, users inherit permissions.

Several fixed database roles are especially pertinent to the use of tables in a database. The db_datareader role lets a user account perform SELECT statements against any table in a database. However, this role doesn't enable other typical functions for tables, such as INSERT, UPDATE, and DELETE. If you assign a user to the db_datawriter role and the db_datareader role, then the user can execute INSERT, UPDATE, and DELETE statements against all the tables in a database. SQL Server Books Online (BOL) states that the db_datawriter role supports data-maintenance functions, such as UPDATE, without the addition of the db_datareader role. However, in my experience, a user requires both roles to enable UPDATE statements.

User accounts that belong to the db_ddladmin role can create and drop tables in a database. However, membership in this role doesn't include the permission to execute SELECT, INSERT, UPDATE, or DELETE statements. A user can belong to any combination of fixed database roles. Therefore, by assigning a user account to the db_datareader and db_datawriter roles but not the db_ddladmin role, you can enable only SELECT, INSERT, UPDATE, and DELETE permissions without conveying table-creation capabilities.

The db_owner role has all the permissions in a database, including the ability to assign role status to other users. Membership in this role establishes a user as a dbo user. If a login is a dbo user or if a user has membership in the db_owner role, the corresponding user account has unlimited permissions within the database.

Two system stored procedures can give you more information about fixed database roles. The sp_helpdbfixedrole procedure lists and briefly describes all fixed database role names. The system stored procedure sp_dbfixedrolepermission lists the specific permissions associated with each fixed database role. You can create a script inside an Access project's stored procedure template to run either sp_helpdbfixedrole or sp_dbfixedrolepermission. The following example code demonstrates the syntax for listing the fixed database role names and descriptions:

ALTER PROCEDURE list_fixed_db_rolesASEXEC sp_helpdbfixedrole

The public role is a special role to which every user belongs in addition to any fixed database roles. You can't remove a user from the public role, nor can you drop this role, but you can add and drop the role's permissions for individual database objects. By default, the public role has no permissions for user-defined objects in a database. If the public role has permissions for existing database objects and a database has a guest account, every login will have at least the permissions assigned to the public role. This holds true even if the login has no user account for the database.

Working with Databases

The Northwind database, which ships with SQL Server 2000 and 7.0, is useful for getting to know SQL Server database security features for tables. Northwind has a guest user account and a public role that includes all permissions for all user-defined database objects that ship with the database. So a user logging in to the Northwind database without an explicit user account can connect to the database and can select, insert, update, and delete records in all existing tables in the database.

Figure 1 shows the Data Link Properties dialog box from an Access project that connects to the Northwind database on a SQL Server machine named CABLAT. (You open the Data Link Properties dialog box by choosing File, Connection from the Database window menu of an Access project.) The user name cabtest3 on the dialog box is a login account name, which I showed you how to create in the December 2000 article's example. This login has no fixed server role assignments, nor does it have a user account for the Northwind database. Nevertheless, the connection succeeds because the Northwind database includes a guest account, which has the permissions belonging to the public role—namely, all permissions for all existing database objects.

When you use the cabtest3 login to open the project's Database window and select Tables in the Objects bar, the table names appear, with dbo in parentheses behind each name. The names have added dbo because cabtest3 isn't a member of the System Administrators group or the db_owner role. However, a workstation connecting to Northwind through the cabtest3 login can open and edit any existing tables. Open a couple of tables, such as Categories and Products. Changing and restoring selected field values, such as those in the CategoryName and ProductName columns, confirms your ability to select and edit values with the Northwind database's guest user account. Now close the first Access project.

Next, let's remove the cabtest3 login account's ability to log in to Northwind. Create a new Access project that uses the sa login to connect to the Northwind database on the CABLAT (or your local) server. From this project, choose Tools, Security, Database Security, then click the Database Users tab on the SQL Server Security dialog box. Select guest, click Delete, then confirm that you want to remove guest. Click OK to close the SQL Server Security dialog box. Finally, attempt to reopen the first Access project that connects to the Northwind database through the cabtest3 login. If you select the Access project from the most recently used files on your Files menu, enter a password for the cabtest3 login, and click OK, Access pops up a message saying that the login failed. A login account without a user account can't connect to a SQL Server database unless the database contains a guest account.

Using the Northwind database is attractive for tutorial purposes because Northwind contains many built-in database objects and security settings, but you won't build security solutions for it. Let's look at how to apply security to a new, user-defined database—a more typical scenario. From an Access project, use the sa login to create a new SQL Server database named sqlmag_secure_dbSQL. (For details about how to perform this task, see "Defining Databases and Tables with Access Projects," January 2000.) Next, import the Categories and Products tables from the Access project that connects to the Northwind database through the sa login. Choose File, Get External Data, Import to import tables from one Access project to another. Make CategoryID in the Categories table and ProductID in the Products table the primary keys for their respective tables.

Next, create a user account for the cabtest3 login. Open the SQL Server Security dialog box by choosing Tools, Security, Database Security. Next, select the Database Users tab, and click Add to open the Database Users Properties­New User dialog box. Choose cabtest3 from the Login name drop-down menu. Note that Access selects the public check box because all SQL Server logins must belong to this role. Select the check boxes for the db_ddladmin, db_datareader, and db_datawriter roles, as Figure 2 shows. Then, click OK twice to confirm your permissions for the cabtest3 user. These settings enable a user account named cabtest3, which can select, add, edit, and delete data in all existing tables and create new tables.

To confirm the permissions that the db_ddladmin, db_datareader, and db_datawriter roles convey, open the first Access project and set its data link properties to have the project use the cabtest3 login for the sqlmag_secure_dbSQL database. Use the Database window to select the Categories table, then the Products table. Then, edit and restore several values in the CategoryName and ProductName columns. After restoring the data, select Tables in the Objects bar, click New on the Database window control, and accept the default name TABLE1. Enter COLUMN1 as the column name, clear the Allow Nulls check box, and click the Primary Key control on the toolbar, making sure you have the cursor in the row for the COLUMN1 column. Click Close, and accept the prompt to save the changes to TABLE1.

Now, return to the Access project that has the sa login to the sqlmag_secure_dbSQL database. Choose Tools, Security, Database Security. Select the Database Users tab, select cabtest3, and click Edit. Remove the check next to db_ddladmin, and click OK twice. Then, reopen the Access project with a cabtest3 login to the sqlmag_secure_dbSQL database. (You must reopen the project for the new settings to take effect.) If you now follow the same steps you used for TABLE1 and create a table named TABLE2, you can't save the second table because you removed the db_ddladmin role from the cabtest3 user.

SQL Server Table Security

SQL Server divides security into two major areas. First, as I discussed in December, you can let a user log in to SQL Server without necessarily granting database rights for individual databases. Second, you can create database user accounts that correspond to login accounts, and you can grant permissions to the user accounts. These user accounts and permissions are specific to individual databases. From Access projects, you can implement SQL Server security for tables in a SQL Server database. You create user accounts and assign permissions to those accounts by selectively designating membership in fixed database roles.