REVOKE Object-Level Privilege Statement

Quick Links:

Go to Parameters

Go to Examples

Go to Standards

Go to Permissions

Syntax

REVOKE { object-level-privilege [,...]
    [ owner.]table-name 
   FROM userID [,...]

object-level-privilege
   ALL [ PRIVILEGES ] 
   | ALTER 
   | DELETE 
   | INSERT
   | LOAD
   | REFERENCE [ ( column-name [, …] ) ] 
   | SELECT [ ( column-name [, …] ) ] 
   | TRUNCATE
   | UPDATE [ ( column-name, …) ] }

Parameters

(back to top)

• userID – must be the name of an existing user or immutable role. The list must consist of existing users with login passwords. Separate the userIDs in the list with commas.

• ALL – grants all privileges to users

• ALTER – users can alter this table with the ALTER TABLE statement. This privilege is not allowed for views.

• DELETE – users can delete rows from this table or view.

• INSERT – users can insert rows into the named table or view.

• LOAD – users can load data into the named table or view.

• REFERENCES – users can create indexes on the named tables, and foreign keys that reference the named tables. If column names are specified, then users can reference only those columns. REFERENCES privileges on columns cannot be granted for views, only for tables.

• SELECT – users can look at information in this view or table. If column names are specified, then the users can look at only those columns. SELECT permissions on columns cannot be granted for views, only for tables.

• TRUNCATE – users can truncate the named table or view.

• UPDATE – users can update rows in this view or table. If column names are specified, users can update only those columns. UPDATE privileges on columns cannot be granted for views, only for tables. To update a table, users must have both SELECT and UPDATE privilege on the table.

Examples

(back to top)

• Example 1 – prevents user Dave from inserting into the Employees table:

  REVOKE INSERT ON Employees FROM Dave

• Example 2 – prevents user Dave from updating the Employees table:

  REVOKE UPDATE ON Employees FROM Dave

Standards

(back to top)

Permissions

(back to top)

Requires one of:

• Own the table, or
• Have the MANAGE ANY OBJECT PRIVILEGE system privilege granted with the GRANT OPTION clause.