grant

Description

Assigns permissions to individual users, groups of users, and roles.

Syntax

Grants permission to access database objects:

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]

Grants permission to use built-in functions:

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

Grants system privileges to execute certain commands:

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

Grants dbcc privileges:

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

Grants the default permissions for specific system tables:

grant default permissions on system tables

Grants permission that allows grantee to switch server user identity to any other server login and limit its use based on the target login’s roles:

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, Adaptive 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 *

NotePermissions 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 Adaptive 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, Adaptive 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 Table 1-21, Table 1-22 for list of grantable system privileges. Also 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. Table 1-21 and Table 1-22 include grantable server-wide dbcc and database-wide dbcc privileges.

NoteYou 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”.

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

NoteBefore 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.

NoteYou 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.

NoteYou 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

NoteThe 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

Adaptive 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 will result 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


Syntax substitution

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


Using set fipsflagger

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.

Privileges

Server-wide system privileges Table 1-21 lists all grantable server-wide system privileges. Server-wide privileges must be granted in the master database. For operations each privilege is authorized to perform, see Table 8-15 Server-wide privileges in the “Using Granular Permissions” chapter of the Security Administration Guide.

Note In Table 1-21, when granular permissions is disabled, only privileges marked with an asterisk ( * ) can be granted.

dbcc privilege syntax dbcc dbcc_subcmd on all is alias to dbcc dbcc_subcmd any database. Both syntaxes are supported.

Table 1-21: Grantable server-wide system privileges

Category

Privilege

Privilege Management

  • manage security permissions

  • manage server permissions

Audit Management

  • manage auditing

Login and Role Management

  • allow exceptional login

  • change password

  • manage any login

  • manage any login profile

  • manage remote login

  • manage roles

Database Management

  • checkpoint (on database)

  • checkpoint any database

  • create database*

  • dump any database

  • dump database (on database)

  • load any database

  • load database (on database)

  • manage any database

  • mount any database

  • online any database

  • online database (on database)

  • own any database

  • own database (on database)

  • quiesce any database

  • unmount any database

Server Management

  • manage any thread pool

  • manage cluster

  • manage disk

  • manage security configuration

  • manage server

  • manage server configuration

  • shutdown

DBCC Privilege

  • dbcc checkalloc any database*

  • dbcc checkcatalog any database*

  • dbcc checkdb any database*

  • dbcc checkindex any database*

  • dbcc checkstorage any database*

  • dbcc checktable any database*

  • dbcc checkverify any database*

  • dbcc fix_text any database*

  • dbcc indexalloc any database*

  • dbcc reindex any database*

  • dbcc tablealloc any database*

  • dbcc textalloc any database*

  • dbcc tune*

Application Management

  • manage any execution class

  • manage any ESP

  • manage data cache

  • manage dump configuration

  • manage lock promotion threshold

  • monitor qp performance

  • manage resource limit

Others

  • connect*

  • kill

  • kill any process

  • map external file

  • monitor server replication

  • set proxy

  • set tracing*

  • set tracing any process

  • set switch

  • show switch

  • use any database

  • use database

Database-wide privileges Table 1-22 lists all grantable database-wide system privileges. Database-wide privilege must be granted in the database for which the privilege is intended to be exercised. For operations each privilege is authorized to perform, see Table 8-16 Database-wide privileges, the “Using Granular Permissions” chapter of the Security Administration Guide.

NoteIn Table 1-22, when granular permissions is disabled, only privileges marked with an asterisk ( * ) can be granted.

Table 1-22: Grantable database-wide privileges

Catagory

Privilege

Privilege Management

  • manage any object permission

  • manage database permissions

User Management

  • manage any user

Set User

  • setuser *

Replication Management

  • manage replication

Database Management

  • manage database

Query Plan Management

  • manage abstract plans

DBCC Privilge

  • dbcc checkalloc *

  • dbcc checkcatalog *

  • dbcc checkdb *

  • dbcc checkindex *

  • dbcc checkstorage *

  • dbcc checktable *

  • dbcc checkverify *

  • dbcc fix_text *

  • dbcc indexalloc *

  • dbcc reindex *

  • dbcc tablealloc *

  • dbcc textalloc *

  • manage checkstorage

  • report checkstorage

