Caché SQL Reference
Removes privileges from a user or role.
REVOKE admin-privilege FROM grantee
REVOKE role FROM grantee
REVOKE [GRANT OPTION FOR] object-privilege
ON object-list FROM grantee [CASCADE | RESTRICT] [AS grantor]
REVOKE [GRANT OPTION FOR] SELECT ON CUBE[S] object-list
REVOKE column-privilege (column-list) ON table FROM grantee [CASCADE | RESTRICT]
An administrative-level privilege or a comma-separated list of administrative-level privileges previously granted to be revoked. The available syspriv
options include sixteen object definition privileges and four data modification privileges.
The object definition privileges are: %CREATE_FUNCTION, %DROP_FUNCTION, %CREATE_METHOD, %DROP_METHOD, %CREATE_PROCEDURE, %DROP_PROCEDURE, %CREATE_QUERY, %DROP_QUERY, %CREATE_TABLE, %ALTER_TABLE, %DROP_TABLE, %CREATE_VIEW, %ALTER_VIEW, %DROP_VIEW, %CREATE_TRIGGER, %DROP_TRIGGER. Alternatively, you can specify %DB_OBJECT_DEFINITION, which revokes all 16 object definition privileges.
The data modification privileges are the %NOCHECK, %NOINDEX, %NOLOCK, %NOTRIGGER privileges for INSERT, UPDATE, and DELETE operations.
||A list of one or more users having SQL System Privileges, SQL Object Privileges, or Roles. Valid values are a comma-separated list of users or roles, or "*". The asterisk (*) specifies all currently defined users who do not have the %All role.
||This clause permits you to revoke a privilege granted by another user by specifying the name of the original grantor. Valid grantor values are a user name, a comma-separated list of user names, or "*". The asterisk (*) specifies all currently defined users who are grantors. To use the AS grantor clause, you must have the %All role or the %Admin_Secure resource.
||A role or comma-separated list of roles whose privileges are being revoked from a user.
||A basic-level privilege or comma-separated list of basic-level privileges previously granted to be revoked. The list may consist of one or more of the following: %ALTER, DELETE, SELECT, INSERT, UPDATE, EXECUTE, and REFERENCES. To revoke all privileges, use either "ALL [PRIVILEGES]" or "*" as the value for this argument. Note that you can only revoke SELECT privilege from cubes, because this is the only grantable cubes privilege.
||A comma-separated list of one or more tables, views, stored procedures, or cubes for which the object-privilege(s) are being revoked. You can use the SCHEMA keyword to specify revoking the object-privilege from all objects in the specified schema. You can use * to specify revoking the object-privilege from all objects in the current namespace.
||A basic-level privilege being revoked from one or more column-list listed columns. Available options are SELECT, INSERT, UPDATE, and REFERENCES.
||A list of one or more column names, separated by commas and enclosed in parentheses.
||The name of the table or view that contains the column-list columns.
statement revokes privileges that allow a user or role to perform specified tasks on specified tables, views, columns, or other entities. REVOKE
can also revoke a role assignment from a user. REVOKE
reverses the actions of the GRANT
command; see that command for more details on privileges generally.
A privilege can only be revoked by the user who granted the privilege, or through a CASCADE operation (as described below).
You can revoke a role or privilege from a specified user, a list of users, or all users (using the * syntax).
prepares and executes quickly, and is generally run only once, Caché does not create a cached query for REVOKE
in ODBC, JDBC, or Dynamic SQL.
completes successfully, even if no actual revoke can be performed (for example, the specified privilege was never granted or has already been revoked). However, if an error occurs during the REVOKE
operation, SQLCODE is set to a negative number.
Roles can be granted or revoked via either the SQL GRANT
commands, or via ^SECURITY Caché System Security. You can use REVOKE
to revoke a role from a user or to revoke a role from another role. You cannot use Caché System Security to grant or revoke roles to other roles. The $ROLES
special variable does not display roles granted to roles.
can specify a single role, or a comma-separated list of roles to revoke. REVOKE
can revoke one or more roles from a specified user (or role), a list of users (or roles), or all users (using the * syntax).
command can grant a non-existent role to a user. You can use REVOKE
to revoke a non-existent role from an existing user. However, the role name must be specified using the same letter case that was used to grant the role.
If you attempt to revoke an existing role from a non-existent user or role, Caché issues an SQLCODE -118 error. If you are not the SuperUser, and you attempt to revoke a role that you don't own and don't have ADMIN OPTION for, Caché issues an SQLCODE -112 error.
Revoking Object Privileges
Object privileges give a user or role some right to a particular object. You revoke an object-privilege
ON an object-list
FROM a grantee
. An object-list
can specify one or more tables, views, stored procedures, or cubes in the current namespace. By using comma-separated lists, a single REVOKE
statement can revoke multiple object privileges on multiple objects from multiple users and/or roles.
You can revoke an object privilege from a user or from a role. If you revoke it from a role, a user that only had that privilege through the role no longer has the privilege. A user that no longer has a privilege can no longer execute an existing cached query that requires that object privilege.
revokes an object privilege, it completes successfully and sets SQLCODE to 0. If REVOKE
does not perform an actual revoke (for example, the specified object privilege was never granted or has already been revoked), it completes successfully and sets SQLCODE to 100 (no more data). If an error occurs during the REVOKE
operation, it sets SQLCODE to a negative number.
Cubes are SQL identifiers that are not qualified by a schema name. To specify a cubes object-list
, you must specify the CUBE (or CUBES) keyword. Because cubes can only have SELECT privilege, you can only revoke SELECT privilege from a cube.
Object privileges can be revoked by any of the following:
Via Caché System Security. Go to the Management Portal
, select [Home] > [Security Management] > [Users]
(or [Home] > [Security Management] > [Roles]
) select Edit
for the desired user or role, then select the SQL Tables
or SQL Views
tab. Select the desired Namespace
from the drop-down list. Scroll down to the desired table, then click revoke
to revoke privileges.
You can determine if the current user has a specified object privilege by invoking the %CHECKPRIV
command. You can determine if a specified user has a specified table-level object privilege by invoking the $SYSTEM.SQL.CheckPriv()
Revoking Object Owner Privileges
If you revoke the privileges on an SQL object from the owner of the object, the owner will still implicitly have privileges on the object. In order to completely revoke all privileges on the object from the owner of the object, the object must be changed to specify a different owner or no owner.
Prior to Caché 2013.1, if you revoke the privileges on an SQL object from the owner of the object, the owner will cease to have privileges on the object. However, if the class is later recompiled, the privileges for the owner would be granted again.
Revoking Table-level and Column-level Privileges
can be used to reverse the granting of table-level privileges or column-level privileges. A table-level privilege provides access to all of the columns in a table. A column-level privilege provides access to every specified column in the table. Granting a column-level privilege to all of the columns in a table is functionally equivalent to granting a table-level privilege. However, the two are not functionally identical. A column-level REVOKE
can only revoke privileges granted at the column level. You cannot grant a table-level privilege to the table, then revoke this privilege at the column level for one or more columns. In this case, the REVOKE
statement has no effect on granted privileges.
Caché supports the optional CASCADE and RESTRICT keywords to specify REVOKE object-privilege
behavior. If neither keyword is specified, the default is RESTRICT.
You can use CASCADE or RESTRICT to specify whether revoking an object-privilege
from a user will also revoke that privilege from any other users that received it via the WITH GRANT OPTION. CASCADE revokes all such associated privileges. RESTRICT (the default) causes REVOKE
to fail when an associated privilege is detected. Instead it sets the SQLCODE -126 error REVOKE with RESTRICT failed.
The use of these keywords is shown by the following example:
GRANT Select ON MyTable TO UserB WITH GRANT OPTION
GRANT Select ON MyTable TO UserC
REVOKE Select ON MyTable FROM UserB
-- This REVOKE fails with SQLCODE -126
REVOKE Select ON MyTable FROM UserB CASCADE
-- This REVOKE succeeds
-- It revokes this privilege from UserB and UserC
Note that CASCADE and RESTRICT have no effect on a view created by UserB that references MyTable.
When you revoke a privilege or role, Caché updates all cached queries
on the system to reflect this change in privileges. However, when a namespace is inaccessible for example, when an ECP connection to a database server is down the REVOKE
successfully completes but performs no operation on cached queries in that namespace. This is because REVOKE
cannot update the cached queries in the unreachable namespace to revoke the privileges at the cached query level. No error is issued.
If the database server later comes up, the privileges for the cached queries in that namespace may be incorrect. It is advised that you purge cached queries in a namespace if a role or privilege might have been revoked while the namespace was not accessible.
At Caché Version 5.1 and all subsequent versions, REVOKE
no longer supports the following general administrative privileges: %GRANT_ANY_PRIVILEGE, %CREATE_USER, %ALTER_USER, %DROP_USER, %CREATE_ROLE, %GRANT_ANY_ROLE, %DROP_ANY_ROLE. Control of these privileges is handled at the system level, rather than through SQL. These SQL privileges were available in prior versions of Caché, and may appear in existing code. An attempt to revoke one of these may execute, but it results not in the revoking of a privilege, but in attempting to revoke a role having this name from the specified user(s). Similarly, attempting to revoke %THRESHOLD now results in attempting to revoke a role having this name from the specified user(s).
command is a privileged operation. Prior to using REVOKE
in embedded SQL, it is necessary to be logged in as a user with appropriate privileges. Failing to do so results in an SQLCODE -99 error (Privilege Violation).
The following embedded SQL example creates two users, creates a role, and assigns the role to the users. It then revokes the role from all users using the asterisk (*) syntax. If the user or the role already exists, the CREATE statement issues an SQLCODE -118 error. If the user does not exist, the GRANT or REVOKE statement issues an SQLCODE -118 error. If the user exists but the role does not, the GRANT or REVOKE statement issues SQLCODE 100. If the user and role exist, the GRANT or REVOKE statement issues SQLCODE 0. This is true even when the granting or revoking of the role has already been done, of if you are attempting to revoke a role that was never granted.
&sql(CREATE USER User1 IDENTIFY BY fredpw)
&sql(CREATE USER User2 IDENTIFY BY barneypw)
WRITE !,"CREATE USER error code: ",SQLCODE
&sql(CREATE ROLE workerbee)
WRITE !,"CREATE ROLE error code: ",SQLCODE
&sql(GRANT workerbee TO User1,User2)
WRITE !,"GRANT role error code: ",SQLCODE
&sql(REVOKE workerbee FROM *)
WRITE !,"REVOKE role error code: ",SQLCODE
In the following example, one user (Joe) grants a privilege and a different user (John) revokes that privilege, using the AS grantor
/* User Joe */
GRANT SELECT ON Sample.Person TO Michael
/* User John */
REVOKE SELECT ON Sample.Person FROM Michael AS Joe
Note that John must have the %All role or the %Admin_Secure resource.