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.
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.
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.
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.
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).
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 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.
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 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.
Object Type | Possible Actions |
table | SELECT, UPDATE, DELETE, INSERT, REFERENCE |
column | SELECT, UPDATE |
view | SELECT, UPDATE, INSERT, DELETE |
stored procedure | EXECUTE |
Figure 11.3.
Granting object permissions using the Object Permissions dialog box.
Figure 11.4.
Granting object permissions by user.
Revoking Object Permissions Perform the following steps to revoke object permissions:
Figure 11.5.
Revoking object permissions by object.
Figure 11.6.
Revoking object permissions by user.
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.
The following command syntax can also be used to manage object permissions.
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]
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.
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]
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.
sp_helprotect objectname [, username]
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:
Figure 11.9.
Granting statement permissions.
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:
Figure 11.10.
Revoking statement permissions.
Corresponding Transact SQL Commands to Manage Statement Permissions The following command syntax can also be used to manage statement permissions.
GRANT {ALL | statement_list}
TO {PUBLIC | name_list}
REVOKE {ALL | statement_list}
FROM {PUBLIC | name_list}
sp_helprotect username
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:
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 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 2The 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.
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.
Following are some important notes to remember when managing security:
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.
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.