grant

Syntax

grant {all [privileges] | permission_list} 
	on {table_name as [correlation_name][(column_list)]
		| view_name[(column_list)] 
		| stored_procedure_name | SQL_function_name}
		| keyname}
	[where search_conditions [as pred_name]]
	to {public | name_list | role_list}
	[with grant option]
	[granted by grantor]

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

grant {all [privileges] | privilege_list} 
	to {public | name_list | role_list}
	[granted by grantor]

grant {dbcc_privilege [on database ]
		[, dbcc_privilege [on database ], ...]} 
	to {user_list | role_list }
	[granted by grantor]

grant default permissions on system tables

grant set proxy to name_list
	[restrict role role_list | all | system]
	[granted by grantor]

Parameters

• all – when used to assign permission to access database objects, all specifies that all permissions, except decrypt, that are applicable to the specified object are granted. All object owners can use grant all with an object name to grant permissions on their own objects. You must grant decrypt permissions separately.

  When granular permissions is not enabled, a system administrator or the database owner can use grant all to assign privileges to create database objects (see syntax for “Grants system privileges to execute certain commands”). When used by a system administrator, grant all assigns all create privileges (create database, create default, create procedure, create rule, create table, create function, and create view). When the database owner uses grant all , or executes grant all outside the master database, the SAP ASE server grants all create privileges except create database and prints an informational message.

  Granting all create privileges using grant all is not supported when granular permissions is enabled. For more information, see Using Granular Permissions in the Security Administration Guide.

  all cannot be used for a grant statement that includes a where clause.

• permission_list – is a list of object access permissions granted. If more than one permission is listed, separate them with commas. This table illustrates the access permissions that can be granted on each type of object:

  Object	permission_list Can Include
  Column	select, update, references, decrypt Column names can be specified in column_list.
  Encryption key	select
  Stored procedure	execute
  SQL function	execute
  Table	select, insert, delete, update, references, update statistics, delete statistics, truncate table, decrypt, transfer table, identity_insert *, identity_update *
  View	select, insert, delete, update, decrypt, identity_insert *, identity_update *

  Note: Permissions with asterisk (*) can only be granted when granular permissions is enabled.
• correlation_name – is used only for grant ... where commands as an alias for referencing columns in table_name in the where clause.
• table_name – is the name of the table on which you are granting permissions. The table must be in your current database. Only one object can be listed for each grant statement.
• column_list – is one or more named columns, separated by commas, to which the permissions apply. If columns are specified, only select, references, decrypt, and update permissions can be granted.

  When the grant is made on one or more named columns using a where clause, then the SAP ASE server enforces row-level access on the user's select, update or delete command as follows:

  • one or more of the named columns on a grant select statement is referenced in the target list or where clause of the user's select statement

  • one or more of the named columns on a grant update statement is referenced in the target list of the user's update statement

  • one or more columns on a grant select is referenced in the where clause of the user's update or delete statement where the session has set ansi_permissions on.