System Catalog

  • select any audit table

  • select any system catalog

  • truncate any audit table

General Object

  • alter any object owner

  • create any object

  • drop any object

Encryption key

  • create encryption key *

  • manage any encryption key

  • manage column encryption key

  • manage master key

  • manage service key

Default

  • create default *

  • create any default

  • drop any default

Function

  • create function *

  • create any function

  • drop any function

  • execute any function

Index

  • create any index

Procedure

  • create procedure *

  • create any procedure

  • execute any procedure

  • drop any procedure

Rule

  • create rule *

  • create any rule

  • drop any rule

Table

  • alter any table

  • create any table

  • create table *

  • decrypt any table

  • delete any table

  • drop any table

  • identity_insert any table

  • identity_update any table

  • insert any table

  • manage any statistics

  • references any table

  • reorg any table

  • select any table

  • transfer any table

  • truncate any table

  • update any table

Trigger

  • create trigger *

  • create any trigger

  • drop any trigger

View

  • create view *

  • create any view

  • drop any view

Privilege list Table 1-23 lists all grantable privileges and permissions in alphabetic order. Privileges indicated with a “*” do not require that you enable granular permissions.

Table 1-23: Alphabetical listing of privileges

Privilege name

Privilege type

Managed by (when granular permissions enabled)

Implied by

allow exceptional login

server

manage server permissions

alter any object owner

database

manage database permissions

alter any table

database

manage database permissions

change password

server

manage security permissions

checkpoint any database

server

manage server permissions

checkpoint (on database)

server

manage server permissions

checkpoint any database

checkpoint (on sybsecurity)

server

manage security permissions

connect*

server

manage server permissions

create any default

database

manage database permissions

create any object

create any function

database

manage database permissions

create any object

create any index

database

manage database permissions

create any object

create any object

database

manage database permissions

create any procedure

database

manage database permissions

create any object

create any rule

database

manage database permissions

create any object

create any table

database

manage database permissions

create any object

create any trigger

database

manage database permissions

create any object

create any view

database

manage database permissions

create any view

create database*

server

manage database permissions

create default*

database

manage database permissions

create any default

create encryption key*

database

manage security permissions

manage column encryption key

create function*

database

manage database permissions

create any function

create index*

database

manage database permissions

create any index

create procedure*

database

manage database permissions

create procedure

create rule*

database

manage database permissions

create any rule

create table*

database

manage database permissions

create any table

create trigger*

database

manage database permissions

create any trigger

create view*

database

manage database permissions

create any view

dbcc checkalloc*

database

manage database permissions

dbcc checkalloc any database

dbcc checkalloc any database*

server

manage server permissions

dbcc checkcatalog*

database

manage database permissions

dbcc checkcatalo any database

dbcc checkcatalog any database*

server

manage server permissions

dbcc checkdb*

database

manage database permissions

dbcc checkdb any database

dbcc checkdb any database*

server

manage server permissions

dbcc checkindex*

database

manage database permissions

dbcc checkindex any database

dbcc checkindex any database *

server

manage server permissions

dbcc checkstorage*

database

manage database permissions

dbcc checkstorage any database

dbcc checkstorage any database *

server

manage server permissions

dbcc checktable*

database

manage database permissions

dbcc checktable any database

dbcc checktable any database*

server

manage server permissions

dbcc checkverify*

database

manage database permissions

dbcc checkverify any database

dbcc checkverify any database*

server

manage server permissions

dbcc fix_text*

database

manage database permissions

dbcc fix_text any database

dbcc fix_text any database*

server

manage server permissions

dbcc indexalloc*

database

manage database permissions

dbcc indexalloc any database

dbcc indexalloc any database*

server

manage server permissions

dbcc reindex*

database

manage database permissions

dbcc reindex any database

dbcc reindex any database*

server

manage server permissions

dbcc tablealloc*

database

manage database permissions

dbcc tablealloc any database

dbcc tablealloc any database*

server

manage server permissions

dbcc textalloc*

database

manage database permissions

dbcc textalloc any database

dbcc textalloc any database*

server

manage server permissions

dbcc tune*

server

manage server permissions

decrypt*

object (column)

