Assigns permissions to individual users, groups of users, and roles.
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]
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.
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 * |
Permissions with asterisk (*) can only be granted when granular permissions is enabled.
is used only for grant ... where commands as an alias for referencing columns in table_name in the where clause.
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.
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.
is the name of the view on which you are granting permissions. The view must be in your current database.
is the name of the stored procedure on which you are granting permission. The stored procedure must be in your current database.
is the name of an encryption key on which you are granting access. The key_name must be in your current database.
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.
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.
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.
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.
is a list of users’ and group names, separated by commas.
is a list of roles—either system-defined or user-defined—to which you are granting the permission.
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
indicates the grantor as a user in the database different from the user executing the command.
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.
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).
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.
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.
You cannot grant or revoke dbcc privileges to public or groups.
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.
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.
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.
Allows the grantee to switch identities only if the grantee and the target login have any roles included in the role_list.
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.
specifies that you grant the default permissions for the system tables listed in “granting default permissions on system tables”.
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
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.
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
Grants transfer table permission to user Mary for the titles table:
grant transfer table on titles to mary
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
Grants complete access permissions, except decrypt permission, on the titles table to all users:
grant all on titles to public
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
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
Grants permission to execute the new_sproc stored procedure to all system security officers:
grant execute on new_sproc to sso_role
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
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.
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
Grants Bob permission to create encyption keys:
grant create encyption key to Bob
Grants decrypt permission on all encrypted columns in the customer table:
grant decrypt on customer to accounts_role
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
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
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.
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.
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
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.
You cannot grant or revoke dbcc privileges to public or groups.
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
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.
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.
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'.
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
Allows Billy to use the delete statistics command on the authors table:
grant delete statistics on authors to billy
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
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.
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
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
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
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.
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.
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
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.
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
User Smith grants create table permission to user John, with the dbo as the grantor:
grant create table to john granted by dbo
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.
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
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.
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.
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.
Category |
Privilege |
|
---|---|---|
Privilege Management |
|
|
Audit Management |
|
|
Login and Role Management |
|
|
Database Management |
|
|
Server Management |
|
|
DBCC Privilege |
|
|
Application Management |
|
|
Others |
|
|
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.
In Table 1-22, when granular permissions is disabled, only privileges marked with an asterisk ( * ) can be granted.
Catagory |
Privilege |
|
---|---|---|
Privilege Management |
|
|
User Management |
|
|
Set User |
|
|
Replication Management |
|
|
Database Management |
|
|
Query Plan Management |
|
|
DBCC Privilge |
|
|
System Catalog |
|
|
General Object |
|
|
Encryption key |
|
|
Default |
|
|
Function |
|
|
Index |
|
|
Procedure |
|
|
Rule |
|
|
Table |
|
|
Trigger |
|
|
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.
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 |
Possessing one privilege may imply possessing another, more granular privilege. For example, a user with select any table privilege implies that the user has select permission on all user tables. See Table 1-23 for a complete list of privileges pairs that have an implied relationship.
When granting the following database management privileges, the on database clause must be specified for each privilege: checkpoint, dump database, load database, online database, own database. For example, to grant dump database privilege on db1 to smith, you can use:
grant dump database on db1 to smith
You can grant different database management privileges on different databases in the same grant command. For example, to grant own database on db1 and load database on db2 to smith, you can use:
grant own database on db1, load database on db2 to smith
You can grant permissions only on objects in your current database.
grant and revoke commands are order-sensitive. The command that takes effect when there is a conflict is the one issued most recently.
A user can be granted permission on a view or stored procedure even if he or she has no permissions on objects referenced by the procedure or view. See “Managing User Permissions” in the Security Administration Guide.
Adaptive Server grants all users permission to declare cursors, regardless of the permissions defined for the base tables or views referenced in the declare cursor statement. Cursors are not defined as Adaptive Server objects (such as tables), so no permissions can be applied against a cursor. When a user opens a cursor, Adaptive Server determines whether the user has select permissions on the objects that define that cursor’s result set. It checks permissions each time a cursor is opened.
If the user has permission to access the objects defined by the cursor, Adaptive Server opens the cursor and allows the user to fetch row data through the cursor. Adaptive Server does not apply permission checking for each fetch. However, if the user performs a delete or an update through that cursor, the regular permission checking applies for deleting and updating the data of objects referenced in the cursor result set.
A grant statement adds one row to the sysprotects system table for each user, group, or role that receives the permission. If you subsequently revoke the permission from the user or group, Adaptive Server removes the row from sysprotects. If you revoke the permission from selected group members only, but not from the entire group to which it was granted, Adaptive Server retains the original row and adds a new row for the revoked permission.
A user, group, or role can be granted the same privilege or permission by different grantors. In this situation, there will be multiple rows in sysprotects which represents multiple grants on the same privilege or permissions. If one or more than one grants are later revoked, the user, group, or role may still have the privilege or permission if there is one grant remain unrevoked.
If a user inherits a particular permission by virtue of being a member of a group, and the same permission is explicitly granted to the user, no row is added to sysprotects. For example, if “public” has been granted select permission on the phone column in the authors table, then John, a member of “public,” is granted select permission on all columns of authors. The row added to sysprotects as a result of the grant to John contains references to all columns in the authors table except for the phone column, on which he already had permission.
By default, permission to issue the create trigger command is granted to users. When you revoke permission for a user to create triggers, a revoke row is added in the sysprotects table for that user. To grant permission to that user to issue create trigger, you must issue two grant commands. The first command removes the revoke row from sysprotects; the second inserts a grant row. If you revoke permission to create triggers, the user cannot create triggers even on tables that the user owns. Revoking permission to create triggers from a user affects only the database where the revoke command was issued.
Use these system procedures to display information about permissions:
sp_helprotect reports permissions information for database objects, users, groups, and roles.
sp_column_privileges reports permissions information for one or more columns in a table or view.
sp_table_privileges reports permissions information for all columns in a table or view.
sp_activeroles displays all active roles—and all roles contained by those roles—for the current login session of Adaptive Server.
sp_displayroles displays all roles granted to another role or user, or displays the entire hierarchy tree of roles in table format.
You can view permissions using sp_helprotect:
1> use pubs2 2> go 1> sp_helprotect 2> go
grantor grantee type action object column grantable ------- ------- ---- ------ ------- ------- --------- dbo public Grant Select sysalternates All FALSE ... dbo Walter Grant DBCC DBCC dbcc checkdb FALSE (1 row affected) (return status = 0)
You cannot use the grant with grant option with grant dbcc.
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 without an object name in a database does not grant create encryption key. grant all without an object name is only supported when granular permissions is disabled.
When used without object names, grant all assigns these permissions: create database, create default, create procedure, create rule, create table, create function, 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, when granular permissions is disabled, 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. When granular permissions enabled, the grantor's name appears as the grantor in sysobjects and in sp_helprotect output.
You cannot grant system privileges 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 from the same grantor 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
granted by is not allowed on granting predicated privileges.
It is not required that the grantor has permission to execute the grant command.
The grantor, and not the command executor, is listed under sysprotects.grantor.
You need not enable enable granular permissions to use the granted by parameter.
Users who received their grant permission on an object with the with grant option cannot issue the granted by parameter. All other users may issue the granted by parameter.
For example, if mary grants select on her table books to john with grant option, then john will get an error when he tries to issue the second grant command. Mary:
grant select on mary.books to john with grant option
John:
grant select on mary.books to joe granted by smith
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.”
You 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 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(audflags) from public
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) from public
Revokes select on syslogins(audflags) from public
Revokes select on syslogins(lpid) 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
Revokes select on sysinstances(hostname) from public
Grants select on sysinstances to sso_role
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.
Once 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 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 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.
grant fails if you attempt to grant permissions to user-defined roles in a local temporary database.
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 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
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.
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. |
Values in event and extrainfo columns of sysaudits are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
---|---|---|---|
40 |
grant |
grant |
|
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