Home

	Expand Search

Security Features User's Guide

Chapter 4: Granting Permissions on Objects and Commands

Chapter 4

This chapter discusses discretionary access controls, which are used at the discretion of an object's owner. They are "discretionary" because an object owner can choose to allow or disallow access to the object.

Discretionary access permissions are controlled mainly through the use of the grant and revoke commands. This chapter provides database object owners with the information they need to grant and revoke privileges on their objects to other users.

This chapter discusses:

• Types of permissions assigned to users and groups
• How to grant and revoke permissions
• How to use views and stored procedures as security mechanisms

Assigning Permissions to Users

The SQL commands grant and revoke control SQL Server's command and object protection system. You can give various kinds of permissions to users, groups, and roles with the grant command and rescind them with the revoke command. grant and revoke are used to give users permission to:

• Create databases
• Create objects within a database
• Access tables, views, and columns
• Execute stored procedures

Some commands can be used at any time by any user, with no permission required. Others can be used only by users of certain status (for example, only by a System Administrator) and are not transferable.

The ability to assign permissions for the commands that can be granted and revoked is determined by each user's status (as System Administrator, Database Owner, or database object owner) and by whether or not a particular user has been granted a permission with the option to grant that permission to other users.

The Database Owner does not automatically receive permissions on objects owned by other users. But a Database Owner or System Administrator can always acquire any permission by assuming the identity of the object owner with the setuser command and then writing the appropriate grant or revoke statements.

You can use grant and revoke to assign two kinds of permissions: object access permissions and object creation permissions. These are discussed in "Object Access Permissions" and in "Object Creation Permissions".

This chapter focuses on the aspects of the permissions system that are of interest to non-administrative users, including the owners of database objects. For more information about permissions, see Chapter 6, "Managing User Permissions" in the SQL Server Security Administration Guide, and "grant" and "revoke" in the SQL Server Reference Manual.

Object Access Permissions

Object access permissions regulate the use of certain commands that access certain database objects. For example, you must explicitly be granted permission to use the select command on the authors table. Object access permissions are granted and revoked by the owner of the object, who can grant them to other users.

Table 4-1 lists the types of object access permissions and the objects to which they apply:

Table 4-1: Permissions and the objects to which they apply

Permission	Object
select	table, view, column
update	table, view, column
insert	table, view
delete	table, view
references	table, column
execute	stored procedure

grant and revoke Syntax: Object Access Permissions

Here is the full syntax for granting and revoking object access permissions:

grant {all | permission_list} 
on {table_name [(column_list)] | 
   view_name [(column_list)] | 
   stored_procedure_name} 
to {public | name_list | role_name} 
[with grant option]

revoke [grant option for]
{all | permission_list}
on {table_name [(column_list)] | 
   view_name [(column_list)] | 
   stored_procedure_name} 
from {public | name_list | role_name} 
[cascade]

You can include more than one command in permission_list. Separate the commands with commas.

The parameters of the grant and revoke commands are as follows:

• If you use the keyword all in the statements for object access permissions, every one of the permissions applicable to the object is granted or revoked.
• The contents of permission_list varies according to the type of object on which you are granting permissions:
  • When you use the grant or revoke command to assign permissions on a table or view, permission_list can consist of any combination of select, insert, delete, references, and update.
  • When you grant permissions on columns in a table, permission_list can include select or update or both. To use select *, you must have select permission on all columns in the table.
  • When you are granting permissions on stored procedures, permission_list can include execute only.
• The on clause specifies the object on which you are granting or revoking permission. You can grant or revoke privileges on tables, views, and stored procedures for only one object at a time. table_name, view_name, or stored_procedure_name is the name of the table, view, or stored procedure. You can grant privileges for more than one column at a time, but all the columns must be in the same table or view. column_list is a list of columns separated by commas. For example, to specify the price and total_sales columns of the titles table, enter:

  on titles(price, total_sales)