• view_name – is the name of the view on which you are granting permissions. The view must be in your current database.
• stored_procedure_name – is the name of the stored procedure on which you are granting permission. The stored procedure must be in your current database.
• key_name – is the name of an encryption key on which you are granting access. The key_name must be in your current database.
• SQL_function_name – is the name of the SQL function to which you are granting permission. The stored function must be in your current database. You can list only one function for each grant statement.
• where search_conditions – acts as a row filter, and combines with any where clause specified in select, update, or delete statements. You can use the where syntax only when granting select, update, and delete privileges on a table. search_conditions can make use of all syntax allowed in a generic where clause. If the where clause accesses a different table from the one being granted, you must use a subquery. For information on using a where clause on the grant statement see Granting Predicated Privileges in the Security Administration Guide.
• as pred_name – is the name of the predicate, and must be unique among the names of other objects owned by the grantor in the current database and must conform to the rules for identifiers. If you omit pred_name, the SAP ASE server assigns a unique name to the grant predicate, which you can view by using sp_helprotect. pred_name may not be used on grant statements with no where clause. Predicates can be referenced by name by the revoke command.
• public – is all users. For object access permissions, public excludes the object owner. For object creation permissions or set proxy authorizations, public excludes the database owner.
• name_list – is a list of users’ and group names, separated by commas.
• role_list – is a list of roles—either system-defined or user-defined—to which you are granting the permission.
• with grant option – allows the users specified in name_list to grant object access permissions to other users. You can grant permissions with grant option only to individual users, not to “public” or to a group or role. Predicated privileges cannot be granted with the with grant option.
• granted by grantor – indicates the grantor as a user in the database different from the user executing the command.
• grantor – a valid user name in current database, grantor's user identity instead of the executor's user identity would be recorded in the system catalog sysprotects as the grantor.
• builtin – is a built-in function. Specifying the keyword builtin before the built-in function name allows you to differentiate between a table and a grantable built-in function with the same name. The grantable builtin functions are set_appcontext, get_appcontext, list_appcontext, authmech, rm_appcontext, and next_identity (requires select permission on the IDENTITY column).
• privilege_list – is a list of system privileges that can be granted. System privileges include server-wide and database-wide privileges. See the “Usage” section for details on how to grant system privileges. Use commas to separate multiple commands.
• dbcc_privilege – is the name of the dbcc privilege you are granting. It cannot be a variable. See the Usage section on grantable server-wide dbcc and database-wide dbcc privileges.
  Note: You cannot grant or revoke dbcc privileges to public or groups.
• database – is the name of the database on which you are granting permissions. It is used with granting database-wide dbcc privileges. The on database clause is optional, and the database must be the current database. The grantee must be a valid user in the target database. database conforms to the rules for identifiers and cannot be a variable.

  If there are multiple granted actions in the same command, database must be unique.

• set proxy – grants permission for a user to impersonate another user. If grantees do not have the roles in the role_list already granted to them, set proxy to the target login fails if the target login has any roles in the role_list granted.
• system – the grantee cannot switch their identity with anyone who possesses a system role they do not possess. Use system only with the set proxy parameter.
• restrict role role_list – allows the grantee to switch identities only if the grantee and the target login have any roles included in the role_list.
• all – The grantee can grant their identity to anyone who has the same set of roles they possess. That is, the grantee cannot inherit any new roles by executing the set proxy command.
• default permissions on system tables – specifies that you grant the default permissions for the system tables listed in Granting Default Permissions on System Tables in the Usage section.

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 Mary and John permission to use the create database and create table commands. Mary and John’s create table permission applies only to the master database:

  grant create database, create table
  to mary, john

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

  grant all on titles 
  to public

• Example 7 – 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 8 – 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 9 – Grants permission to execute the new_sproc stored procedure to all system security officers:

  grant execute on new_sproc
  to sso_role

• Example 10 – 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

  Note: Before you create a table that includes a referential integrity constraint to reference another user’s table, you must be granted references permission on that referenced table. The table must also include a unique constraint or unique index on the referenced columns. See create table for more information about referential integrity constraints.
• Example 11 – Grants the database owner permission to specify column encryption using the ssn_key, when executed by the key owner. The database owner requires select permission on ssn_key to reference it on create table, alter table, or select into:

  grant select on ssn_key to dbo

• Example 12 – Grants Bob permission to create encyption keys:

  grant create encyption key to Bob

• Example 13 – Grants decrypt permission on all encrypted columns in the customer table:

  grant decrypt on customer to accounts_role

• Example 14 – Grants dump any database privilege to Joe in master to allow him to dump any database:

  1> use master 
  2> go 
  1> grant dump any database to joe
  2> go

• Example 15 – Grants create any object privilege to Joe in database pubs2 to allow Joe create any object privilege on behalf of himself or on behalf of other users in pubs2:

  1> use pubs2 
  2> go 
  1> grant create any object to joe
  2> go

