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 PropertiesNew 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.
End of Article
Prev. page
1
[2]
next page -->
