Homegrown Security Solutions

This article is the third in a four-part series about managing SQL Server security with Microsoft Access projects. "Access Granted," December 2000, reviewed logins and fixed server roles. "Securing SQL Server Tables," January 2001, introduced user accounts and fixed database roles. User-defined roles can specify permissions for database objects more precisely than fixed database roles can. If you understand user-defined roles and database object permissions, you can fine-tune secure access to database objects. This article gives you a couple of examples of how to manipulate user-defined roles and permissions from Access projects.

User-Defined Roles and Permission Types

Fixed database roles contain clusters of permissions for performing selected tasks in a database. For example, the db_datareader role lets a database user perform a SELECT statement against any table in the database. Within the context of an Access project, the db_datareader role enables the user of a project to open any table in a database. The db_denydatareader role is a fixed database role that denies a user permission to perform a SELECT statement against any table in the database.

Fixed database roles apply to all tables within a database, but what if you need a user account that can select from only certain tables? With user-defined database roles, you can selectively apply permissions to individual objects within a database. For example, a user-defined role can grant permission to execute SELECT statements against one table in a database and deny permission to execute SELECT statements against another table in the same database. Table 1 contains the names and descriptions of the six permissions that you can set within the Access project interface, along with the database objects to which each permission applies. Four permissions pertain both to tables and to views, one permission pertains exclusively to tables, and one applies to stored procedures. Later, I present a couple of examples that demonstrate how to manipulate the SELECT permission for individual tables within a database.

Creating User-Defined Roles and Assigning Permissions

User-defined roles offer a vehicle for conveying permissions for specific database objects to ad hoc collections of users. Only members of the sysadmin fixed server role or the db_owner fixed database role (or the owner of a database object) can assign permissions for a database object to a user-defined role. The sysadmin fixed server role has the broadest scope, so I recommend that you use a login that belongs to the sysadmin fixed server role when you create a user-defined role.

To create a new user-defined role, choose Tools, Security, Database Security from the Database window menu of an Access project. Click the Database Roles tab in the SQL Server Security dialog box, then click Add to open the Database Role Properties - New Role dialog box. When you create a new user-defined role, you give the role a name by typing a string in the Name text box. Then you assign members to the role. Click Add to open a pop-up dialog box that lists the users in the database, then select the users that you want to belong to the role. Next, click OK twice to close the Add Role Members dialog box and return to the Database Roles tab.

