REVOKE (Table or View Privileges)
This form of the REVOKE statement removes privileges on a table or view.
Invocation
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
Authorization
The privileges held by the authorization ID of the statement must include at least one of the following:
- For each table or view identified in the statement:
- Every privilege specified in the statement
- The system authority of *OBJMGT on the table or view
- The system authority *EXECUTE on the library containing the table or view
- Administrative authority
Syntax
.-PRIVILEGES-. >>-REVOKE--+-ALL--+------------+---------------------------+----> | .-,-----------------------------------------. | | V | | '---+-ALTER---------------------------------+-+-' +-DELETE--------------------------------+ +-INDEX---------------------------------+ +-INSERT--------------------------------+ +-REFERENCES--+-----------------------+-+ | | .-,-----------. | | | | V | | | | '-(----column-name-+--)-' | +-SELECT--------------------------------+ '-UPDATE--+-----------------------+-----' | .-,-----------. | | V | | '-(----column-name-+--)-' .-,--------------. .-TABLE-. V | >--ON--+-------+----+-table-name-+-+--FROM----------------------> '-view-name--' .-,----------------------. V | >----+-authorization-name-+-+---------------------------------->< '-PUBLIC-------------'
Description
- ALL or ALL PRIVILEGES
- Revokes
one or more privileges from each authorization-name. The privileges
revoked are those privileges on the identified tables and views that
were granted to the authorization-names. Note that revoking
ALL PRIVILEGES on a table or view is not the same as revoking the
system authority of *ALL.
If you do not use ALL, you must use one or more of the keywords listed below. Each keyword revokes the privilege described, but only as it applies to the tables and views named in the ON clause.
- ALTER
- Revokes the privilege to alter the specified table and the privilege to add a comment, add a label, or create an index on the specified table or view.
- DELETE
- Revokes the privilege to delete rows from the specified table or view.
- INDEX
- Revokes the privilege to create an index on the specified table.
- INSERT
- Revokes the privilege to insert rows in the specified table or view.
- REFERENCES
- Revokes the privilege to add a referential constraint in which the table is a parent.
- REFERENCES (column-name,…)
- Revokes the privilege to add a referential constraint using the specified column(s) in the parent key. Each column name must be an unqualified name that identifies a column in each table identified in the ON clause.
- SELECT
- Revokes the privilege to use the SELECT or CREATE VIEW statement.
- UPDATE
- Revokes the privilege to use the UPDATE statement.
- UPDATE (column-name,…)
- Revokes the privilege to update the specified columns. Each column name must be an unqualified name that identifies a column in each table identified in the ON clause.
- ON table-name or view-name, …
- Identifies the table or view from which the privileges are revoked. The table-name or view-name must identify a table or view that exists at the current server, but must not identify a declared temporary table.
- FROM
- Identifies from whom the privileges
are revoked.
- authorization-name,…
- Lists one or more authorization IDs. Do not specify the same authorization-name more than once.
- PUBLIC
- Revokes a grant of the privilege to PUBLIC. For more information, see Authorization, privileges and object ownership.
Notes
Multiple grants: If the same privilege is granted to the same user more than once, revoking that privilege from that user nullifies all those grants.
If you revoke a privilege, it nullifies any grant of that privilege, regardless of who granted it.
Revoking WITH GRANT OPTION: The only way to revoke the WITH GRANT OPTION is to revoke ALL.
Privilege warning: Revoking a specific privilege from a user does not necessarily prevent that user from performing an action that requires that privilege. For example, the user may still have the privilege through PUBLIC or administrative privileges.
If more than one system authority will be revoked with an SQL privilege, and any one of the authorities cannot be revoked, then a warning occurs and no authorities will be revoked for that privilege.
Corresponding system authorities: When a table privilege is revoked, the corresponding system authorities are revoked, except:
- When revoking authorities to a table or view, *OBJOPR is revoked only when *ADD, *DLT, *READ, and *UPD have all been revoked.
- When revoking authorities to a view, authorities will not be revoked from any tables or views referenced in the fullselect of the view definition.
For information about the system authorities that correspond to SQL privileges see GRANT (Table or View Privileges).
Revoking either the INDEX or ALTER privilege, revokes the system authority *OBJALTER.
Examples
Example 1: Revoke SELECT privileges on table EMPLOYEE from user ENGLES.
REVOKE SELECT
ON TABLE EMPLOYEE
FROM ENGLES
Example 2: Revoke update privileges on table EMPLOYEE previously granted to all users. Note that grants to specific users are not affected.
REVOKE UPDATE
ON TABLE EMPLOYEE
FROM PUBLIC
Example 3: Revoke all privileges on table EMPLOYEE from users PELLOW and ANDERSON.
REVOKE ALL
ON TABLE EMPLOYEE
FROM PELLOW, ANDERSON
Example 4: Revoke the privilege to update column_1 in VIEW1 from FRED.
REVOKE UPDATE(column_1)
ON VIEW1
FROM FRED