Chapter 11
Managing SQL Server Security





by Orryn Sledge

SQL Server provides built-in security and data protection. Its security features are trustworthy and relatively easy to administer. By taking advantage of SQL Server's security features, you can create a secure database that prevents unauthorized access and allows data modification to occur in a controlled and orderly manner.

Levels of Security

The term security is a broad term that carries different meanings depending on how it is applied. It can be applied to the following levels (see Figure 11.1):

When dealing with security, you spend the majority of your time working at the database and object level. Therefore, the remainder of this chapter concentrates on database and object security.

Figure 11.1.
The four levels of security.

Security Hierarchy

SQL Server's security mechanism is hierarchically based. Within the hierarchy exists four types of users: the system administrator, database owners, database object owners, and other users of the database (see Figure 11.2).

Figure 11.2.
Security hierarchy.

System Administrator

The system administrator (login ID sa) is "the almighty one" who has unrestricted access to SQL Server. Any SQL Server statement or command can be executed by the sa. The sa can also grant permissions to other users.

Database Owners (DBO)

The database owner ( DBO) is the user who created the database or has had ownership assigned to him or her. The DBO has complete access to all objects within his or her database and has the ability to assign object permissions to other users.


TIP: To determine the owner of a database, use sp_helpdb [database name] or double-click the database name in the Enterprise Manager (refer to Chapter 9, "Managing Databases," for more information on managing databases).

Database Object Owners

The person who creates the database object is considered the owner of the object and is called the database object owner. SQL Server assumes that if you have the necessary permission to create the object, you are automatically given all permissions to that object (SELECT, UPDATE, INSERT, DELETE, REFERENCE, and EXECUTE). With the exception of the database object owner and the sa, no one else can access an object until the appropriate permission is granted.


TIP: Use sp_help [object name] to determine the owner of an object within a database. There is no SQL Server command to transfer ownership of an object within a database. To get around this limitation, the sa or existing database object owner must drop the object and the new database object owner must re-create the object. To simplify object access, the DBO should create all objects within the database. This automatically makes the DBO the database object owner.

Other Users

Other users must be granted object permissions (SELECT, UPDATE, INSERT, DELETE, REFERENCE, and EXECUTE) to operate within the database. The system administrator can also grant statement permissions to other users so that they can create and drop objects within the database.

Permissions

A permission allows someone to do something within a database. There are two types of permissions: object and statement. As a DBA, you will probably spend more time with object permissions. Object permissions control who can access and manipulate data in tables and views and who can run stored procedures. Statement permissions control who can drop and create objects within a database.

SQL Server uses the commands GRANT and REVOKE to manage permissions.

Object Permissions

Object permissions control access to objects within SQL Server. You can grant and revoke permissions to tables, table columns, views, and stored procedures through the Enterprise Manager or through system procedures. When a user wants to perform an action against an object, he or she must have the appropriate permission. For example, when a user wants to SELECT * FROM table1, he or she must have SELECT permission for the table. Table 11.1 summarizes the different types of object permissions.

Table 11.1. Summary of object permissions.
Object Type Possible Actions
table SELECT, UPDATE, DELETE, INSERT, REFERENCE
column SELECT, UPDATE
view SELECT, UPDATE, INSERT, DELETE
stored procedure EXECUTE


Granting Object Permissions Perform the following steps to grant object permissions:

  1. From the Enterprise Manager, access the Server Manager dialog box, select a server, open the Databases folder, select a database, and select the Objects folder.
  2. From the Object menu, select Permissions.
  3. Select the By Object tab or the By User tab. The By Object tab allows you to select an object and manage each user's permissions to the object. The By User tab allows you to select a user or group and manage the user's or group's permissions to several objects.
  4. If you selected the By Object tab:
  5. If you selected the By User tab:

  6. If you selected the By Object tab:

  7. If you selected the By User tab:

  8. For either the By Object tab or By User tab:

  9. For either the By Object tab or the By User tab:

  10. Click the Set button to commit any changes that have been made.