• Example 16 – Grants manage roles to Alex. This returns an error because server-wide privileges require that master be the current database:

  1> use pubs2 
  2> go 
  1> grant manage roles to alex
  2> go

  Msg 4627, Level 16, State 1:
  Line 1:
  The user must be in the master database to GRANT/REVOKE this command.

• Example 17 – Through the use of a role, the system administrator allows Carlos to run dbcc checkalloc on any database where he is a valid user, or where a database allows a “guest” user.
  Note: You do not need to add Carlos as an actual user in the master database if the user “guest” already exists in master.

  1> use master
  2> go
  1> create role checkalloc_role
  2> go
  1> grant dbcc checkalloc any database to checkalloc_role
  2> go
  1> create login carlos with password carlospassword
  2> go
  1> grant role checkalloc_role to carlos
  2> go

• Example 18 – Gives Frank, a valid user in the master database, the ability to execute dbcc checkdb for all databases in the server:

  1> use master
  2> go
  1> create login frank with password frankpassword
  2> go

  Password correctly set.
  Account unlocked.
  New login created.
   (return status = 0)

  1> sp_adduser frank
  2> go

  New user added.
   (return status = 0)

  1> grant dbcc checkdb any database to frank
  2> go

  Now Frank can execute the dbcc checkdb command on each database in the server where he is a valid user:

  % isql -Ufrank -Pfrankpassword -SSERVER
  1> dbcc checkdb (tempdb)
  2> go

  Checking tempdb: Logical pagesize is 2048 bytes
  Checking sysobjects: Logical pagesize is 2048 bytes
  ...
  The total number of data pages in this table is 1. DBCC 
  execution completed. If DBCC printed error messages, 
  contact a user with system administrator (SA) role.

  Note: You cannot grant or revoke dbcc privileges to public or groups.
• Example 19 – If Walter needs to be a maintenance user for pubs2 but the system administrator does not want to grant him administrator-level privileges elsewhere, the system administrator can execute:

  1> use pubs2 
  2> go 
  1> grant dbcc checkdb on pubs2 to walter
  2> go

  Note: The system administrator must be in the target database—in this case pubs2—and Walter must be a valid user in this target database. The on pubs2 clause is optional.

  Walter can now execute the dbcc checkdb command on the customers database without encountering an error.

• Example 20 – Erroneously applies grant dbcc and revoke dbcc to groups or public:

  1> grant dbcc tablealloc on pubs2 to public

  Msg 4629, Level 16, State 1:
  Line 1:
  GRANT/REVOKE DBCC does not apply to groups or PUBLIC.

  1> sp_addgroup gr

  New group added.
   (return status = 0)

  1> grant dbcc tablealloc on pubs2 to gr

  Msg 4629, Level 16, State 1:
  Line 1:
  GRANT/REVOKE DBCC does not apply to groups or PUBLIC.

• Example 21 – You cannot grant system privileges using the grant option:

  grant change password to alex with grant option

  Msg 156, Level 15, State 1:
  Line 1:
  Incorrect syntax near the keyword 'with'.

• Example 22 – Allows Harry to use truncate table and updates statistics on the authors table:

  grant truncate table on authors to harry
  grant update statistics on authors to harry

• Example 23 – Allows Billy to use the delete statistics command on the authors table:

  grant delete statistics on authors to billy

• Example 24 – Grants truncate table, update, and delete statistics privileges to all users with the oper_role (if Billy and Harry possess the oper_role, they can now execute these commands on authors):

  grant truncate table on authors to oper_role
  grant update statistics on authors to oper_role
  grant delete statistics on authors to oper_role

