Use the REVOKE statement to remove 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 following types of privileges can be revoked:
- Delete data from a specific table.
- Insert data into a specific table.
- Create a foreign key reference to the named table or to a subset of columns
from a table.
- Select data from a table, view, or a subset of columns in a table.
- Create a trigger on a table.
- Update data in a table or in a subset of columns in a table.
- Run a specified routine (function or procedure).
- Use a sequence generator or a user-defined type.
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 SQL standard
authorization" in the Derby Developer's Guide.
Syntax for tables
REVOKE privilege-type ON [ TABLE ] { table-Name | view-Name } FROM grantees
Revoking a privilege without specifying a column list revokes
the privilege for all of the columns in the table.
Syntax for routines
REVOKE EXECUTE ON { FUNCTION | PROCEDURE } routine-designator FROM grantees 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.
Syntax for sequence generators
REVOKE USAGE ON SEQUENCE [ schemaName. ] SQL92Identifier FROM grantees 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.
The sequence name is composed of an optional schemaName and a
SQL92Identifier. If a schemaName is not provided, the current
schema is the default schema. If a qualified sequence name is specified, the
schema name cannot begin with SYS.
Syntax for user-defined types
REVOKE USAGE ON TYPE [ schemaName. ] SQL92Identifier FROM grantees 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.
The type name is composed of an optional schemaName and a
SQL92Identifier. If a schemaName is not provided, the current
schema is the default schema. If a qualified type name is specified, the schema
name cannot begin with SYS.
privilege-types
ALL PRIVILEGES |
privilege-list
privilege-list
table-privilege {, table-privilege }*
table-privilege
DELETE |
INSERT |
REFERENCES [column list] |
SELECT [column list] |
TRIGGER |
UPDATE [column list]
column list
( column-identifier {, column-identifier}* )
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 privilege-list.
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.
grantees
{ 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 granted 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.
Restriction: You cannot revoke the privileges
of the owner of an object.
routine-designator
{
qualified-name [ signature ]
}
sequenceName
[ schemaName. ] SQL92Identifier
If schemaName is not provided, the current schema is the default
schema. If a qualified sequence name is specified, the schema name cannot
begin with SYS.
Prepared statements and open result sets/cursors
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.
Cascading object dependencies
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 "Using SQL standard
authorization" and "Privileges on views, triggers, and constraints" in the
Derby Developer's Guide.
Limitations
The following limitations apply to the
REVOKE statement:
- Table-level privileges
- All of the table-level privilege types for a specified grantee and table
ID are stored in one row in the SYSTABLEPERMS system table. For example, when user2 is
granted the SELECT and DELETE privileges on table user1.t1,
a row is added to the SYSTABLEPERMS table. The GRANTEE field contains user2 and
the TABLEID contains user1.t1. The SELECTPRIV and DELETEPRIV
fields are set to Y. The remaining privilege type fields are set to N.
When
a grantee 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 grantee and table ID, all of the objects that are
dependent on the grantee 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).
- Column-level privileges
- Only one type of privilege for a specified grantee and table ID are stored
in one row in the SYSCOLPERMS system table. For example, when user2 is
granted the SELECT privilege on table user1.t1 for columns
c12 and c13, a row is added to the SYSCOLPERMS. The GRANTEE field contains user2,
the TABLEID contains user1.t1, the TYPE field contains S,
and the COLUMNS field contains c12, c13.
When a grantee
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 grantee, table
ID, and type, all of the objects that are dependent on the grantee, 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).
- Roles
- Derby tracks any
dependencies on the definer's current role for views, constraints, and triggers.
If privileges were obtainable only via the current role when the object in
question was defined, that object depends on the current role. The object will
be dropped if the role is revoked from the defining user or from PUBLIC, as the
case may be. Also, if a contained role of the current role in such cases is
revoked, dependent objects will be dropped. Note that dropping may be too
pessimistic. This is because
Derby does not currently make
an attempt to recheck if the necessary privileges are still available in such
cases.
Revoke examples
To revoke the SELECT privilege
on table
t from the authorization IDs
maria and
harry,
use the following syntax:
REVOKE SELECT ON TABLE t FROM maria,harry
To
revoke the UPDATE and TRIGGER privileges on table
t from
the authorization IDs
anita and
zhi, use
the following syntax:
REVOKE UPDATE, TRIGGER ON TABLE t FROM anita,zhi
To
revoke the SELECT privilege on table
s.v from all users,
use the following syntax:
REVOKE SELECT ON TABLE s.v FROM PUBLIC
To
revoke the UPDATE privilege on columns
c1 and
c2 of
table
s.v from all users, use the following syntax:
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;
