Corporate Environment Security Example
In a large corporate security system, there is a complex web of users who perform specialized, exclusive tasks.
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). An evening team backs up data, but these workers need not see the data, nor create tables and databases. The Personnel department must have access to general employee information, and a few select individuals in this department are the only people in the company with access to confidential and sensitive employee information. Also, customer service employees 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 |
The LONDON\annej user account must be granted login rights to Microsoft® SQL Server™ and added to the sysadmin role because the sysadmin role has full permissions across the server. The LONDON\dbadmins Microsoft Windows NT® 4.0 and Windows® 2000 group user account must be added in SQL Server and granted permission to create databases. The LONDON\operations Windows NT 4.0 group should be added also and granted only the BACKUP DATABASE permissions to allow them to perform backups.
The LONDON\personnel Windows NT 4.0 and Windows 2000 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 4.0 group, so they already have the permissions necessary to do most of their work. However, they also need special access to the sensitive employee information columns. To meet this need, create a database role called PersonnelSecure in SQL Server and grant the permissions required to see the sensitive employee information. Individual users get the special permissions in SQL Server when added to the role. Or, add the special permissions to their user accounts directly.
The final step is to add an account for the LONDON\custservice Windows NT 4.0 group in SQL Server, and grant it permission to see product information.