This
form of the REVOKE statement revokes authorities that apply to the
entire database.
Invocation
This statement can be embedded
in an application program or issued through the use of dynamic SQL
statements. It is an executable statement that can be dynamically
prepared only if DYNAMICRULES run behavior is in effect for the package
(SQLSTATE 42509).
Authorization
To
revoke ACCESSCTRL, CREATE_SECURE_OBJECT, DATAACCESS, DBADM, or SECADM
authority, SECADM authority is required. To revoke other authorities,
ACCESSCTRL or SECADM authority is required.
Syntax
.-,-----------------------------.
V |
>>-REVOKE----+-ACCESSCTRL----------------+-+--ON DATABASE------->
+-BINDADD-------------------+
+-CONNECT-------------------+
+-CREATETAB-----------------+
+-CREATE_EXTERNAL_ROUTINE---+
+-CREATE_NOT_FENCED_ROUTINE-+
+-CREATE_SECURE_OBJECT------+
+-DBADM---------------------+
+-DATAACCESS----------------+
+-EXPLAIN-------------------+
+-IMPLICIT_SCHEMA-----------+
+-LOAD----------------------+
+-QUIESCE_CONNECT-----------+
+-SECADM--------------------+
+-SQLADM--------------------+
'-WLMADM--------------------'
.-,---------------------------------.
V | .-BY ALL-.
>--FROM----+-+-------+--authorization-name-+-+--+--------+-----><
| +-USER--+ |
| +-GROUP-+ |
| '-ROLE--' |
'-PUBLIC------------------------'
Description
- ACCESSCTRL
- Revokes the authority to grant and revoke most database authorities
and object privileges.
- BINDADD
- Revokes the authority to create packages. The creator of a package
automatically has the CONTROL privilege on that package and retains
this privilege even if his BINDADD authority is subsequently revoked.
The BINDADD authority cannot be revoked from an authorization-name holding
DBADM authority without also revoking the DBADM authority.
- CONNECT
- Revokes the authority to access the database.
Revoking the
CONNECT authority from a user does not affect any privileges that
were granted to that user on objects in the database. If the user
is subsequently granted the CONNECT authority again, all previously
held privileges are still valid (assuming they were not explicitly
revoked).
The CONNECT authority cannot be revoked from an authorization-name holding
DBADM authority without also revoking the DBADM authority (SQLSTATE
42504).
- CREATETAB
- Revokes the authority to create tables. The creator of a table
automatically has the CONTROL privilege on that table, and retains
this privilege even if his CREATETAB authority is subsequently revoked.
The CREATETAB authority cannot be revoked from an authorization-name holding
DBADM authority without also revoking the DBADM authority (SQLSTATE
42504).
- CREATE_EXTERNAL_ROUTINE
- Revokes the authority to register external routines. Once an external
routine has been registered, it continues to exist, even if CREATE_EXTERNAL_ROUTINE
is subsequently revoked from the authorization ID that registered
the routine.
CREATE_EXTERNAL_ROUTINE authority cannot be revoked
from an authorization-name holding DBADM
or CREATE_NOT_FENCED_ROUTINE authority without also revoking DBADM
or CREATE_NOT_FENCED_ROUTINE authority (SQLSTATE 42504).
- CREATE_NOT_FENCED_ROUTINE
- Revokes the authority to register routines that execute in the
database manager's process. Once a routine has been registered as
not fenced, it continues to run in this manner, even if CREATE_NOT_FENCED_ROUTINE
is subsequently revoked from the authorization ID that registered
the routine.
CREATE_NOT_FENCED_ROUTINE authority cannot be revoked
from an authorization-name holding DBADM
authority without also revoking the DBADM authority (SQLSTATE 42504).
- CREATE_SECURE_OBJECT
- Revokes the authority to create secure triggers and secure functions.
Revokes the authority to alter the secure attribute of such objects
as well.
- DATAACCESS
- Revokes the authority to access data.
- DBADM
- Revokes the DBADM authority.
DBADM authority cannot be revoked
from PUBLIC (because it cannot be granted to PUBLIC).
CAUTION:
Revoking DBADM authority does not
automatically revoke any privileges that were held by the authorization-name on
objects in the database.
- EXPLAIN
- Revokes the authority to explain, prepare, and describe static
and dynamic statements without requiring access to data.
- IMPLICIT_SCHEMA
- Revokes the authority to implicitly create a schema. It does not
affect the ability to create objects in existing schemas or to process
a CREATE SCHEMA statement.
IMPLICIT_SCHEMA authority cannot be
revoked from an authorization-name holding
DBADM authority without also revoking the DBADM authority (SQLSTATE
42504).
- LOAD
- Revokes the authority to LOAD in this database.
- QUIESCE_CONNECT
- Revokes the authority to access the database while it is quiesced.
- SECADM
- Revokes the authority to administer database
security.
- SQLADM
- Revokes the authority to monitor and tune SQL statements.
- WLMADM
- Revokes the authority to manage workload manager objects.
- FROM
- Indicates from whom the authorities are revoked.
- USER
- Specifies that the authorization-name identifies
a user.
- GROUP
- Specifies that the authorization-name identifies
a group name.
- ROLE
- Specifies that the authorization-name identifies
a role name.
- authorization-name,...
- Lists the authorization IDs of one or more users, groups, or
roles.
The list of authorization IDs cannot include the authorization
ID of the user issuing the statement (SQLSTATE 42502).
- PUBLIC
- Revokes the authorities from PUBLIC.
- BY ALL
- Revokes each named privilege from all named users who were explicitly
granted those privileges, regardless of who granted them. This is
the default behavior.
Rules
Security administrator
mandatory: The database must have at least one authorization ID
of type USER with the SECADM authority. The SECADM authority cannot
be revoked from every user authorization ID (SQLSTATE 42523).
- For each authorization-name specified, if neither
USER, GROUP, nor ROLE is specified, then:
- For all rows for the specified object in the SYSCAT.DBAUTH catalog
view where the grantee is authorization-name:
- If all rows have a GRANTEETYPE of 'U', USER is assumed.
- If all rows have a GRANTEETYPE of 'G', GROUP is assumed.
- If all rows have a GRANTEETYPE of 'R', ROLE is assumed.
- If all rows do not have the same value for GRANTEETYPE, an error
is returned (SQLSTATE 56092).
Notes
- Revoking a specific privilege does not necessarily revoke the
ability to perform an action. A user can proceed with a task if other
privileges are held by PUBLIC, a group, or a role, or if
the user holds a higher level authority, such as DBADM.
- Syntax
alternatives: The following syntax alternatives are supported
for compatibility with previous versions of DB2® and with other database products.
- CREATE_NOT_FENCED can be specified in place of CREATE_NOT_FENCED_ROUTINE
- SYSTEM can be specified in place of DATABASE
- NOT INCLUDING DEPENDENT PRIVILEGES may be specified as a syntax
alternative
Examples
- Example 1: Given that USER6 is only a user and not a group,
revoke the privilege to create tables from the user USER6.
REVOKE CREATETAB ON DATABASE FROM USER6
- Example 2: Revoke BINDADD authority on the database from
a group named D024. There are two rows in the SYSCAT.DBAUTH catalog
view for this grantee; one with a GRANTEETYPE of U and one with a
GRANTEETYPE of G.
REVOKE BINDADD ON DATABASE FROM GROUP D024
In
this case, the GROUP keyword must be specified; otherwise an error
will occur (SQLSTATE 56092).
- Example 3: Revoke security administrator authority from
user Walid.
REVOKE SECADM ON DATABASE FROM USER Walid
- Example 4: A user with SECADM
authority revokes the CREATE_SECURE_OBJECT authority from user Haytham.
REVOKE CREATE_SECURE_OBJECT ON DATABASE FROM USER HAYTHAM