• Use with grant option to allow a specified user(s) to grant permissions to other users.
• The grant option for clause of the revoke command revokes grant permission from the specified user or users. If the user has granted permissions to other users, you must use the cascade option as well to revoke permissions from those users.
• The keyword public refers to the "public" group, which includes all users of SQL Server. public means slightly different things for grant and revoke:
  • For grant, public includes you, the object owner. Therefore, if you have revoked permissions from yourself on your object, and later you grant permissions to public, you regain the permissions along with the rest of "public".
  • For revoke on object access permissions, public excludes the owner. If a user has with grant option permission on an object, that permission is not revoked when permissions on the object are revoked from public.
• The name_list includes the names of:
  • Groups
  • Users
  • A combination of users and groups with each name separated from the next by a comma
• The role_name is the name of a SQL Server role. This allows you to grant permissions to all users who have been granted a specific role. The roles are sa_role (System Administrator), sso_role (System Security Officer), and oper_role (Operator).
• The with grant option clause in a grant statement specifies that the user(s) specified in name_list can grant the specified object access permission(s) to other users. If a user has with grant option permission on an object, that permission is not revoked when permissions on the object are revoked from public or from a group to which the user belongs.
• The cascade option in a revoke statement removes the specified object access permissions from the user(s) specified in name_list and also from any users they granted those permissions to.

Special Requirements for Compliance to SQL92 Standards

When you have used the set command to turn ansi_permissions to "on", additional permissions are required for update and delete statements. The following table summarizes the required permissions.

Table 4-2: ANSI permissions for update and delete

	Permissions Required: set ansi_permissions off	Permissions Required: set ansi_permissions on
update	update permission on columns where values are being set	update permission on columns where values are being set and select permission on all columns appearing in where clause select permission on all columns on the right side of the set clause
delete	delete permission on the table	delete permission on the table from which rows are being deleted and select permission on all columns appearing in the where clause

If ansi permissions is set to "on" and you attempt to update or delete without having the additional required select permissions, the transaction is rolled back and you receive an error message. If this occurs, the column owner must grant you select permission on all relevant columns.

Examples: Object Access Permissions

The following statement gives Mary and the "sales" group permission to insert into and delete from the titles table:

grant insert, delete 
on titles 
to mary, sales

The following statement gives Harold permission to use the stored procedure makelist:

grant execute
on makelist
to harold

The following statement grants permission to execute the stored procedure sa_only_proc to users who have been granted the System Administrator role:

grant execute
on sa_only_proc
to sa_role

The following statement gives Aubrey permission to select, update, and delete from the authors table and to grant the same permissions to other users:

grant select, update, delete 
on authors
to aubrey
with grant option

Both of the following statements revoke permission for all users except the table owner to update the price and total_sales columns of the titles table:

revoke update 
on titles (price, total_sales) 
from public

revoke update(price, total_sales)
on titles
from public

The following statement revokes permission from Clare to update the authors table and simultaneously revokes that permission from all users to whom she had granted that permission:

revoke update
on authors
from clare
cascade

The following statement revokes permission from Operators to execute the stored procedure new_sproc:

revoke execute
on new_sproc
from oper_role

Object Creation Permissions

Object creation permissions regulate the use of commands that create objects. These permissions can be granted only by a System Administrator or a Database Owner.

The commands to which the object creation permissions apply are:

create database
create default
create procedure
create rule
create table
create view

Each database has its own independent protection system. In other words, being granted permission to use a certain command in one database has no effect in other databases.

grant and revoke Syntax: Object Creation Permissions

The syntax for granting and revoking these permissions is:

grant {all | command_list} 
to {public | name_list | role_name} 

revoke {all | command_list} 
from {public | name_list | role_name} 

The parameters for these commands are used as follows:

