grant

Description

Assigns permissions to individual users, groups of users, and roles. Assigns roles to users, or system- or user-defined roles.

Syntax

Grants permission to access database objects:

grant {all [privileges] | permission_list} 
	on {table_name [correlation_name]
		[(column_list)]
		| view_name[(column_list)] 
		| stored_procedure_name}
		| function_name
		| keyname}
	[where search_condition]
	[as pred_name]
	to {public | name_list | role_list}
	[with grant option]

Grants permission to use built-in functions:

grant select 
	on [builtin] builtin 
	to {name_list | role_list}

Grants permission to execute certain commands:

grant {all [privileges] | command_list} 
	to {public | name_list | role_list}

Grants access on certain dbcc commands:

grant dbcc {dbcc_command [on {all | database}]
		[, dbcc_command [on {all | database}], ...]} 
	to {user_list | role_list }

Grants permission to create encryption keys:

grant create encryption key to {user_list | role_list  | group_list}

Grants decrypt permission on a table or a list of columns in a table:

grant decrypt on [ owner. ]tablename[(columnname [{,columname}])] 
	to {user | group | role}

Grants the default permissions for specific system tables:

grant default permissions on system tables

Grants a role to a user or a role:

grant {role role_granted [, role_granted ...]}
	to grantee [, grantee...]

Switches your server user identity to any other server login and limit its use based on the target login roles:

grant set {proxy | tracing} to role_list
	[restrict role role_list | all | system]

Parameters

Examples

Example 1

Grants Mary and the “sales” group permission to use the insert and delete commands on the titles table:

grant insert, delete
on titles
to mary, sales

Example 2

Grants select permission on the get_appcontext function to “public” (which includes all users):

grant select on builtin get_appcontext to public 

Compare this to the following, which grants select permission on a table called get_appcontext, if a table with that name exists:

grant select on get_appcontext to public

Specifically including the builtin argument in your grant statement ensures that you do not mistakenly select a table that has the same name as a function—in this example, the get_appcontext function versus a table called get_appcontext.

Example 3

Two ways to grant update permission on the price and advance columns of the titles table to “public” (which includes all users):

grant update
on titles (price, advance)
to public

or:

grant update (price, advance)
on titles 
to public

Example 4

Grants transfer table permission to user mary for the titles table:

grant transfer table on titles to mary

Example 5

Grants Harry and Billy permission to execute either set proxy or set session authorization to impersonate another user in the server:

grant set proxy to harry, billy

Example 6

Grants users with sso_role permission to execute either set proxy or set session authorization to impersonate another user in the server:

grant set session authorization to sso_role

Example 7

Grants users with vip_role the ability to impersonate another user in the server. vip_role must be a role defined by a system security officer with the create role command:

grant set proxy to vip_role

Example 8

Grants Mary and John permission to use the create database and create table commands. Because create database permission is being granted, the grant command can be executed only by a system administrator within the master database. Mary and John’s create table permission applies only to the master database:

grant create database, create table
to mary, john

Example 9

Grants complete access permissions, except decrypt permission, on the titles table to all users:

grant all on titles 
to public

Example 10

Grants all object creation permissions, except create encryption key, in the current database to all users. If this command is executed by a system administrator from the master database, it includes create database permission:

grant all
to public

Example 11

Gives Mary permission to use the update command on the authors table and to grant that permission to others:

grant update on authors
to mary
with grant option

Example 12

Gives Bob permission to use the select and update commands on the price column of the titles table and to grant that permission to others:

grant select, update on titles (price)
to bob
with grant option

Example 13

Grants permission to execute the new_sproc stored procedure to all system security officers:

grant execute on new_sproc
to sso_role

Example 14

Grants James permission to create a referential integrity constraint on another table that refers to the price column of the titles table:

grant references on titles (price)
to james

• grant all in a database does not grant create encryption key or decrypt permission.

• When used with only user or group names (no object names), grant all assigns these permissions: 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.

• Only the database owner and a system administrator can use the grant all syntax without an object name to grant create command permissions to users or groups. When the grant all command is used by the database owner, an informational message is printed, stating that only a system administrator can grant create database permission. All other permissions noted above are granted.

• All object owners can use grant all with an object name to grant permissions on their own objects. When used with a table or view name plus user or group names, grant all enables delete, delete statistics, insert, select, truncate table, update, and update statistics permissions on the table.

