The REVOKE statement allows authorized users to revoke
privileges previously granted to other users.
About this task
To revoke privileges on database objects,
you must have ACCESSCTRL authority, SECADM authority, or CONTROL privilege
on that object. Table space privileges can also be revoked by users
with SYSADM and SYSCTRL authority. Note that holding a privilege WITH
GRANT OPTION is not sufficient to revoke that privilege. To revoke
CONTROL privilege from another user, you must have ACCESSCTRL, or
SECADM authority. To revoke ACCESSCTRL, DATAACCESS, DBADM or SECADM
authority, you must have SECADM authority. Table space privileges
can be revoked only by a user who holds SYSADM, or SYSCTRL authority.
Privileges can only be revoked on existing objects.
Note: A user without ACCESSCTRL authority, SECADM authority,
or CONTROL privilege is not able to revoke a privilege that they granted
through their use of the WITH GRANT OPTION. Also, there is no cascade
on the revoke to those who have received privileges granted by the
person being revoked.
If an explicitly granted table (or view)
privilege is revoked from a user with DBADM authority, privileges
will not be revoked from other views defined on that table. This is
because the view privileges are available through the DBADM authority
and are not dependent on explicit privileges on the underlying tables.
If a privilege has been granted to a user, a group,
or a role with the same name, you must specify the GROUP, USER, or
ROLE keyword when revoking the privilege. The following example revokes
the SELECT privilege on the EMPLOYEE table from the user HERON:
REVOKE SELECT
ON EMPLOYEE FROM USER HERON
The following
example revokes the SELECT privilege on the EMPLOYEE table from the
group HERON:
REVOKE SELECT
ON EMPLOYEE FROM GROUP HERON
Note that revoking
a privilege from a group may not revoke it from all members of that
group. If an individual name has been directly granted a privilege,
it will keep it until that privilege is directly revoked.
If
a table privilege is revoked from a user, privileges are also revoked
on any view created by that user which depends on the revoked table
privilege. However, only the privileges implicitly granted by the
system are revoked. If a privilege on the view was granted directly
by another user, the privilege is still held.
If a table privilege
is revoked from a user, privileges are also revoked on any view created
by that user which depends on the revoked table privilege. However,
only the privileges implicitly granted by the system are revoked.
If a privilege on the view was granted directly by another user, the
privilege is still held.
You may have a situation where you
want to GRANT a privilege to a group and then REVOKE the privilege
from just one member of the group. There are only a couple of ways
to do that without receiving the error message SQL0556N:
- You can remove the member from the group; or, create a new group
with fewer members and GRANT the privilege to the new group.
- You can REVOKE the privilege from the group and then GRANT it
to individual users (authorization IDs).
Note: When CONTROL privilege is revoked from a user on
a table or a view, the user continues to have the ability to grant
privileges to others. When given CONTROL privilege, the user also
receives all other privileges WITH GRANT OPTION. Once CONTROL is revoked,
all of the other privileges remain WITH GRANT OPTION until they are
explicitly revoked.
All packages that are dependent on
revoked privileges are marked invalid, but can be validated if rebound
by a user with appropriate authority. Packages can also be rebuilt
if the privileges are subsequently granted again to the binder of
the application; running the application will trigger a successful
implicit rebind. If privileges are revoked from PUBLIC, all packages
bound by users having only been able to bind based on PUBLIC privileges
are invalidated. If DBADM authority is revoked from a user, all packages
bound by that user are invalidated including those associated with
database utilities. Attempting to use a package that has been marked
invalid causes the system to attempt to rebind the package. If this
rebind attempt fails, an error occurs (SQLCODE -727). In this case,
the packages must be explicitly rebound by a user with:
- Authority to rebind the packages
- Appropriate authority for the objects used within the packages
These packages should be rebound at the time the privileges
are revoked.
If you define a trigger or SQL function based on
one or more privileges and you lose one or more of these privileges,
the trigger or SQL function cannot be used.
DB2 Version 9.7 for Linux, UNIX, and Windows