The security systems in central databases used by large corporations are nearly as complex as the organizational structure of the large companies themselves. Corporations have large numbers of users who perform specialized, exclusive tasks.
In this example, there is a single person is responsible for all aspects of the database application. A few people are responsible for creating databases and tables, but they must not be allowed to see sensitive personnel information about their coworkers (or even themselves). There is an evening team that backs up data, but these workers need not see the data, or create tables and databases. The personnel department must have access to general employee information, and a few select individuals in Personnel will be the only people in the company with access to confidential and sensitive employee information. There are also customer service employees who will need to see but not change product specifications in response to customer inquiries.
The users-to-activity map for this example is fairly complex.
| User account | Activity |
|---|---|
| LONDON\annej | All database access |
| LONDON\dbadmins | Create databases |
| LONDON\dboperations | Perform evening backups |
| LONDON\personnel | Full access to general employee data |
| LONDON\mikebo, LONDON\marym, LONDON\billsm | Full access to confidential data |
| LONDON\custservice | Read-only access to product information |
As in the previous example, the LONDON\annej user account must be created in Microsoft® SQL Server™ and added to the sysadmin role that has full permissions. The LONDON\dbadmins Microsoft Windows NT® group user account must be added in SQL Server and granted the permission to create databases. The LONDON\operations Windows NT group should be added also and granted only the BACKUP DATABASE permissions to allow them to perform backups.
The LONDON\personnel Windows NT group should be added and granted the permissions to see only the nonsensitive columns in the employees table, as well as the permissions to see other tables.
The users LONDON\mikebo, LONDON\marym, and LONDON\billsm are members of the LONDON\personnel Windows NT group, so they already have the permissions necessary to do most of their work, but they also need special access to the sensitive employee information columns. To fit this need, a database role called PersonnelSecure could be created in SQL Server and granted the permissions required to see the sensitive employee information. Individual users get the special permissions in SQL Server when added to the role. An alternative method is to add the special permissions to their user accounts directly.
The final step is to add an account for the LONDON\custservice Windows NT group in SQL Server, and grant the permission to see product information.