• The command_list is a list of the object creation permissions that you are granting or revoking. If more than one command is listed, separate them with commas. The command list can include create database, create default, create procedure, create rule, create table, and create view. create database permission can be granted only by a System Administrator, and only from within the master database.
• all can be used only by a System Administrator or the Database Owner. When used by a System Administrator in the master database, grant all assigns all create permissions, including create database. If the System Administrator executes grant all from another database, all create permissions are granted except create database. When a Database Owner who is not a System Administrator uses grant all, SQL Server grants all create permissions except create database and prints an informational message.
• The keyword public refers to the group "public", which includes all users of SQL Server. public means slightly different things for grant and revoke:
  • For grant, public includes the Database Owner. Therefore, if you have revoked permissions from yourself for a command, and later you grant permissions to public, you regain the permissions along with the rest of "public".
  • For revoke, public excludes the Database Owner.
• The name_list is a list of the names of:
  • Groups
  • Users
  • A combination of users and groups with each name separated from the next by a comma
• The role_name is the name of a SQL Server role. This allows you to grant permissions to all users who have been granted a specific role. The roles are sa_role (System Administrator), sso_role (System Security Officer), and oper_role (Operator).

Examples: Object Creation Permissions

The following examples show how to grant and revoke object creation permissions.

This example grants permission to Mary, Jane, and Bob to create tables and views in the current database:

grant create table, create view 
to mary, jane, bob

This example grants permission to the "admin" group to create stored procedures:

grant create procedure
to admin

This example revokes permission from Mary to create tables and rules in the current database:

revoke create table, create rule 
from mary 

grant and Roles

You can use the grant command to grant permission on objects to all users who have been granted a specified role. This allows you to restrict use of an object to users who are System Administrators, System Security Officers, or Operators.

However, grant permission does not prevent users who do not have the specified role from being granted execute permission on a stored procedure. If you want to ensure, for example, that only System Administrators can successfully execute a stored procedure, you can use the proc_role system function within the stored procedure itself. It checks to see whether the invoking user has the correct role to execute the procedure. See Chapter 5, "Roles in SQL Server," in the Security Administration Guide for more information.

Permissions granted to roles override permissions granted to users or groups. For example, assume John has been granted the System Security Officer role, and sso_role has been granted permission on the sales table. If John's individual permission on sales is revoked, he is still able to access sales when he has sso_role active because his role permissions override his individual permissions.

Combining grant and revoke Statements

grant and revoke statements are order-sensitive: in case of a conflict, the most recently issued statement supersedes all others.

There are two basic styles of setting up permissions in a database or on a database object. The most straightforward style is to assign specific permissions to specific users.

However, if most users are going to be granted most privileges, it's easier to assign all permissions to all users and then revoke specific permissions from specific users.

For example, a Database Owner can grant all permissions on the titles table to all users by issuing the following statement:

grant all 
on titles 
to public 

Then the Database Owner can issue a series of revoke statements, for example:

revoke update 
on titles (price, advance) 
from public 

revoke delete 
on titles 
from mary, sales, john 

Conflicting grant and revoke Statements

As mentioned in the previous section, grant and revoke statements are sensitive to the order in which they are issued. For example, if Jose's group has been granted select permission on the titles table and then Jose's permission to select the advance column has been revoked, Jose can select all the columns except advance, while the other users in his group can still select all the columns.

A grant or revoke statement that applies to a group or role changes any conflicting permissions that have been assigned to any member of that group or role. For example, if the owner of the titles table has granted different permissions to various members of the sales group, and wants to standardize, he or she might issue the following statements:

revoke all on titles from sales 
grant select on titles(title, title_id, type,
        pub_id) 
    to sales 

Similarly, a grant or revoke statement issued to public will change, for all users, all previously issued permissions that conflict with the new regime.

The same grant and revoke statements issued in different orders can create entirely different situations. For example, the following set of statements leaves Jose, who belongs to the public group, without any select permission on titles:

grant select on titles(title_id, title) to jose 
revoke select on titles from public 

In contrast, the same statements issued in the opposite order result in only Jose having select permission and only on the title_id and title columns:

revoke select on titles from public 
grant select on titles(title_id, title) to jose 

Remember that when you use the keyword public with grant, you are including yourself. With revoke on object creation permissions, you are included in public unless you are the Database Owner. With revoke on object access permissions, you are included in public unless you are the object owner. You may wish to deny yourself permission to use your own table, while giving yourself permission to access a view built on it. To do this you must issue grant and revoke statements explicitly setting your permissions. (You can always change your mind and reinstitute the permission with a grant statement.)