• You cannot grant permissions with grant option to “public” or to a group or role.

• In granting permissions, a system administrator is treated as the object owner. If a system administrator grants permission on another user’s object, the owner’s name appears as the grantor in sysprotects and in sp_helprotect output.

• You cannot grant create encryption key permission with the grant option parameter.

• Information for each grant command is kept in the sysprotects system table, with the following exceptions:

  • Adaptive Server displays an informational message if a specific permission is granted to a user more than once by the same grantor. Only the first grant record is kept.

  • If two grants are exactly same except that one of them is granted with grant option, the grant with grant option is kept.

  • If two grant statements grant the same permissions on a particular table to a specific user, but to different columns, Adaptive Server treats the grants as if they were one statement. For example, the following grant statements are equivalent:

    grant select on titles (price, contract) 
        to keiko
    grant select on titles (advance) to keiko
    

    grant select on titles (price, contract,
        advance)
    to keiko
    

• You can use the grant command to grant permissions to all users who have been granted a specified role. The role can be either a system role, like sso_role or sa_role, or a user-defined role. The system security officers must create the role user-defined roles using a create role command.

  However, grant execute permission does not prevent users who do not have a specified role from being individually granted permission to execute a stored procedure. To ensure, for example, that only system security officers can ever be granted permission to execute a stored procedure, 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 proc_role.

• Permissions that are granted to roles override permissions that are granted to users or groups. For example, say 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 can still access sales because his role permissions override his individual permissions.

• User groups allow you to grant or revoke permissions to more than one user with a single statement. Each user can be a member of one other group and is always a member of “public.”

• The database owner or system administrator can add new users with sp_adduser and create groups with sp_addgroup. To allow users with logins on Adaptive Server to use the database with limited privileges, you can add a “guest” user with sp_adduser and assign limited permissions to “guest.” All users with logins can access the database as “guest.”

• To remove a user, use sp_dropuser. To remove a group, use sp_dropgroup.

  To add a new user to a group other than “public,” use sp_adduser. To change an established user’s group, use sp_changegroup.

  To display the members of a group, use sp_helpgroup.

• When sp_changegroup is executed to change group membership, it clears the in-memory protection cache by executing:

   grant all to null
  

  so that the cache can be refreshed with updated information from the sysprotects table. To modify sysprotects directly, contact Sybase Technical Support.

The on database parameter specifies the database on which to invoke the database-level grant dbcc command. Because on master grants the ability to use dbcc commands on all databases, on master is the same as on all. You must be in the master database to use either the on all or on master parameters.

Neither the on database nor on all parameters work when invoking a server-level grant dbcc command such as dbcc tune, because by doing so, you are forcing a server-level command to restrict itself to individual databases. For this reason, using the server-level grant dbcc tune on master command raises an error.

Before you grant dbcc permission for a database to a user, that user must first be a valid user in the database, and cannot be a “guest” user. However, if you grant dbcc through roles, the users can then execute that dbcc command in any database where they are a valid user, including the user “guest.”

The system tables that you can grant and revoke the default permissions for when you issue the command from any database are:

The command also makes the following changes:

• Revokes syscolumns (encrkyid) and syscolumns (encrkydb) permissions from public.

• Revokes syscolumns (encrkydb) and syscolumns (encrkyid) permissions from public.

• Revokes sysobjects(audflags) permissions from public

• Grants permissions for sysobjects to sso_role

• Revokes select on all columns of sysencryptkeys from public

• Grants select on all sysencryptkeys columns to sso_role

• Grants permissions for syscolumns to sso_role

The system tables for which you can grant and revoke the default permissions when you issue the command from the master database are:

The command also:

• Revokes select on sysdatabases(deftabaud) from public

• Revokes select on sysdatabases(defvwaud) from public

• Revokes select on sysdatabases(defpraud) from public

• Revokes select on sysdatabases(audflags2) from public

• Grants select on sysdatabases to sso_role

• Revokes select on syslogins(password) to public

• Revokes select on syslogins(audflags) from public

• Grants select on syslogins to sso_role

• Revokes select on syslisteners(net_type) from public

• Revokes select on syslisteners(address_info) from public

• Grants select on syslisteners to sso_role