• Example 25 – Implicitly grants permissions for truncate table, delete statistics, and update statistics through a stored procedure. For example, assuming Billy owns the authors table, he can execute the following to grant Harry privileges to run truncate table and update statistics on authors:

  create procedure sproc1
  as
  truncate table authors
  update statistics authors
  go
  grant execute on sproc1 to harry
  go

  You can also implicitly grant permissions at the column level for update statistics and delete statistics through stored procedures.

• Example 26 – 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 27 – 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 28 – Grants set proxy to Joe but restricts him from switching identities to any user with the sa, sso, or admin roles (however, if he already has these roles, he can set proxy for any user with these roles):

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

  When Joe tries to switch his identity to a user with admin_role (in this example, Our_admin_role), the command fails unless he already has admin_role:

  set proxy Our_admin_role

  Msg 10368, Level 14, State 1:
  Server 's', Line 2:Set session authorization permission
  denied because the target login has a role that you do
  not have and you have been restricted from using.

  After Joe is granted the admin_role and retries the command, it succeeds:

  grant role admin_role to joe
  set proxy Our_admin_role

• Example 29 – Restricts Joe from being granted any new roles when switching identities:

  grant set proxy to joe
  restrict role all

  Joe can set proxy only to those users who have the same roles (or roles with fewer privileges) than he has.

• Example 30 – Restricts Joe from acquiring any new system roles when using set proxy:

  grant set proxy to joe
  restrict role system

  set proxy fails if the target login has system roles that Joe lacks.

• Example 31 – Students are allowed to view information only about their own grades:

  grant select on grades
    where user_name(uid) = USER as predicate_grades
    to public

• Example 32 – Allows registered students to see information about all courses. The first grant allows anyone to peruse the courses and sections offered. The second grant allows a user to see only his own enrollments in those courses.

  grant select on enrollment
    (course_id, quarter, section_id)
    to public
  
  grant select on enrollment as e
    (uid, with_honors)
    where e.uid in
    (select r.uid from 
    registered_students r 
    where USER = user_name(r.uid))
    to public

  When a registered student enters the following query, he becomes restricted to seeing his own courses (because the with_honors column has been selected):

  select course_id, quarter, with_honors
    from enrollment

  Similarly, when a registered student tries to see how many courses people are taking with the following query:

  select course_id, count(uid) from enrollment
    group by course_id

  The SAP ASE server returns one row giving the count of courses enrolled in by the user.
• Example 33 – User Smith grants select permission to user John on mary.books, with table owner Mary as the grantor:

  grant select on mary.books to john
  granted by mary

• Example 34 – User Smith grants create table permission to user John, with the dbo as the grantor:

  grant create table to john
  granted by dbo

• Example 35 – With granular permissions disabled, granting system privilege manage any login results in an error:

  1>sp_configure "enable granular permissions"
  2>goParameter Name  Default  Memory Used  Config Value  Run Value  Unit   Type                 --------------  -------  ----------  ------------  ---------  ----  ---- enable granular permissions       0         0           0           0    switch   dynamic (1 row affected)
  (return status = 0)
  
  >grant manage any login to smith
  >go
  Msg 16325, Level 15, State 87:
  Line 1:
  Cannot GRANT/REVOKE permission 'MANAGE ANY LOGIN'. Verify that the granular permissions option is enabled.

• Example 36 – You must specify on database clause when granting system privilege own database:

  1>grant own database to smith
  2>goMsg 156, Level 15, State 2:
  Line 1:
  Incorrect syntax near the keyword 'to'.
  1>grant own database on tdb1 to smith
  2>go

Usage

• You can substitute the word from for to in the grant syntax.

• grant dbcc issues the following warning when you execute it while set fipsflagger option is enabled:

  SQL statement on line number 1 contains Non-ANSI
  text. The error is caused due to the use of DBCC.

• Revoking a specific permission from “public” or from a group also revokes it from users who were individually granted the permission. An exeption are grants and revokes of predicated privileges.

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

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

The permission checks for grant differ based on your granular permissions settings.

Auditing

Values in event and extrainfo columns of sysaudits are: