Issue REVOKE statements to withdraw privileges.
For example, the following statement withdraws the
SELECT privilege from user BAKER on the table SMITH.TABLEA:
REVOKE SELECT ON TABLE SMITH.TABLEA FROM BAKER
You can always withdraw grants for which your authorization
ID is the grantor.
If you revoke a privilege from a grantee and find that the grantee
still has the privilege, that grantee received the privilege from
another user. If your SQL authorization ID has SYSADM or equivalent
authority, however, you can revoke the grants of others. By using
SYSADM or equivalent authority you can revoke privileges even if they
are a result of multiple grants. For example, BAKER has the SELECT
privilege on the table SMITH.TABLEA. The QMF administrator
wants to remove this privilege from BAKER, but does not know who the
grantors are. The QMF administrator,
who has SYSADM authority, can run the following statement:
REVOKE SELECT ON TABLE SMITH.TABLEA FROM BAKER BY ALL
The
clause BY ALL removes every grant of the privilege.
Use the PUBLIC keyword to revoke privileges from all QMF users.
You cannot remove a table privilege from the owner of a table.
Additionally, you cannot remove an implied database privilege, such
as CREATETAB, from someone with, for example, DBADM authority over
a database.
Database privileges have a cascading structure. Privileges that
are revoked from a user are automatically revoked from any additional
users to whom that user granted them.
The loss of privileges can spread to many users, especially if
some of those users who lost privileges granted privileges to others.
With this loss of privileges might come other losses as well:
- The owner of a view loses the view if the owner loses the SELECT
privilege on one of the underlying objects. Views for which the lost
view is an underlying object are also lost, and so on.
- A Db2® application plan can
become invalid if the authorization ID under which it was bound loses
a privilege that the plan needs for the operation of the program.
For example, if the SELECT privilege is lost on a table, no one can
run the program.
Problems that result from cascading privileges are more likely
when many users can grant database privileges. So consider carefully
which users in your organization you want to be responsible for this
task.
Tip: If you must revoke different privileges from many
users at once, you can include multiple REVOKE statements in a single
SQL query. To create a query that includes multiple statements, place
a semicolon between statements and set the DSQEC_RUN_MQ global variable
to 1. Information about how to create queries that include multiple
statements as well as restrictions on the types of statements that
can be used is included in the description of the RUN command. Alternatively,
you can create a template query that uses QMF variables
in place of the parts of the query that frequently change (such as
the type of privilege, the object name, and the authorization ID).
You might also use a QMF procedure
to do the task if there is more than one query.