The REVOKE statement removes privileges from a specific user or role, or from all users, to perform actions on database objects.
You can also use the REVOKE statement to revoke a role from a user, from PUBLIC, or from another role.
The derby.database.sqlAuthorization property must be set to true before you can use the GRANT statement or the REVOKE statement. The derby.database.sqlAuthorization property enables SQL Authorization mode.
You can revoke privileges for an object if you are the owner of the object or the database owner.
The syntax that you use for the REVOKE statement depends on whether you are revoking privileges to a schema object or revoking a role.
For more information on using the REVOKE statement, see "Using fine-grained user authorization" in the Derby Security Guide.
REVOKE privilegeType ON [ TABLE ] { tableName | viewName } FROM revokees
Revoking a privilege without specifying a column list revokes the privilege for all of the columns in the table.
REVOKE EXECUTE ON FUNCTION functionName FROM revokees RESTRICT
REVOKE EXECUTE ON PROCEDURE procedureName FROM revokees RESTRICT
You must use the RESTRICT clause on REVOKE statements for routines. The RESTRICT clause specifies that the EXECUTE privilege cannot be revoked if the specified routine is used in a view, trigger, or constraint, and the privilege is being revoked from the owner of the view, trigger, or constraint.
REVOKE USAGE ON SEQUENCE sequenceName FROM revokees RESTRICT
In order to use a sequence generator, you must have the USAGE privilege on it. This privilege can be revoked from users and roles. Only RESTRICTed revokes are allowed. This means that the REVOKE statement cannot make a view, trigger, or constraint unusable by its owner. The USAGE privilege cannot be revoked from the schema owner. See CREATE SEQUENCE statement for more information.
REVOKE USAGE ON TYPE typeName FROM revokees RESTRICT
In order to use a user-defined type, you must have the USAGE privilege on it. This privilege can be revoked from users and roles. Only RESTRICTed revokes are allowed. This means that the REVOKE statement cannot make a view, trigger, or constraint unusable by its owner. The USAGE privilege cannot be revoked from the schema owner. See CREATE TYPE statement for more information.
REVOKE USAGE ON DERBY AGGREGATE aggregateName FROM revokees RESTRICT
In order to use a user-defined aggregate, you must have the USAGE privilege on it. This privilege can be revoked from users and roles. Only RESTRICTed revokes are allowed. This means that the REVOKE statement cannot make a view or trigger unusable by its owner. The USAGE privilege cannot be revoked from the schema owner. See CREATE DERBY AGGREGATE statement for more information.
REVOKE roleName [ , roleName ]* FROM revokees
Only the database owner can revoke a role.
DELETE | INSERT | REFERENCES [ columnList ] | SELECT [ columnList ] | TRIGGER | UPDATE [ columnList ]
Use the ALL PRIVILEGES privilege type to revoke all of the privileges from the user or role for the specified table. You can also revoke one or more table privileges by specifying a privilegeList.
Use the DELETE privilege type to revoke permission to delete rows from the specified table.
Use the INSERT privilege type to revoke permission to insert rows into the specified table.
Use the REFERENCES privilege type to revoke permission to create a foreign key reference to the specified table. If a column list is specified with the REFERENCES privilege, the permission is revoked on only the foreign key reference to the specified columns.
Use the SELECT privilege type to revoke permission to perform SELECT statements on a table or view. If a column list is specified with the SELECT privilege, the permission is revoked on only those columns. If no column list is specified, then the privilege is valid on all of the columns in the table.
Use the TRIGGER privilege type to revoke permission to create a trigger on the specified table.
Use the UPDATE privilege type to revoke permission to use the UPDATE statement on the specified table. If a column list is specified, the privilege is revoked only on the specified columns.
{ authorizationIdentifier | roleName | PUBLIC } [ , { authorizationIdentifier | roleName | PUBLIC } ]*
You can revoke the privileges from specific users or roles or from all users. Use the keyword PUBLIC to specify all users. The privileges revoked from PUBLIC and from individual users or roles are independent privileges. For example, a SELECT privilege on table t is revokeed to both PUBLIC and to the authorization ID harry. The SELECT privilege is later revoked from the authorization ID harry, but the authorization ID harry can access the table t through the PUBLIC privilege.
You can revoke a role from a role, from a user, or from PUBLIC.
Checking for privileges happens at statement execution time, so prepared statements are still usable after a revoke action. If sufficient privileges are still available for the session, prepared statements will be executed, and for queries, a result set will be returned.
Once a result set has been returned to the application (by executing a prepared statement or by direct execution), it will remain accessible even if privileges or roles are revoked in a way that would cause another execution of the same statement to fail.
For views, triggers, and constraints, if the privilege on which the object depends on is revoked, the object is automatically dropped. Derby does not try to determine if you have other privileges that can replace the privileges that are being revoked. For more information, see "Configuring fine-grained user authorization" and "Privileges on views, triggers, constraints, and generated columns" in the Derby Security Guide.
The following limitations apply to the REVOKE statement:
When a revokeee creates an object that relies on one of the privilege types, the Derby engine tracks the dependency of the object on the specific row in the SYSTABLEPERMS table. For example, user2 creates the view v1 by using the statement SELECT * FROM user1.t1, the dependency manager tracks the dependency of view v1 on the row in SYSTABLEPERMS for GRANTEE(user2), TABLEID(user1.t1). The dependency manager knows only that the view is dependent on a privilege type in that specific row, but does not track exactly which privilege type the view is dependent on.
When a REVOKE statement for a table-level privilege is issued for a revokeee and table ID, all of the objects that are dependent on the revokeee and table ID are dropped. For example, if user1 revokes the DELETE privilege on table t1 from user2, the row in SYSTABLEPERMS for GRANTEE(user2), TABLEID(user1.t1) is modified by the REVOKE statement. The dependency manager sends a revoke invalidation message to the view user2.v1 and the view is dropped even though the view is not dependent on the DELETE privilege for GRANTEE(user2), TABLEID(user1.t1).
When a revokeee creates an object that relies on the privilege type and the subset of columns in a table ID, the Derby engine tracks the dependency of the object on the specific row in the SYSCOLPERMS table. For example, user2 creates the view v1 by using the statement SELECT c11 FROM user1.t1, the dependency manager tracks the dependency of view v1 on the row in SYSCOLPERMS for GRANTEE(user2), TABLEID(user1.t1), TYPE(S). The dependency manager knows that the view is dependent on the SELECT privilege type, but does not track exactly which columns the view is dependent on.
When a REVOKE statement for a column-level privilege is issued for a revokeee, table ID, and type, all of the objects that are dependent on the revokeee, table ID, and type are dropped. For example, if user1 revokes the SELECT privilege on column c12 on table user1.t1 from user2, the row in SYSCOLPERMS for GRANTEE(user2), TABLEID(user1.t1), TYPE(S) is modified by the REVOKE statement. The dependency manager sends a revoke invalidation message to the view user2.v1 and the view is dropped even though the view is not dependent on the column c12 for GRANTEE(user2), TABLEID(user1.t1), TYPE(S).
REVOKE SELECT ON TABLE t FROM maria,harry
REVOKE UPDATE, TRIGGER ON TABLE t FROM anita,zhi
REVOKE SELECT ON TABLE s.v FROM PUBLIC
REVOKE UPDATE (c1,c2) ON TABLE s.v FROM PUBLIC
To revoke the EXECUTE privilege on procedure p from the authorization ID george, use the following syntax:
REVOKE EXECUTE ON PROCEDURE p FROM george RESTRICT
To revoke the role purchases_reader_role from the authorization IDs george and maria, use the following syntax:
REVOKE purchases_reader_role FROM george,maria
To revoke the SELECT privilege on table t from the role purchases_reader_role, use the following syntax:
REVOKE SELECT ON TABLE t FROM purchases_reader_role
To revoke the USAGE privilege on the sequence generator order_id from the role sales_role, use the following syntax:
REVOKE USAGE ON SEQUENCE order_id FROM sales_role;
To revoke the USAGE privilege on the user-defined type price from the role finance_role, use the following syntax:
REVOKE USAGE ON TYPE price FROM finance_role;
To revoke the USAGE privilege on the user-defined aggregate types.maxPrice from the role sales_role, use the following syntax:
REVOKE USAGE ON DERBY AGGREGATE types.maxPrice FROM sales_role;