602SQL Documentation Index  

REVOKE Statement

statement_REVOKE ::= statement_REVOKE_for_tables | statement_REVOKE_for_routines

statement_REVOKE_for_tables ::= REVOKE [ GRANT OPTION FOR ] privilege_list ON [scheme.]table_name 
    FROM subject_list [ WHERE where_clause ]
privilege_list ::= ALL [ PRIVILEGES ] | single_privilege {, single_privilege }
single_privilege ::=  DELETE | INSERT | SELECT | UPDATE [( column_list )] 
    | REFERENCES [( column_list ) | GRANT ]
column_list ::= column { , column }
subject_list ::= PUBLIC | subject {, subject }
subject ::= [ USER ] user_name | [ GROUP ] role_name | ROLE [scheme. ] role_name

statement_REVOKE_for_routines ::= REVOKE [ GRANT OPTION FOR ] privilege_list ON [ PROCEDURE | FUNCTION ] 
    [scheme.]routine_name FROM subject_list
privilege_list ::= ALL [ PRIVILEGES ] | single_privilege {, single_privilege }
single_privilege ::= EXECUTE | UPDATE 

Description

The REVOKE statement removes a user, group or role privileges specified in the subject_list on one of the following:

It is recommended to distinguish routines with the PROCEDURE or FUNCTION keyword in order to avoid a conflict with a table of the same routine name.

If individual record privileges must be removed, the WHERE clause is used as the restricting condition for table table_name records (record privileges must be enabled on the table).

The privilege_list defines which privileges should be removed. If you use the ALL keyword, all possible privileges on the table will be removed. If you only want to remove certain privileges, you must specify them individually, separated with a comma. The following privileges are available for tables:

The INSERT privilege is not available for record privileges.

Privilege list for routines:

The subject_list specifies which privilege subjects is affected by the privilege removal. If you use the PUBLIC keyword, privileges will be removed from the EVERYBODY group. Otherwise, only the users, groups or roles specified in the list are affected. If you use a group name, the privileges will be removed from this group (e.g. users assigned to this group automatically lose these privileges, unless granted explicitly). If the keyword USER, GROUP or ROLE is not specified for the privilege subject, then the name is searched for among users, then groups.

If you use the GRANT OPTION FOR clause, users and groups lose the privilege to grant their privileges to other subjects (same as GRANT in the privilege_list).

Example:

Prevent users from deleting records and editing the employee number and salary.

REVOKE DELETE, UPDATE (Salary, Number)
ON Employed
FROM PUBLIC

See