After naming a role and specifying the users that belong to it, you can designate the permissions for the role. Highlight the role's name on the Database Roles tab and click Edit. Then, click Permissions on the Database Role Properties dialog box to show the Permissions tab. By default, this tab exposes all the tables, views, and stored procedures in a database. You can use this tab to grant, revoke, and deny permissions for the user-defined role that you're editing. (I describe how to manipulate a role's permissions in the second example below.)

One user can belong to multiple roles, including user-defined and fixed database roles. A user's permissions are a combination of the permissions for all the roles the user belongs to. Therefore, if a user belongs to two roles—one with a Revoke status and another with a Grant status for the same permission on a database object—the user can perform the task that the Grant setting authorizes. An important exception to this rule is that Deny status for a permission in one role overrides any Grant permission settings in other roles a user belongs to.

Creating a User and Adding the User to a Role

Using the Northwind sample database, the following example creates a role and adds a user to it. Start by creating a new Access project and connecting it to the Northwind database. Then, log in to the database with a login that is a member of the sysadmin fixed server role (for example, sa is a member of the sysadmin fixed server role). Create a new login named sm_user1 that isn't a member of any fixed server role. ("Access Granted" lists the steps for accomplishing these tasks.) Create a user account for the sm_user1 login with the same name as the login. Grant the user account access to the Northwind database, but don't make the user account a member of any fixed database roles. Then, in the Northwind database, create a new table named TABLE1 with a single column named COLUMN1. Make the column a primary key for the table. ("Securing SQL Server Tables" describes the steps for accomplishing these tasks.)

With the sm_user1 login, open a second Access project that connects to the Northwind database. Because the sm_user1 user belongs to no user-defined or fixed database roles, this user inherits the permissions associated with the public role, which all users belong to by default. Access implicitly assigns the user to the public role. SQL Server 2000 and SQL Server 7.0 define public role permissions for a slightly different set of tables in the Northwind database. Both SQL Server versions enable SELECT, UPDATE, INSERT, and DELETE permissions for several tables, including the Categories table. Click Tables in the Objects bar for the Access project's Database window to expose all the tables for which the public role has SELECT permission. Open the Categories table by double-clicking it in the Database window. If you update and restore a column value in the table, the edits succeed. These edits confirm the UPDATE permission for the sm_user1 user through the public role.

Note that no table named TABLE1 appears in the Database window because you haven't assigned any public role permissions for the table. Therefore, the sm_user1 user can't select records from the table. Exit the Access project by choosing File, Close from the Database window menu.

Creating a User-Defined Role and Explicitly Assigning Permissions

Now let's create a user-defined role and apply Select permissions for different tables within a database. This example demonstrates the impact of Grant and Deny status settings and shows how SQL Server resolves conflicting status settings for permissions. To begin, return to the first Access project that connects to the Northwind database through a login that belongs to the sysadmin fixed server role. Create a new role with the sm_user1 user as a member: Choose Tools, Security, Database Security from the Database window menu. Select the Database Roles tab on the SQL Server Security dialog box and click Add. In the Database Role Properties - New Role dialog box, enter sql_mag_role into the Name text box and add the sm_user1 user to the role, as Figure 1 shows. Note that Access doesn't enable the Permissions button to the right of the Name text box, so you can't assign Permissions when you initially specify a user-defined role. Click OK to close the dialog box and complete role creation.

After you create the role, you can assign permissions to it. Highlight sql_mag_role on the Database Roles tab and click Edit. When the Database Role Properties dialog box for sql_mag_role opens, Access enables the Permissions button. Click Permissions. Figure 2 shows one possible SELECT permission setting for the Categories table. Clicking the control box once places a check in the box. A second click replaces the check with an x, as the figure shows. A third click returns the box to its original blank condition. The check, x, and blank correspond respectively to the Grant, Deny, and Revoke status settings for a permission on an object. The x in Figure 2 means that members of the sql_mag_role role, such as the sm_user1 user, can't open the Categories table to perform a SELECT statement. Scroll down the object column until TABLE1 appears. Click the control once in the SELECT column for TABLE1 so that a check appears, leaving the other columns blank. Then, click OK three times to commit the design changes to your role and return to the Database window.

Now, reopen the second Access project that connects to the Northwind database through the sm_user1 login. This time, TABLE1 appears in the Database window when you highlight Tables in the Objects bar because you explicitly granted SELECT permission to the user account associated with the sm_user1 login. If you double-click TABLE1 to open the table and attempt to enter the string 'abcdefghij' into the empty table cell, a dialog box pops up, as Figure 3 shows. This box reminds you that the database owner (DBO) of the Northwind database didn't grant INSERT permission to the current user, sm_user1. The sm_user1 user has permissions through its membership in the public and sql_mag_role roles, but neither of these has INSERT permission for TABLE1 because you didn't explicitly grant INSERT permission when setting the permissions for the sql_mag_role role. The public role doesn't have any permissions for new database objects unless you explicitly designate them. Click OK to remove the message from the screen, then press the Esc key to clear the value in the datasheet's cell for the table. Click Close to return to the Database window.

Note that the list of tables in the Database window doesn't include the Categories table because the sql_mag_role role denies SELECT permission for this table. Although sm_user1 belongs to the public role, which grants SELECT permission for the Categories table, the Deny status for the SELECT permission in the sql_mag_role role overrides the Grant setting in the public role.

Controlling access to a database server and its contents is imperative for many mission-critical database applications because businesses don't want unauthorized individuals accessing proprietary data. The Access project UI provides a graphical approach for managing SQL Server's rich set of security features. This article shows you how to set specific kinds of permissions for individual database objects within a database. The concluding article in the security series will examine programmatic means of managing SQL Server security.