• Revokes select on syssrvroles(srid) from public

• Revokes select on syssrvroles(name) from public

• Revokes select on syssrvroles(password) from public

• Revokes select on syssrvroles(pwdate) from public

• Revokes select on syssrvroles(status) from public

• Revokes select on syssrvroles(logincount) from public

• Grants select on syssrvroles to public

• Revokes select on sysloginroles(suid) from public

• Revokes select on sysloginroles(srid) from public

• Revokes select on sysloginroles(status) from public

• Grants select on sysloginroles to sso_role

Adaptive Server allows you to grant permissions for users, roles, and groups for the update statistics, delete statistics, and truncate table commands. Table owners can also provide permissions through an implicit grant by adding update statistics, delete statistics, and truncate table to a stored procedure and then granting execute permissions on that procedure to a user or role.

You cannot grant permissions for update statistics at the column level. You must have the sso_role to run update statistics or delete statistics on sysroles, syssrvroles, and sysloginroles security tables.

By default, users with the sa_role have permission to run update statistics and delete statistics on system tables other than sysroles, syssrvroles, and sysloginroles, and can transfer this privilege to other users.

You can also issue grant all to grant permissions on update statistics, delete statistics, and truncate table.

grant update statistics on authors to billy

You cannot grant variants of update statistics (for example, update index statistics) separately. That is, you cannot issue:

grant update all statistics to harry

You can, however, write stored procedures that control who executes these commands. For example, the following grants Billy execute permission for update index statistics on the authors table:

create proc sp_ups as
update index statistics on authors
go
revoke update statistics on authors from billy
go
grant execute on sp_ups to billy

You cannot grant and revoke delete statistics permissions at the column level.

Although Adaptive Server audits truncate table as a global, miscellaneous audit, it does not audit update statistics. To retain clear audit trails for both truncate table and update statistics, Sybase recommends that you include both commands in a stored procedure to which you grant users execute permission, as described above.

The command fails and generates an error message if a user issues update statistics, delete statistics, or truncate table and:

• Does not own the table.

• Does not have the sa_role.

• Is not a database owner who has successfully used setuser to become the table owner.

• Has not been granted update statistics, delete statistics, or truncate table privileges.

• Granting permission to execute set proxy or set session authorization allows the grantee to impersonate another login in Adaptive Server. set proxy and set session authorization are identical, except set session authorization follows the SQL standard, and set proxy is a Transact-SQL extension.

• To grant set proxy or set session authorization permission, you must be a system security officer, and you must be in the master database.

• The name you specify in the grant set proxy command must be a valid user in the database; that is, the name must be in the sysusers table in the database.

• grant all does not include the set proxy or set session authorization permissions.

• You can restrict roles incrementally using grant set proxy. For example, you can first restrict the sa_role, then the sso_role:

  grant set proxy to joe
  restrict role sa_role
  grant set proxy to joe
  restrict role sso_role
  

• You cannot unrestrict individual roles. You must revoke set proxy to revoke permissions from all roles, as illustrated in this query:

  select distinct user_name (p.uid), b.name, p.grantor,
                   Restricted_role=case 
  convert (tinyint,substring (isnull (p.columns,0x1),1,1)) & 1
                         when 1 then
                              "None"
                         else
                              isnull (role_name (c.number - 1), "System 
  "+convert (char,c.number))
                                      end
      from sysprotects p, master.dbo.spt_values b, master.dbo.spt_values c
      where
            convert (tinyint,substring (isnull (p.columns,0x1), c.low,1)) & 
  c.high = 0
           and c.type = "P" and c.number <= 1024 and c.number >0 and 
  p.action = 167
           and b.type = "T"
           and b.number = (p.protecttype + 204)
            and role_name (c.number - 1) is not null
  

grant fails if you attempt to grant permissions to user-defined roles in a local temporary database.

Standards

ANSI SQL – Compliance level: Entry-level compliant. grant dbcc is also a Transact-SQL extension.

grant dbcc, and granting permissions to groups and granting set proxy are Transact-SQL extensions. Granting set session authorization (identical in function to set proxy) follows the ANSI standard.

Permissions

Only system security officers can grant create trigger and create encryption key permissions.

Auditing

Values in event and extrainfo columns of sysaudits are:

See also