manage any object permission/object owner

decrypt any table

decrypt any table

database

manage database permissions

delete*

object

manage any object permission/object owner

delete any table

delete any table

database

manage database permissions

delete statistics*

object

manage any object permission/object owner

manage any statistics

drop any default

database

manage database permissions

drop any object

drop any function

database

manage database permissions

drop any object

drop any object

database

manage database permissions

drop any procedure

database

manage database permissions

drop any object

drop any rule

database

manage database permissions

drop any object

drop any table

database

manage database permissions

drop any object

drop any trigger

database

manage database permissions

drop any object

drop any view

database

manage database permissions

drop any object

dump any database

server

manage server permissions

dump database (on database)

server

manage server permissions

dump any database

dump database (on sybsecurity)

server

manage security permissions

execute*

object

manage any object permission/object owner

execute any function (for udf)

execute any procedure (for system procedures)

execute any function

database

manage database permissions

execute any procedure

database

manage database procedures

identity_insert

object

manage any object permission/object owner

identity_insert any table

database

manage database permissions

identity_update

object

manage any object permission/object owner

identity_update any table

database

manage database permissions

insert*

object

manage any object permission/object owner

insert any table

insert any table

database

manage database permissions

kill

server

manage server permissions

kill any process

kill any process

server

manage server permissions

load any database

server

manage server permissions

load database (on database)

server

manage server permissions

load any database

load database (on sybsecurity)

server

manage security permissions

manage abstract plans

database

manage database permissions

manage any database

server

manage server permissions

manage any encryption key

database

manage security permissions

manage any ESP

server

manage server permissions

manage any execution class

server

manage server permissions

manage any login

server

manage security permissions

manage any login profile

server

manage security permissions

manage any remote login

server

manage security permissions

manage any statistics

database

manage database permissions

manage any thread pool

server

manage server permissions

manage any user

database

manage database permissions

manage auditing

server

manage security permissions

manage checkstorage

database

manage database permissions

manage cluster

server

manage server permissions

manage column encryption key

database

manage security permissions

manage any encryption key

manage data cache

server

manage server permissions

manage database

database

manage database permissions

manage any database

manage database permissions

database

manage security permissions

manage disk

server

manage server permissions

manage dump configuration

server

manage server permissions

manage lock promotion threshold

server

manage server permissions

manage master key

database

manage security permissions

manage any encryption key

manage replication

server

manage server permissions

manage resource limit

server

manage server permissions

manage roles

server

manage security permissions

manage security configuration

server

manage security permissions

manage security permissions

server

manage security permissions

manage server

server

manage server permissions

manage server configuration

server

manage server permissions

manage server permissions

server

manage server permissions

manage service key

database

manage security permissions

manage any encryption key

map external file

server

manage server permissions

monitor qp performance

server

manage server permissions

monitor server replication

server

manage server permissions

mount any database

server

manage server permissions

own any database

server

manage server permissions

online any database

server

manage server permissions

online database (on database)

server

manage server permissions

online any database

online database (on sybsecurity)

server

manage security permissions

own database (on database)

server

manage server permissions

own database (on sybsecurity)

server

manage security permissions

quiesce any database

server

manage server permissions

references*

object (column)

manage any object permission/object owner

references any table

references any table

database

manage database permissions

report checkstorage

database

manage database permissions

reorg any table

database

manage database permissions

select*

object (column)

manage any object permission/object owner

select any table (for user tables or views)

select any audit table (for audit tables)

select any system catalog (for system tables)

select any audit table

database

manage database permissions

select any system catalog

database

manage database permissions

select any table

database

manage database permissions

set proxy*

server

manage security permissions

set switch

server

manage server permissions

set tracing*

server

manage server permissions

set tracing for any process

set tracing any process

server

manage server permissions

setuser*

database

manage database permissions

show switch

server

manage server permissions

shutdown

server

manage server permissions

transfer any table

database

manage database permissions

transfer table

object

manage any object permission/object owner

transfer any table

truncate any audit table

database

manage database permissions

truncate any table

database

manage database permissions

truncate table*

object

manage any object permission/object owner

truncate any table

unmount any database

server

manage server permissions

update*

object (column)

manage any object permission/object owner

