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 ]  { privilege[,...] | ALL [ PRIVILEGES ] }
... ON {
..... [ TABLE ] [[database.]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.
privilege	One of the following privileges: Important: Only SELECT privileges can be revoked on system tables. `SELECT`: Query tables of this schema. `INSERT`: Insert rows into tables of this schema, or and load data into tables with `COPY`. Note: `COPY FROM STDIN` is allowed all users with INSERT privileges, while `COPY FROM file` requires admin privileges. `UPDATE`: Update rows of tables of this schema. `DELETE`: Delete rows of tables of this schema. `REFERENCES`: Create foreign key constraints for tables of this schema. `TRUNCATE`: Truncate table contents. Non-owners of the tables can no longer execute the following partition operations on them: `DROP_PARTITIONS` `SWAP_PARTITIONS_BETWEEN_TABLES` `MOVE_PARTITIONS_TO_TABLE`
`ALL [PRIVILEGES]`	Revokes all table privileges. The optional keyword `PRIVILEGES` is supported to comply with the SQL standard.
`[database.]schema`	Specifies a schema, by default `public`. If schema is any schema other than `public`, you must supply the schema name. For example: myschema.thisDbObject One exception applies: you can specify system tables without their schema name. If you specify a database, it must be the current database.
`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

REVOKE (Table)

Syntax

Parameters

Examples

See Also