Getting Information about Permissions

These system procedures provide information about permissions:

• sp_helprotect reports on permissions on database objects or users.
• sp_column_privileges reports on permissions on specific columns in a table.
• sp_table_privileges reports on permissions on a specific table.

sp_helprotect

The system procedure sp_helprotect reports on permissions by database object or by user, and (optionally) by user for a specified object. Any user can execute this procedure. Its syntax is:

sp_helprotect name [, name_in_db [, "grant"]]

• The first parameter, name, is either the name of the table, view, or stored procedure; or the name of a user, group, or role in the current database.
• If you specify the second parameter, name_in_db, only that user's permissions on the specified object are reported. If name is not an object, sp_helprotect checks whether name is a user, group, or role. If so, the permissions for the user, group, or role are listed.
• If you specify the third parameter, the keyword grant, and name is not an object, sp_helprotect displays all permissions granted by with grant option.

For example, suppose you issue the following series of grant and revoke statements:

grant select on titles to judy 
grant update on titles to judy 
revoke update on titles(contract) from judy 
grant select on publishers to judy
    with grant option

To determine the permissions Judy now has on each column in the titles table, type:

sp_helprotect titles, judy

The following display results:

grantor grantee type   action   object     column      grantable 
------- ------ -----   ------   ------     ------      ------- 
dbo     judy   Grant   Select   titles     All         FALSE 
dbo     judy   Grant   Update   titles     advance     FALSE 
dbo     judy   Grant   Update   titles     notes       FALSE
dbo     judy   Grant   Update   titles     price       FALSE
dbo     judy   Grant   Update   titles     pub_id      FALSE 
dbo     judy   Grant   Update   titles     pubdate     FALSE 
dbo     judy   Grant   Update   titles     title       FALSE 
dbo     judy   Grant   Update   titles     title_id    FALSE 
dbo     judy   Grant   Update   titles     total_sales FALSE 
dbo     judy   Grant   Update   titles     type        FALSE

The first row of the display shows that the Database Owner ("dbo") gave Judy permission to select all columns of the titles table. The rest of the lines indicate that she can update only the columns listed in the display. Judy's permissions are not grantable: she cannot give select or update permissions to any other user.

To see Judy's permissions on the publishers table, type:

sp_helprotect publishers, judy

In the display below, the grantable column indicates TRUE, meaning that Judy can grant the permission to other users.

grantor grantee type   action   object     column      grantable 
------- ------ -----   ------   ------     ------      ------- 
dbo     judy   Grant   Select   publishers all         TRUE

sp_column_privileges

sp_column_privileges is a catalog stored procedure that returns information about permissions on columns in a table. The syntax is:

sp_column_privileges table_name [, table_owner 
[, table_qualifier [, column_name]]]

• table_name is the name of the table. This is required.
• table_owner can be used to specify the name of the table owner, if it is not "dbo" or the user executing sp_column_privileges.
• table_qualifier is the name of the current database.
• column_name is the name of the column on which you want to see permissions information.
• Use null for parameters that you do not wish to specify.

For example, this statement:

sp_column_privileges publishers, null, null, pub_id

returns information about the pub_id column of the publishers table. See the SQL Server Reference Manual for more specific information about the output of sp_column_privileges.

sp_table_privileges

sp_table_privileges is a catalog stored procedure that returns permissions information about a specified table. The syntax is:

sp_table_privileges table_name [, table_owner 
[, table_qualifier]]

• table_name is the name of the table. It is required.
• table_owner can be used to specify the name of the table owner, if it is not "dbo" or the user executing sp_column_privileges.
• table_qualifier is the name of the current database.
• Use null for parameters that you do not wish to specify.

For example, this statement:

sp_table_privileges titles

returns information about all permissions granted on the titles table. See the SQL Server Reference Manual for more specific information about the output of sp_table_privileges.

Permissions on Views and Stored Procedures