update any table

update any security catalog

server

manage security permissions

update any table

database

manage database permissions

update statistics

object

manage any object permission/object owner

manage any statistics

use any database

server

manage server permissions

use database (on database)

server

manage server permissions

use any database

use database (on sybsecurity)

server

manage security permissions


Grant access to roles

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.

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.


grant all object creation privileges


grant with grant option rules


Using granted by


Users and user groups


granting default permissions on system tables

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

  • sysalternates

  • sysattributes

  • syscolumns

  • syscomments

  • sysconstraints

  • sysdepends

  • sysindexes

  • sysjars

  • syskeys

  • syslogs

  • sysobjects

  • syspartitions

  • sysprocedures

  • sysprotects

  • sysqueryplans

  • sysreferences

  • sysroles

  • syssegments

  • sysstatistics

  • systabstats

  • systhresholds

  • systypes

  • sysusermessages

  • sysusers

  • sysxtypes

The command also makes the following changes:

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

  • sysdatabases

  • sysdevices

  • syslocks

  • sysmessages

  • sysprocesses

  • systransactions

  • sysusages

  • sysconfigures

  • syscurconfigs

  • syslanguages

  • syscharsets

  • sysservers

  • systimeranges

  • sysresourcelimits

  • syslogins

  • sysremotelogins

  • syssessions

The command also:


Granting permissions for update statistics, delete statistics, and truncate table

Adaptive Server allows you to grant permissions to 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.

NoteOnce you grant permission to execute update statistics to a user, he or she also has permission to execute variations of this command, such as update all statistics, update partition statistics, update index statistics, update table statistics, and so on. For example, the following grants Billy permission to run all variations of update statistics on the authors table:

grant update statistics on authors to billy

If you revoke a user’s permission to execute update statistics, you also revoke his or her ability to execute the variations of this command.

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.


Granting proxies and session authorizations


Granting permissions in a shared-disk cluster

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


Granting a privilege to a 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.

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.


Revoking a privilege from public or a group

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. See “How Adaptive Server saves predicated privileges in sysprotects” in the Security Administration Guide

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.

Granular permissions enabled

With granular permissions enabled, in general, grant can be executed by a user with one of the following privilege management privileges, depending the privilege or permission being granted.

For server-wide privileges, you must be a user with manage server permissions privilege or manage security permissions privilege.

For database-wide privileges, you must be a user with manage database permissions privilege.

For object privileges, you must be the object owner or a user with manage any object permission privilege

To execute grant default, you must be the database owner or a user with own database privilege on the database.

See Table 1-23 "Managed by (when granular permissions enabled)" column for more details.

Granular permissions disabled

With granular permissions disabled, grantable system privileges are limited to create database, create default, create function, create procedure, create rule, create table, create view, connect, set proxy, and set tracing

Command execution – Only system administrators can grant create database, connect, and set tracing permissions, and only from the master database. Only system security officers can grant create trigger permission.

To execute grant default, you must be the database owner or a user with sa_role.

Database consistency checking – Only system administrators can run grant dbcc commands.

Database object grant access – Permission for database objects defaults to object owners. Object owners can grant permission to other users on their own database objects.

Functions – Only system administrators can grant permissions on built-in functions.

Encrypted columns – Only the systems security officer and the key custodian have implicit permission to create encryption keys.

Proxy and session authorization – Only system security officers can grant set proxy or set session authorization, and only from the master database. Granting permission to execute set proxy or set session authorization allows the grantee to impersonate another login in the server. set proxy and set session authorization are identical, except that set session authorization follows the ANSI92 standard, and set proxy is a Transact-SQL extension.

System tables – Database owners can grant default permissions on system tables.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event

Audit option

Command or access audited

Information in extrainfo

40

grant

grant

  • Roles – current active roles

  • Keywords or options – Full command text of the grant statement

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

See also

Catalog stored procedures sp_column_privileges, sp_table_privileges

Commands create role, revoke, setuser, set

Functions proc_role, show_role

System procedures sp_addgroup, sp_adduser, sp_changedbowner, sp_changegroup, sp_dropgroup, sp_dropuser, sp_helpgroup, sp_helprotect, sp_helpuser, sp_role