
	About Us
	SSWUG Articles
	Articles

	Administering
	Comparison
	General
	Know How
	Replication
	Tuning
	Undocumented
	UDF
	SQL 6.5

	FAQ
	Scripts
	Tips
	Test Exams
	Advertise
	Download
	History
	Search
	Traffic
	Related Links


Add to Favorites

Managing Users Permissions

Alexander Chigrik
chigrik@mssqlcity.com

Introduction
Permissions Types
Object Permissions
Statement Permissions
Implied Permissions
Managing Permissions
Enhancing Security Using Views
Using Stored Procedures to Enhance Security
Permissions Intersection

Introduction

Permissions are the rights to access the database objects. Permissions can be granted to a user or role to allow that user or role to perform operations such as selection, insertion, or modification of data rows.

Each database object has an owner. By default, the owner is the creator of an object, but the ownership can be transferred later after the object has been created. In addition to the owner, the members of the sysadmin fixed server roles have full permissions on all objects in all user and system databases.

There is also a public role. The public role is a special database role to which each database user belongs. The public role contains default access permissions for any user who can access the database. This database role cannot be dropped, but it is strongly recommended to not grant superfluous permissions to the public role, because each databases user has the public role's permissions.

SQL Server 2000 provides the GRANT, DENY, and REVOKE statements to give or take away permissions from a user or role.

Permissions Types

To perform any activity in a database, user must have the appropriate permissions. These permissions fall into three categories, which we call permissions types:

Permissions to work with data and execute procedures (object permissions).

Permissions to create a database or an item in the database (statement permissions).

Permissions to utilize permissions granted to predefined roles (implied permissions).

SQL Server 2000 supports granting or revoking user rights to the following permissions types:

Object Permissions

The object permissions are the permissions to act on the database objects (such as tables, stored procedures, and views). They consist of the following permissions:

SELECT
Enables a user to select or read data from a table or view. The SELECT permission can be applied to individual columns within a table or view, and may be applied to user-defined functions.

INSERT
Enables a user to insert new data to a table or view.

DELETE
Enables a user to delete data from a table or view.

UPDATE
Enables a user to update data in a table or view. The UPDATE permission can be applied to individual columns within a table or view, not just the entire table.

EXECUTE
Enables a user to execute a stored procedure.

DRI (declarative referential integrity)
Enables a user to add foreign key constraints on a table.

Statement Permissions

These are the permissions to create a database or an object in the database. These permissions are applied to the statement itself, rather than to a specific object defined in the database. They consist of the following permissions:

BACKUP DATABASE
The BACKUP DATABASE statement uses to backing up an entire database or one or more files or filegroups.

BACKUP LOG
The BACKUP LOG statement uses to backing up the transaction log.

CREATE DATABASE
The CREATE DATABASE statement uses to create a new database and the files used to store the database.

CREATE DEFAULT
The CREATE DEFAULT statement uses to create an object called a default.

CREATE FUNCTION
The CREATE FUNCTION statement uses to create a user-defined function, which is a saved Transact-SQL routine that returns a value.

CREATE PROCEDURE
The CREATE PROCEDURE statement uses to create a stored procedure, which is a saved collection of Transact-SQL statements.

CREATE RULE
The CREATE RULE statement uses to create an object called a rule.

CREATE TABLE
The CREATE TABLE statement uses to create a new table.

CREATE VIEW
The CREATE VIEW statement uses to create an object called a view.

Implied Permissions

These are the permissions granted to the predefined roles (such as fixed server roles or fixed database roles). For example, a member of the db_owner fixed database role has all permissions in the database.

Managing Permissions

You can use the GRANT, DENY, and REVOKE statements to give or take away permission from a user or role.

The GRANT statement is used to give permissions to a user or role. By using the GRANT statement, it is possible to assign permissions to both statements as well as objects. You can use the GRANT statement with the WITH GRANT OPTION clause to permit the user or role receiving the permission to further grant/revoke access to other accounts.

This example grants the SELECT permission on the authors table to Alex

GRANT SELECT ON authors TO Alex

The DENY statement is used to deny a permission from a security account in the current database and prevents the security account from inheriting the permission through its group or role memberships. You can use the DENY statement to deny both statements and objects permissions.

The following example denies the user Alex SELECT permissions to the authors table:

DENY SELECT ON authors TO Alex

The REVOKE statement is used to remove a previously granted or denied permission from a user in the current database. You can use the REVOKE statement to remove both statements and objects permissions. You can specify the GRANT OPTION FOR clause with the REVOKE statement to remove the WITH GRANT OPTION permissions. Therefore, the user will have the objects permissions, but cannot grant the permissions to other users. Specify the CASCADE clause along with the WITH GRANT OPTION clause, if the permissions being revoked were originally granted using the WITH GRANT OPTION setting.

The following example revokes SELECT permissions to the authors table from the Alex:

REVOKE SELECT ON authors TO Alex

Use the WITH GRANT OPTION setting very carefully, because in this case users can grant permissions to the objects to other users and it will be more difficult to manage security.

Don't grant the superfluous permissions to the public role, because each database user has the public role's permissions.

Enhancing Security Using Views

Views can be used to enhance security because permissions to access a view can be granted, denied, or revoked, regardless of the set of permissions to access the underlying table(s).

Views can also be used to limit the data that is available to a user. For example, one peace of data can be accessible to users for modifications while another piece of data can accessible to users for query, and the rest of the data is invisible and inaccessible.

