Revokes permissions or roles from users, groups, or roles.
Revokes permission to access database objects:
revoke [grant option for]
{all [privileges] | permission_list}
on {table_name [(column_list)]
| view_name [(column_list)]
| stored_procedure_name}
| keyname}
from {public | name_list | role_list}
[cascade]
Revokes permission to select built-in functions:
revoke select
on [builtin] builtin
to {name_list | role_list}
Revokes permission to create database objects, execute set proxy, or execute set session authorization:
revoke {all [privileges] | command_list}
from {public | name_list | role_list}
Revokes permission to run set proxy or set tracing:
[revoke set {proxy | tracing}
from {public | name_list | role_list
Revokes a role from a user or another role:
revoke role {role_name [, role_list ...]} from
{grantee [, grantee ...]}
Revokes access on some dbcc commands:
revoke dbcc {dbcc_command [on {all | database}]
[, dbcc_command [on {all | database}], ...]}
from {user_list | role_list}
Revokes permission from other users, groups, and roles to create encryption keys.
revoke create encryption key from user | role | group
Revokes row-filtering predicates.
revoke {all [privileges]
| [all] permission_list}
on table_name (column_list)
[with { pred_name | {all |no} predicates}]
from {public | name_list | role_list}
Revokes decrypt permission on a table or a list of columns in a table:
revoke decrypt on [owner.] tablename[(columnname [{,columname}])]
from user | group | role
Revokes the default permissions from public:
revoke default permissions on system tables
when used to revoke permission to access database objects (the first syntax format), all revokes all permissions applicable to the specified object. All object owners can use revoke all with an object name to revoke permissions on their own objects.
Only the system administrator or the database owner can revoke permission to revoke create command permissions (the third syntax format). When used by the system administrator, revoke all revokes all create permissions (create database, create default, create procedure, create rule, create table, and create view). When the database owner uses revoke all, Adaptive Server revokes all create permissions except create database, and prints an informational message.
all does not apply to set proxy, set session authorization, create encryption key, and create trigger.
is a list of permissions to revoke. If more than one permission is listed, separate them with commas. The following table illustrates the access permissions that can be granted and revoked on each type of object:
Object |
permission_list can include |
|---|---|
Table |
select, insert, delete, update references, update statistics, delete statistics, and truncate table, decrypt |
View |
select, insert, delete, update, decrypt |
Column |
select, update, references, decrypt Column names can be specified in either permission_list or column_list. |
Stored procedure |
execute |
Encryption key |
select |
Permissions can be revoked only by the user who granted them.
is a built-in function. Specifying built-in functions allows you to differentiate between a table and a revocable built-in function with the same name. The functions are set_appcontext, get_appcontext, list_appcontext, and rm_appcontext.
is a list of commands. If more than one command is listed, separate them with commas. The command list can include create database, create default, create procedure, create rule, create table, create view, create encryption key, set proxy, or set session authorization. create database permission can be revoked only by a system administrator and only from within the master database.
set proxy and set session authorization are identical; the only difference is that set session authorization follows the SQL standard, and set proxy is a Transact-SQL extension. Revoking permission to execute set proxy or set session authorization revokes permission to become another user in the server. Permissions for set proxy or set session authorization can be revoked only by a system security officer, and only from within the master database.
is the name of the table on which you are revoking permissions. The table must be in your current database. Only one object can be listed for each revoke statement.
is a list of columns, separated by commas, to which the privileges apply. If columns are specified, only select and update permissions can be revoked. References permissions also can be revoked on columns.
is the name of the view on which you are revoking permissions. The view must be in your current database. Only one object can be listed for each revoke statement.
is the name of the stored procedure on which you are revoking permissions. The stored procedure must be in your current database. Only one object can be listed for each revoke statement.
is the name of the key from which you are revoking permission. The encryption key must be in your current database. Only one object can be listed for each revoke statement. You can revoke only select permission from a key.
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. You cannot grant permissions with grant option to “public” or to other groups or roles.
is a list of user and group names, separated by commas.
Revokes permission from a user to impersonate another user. Only the system security officer can revoke set proxy.
Revokes permission from a user to enable or disable tracing for the set option, set plan, and dbcc traceon or traceoff. Only the system administrator can revoke set tracing permission, and only from the master database.
is the name of a system or user-defined role. Use revoke role to revoke revoked roles from roles or users.
is the name of a system or user-defined role. This allows you to revoke permissions from all users who have been revoked a specific role. The role name can be either a system role or a user-defined role created by a system security officer with create role. Either type of role can be revoked to a user with the revoke role command. In addition, you can use sp_role to revoke system roles.
is the name of a system role, user-defined role, or a user, from whom you are revoking a role.
revokes with grant option permissions, so that the users specified in name_list can no longer grant the specified permissions to other users. If those users have granted permissions to other users, you must use the cascade option to revoke permissions from those users. The user specified in name_list retains permission to access the object, but can no longer grant access to other users. grant option for applies only to object access permissions, not to object creation permissions.
revokes the specified object access permissions from all users to whom the revokee granted permissions. Applies only to object access permissions, not to object creation permissions. When you use revoke without grant option for, permissions granted to other users by the revokee are also revoked: the cascade occurs automatically.
is the name of the dbcc command you are revoking. It cannot be a variable. Table 1-26 lists the valid revoke dbcc commands.
is the name of the database on which you are revoking permissions. It is used with database-specific dbcc commands to revoke permission only on the target database. The revokee 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 revoked actions in the same command, database must be unique.
See “on all | database parameter and server-level commands” for more information.
is a list of users from whom you are revoking the permission, and cannot be a variable.
is a list of the name of system or user-defined roles from whom you are revoking the permission, and cannot be a variable.
You cannot grant or revoke dbcc commands
to public or groups.
uses all or all privileges to revoke all granted and denied privileges. Only select is relevant to a column-filtering predicate.
uses all to revoke all predicated and non-predicated grants for a given table and grantee.
if used with with pred_name, the predicated row- or column-level access is removed for the named columns. If there still exist other columns referenced for this row-level privilege, the privilege and its related named predicate remain in sysprotects for the reduced column list.
may be followed by a named predicate, the double keyword all predicates, or the double keyword no predicates.
all predicates – instructs Adaptive Server to remove any and all privileges with predicates on the named table from the grantee.
no predicates – is the default behavior, and it instructs Adaptive Server to remove only unpredicated grants for the given access from the named grantee.
if a predicate with the given name that applies to the revoked privilege and grantee does not exist, Adaptive Server returns an error.
specifies that you revoke the default permissions for the system tables listed in “revoking default permissions on system tables”.
Revokes insert and delete permissions on the titles table from Mary and the “sales” group:
revoke insert, delete on titles from mary, sales
Revokes select permission on the get_appcontext function from “public” (which includes all users):
revoke select on builtin get_appcontext from public
Compare this to the following, which revokes select permission on a table called get_appcontext, if a table with that name exists:
revoke select on get_appcontext from public
Two ways to revoke update permission on the price and advance columns of the titles table from “public:”
revoke update on titles (price, advance) from public
or:
revoke update (price, advance) on titles from public
Revokes permission from Mary and John to use the create database and create table commands. Because create database permission is being revoked, this command must be executed by a system administrator from within the master database. Mary’s and John’s create table permission is revoked only within the master database:
revoke create database, create table from mary, john
Revokes permission from Harry and Billy to execute either set proxy or set session authorization to impersonate another user in the server:
revoke set proxy from harry, billy
Revokes permission from users with sso_role to execute either set proxy or set session authorization:
revoke set session authorization from sso_role
Revokes permission from users with vip_role to impersonate another user in the server. vip_role must be a role defined by a system security officer with the create role command:
revoke set proxy from vip_role
Revokes all object creation permissions from Mary in the current database (except create encryption key):
revoke all from mary
Revokes all object access permissions on the titles table from Mary (except decrypt permission):
revoke all on titles from mary
Two ways to revoke Tom’s permission to create a referential integrity constraint on another table that refers to the price and advance columns in the titles table:
revoke references on titles (price, advance) from tom
or:
revoke references (price, advance) on titles from tom
Revokes permission to execute new_sproc from all users who have been granted the “operator” role:
revoke execute on new_sproc from oper_role
Revokes John’s permission to grant insert, update, and delete permissions on the authors table to other users. Also revokes from other users any such permissions that John has granted:
revoke grant option for insert, update, delete on authors from john cascade
Revokes “doctor_role” from “specialist_role”:
revoke role doctor_role from specialist_role
Revokes “doctor_role” and “surgeon_role” from “specialist_role” and “intern_role”, and from users Mary and Tom:
revoke role doctor_role, surgeon_role from specialist_role, intern_role, mary, tom
Revokes dbcc privileges from Frank:
1> use pubs2 2> go 1> revoke dbcc checkdb on pubs2 from checkdb_role 2> go 1> use master 2> go 1> revoke dbcc checkdb on all from frank 2> go ...
Revokes truncate table and update statistics privileges from Harry on the authors table:
revoke truncate table on authors from harry revoke update statistics on authors from harry
Revokes the delete statistics privileges from user Billy on the authors table:
revoke delete statistics on authors from billy
Revokes truncate table and update and delete statistics privileges from all users with the oper_role:
revoke truncate table on authors from oper_role revoke update statistics on authors from oper_role revoke delete statistics on authors from oper_role
Users Billy and Harry can no longer run these commands on authors.
Revokes decrypt permissions from public:
revoke decrypt on customer from public
Revokes create encryption key permissions from user joe:
revoke create encryption key from joe
Revokes select on permission for the ssn_key from the database owner.
grant select on ssn_key to dbo
The following examples assume the following grants have been made for selecting from table t1 (columns col1, col2, col3, col4) by user1:An unconditional grant to see all rows for t1.col1 and t1.col4:
grant select on t1 (col1, col4) to user1
A row-filtering grant to be applied when selecting t1.col2 or t1.col3:
grant select on t1 (col2, col3) where col1 = 1 as pred1 to user1
The following examples illustrate the functionality of the revoke command where a single grantee has multiple row- filtering grants on the same object from the same grantor:
revoking an individual row-filtering privilege that was granted to a grantee. The revoke command specifies a with clause and the predicate name.
revoking all predicated row-level privileges granted to the grantee. The revoke command uses the keywords all predicates.
revoking a non-predicated grant while leaving any predicated row-filtering grants in place. The syntax omits the with clause.
Removes select permission on t1.col2 with pred1. Note: If user1 selects t1.col3, then pred1 will still be applied:
revoke select on t1 (col2) with pred1 from user1
If the grantor issued either of the following, then all permissions on t1 using pred1 would be revoked from user1:
revoke select on t1 (col1, col3) with pred1 from user1
or
revoke select on t1 with pred1
Removes the grant on t1.col2 and t1.col3 with predicate pred1, as all predicates revokes all row-filtering predicated grants for a given access and grantee:
revoke select on t1 with all predicates from user1
Removes all select access on t1 from user1; that is, the non-predicated grant and the row filtering grant with pred1:
revoke select on t1 from user1
Applies to only the non-predicated grant:
revoke select on t1 with no predicates
See the grant command for more information about permissions.
You can revoke permissions only on objects in your current database.
You can revoke only permissions that were granted by you.
You cannot revoke a role from a user while the user is logged in.
grant and revoke commands are order-sensitive. When there is a conflict, the command issued most recently takes effect.
You can substitute the word to for the word from in the revoke syntax.
If you do not specify grant option for in a revoke statement, with grant option permissions are revoked from the user along with the specified object access permissions. In addition, if the user has granted the specified permissions to any other users, all of those permissions are revoked. In other words, the revoke cascades.
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 only selected group members, but not from the entire group to which it was granted, Adaptive Server retains the original row and adds a new row for the revoke.
Permission to issue create trigger is granted to users by default. 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 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.
revoke grant option for revokes the user’s ability to grant the specified permission to other users, but does not revoke the permission itself from that user. If the user has granted that permission to others, you must use the cascade option; otherwise, you receive an error message and the revoke fails.
For example, say you revoke the with grant option permissions from the user Bob on titles, with this statement:
revoke grant option for select on titles from bob cascade
If Bob has not granted this permission to other users, this command revokes his ability to do so, but he retains select permission on the titles table.
If Bob has granted this permission to other users, you must use the cascade option. If you do not, you receive an error message and the revoke fails. cascade revokes this select permission from all users to whom Bob has granted it, as well as their ability to grant it to others.
You cannot use revoke with the cascade option to revoke privileges granted by the table owner. For example, the owner of a table (UserA) can grant privileges to another user (UserB) as in this scenario:
create table T1 (...) grant select on T1 to UserB
However, the system administrator cannot revoke UserB’s privileges using the revoke privileges command with the cascade option as in this statement:
revoke select on T1 from UserA cascade
This statement revokes the select privileges of the table owner, but does not revoke those privileges from UserB.
By default, all data manipulation language (DML) operations are revoked implicitly for users other than the table owner. Because the sysprotects table contains no records indicating that the table owner has granted and then revoked privileges, the cascade option is not invoked.You must revoke explicitly the select privilege from UserB.
To revoke set proxy or set session authorization permission, or to revoke roles, you must be a system security officer, and you must be in the master database.
set proxy and set session authorization are identical, with one exception: set session authorization follows the SQL standard. If you are concerned about using only SQL standard commands and syntax, use set session authorization.
revoke all does not include set proxy or set session authorization permissions.
Permissions granted to roles override permissions granted to individual users or groups. Therefore, if you revoke a permission from a user who has been granted a role, and the role has that same permission, the user retains it. 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.
Revoking a specific permission from “public” or from a group also revokes it from users who were individually granted the permission.
Database user groups allow you to grant or revoke permissions to more than one user at a time. A user is always a member of the default group, “public” and can be a member of only one other group. The Adaptive Server installation script assigns a set of permissions to “public.”
Create groups with sp_addgroup and remove groups with sp_dropgroup. Add new users to a group with sp_adduser. Change a user’s group membership with sp_changegroup. To display the members of a group, use sp_helpgroup.
Table 1-26 lists the valid revoke dbcc commands.
Command name |
Description |
|---|---|
checkalloc |
Checks the specified database to make sure all of its pages are correctly allocated, and that there are no unused allocated pages. |
checkcatalog |
Checks for consistency in and between system tables. |
checkdb |
Runs the same checks as checktable, but on each table in the specified database, including syslogs. |
checkindex |
Checks the specified index to make sure that:
|
checkstorage |
Checks the specified database for:
|
checktable |
Checks the specified table to make sure that:
|
checkverify |
Verifies the results of the most recent run of dbcc checkstorage for the specified database. |
fix_text |
Upgrades text values after any Adaptive Server character set is converted to a new multibyte character set. |
indexalloc |
Checks the specified index to make sure all pages are correctly allocated, and that there are no unused allocated pages. |
reindex |
Checks the integrity of indexes on user tables by running a fast version of dbcc checktable. |
tablealloc |
Checks the specified table to make sure that all pages are correctly allocated, and that there are no unused allocated pages. |
textalloc |
Checks for a violation of the format of the root page of a text or image index. |
tune |
Enables or disables tuning flags for special performance situations. |
All of the options in Table 1-26 are database-level commands except for tune, which is a server-level command.
See Chapter 25, “Checking Database Consistency” in the System Administration Guide for more information on these dbcc commands.
The on database parameter specifies the database on which to invoke the database-level revoke dbcc command. Because on master revokes the ability to use dbcc commands on all databases, on master is the same as on all. You must be in the master database to use either the on all and on master parameters.
Neither the on database nor on all parameters work when invoking a server-level revoke dbcc command such as dbcc tune, because by doing so, you are forcing a server-level command to restrict itself to individual databases. For this reason, using the server-level revoke dbcc tune on master command raises an error.
default permissions on system tables revokes sysobjects (audflags) permissions from “public.”
The system tables you can revoke the default permissions for when you issue the command from any database are:
|
|
|
The system tables you revoke the default permissions for when you issue this command from the master database are:
|
|
|
Adaptive Server allows you to revoke permissions for users, roles, and groups for the update statistics, delete statistics, and truncate table commands. Table owners can also provide permissions through an implicit grant by adding update statistics, delete statistics, and truncate table to a stored procedure and then granting execute permissions on that procedure to a user or role.
You cannot revoke 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 revoke permission to execute update
statistics from a user, they also lose permission to execute
variations of this command, such as update all statistics, update
partition statistics, update index statistics, update
statistics table, and so on. For
example, the following revokes Billy permission from running all
variations of update statistics on the authors table:
revoke update statistics on authors to billy
If you revoke a user’s permission to execute update statistics, you also revoke their ability to execute the variations of this command.
You cannot revoke variants of update statistics (for example, update index statistics) separately. That is, you cannot issue:
revoke update all statistics from harry
You cannot grant and revoke delete statistics permissions at the column level. See the “Usage” section of grant.
The command fails and generates an error if a user issues update statistics, delete statistics, or truncate table and they:
Do not own the table.
Do not have the sa_role.
Are not a database owner who has successfully used setuser to become the user who is the owner of the table.
Have not been granted update statistics, delete statistics, or truncate table privileges.
revoke fails if you attempt to revoke permissions from user-defined roles in a local temporary database.
ANSI SQL – Compliance level: Transact-SQL extension.
Command execution Only a system administrator can revoke create database permission, and only from the master database. Only a system security officer can revoke create trigger and create encryption key permission.
Database consistency checking Only system administrators can run revoke dbcc commands. Database owners cannot run revoke dbcc.
Database object access revoke permission for database objects defaults to object owners. An object owner can revoke permission from other users on his or her own database objects.
Functions Only system administrators can revoke permissions on built-in functions.
Proxy and session authorization Only a system security officer can revoke set proxy or set session authorization, and only from the master database.
Roles You can revoke roles only from the master database. Only a system security officer can revoke sso_role, oper_role, or a user-defined role from a user or a role. Only system administrators can revoke sa_role from a user or a role. Only a user who has both sa_role and sso_role can revoke a role that includes sa_role.
Tables Database owners can revoke default permissions on system tables.
Table owners and the system security officer can revoke decrypt permission on a table or a list of columns in a table.
Values in event and extrainfo columns of sysaudits are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
47 |
revoke |
revoke |
|
85 |
roles |
create role, drop role, alter role, grant role, or revoke role |
|
Functions proc_role
System procedures sp_activeroles, sp_adduser, sp_changedbowner, sp_changegroup, sp_displaylogin, sp_displayroles, sp_dropgroup, sp_dropuser, sp_helpgroup, sp_helprotect, sp_helpuser, sp_modifylogin, sp_role