Revoking Object Permissions Perform the following steps to revoke object permissions:

  1. From the Enterprise Manager, access the Server Manager dialog box, select a server, open the Databases folder, select a database, and select the Objects folder.

  2. From the Object menu, select Permissions.

  3. Select the By Object tab or the By User tab. The By Object tab enables you to select an object and manage each user's permissions to the object. The By User tab enables you to select a user or group and manage the user's or group's permissions to several objects.

  4. If you selected the By Object tab:

  5. If you selected the By User tab:

  6. From the User/Group list, select a user or group to work with. After you select the user or group, a list of objects and their corresponding permissions is displayed (see Figure 11.6).

    Figure 11.6.
    Revoking object permissions by user.

  7. If you selected the By Object tab:

  8. If you selected the By User tab:

  9. For either the By Object tab or the By User tab:

  10. For either the By Object tab or the By User tab:

  11. Click the Set button to commit any changes that have been made.

Tips For Managing Object Permissions Use the following tips to help manage object permissions:

Figure 11.7.
Viewing permissions through object filters (table filter).


Figure 11.8.
Viewing permissions through object filters (stored-procedure filter).

SETUSER [`username' [WITH NORESET]]

SELECT user_name()


Permission Differences between Versions 6.0 and 6.5
When assigning and revoking permission in version 6.0, it was important to keep in mind the order in which the assignment occured. The order in which permissions were assigned determined a user's access to an object. If the order of permission assignment was not properly followed, it would often lead to undesirable results. Consider the following: Grant the SELECT permission to the authors table for Joe. Revoke the SELECT permission to the authors table from the public group (by default, Joe is a member of the public group). In version 6.0, when Joe tried to issue a SELECT against the authors table, he was denied access because the SELECT permission was revoked from the public group after the SELECT permissions were granted to Joe. In version 6.5, Joe can still access the authors table because the SELECT permission granted in step 1 still allows Joe access to the object.

Corresponding Transact SQL Commands to Manage Object Permissions

The following command syntax can also be used to manage object permissions.

To add object permissions (version 6.5):

GRANT {ALL [column_list]| permission_list [column_list]}
ON {table_name [(column_list)] | view_name [(column_list)] |
stored_procedure_name} TO {PUBLIC | name_list } [WITH GRANT OPTION]

To add object permissions (version 6.0):

GRANT {ALL | permission_list}
ON {table_name [(column_list)] | view_name [(column_list)] |
stored_procedure_name | extended_stored_procedure_name}
TO {PUBLIC | name_list}


NOTE: In SQL Server version 6.5, the GRANT statement has been enhanced to conform to ANSI-SQL standards. The GRANT statement now provides ANSI column-level grants (previous versions provided a non-ANSI standard syntax); through WITH GRANT OPTION, permissions can be granted to other users. For example, the following GRANT statement uses the ANSI column-level syntax to grant mary the permission to SELECT the au_id column in the authors table:

GRANT SELECT (au_id) ON authors to mary

Using non-ANSI SQL (versions 6.x, 4.2x), the following example grants mary the permission to SELECT the au_id column in the authors table (this example has the same effect as the preceding example, it just uses a different syntax):

GRANT SELECT ON authors(au_id) to mary

With version 6.5, you can now grant a permission to a user and through WITH GRANT OPTION, that user can in turn grant the permission to another user. For example, the following statement grants the SELECT permission for the authors table to mary:

GRANT SELECT ON authors to mary WITH GRANT OPTION

User mary can, in turn, grant the SELECT permission to user sam:

GRANT SELECT ON authors to sam

User sam is now granted the SELECT permission for the authors table. The GRANT enhancements in version 6.5 are available only when using the GRANT statement; they are not available when graphically managing security from the Enterprise Manager.


To remove object permissions (version 6.5):

REVOKE [GRANT OPTION FOR] {ALL | permission_list } [(column_list)]
ON { table_name [(column_list)] | view_name [(column_list)] |
stored_procedure_name} FROM {PUBLIC | name_list | role_name} [CASCADE]

To remove object permissions (version 6.0):

REVOKE {ALL | permission_list}
ON {table_name [(column_list)] | view_name [(column_list)] |
stored_procedure_name | extended_stored_procedure_name}
FROM {PUBLIC | name_list}


NOTE: In SQL Server version 6.5, the REVOKE statement has been enhanced to conform to ANSI-SQL standards. The REVOKE statement now provides the GRANT OPTION FOR and CASCADE syntax. When revoking a permission that was granted through the WITH GRANT OPTION, you must use the CASCADE option in conjunction with the REVOKE statement. These version 6.5 REVOKE enhancements are available only when using the REVOKE statement; they are not available when graphically managing security from the Enterprise Manager.

To view permissions by object or by user:

sp_helprotect objectname [, username]

Statement Permissions

Statement permissions control who can drop and create objects within a database. Only the sa or database owner can administer statement permissions. I advise prudence in granting access to statement permissions such as CREATE DATABASE, DUMP DATABASE, and DUMP TRANSACTION. Usually, it is better to let the sa or database owner manage these statements. Following is a list of statement permissions that can be granted or revoked:

Granting Statement Permissions
Perform the following steps to grant statement permissions:

  1. From the Enterprise Manager, access the Server Manager dialog box, select a server, and double-click a database.

  2. Select the Permissions tab. Existing permissions are indicated with checkmarks (see Figure 11.9).

    Figure 11.9.
    Granting statement permissions.

  3. Select the appropriate user and statement permission until a green check appears in the checkbox.

  4. Click OK to commit any changes that have been made.


NOTE: The CREATE DB permission can be granted only by the sa and only to users in the master database.

Revoking Statement Permissions Perform the following steps to revoke statement permissions:

  1. From the Enterprise Manager, access the Server Manager dialog box, select a server, and double-click a database.

  2. Select the Permissions tab. Existing permissions are indicated with checkmarks (see Figure 11.10).

    Figure 11.10.
    Revoking statement permissions.

  3. Select the appropriate user and statement permission until a red circle and slash appears in the checkbox.

  4. Click OK to commit any changes that have been made.

Corresponding Transact SQL Commands to Manage Statement Permissions The following command syntax can also be used to manage statement permissions.

To add statement permissions:

GRANT {ALL | statement_list}
TO {PUBLIC | name_list}

To remove statement permissions:

REVOKE {ALL | statement_list}
FROM {PUBLIC | name_list}

To view statement permissions by user or group:

sp_helprotect username

Beyond Security Basics: Suggested Strategies

In addition to object and statement permissions, you can combine different components within SQL Server to facilitate administration and provide improved security. Following is a list of suggested security strategies:

Group-Based Security Management

In the corporate environment, users often work in groups. People in these groups require similar permissions to the database. Whenever multiple users require similar permissions, you should use group-based security. With group-based security, you reduce the number of GRANT and REVOKE statements that must be maintained (refer to Chapter 10, "Managing Users," for more information on the creation and management of groups).

Before diving headfirst into group-based security management, you should keep in mind the following points:

Views for Data Security

Views help control data security for the following reasons:

Using Views for Column-Level Security Often, you use a view when a user needs access to a table but, for security reasons, you want to restrict access to certain columns (such as salary data) within the table. By using a view, you can easily restrict access to sensitive data.

Syntax:

CREATE VIEW [owner.]view_name
[(column_name [, column_name]...)]
[WITH ENCRYPTION]
AS select_statement [WITH CHECK OPTION]

The following listing shows the schema for the employee table:

employee_ssn char (9)
name char (35)
address char (35)
city char (35)
state char (35)
zip char (35)
salary money
last_updated_by char (50)
last_update_datetime datetime

For example, to prohibit access to the employee_ssn, salary, last_updated_by, and last_update_datetime columns in the employee table, use the following syntax:

CREATE VIEW employee_view AS
SELECT name, address, city, state, zip
FROM employee

When the user issues SELECT * FROM employee_view, he or she gets back only the following columns:

name
address
city
state
zip

To users, the view looks like a real table except that they never see the employee_ssn, salary, last_updated_by, and last_update_datetime columns. If they can't see it, they can't modify it. Using Views for Row-Level and Column-Level Security A simple way to implement row-level security is to add a WHERE clause to the CREATE VIEW statement. For example, use the following syntax to create a view that limits column and row access:

CREATE VIEW employee_view_by_state AS
SELECT name, address, city, state, zip
FROM employee
WHERE state = `VA' OR state = `MA'

When users issue this statement, they see only the employees with a state code of VA or MA:

SELECT * FROM employee_view_by_state


TIP: To further ensure data security and to prevent typing errors, you can add the WITH CHECK OPTION to the CREATE VIEW statement. The WITH CHECK OPTION is new to SQL Server 6.x. The WITH CHECK OPTION prevents users from inserting rows or updating columns that do not conform to the WHERE clause, as in the following example:

CREATE VIEW employee_view_by_state AS

SELECT name, address, city, state, zip

FROM employee

WHERE state = `VA' OR state = `MA'

WITH CHECK OPTION

With this view, users can only add rows with a VA or MA state code; they can only update a state code to MA or VA. If users try to change the state code to something other than VA or MA, they receive the following message:

Msg 550, Level 16, State 2

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view which specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

Command has been aborted.


How Views and Permissions Work Together When you grant object permissions to a view, you do not have to grant permissions to the underlying tables in the view. Therefore, users can SELECT employee data from the employee view, even though they do not have SELECT permission for the employee table. This feature can simplify administration when the view consists of multiple tables.


TIP: You may be wondering, "Why not use column-level permissions to prevent access to the employee_ssn, salary, last_updated_by, and last_update_datetime columns?" Good question! Both views and column-level permissions can prevent users from accessing restricted columns. The reason for using a view rather than column-level security is that the view allows a user to issue the SELECT * statement without receiving error messages while still providing column-level security. Consider the following examples:

Example A
: John's SELECT permission has been revoked from the employee_ssn column in the employee table. When John issues SELECT * FROM employee, he receives the following error message:

Msg 230, Level 14, State 1

SELECT permission denied on column employee_ssn of object employee, database xxx, owner dbo

To avoid the error message, John must explicitly name each column in the SELECT statement.

Example B: A view has been developed for John to use. The view does not include the employee_ssn column. John can issue a SELECT * statement against the view and he will see only the columns specified in the view. He does not receive any error messages.


Stored Procedures for Data Security

The advantage of using stored procedures to access and modify data is that users only need EXECUTE permission to run a stored procedure; they do not need access to the tables and views that make up the stored procedure. This alleviates the headache of assigning permissions to all underlying tables and views referenced within a stored procedure. The following syntax is an example of a stored procedure that returns all rows in the employee table:

CREATE PROCEDURE usp_employee AS
SELECT * FROM employee

To run the procedure, the user needs only EXECUTE permission for usp_employee. The user does not need the SELECT permission for the employee table.


Going to Extremes (But It May Be Worth It!)
You can really clamp down on end-user data modifications by implementing stored procedures to handle all data modifications. To implement this strategy, you must design your applications to use only stored procedures and not embedded SQL to handle data modifications. Next, you must revoke all UPDATE, DELETE, and INSERT (and maybe even SELECT) privileges to all tables and views in the database. End users are now denied access whenever they try to modify data. For this approach to be successful, it requires extensive use of stored procedures, careful planning, and tight coordination between the application developers and the DBA.

Triggers for Audit Trails Triggers are comprised of Transact SQL statements that automatically execute when a table is modified through INSERT, UPDATE, or DELETE statements. Because a trigger is automatically executed, it can be a useful facility for auditing data changes. Additionally, you do not have to grant a user the privilege to execute a trigger.

An often-used type of trigger is one that tracks who made the last change to a table and when the change occurred. To track this information, use the following syntax:

CREATE TRIGGER tiu_employee ON dbo.employee
FOR INSERT,UPDATE
AS
UPDATE employee
SET employee.last_updated_by = USER_NAME(),
employee.last_update_datetime = GETDATE()
FROM inserted,employee
WHERE inserted.employee_ssn = employee.employee_ssn

Whenever an INSERT or UPDATE statement is run against the employee table in this example, the column last_updated_by is set to the name of the user who made the change and the column last_update_datetime is set to the time the change was made.


CAUTION: BCP bypasses triggers! Therefore, any audit trail that is maintained through a trigger must be manually updated after using BCP.

Between the Lines

Following are some important notes to remember when managing security:

Summary

You now should have a good understanding of object and database security. By having a solid understanding of SQL Server's security architecture, you can lock down your data and ward off unauthorized data changes. In the next chapter, you discover how to implement data replication.


DISCLAIMER


To order books from QUE, call us at 800-716-0044 or 317-361-5400.

For comments or technical support for our books and software, select
Talk to Us.

© 1997, QUE Corporation, an imprint of
Macmillan Publishing USA, a Simon and Schuster Company.