Views can be used to restrict access to the following subsets of data:

A subset of the rows of a base table

A subset of the columns of a base table

A subset of combination of rows and columns of a base table

A subset of another view or some combination of views and tables

A statistical summary of data in a base table

For example, consider a scenario where you need to manage permissions to the employee table for the Accounting, Sales, and the Tech Windows 2000 groups, and each groups have their corresponding SQL Server roles namely: the Accounting, Sales, and Tech respectively. Let's say the employee table was created using the following CREATE TABLE statement:

CREATE TABLE employee(
   EmployeeId INT NOT NULL PRIMARY KEY,
   LName VARCHAR(30) NOT NULL,
   FName VARCHAR(30) NOT NULL,
   Address VARCHAR(100) NOT NULL,
   HireDate DATETIME NOT NULL,
   Salary MONEY NOT NULL
)

Let's say you need to assign the Accounting role access to query the entire employee table, the Sales role to access only the LName, FName, and HireDate columns of the employee table, and the Tech role should not have any access either the view or the employee table itself. This can be achieved using the following script:

-- grant select permission on the employee table to the Accounting role
GRANT SELECT ON employee TO Accounting
GO

-- deny select permission on the employee table from the Tech and Sales role
-- to ensure these roles do not have select permission on the employee table
DENY SELECT ON employee TO Sales, Tech
GO

-- create the view that limits access the employee table to the Sales role
CREATE VIEW employee_view
AS
SELECT LName, FName, HireDate
FROM employee
GO

-- grant select on the employee_view to the Sales group
GRANT SELECT ON employee_view TO Sales
GO

Using Stored Procedures to Enhance Security

Stored procedures can be used to enhance security in much the same way as views. The permissions to execute a stored procedure can be granted, denied, or revoked instead of granting permissions on the underlying data objects. Stored procedures can be used to conceal the underlying data objects too. For example, you can give a user only the permission to execute a stored procedure and the user will not know anything about underlying data objects. By using stored procedures, you can also limit the data that is available to a user. You can give the users only permission to execute a stored procedure to work with the restricted set of the columns and rows instead of querying the entire table.

For example, the Salary and the Address columns in the employee table contain confidential employee information and should be available to only the members of the Accounting database role, but the rest of the columns contain information that should be available to all database users.

This script shows how the above security task can be achieved:

-- grant select permission on the employee table to the Accounting
-- role
GRANT SELECT ON employee TO Accounting
GO

-- create the stored procedure that hides
-- Salary and the Address columns in the employee table
CREATE PROCEDURE employee_proc
AS
SELECT LName, FName, HireDate
FROM employee
GO

-- grant select permission on the employee_proc to the public role
GRANT EXECUTE ON employee_proc TO public
GO

Because each database user has the public role permission, you can grant the desirable permission to the public role if you need to grant this permission to all database users. If the business logic of your application allows some users to update values in the several columns in the table without having permission to update anything else on the table, you can also use a stored procedure.

For example, any members of the Accounting database role are allowed to update the Salary column in the employee table without having permission to update other columns. The employee table was created using the following CREATE TABLE statement:

The following script shows how the above security task can be achieved:

-- deny UPDATE, DELETE and INSERT permissions
-- on the employee table to the Accounting role
-- to ensure this role does not have these permissions
DENY UPDATE, DELETE, INSERT ON employee TO Accounting
GO

-- create the stored procedure that modify the Salary
-- column on the employee table for the employee passed
CREATE PROCEDURE employee_proc_upd
  @EmployeeID int,
  @Salary money
AS
UPDATE employee
SET Salary = @Salary
WHERE EmployeeID = @EmployeeID
GO

-- grant execute on the employee_proc_upd to the Accounting role
GRANT EXECUTE ON employee_proc_upd TO Accounting
GO

Permissions Intersection

Under the permissions intersection we understand the results permission, which a user will have when the different permissions were granted or revoked to this user or (and) for the roles to which this user belong. The permissions conflict can arise, if the user is the member of the several roles with different permissions to access the object.

As we described above, the permissions can be granted, revoked, or denied.

The GRANT statement removes the denied or revoked permission at the level granted, so the denied permission at another level still applies. For example, if you need to allow the user Alex to select the employee table and the select permissions were denied to both user Alex and Accounting role to which the Alex belong, you can run the following statement:

GRANT SELECT ON employee TO Alex, Accounting

A denied permission overrides all other permissions and always takes precedence. For example, a user belongs to two roles: Accounting and Technology. The Accounting role gives the user select, insert, delete, and update permissions. The Technology role provides select but denies permissions for insert, delete, and update. The result is the user only can select regardless that the user belongs to the Accounting role. You can deny permissions to the public role, if you need to prevent anyone from accessing an object. The results of using the DENY statement against the database object looks like the results of granting the 'No Access' permission to the Windows NT user account.

The REVOKE statement is used to remove a previously granted or denied permission at the level revoked, so the same permission granted or denied at another level still applies. For example, if you need to prevent the user Alex to select the employee table and the select permissions were granted to both user Alex and Accounting role to which the Alex belong, you can run one of the following statements:

REVOKE SELECT ON employee FROM Alex, Accounting
DENY SELECT ON employee TO Alex
DENY SELECT ON employee TO Accounting

Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2005 Bits on the Wire, Inc