Establishing security permissions for database access can be a daunting task. An organization's data needs are often complex and challenging to meet. Microsoft might have intended the graphical interface of SQL Server's Enterprise Manager to make managing security permissions easier. But the GUI can quickly become an ineffective, confusing tool when you're managing security permissions for hundreds or thousands of users, each with individual needs for data access and retrieval—unless you know how to plan permissions.
You analyze security permissions in the planning phase at the start of the database development life cycle. In the planning phase, you ask questions such as who are the data users, what organizational units do they belong to, what are each organizational unit's purpose and function, how do people use the data, and which parts of the data do they need access to? If you collect and store corporate data and employees need to use that data, then you have occasional competition for the data. One group might want to define or describe the data in a way that's inconsistent with how another group views the data. Giving both groups unlimited update ability on the same data set can result in confusion. To avoid such a situation, follow this simple formula for developing a security permission plan for your database: First, define groups or roles that describe the data-use requirements of your organization's functional units. Second, assign individual users to these groups or roles. Then, map the group or role permissions to the various objects (tables, views, stored procedures, etc.) in your database. (See Certifiably SQL, "Login Security," January 2000, and "Security and Permissions," February 2000, Web Exclusive Article ID 7858, in the article index at http://www.sqlmag.com for detailed information on implementing your security plan.) Finally, write and execute security scripts based on these mappings.
Define Groups or Roles
A SQL Server 7.0 role is similar to a SQL Server 6.5 group. Groups and roles, according to SQL Server Books Online (BOL), are "a powerful tool that allows you to collect users into a single unit against which you can apply permissions." Whereas in SQL Server 6.5 each user can belong only to Public and one other group, a SQL Server 7.0 user can belong to many roles. Figure 1, page 70, is a security decision matrix, a form to help you decide how to set up security permissions in your database. It's similar to the familiar create, read, update, delete (CRUD) matrix, but whereas the CRUD matrix is general-purpose, this security decision matrix is tailored to the SQL Server environment. When you use it properly, the security decision matrix can help you develop and document your security plan.
A security decision matrix consists of a set of grids, each of which corresponds to a table, view, or stored procedure in the database. You can manage the stored procedures with a special type of grid, such as the one in Figure 2, page 70. In the case of the Pubs database, which has 11 tables and one view, you need 11 grids for the tables and one for the view. Figure 1 shows three of these 12 table and view grids, for the Authors, Titles, and AuthorTitle tables. The roles are listed in the first column of each grid. (You can use user names if you need to apply permissions to specific users.) You might expect roles such as these at a publishing company. Although people in marketing and sales departments do different things, marketers and salespeople need similar kinds of data. So in this case, combining them into one role is reasonable. The names of the groups or roles can correspond to names of departments, but the groups or roles need to represent a defined set of tasks that discrete groups of people in the organization perform.
You create SQL Server 6.5 groups with the sp_addgroup command. You create SQL Server 7.0 roles with the sp_addrole command. Or, if the pre-defined roles that come with each installation of SQL Server 7.0 meet your needs, you can use those roles instead of or in addition to the roles you've identified.
Assign Users
After you create the groups or roles, decide which users will belong to each. Users inherit the permissions assigned to each group or role they belong to. Because, in the SQL Server 6.5 environment, each user account can belong to only two groups, Public (mandatory participation) and one other, permission assignments can be tricky. If one person in your organization does many things or works across departments, you could create a group of one. But the usual practice is to attach such people to an existing group that has the kinds of permissions they need.
A second solution in SQL Server 6.5 is to create multiple logins for the users, one for each function, and assign each to the appropriate group. Remem-ber that a login gives an individual or a process access to the SQL Server environment; a user account gives access to each database and is the object you attach the permissions to in both SQL Server 6.5 and 7.0. A SQL Server 6.5 login is mapped to one user account. You can use the sp_adduser command to assign users to SQL Server 6.5 groups when you create the user account.
In the SQL Server 7.0 environment, you can give a single login membership in as many roles, fixed or user-defined, as necessary. Use the sp_addrolemember command to assign SQL Server 7.0 user logins to roles.
Map to Objects, Grant Permissions
You can use the security decision matrix to plan the permissions that you'll grant to each group or role. The permission types appear across the top of each table grid. The user can use the Select, Insert, Update, and Delete permissions to retrieve and modify data stored in the specified database object (a table or view). Execute is the only kind of permission you can grant on a stored procedure. The Execute permission lets the user execute stored procedures attached to the database object.
The References permission has two major functions. It lets the owner of one database object (e.g., a table) reference data stored in a table another user owns. And the References permission lets a user insert rows into a table that has an enforced relationship with another table that the user has no permissions on. When a user inserts a row into a table that has a foreign-key constraint, SQL Server must validate the data in the foreign-key column with the data in the referenced table. If this user doesn't have Select permission on the referenced table and the tables have different owners, the insert operation fails. You can avoid this situation by giving the user References permission on the referenced table.
In the security decision matrix, all groups have Select permission on the Titles, Authors, and TitleAuthor tables, but only the InfoSystems and Developers groups have all permissions—Select, Insert, Update, Delete, and References on all tables. The Managers and Human-Resources groups can modify the Authors data; Managers and EditorialStaff can modify the Titles and TitleAuthor data. In the Stored Procedures grid, the InfoSystems and Developers groups have Execute permissions on all stored procedures, and Human-Resources and Managers can execute the ByRoyalty stored procedure. But only Managers can execute the quarterly reports stored procedures (e.g., reptq1).
A decision matrix is handy because you can take it to meetings and discuss it with people who understand the business but not the technology or programming statements. When you have completed the matrix, you can use it as a guide when you write the SQL scripts for granting (and revoking) permissions. (You use the GRANT command to assign permissions to groups and roles; use the REVOKE command to remove permissions already granted.) Or you can use the matrix as a guide when you assign permissions through the Enterprise Manager. The security decision matrix is a useful tool for administering and documenting security in your SQL Server environment.
Permission Idiosyncrasies
Each database has a permissions scheme that is independent of the permissions of the other databases on the same server. In SQL Server 6.5, permissions assigned to an individual add to the permissions given to a group the individual belongs to. Here's an example: Let's say that you've granted SELECT permission on the Authors table to the EditorialStaff group. Everyone in this group can write SELECT statements and see the data in the Authors table. One user in this group, LSimpson, is responsible for data maintenance on the Authors table. You can grant her permission to insert, update, and delete on the Authors table. In SQL Server 6.5, the modification permissions you give to LSimpson add to the Select permission that she has as part of the EditorialStaff group. LSimpson now has Select, Insert, Update, and Delete permissions on the Authors table, but the rest of her group has only Select permission on that table.
On the other end of the spectrum, everyone in the HumanResources group has Select, Insert, Update, and Delete permissions on the Authors table. You want to prevent one user in that group, KKimble, from making any modifications to the data, but still let him select from the table. You can revoke the Insert, Update, and Delete permissions from KKimble, who then can only select from the Authors table. The rest of the HumanResources group can still select, insert, update, and delete. In SQL Server 6.5, you can revoke only the permissions that are already granted, and individual permissions override group permissions. Therefore, if permission conflicts occur between a group and an individual member, the most restrictive permission takes precedence.
In the SQL Server 7.0 environment, permissions are also cumulative. You can grant a set of permissions directly to individuals, and they inherit the permissions from all the roles they have membership in. However, in SQL Server 7.0, you can deny someone a permission (by using the DENY command) even before you've granted any permissions. So you can deny KKimble the Insert, Update, and Delete permissions on the Authors table, and then no matter how many groups he's a member of, he won't get these permissions reinstated. If you deny users permission on an object, that denial overrides any role permissions the users might inherit, even after the denial. In SQL Server 7.0, users can perform an action on an object if you've granted them the permission directly or if they're members of a role that has been granted permission, as long as you haven't denied them permission, either directly or indirectly (through role membership).
You can use the security decision matrix to help determine which groups, roles, or users should have what kinds of permissions on which database objects. This matrix is a planning and documentation tool that helps guide you when you write permission scripts to manage database security.