REVOKE (Table)

Revokes privileges on a table from a user or role. Optionally revokes privileges on all tables within one or more schemas.

Note: Revoking privileges on all tables within a schema includes all views in the same schema.

In a database with trust authentication, the GRANT and REVOKE statements appear to work as expected but have no actual effect on the security of the database.

Syntax

REVOKE [ GRANT OPTION FOR ]... {
......{ SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRUNCATE } [ ,... ] 
......| ALL [ PRIVILEGES ] 
... }
... ON [ TABLE ] [ [ db-name.]schema.]tablename [ , ... ]
... | ON ALL TABLES IN SCHEMA schema-name [, ...]
... FROM { username | PUBLIC | role } [ , ... ]
... [ CASCADE ]

Parameters

GRANT OPTION FOR

Revokes the grant option for the privilege, not the privilege itself. If omitted, revokes both the privilege and the grant option.

 
SELECT

Revokes the user's ability to SELECT from any column of the specified table.

 
INSERT

Revokes the user from being able to INSERT tuples into the specified table and to use the COPY command to load the table.

Note: COPY FROM STDIN is allowed to any user granted the INSERT privilege, while COPY FROM <file> is an admin-only operation.

 
UPDATE

Revokes user from being allowed to UPDATE tuples in the specified table.

 
DELETE

Revokes user from being able to DELETE a row from the specified table.

 
REFERENCES

Revokes the user's privilege on both the referencing and referenced tables for creating a foreign key constraint. 

TRUNCATE

Revokes TRUNCATE TABLE privileges from non-owners of a table. It also revokes from the non-owner of the table the following partition functions:

 
ALL

Revokes all previously granted privileges.

 
PRIVILEGES

Is for SQL standard compatibility and is ignored.

 
[db-name.]schema

Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example:

myschema.thisDbObject
 
tablename

Specifies the table from which to remove privileges. 

ON ALL TABLES IN SCHEMA

Revokes privileges on all tables (and by default views) within one or more schemas from a user and/or role.

 
username

Revokes the privilege from the specified user.

 
PUBLIC

Revokes the privilege from all users.

 
role

Revokes the privilege from the specified role.

 
CASCADE

Revokes the privilege from the specified user or role and then from others. After a user or role has been granted a privilege, the user can grant that privilege to other users and roles. The CASCADE keyword first revokes the privilege from the initial user or role, and then from other grantees extended the privilege.

Examples

This example shows how to revoke user Joe's privileges on the customer_dimension table.

=> REVOKE ALL PRIVILEGES ON TABLE customer_dimension FROM Joe;
REVOKE PRIVILEGE

See Also