Views and stored procedures can serve as security mechanisms. A user can be granted permission on a view or on a stored procedure even if he or she has no permissions on objects that the view or procedure references. For this to be possible, the view or stored procedure and its underlying objects must be owned by the same user. Otherwise, the person using the stored procedure or view must be granted object access permissions on the underlying objects as well as on the view or stored procedure.

SQL Server makes permission checks, as required, when the view or procedure is used. When you create the view or procedure, SQL Server makes no permission checks on the underlying objects. One exception to this occurs when you try to create a procedure that accesses objects in another database. If you do not have the required permissions for the database objects in the other database, SQL Server gives you an error message, and the procedure creation fails.

Views As Security Mechanisms

Through a view, users can query and modify only the data defined by the view. The rest of the database is neither visible nor accessible. Data in an underlying table that is not included in the view is hidden from users who are authorized to access the view but not the underlying table.

Permission to access the view must be explicitly granted or revoked, regardless of the set of permissions in force on the view's underlying tables. By defining different views and selectively granting permissions on them, a user (or any combination of users) can be restricted to different subsets of data. The following examples illustrate the use of views for security purposes:

• Access can be restricted to a subset of the rows of a base table (a value-dependent subset). For example, you might define a view that contains only the rows for business and psychology books, in order to keep information about other types of books hidden from some users.
• Access can be restricted to a subset of the columns of a base table (a value-independent subset). For example, you might define a view that contains all the rows of the titles table, but omits the price and advance columns, since this information is sensitive.
• Access can be restricted to a row-and-column subset of a base table.
• Access can be restricted to the rows that qualify for a join of more than one base table. For example, you might define a view that joins the titles, authors, and titleauthor tables in order to display the names of the authors and the books they have written. This view would hide personal data about authors and financial information about the books.
• Access can be restricted to a statistical summary of data in a base table. For example, you might define a view that contains only the average price of each type of book.
• Access can be restricted to a subset of another view, or of some combination of views and base tables.

As an example, you want to prevent some users from accessing the columns in the titles table that have to do with money and sales. You could create a view of the titles table that omits those columns, revoke permission from "public" to access the table, and then give all users permission on the view but only the Sales department permission on the table. Here's how:

grant all on bookview to public

revoke all on titles from public

grant all on titles to sales 

You can also set up these privilege conditions without using a view by typing:

grant all on titles to public 

revoke select, update on titles (price, advance,
   total_sales)
from public

grant select, update on titles (price, advance,
   total_sales) 
to sales 

One possible problem with the second scheme is that users not in the sales group who enter the command:

select * from titles

might be surprised to see the message that includes the phrase:

permission denied

SQL Server expands the asterisk into a list of all the columns in the titles table, and because permission on some of these columns has been revoked from non-sales users, SQL Server returns an error message. The message lists the columns for which the user does not have access.

In order to see all the columns for which they do have permission, the non-sales users would have to name them explicitly. For this reason, creating a view and granting the appropriate permissions on it is a better solution.

In addition to protecting data based on a selection of rows and/or columns, views can be used for context-sensitive protection. For example, you can create a view that gives a data entry clerk permission to access only those rows that he or she has added or updated. To do this, you would add a column to a table in which the user ID of the user entering each row is automatically recorded with a default. You can define this default in the create table statement like this:

create table testtable
    (empid       int, 
    startdate    datetime,
    username     varchar(30) default user)

Next, define a view that includes all the rows of the table where uid is the current user:

create view context_view 
as
    select * 
    from testtable 
    where username = user_name()
with check option

The rows that are retrievable through this view depend on the identity of the person who issues the select command against the view. By adding the with check option to the view definition, you make it impossible for any data entry clerk to falsify the information in the username column.

Stored Procedures As Security Mechanisms

A user with permission to execute a stored procedure can do so even if he or she does not have permissions on tables or views referenced in it, if the stored procedure and its referenced objects have the same owner. For example, a user might be given permission to execute a stored procedure that updates a row-and-column subset of a specified table even though that user does not have any direct permissions on that table.