DB2 10.5 for Linux, UNIX, and Windows

REVOKE (table, view, or nickname privileges) statement

This form of the REVOKE statement revokes privileges on a table, view, or nickname.

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

The privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • CONTROL privilege on the referenced table, view, or nickname
  • ACCESSCTRL or SECADM authority

ACCESSCTRL or SECADM authority is required to revoke the CONTROL privilege, or to revoke privileges on catalog tables and views.

Syntax

Read syntax diagramSkip visual syntax diagram
                  .-PRIVILEGES-.        .-TABLE-.   
>>-REVOKE--+-ALL--+------------+-+--ON--+-------+--------------->
           | .-,--------------.  |                  
           | V                |  |                  
           '---+-ALTER------+-+--'                  
               +-CONTROL----+                       
               +-DELETE-----+                       
               +-INDEX------+                       
               +-INSERT-----+                       
               +-REFERENCES-+                       
               +-SELECT-----+                       
               '-UPDATE-----'                       

>--+-table-name-+----------------------------------------------->
   +-view-name--+   
   '-nickname---'   

         .-,---------------------------------.               
         V                                   |  .-BY ALL-.   
>--FROM----+-+-------+--authorization-name-+-+--+--------+-----><
           | +-USER--+                     |                 
           | +-GROUP-+                     |                 
           | '-ROLE--'                     |                 
           '-PUBLIC------------------------'                 

Description

ALL or ALL PRIVILEGES
Revokes all privileges (except CONTROL) held by an authorization-name for the specified tables, views, or nicknames.

If ALL is not used, one or more of the keywords listed in the option stack (ALTER through UPDATE) must be used. Each keyword revokes the privilege described, but only as it applies to the tables, views, or nicknames named in the ON clause. The same keyword must not be specified more than once.

ALTER
Revokes the privilege to add columns to the base table definition; create or drop a primary key or unique constraint on the table; create or drop a foreign key on the table; add/change a comment on the table, view, or nickname; create or drop a check constraint; create a trigger; add, reset, or drop a column option for a nickname; or, change nickname column names or data types.
CONTROL
Revokes the ability to drop the table, view, or nickname, and the ability to execute the RUNSTATS utility on the table and indexes.

Revoking CONTROL privilege from an authorization-name does not revoke other privileges granted to the user on that object.

DELETE
Revokes the privilege to delete rows from the table, updatable view, or nickname.
INDEX
Revokes the privilege to create an index on the table or an index specification on the nickname. The creator of an index or index specification automatically has the CONTROL privilege over the index or index specification (authorizing the creator to drop the index or index specification). In addition, the creator retains this privilege even if the INDEX privilege is revoked.
INSERT
Revokes the privileges to insert rows into the table, updatable view, or nickname, and to run the IMPORT utility.
REFERENCES
Revokes the privilege to create or drop a foreign key referencing the table as the parent. Any column level REFERENCES privileges are also revoked.
SELECT
Revokes the privilege to retrieve rows from the table or view, to create a view on a table, and to run the EXPORT utility against the table or view.

Revoking SELECT privilege may cause some views to be marked inoperative. (For information about inoperative views, see "CREATE VIEW".)

UPDATE
Revokes the privilege to update rows in the table, updatable view, or nickname. Any column level UPDATE privileges are also revoked.
ON TABLE table-name or view-name or nickname
Specifies the table, view, or nickname on which privileges are to be revoked. The table-name cannot be a declared temporary table (SQLSTATE 42995).
FROM
Indicates from whom the privileges 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 privileges 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

Notes

Examples