REVOKE (Schema)

Revokes privileges on a schema from a user or role.

Note: 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 ] { 
... { CREATE | USAGE } [ ,... ] 
 .. { SELECT | INSERT | UPDATE | DELETE | REFERENCES } [ ,... ] ... | ALL [ PRIVILEGES ] }
... ON SCHEMA [db-name.] schema [ , ... ]
... 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.

CREATE

Revokes the right to create tables and views in the schema.

USAGE

Revokes user access to the objects contained in the schema. Note that the user can also have access to the individual objects revoked. See the GRANT TABLE and GRANT VIEW statements.

SELECT
          
Revokes the user's ability to perform a SELECT on any column of any table in the schema.
INSERT
Revokes the user's ability to INSERT tuples into tables in the schema and use the COPY command to load data into the tables.
UPDATE
Revokes the user's ability to UPDATE tuples in a schema table.
DELETE
Revokes the user's ability to DELETE rows from a schema table.
REFERENCES
          
Revokes the user's privilege on both the referencing and referenced tables for creating a foreign key constraint.
ALL

Revokes all privileges previously granted.

PRIVILEGES

Is for SQL standard compatibility and is ignored.

[db-name.]

[Optional] Specifies the current database name. Using a database name prefix is optional, and does not affect the command in any way. You must be connected to the specified database.

schema

Identifies the schema from which to revoke privileges.

username

Revokes the privilege to a specific user.

PUBLIC

Revokes the privilege to all users.

role

Revokes the privilege to a specific 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 usage privileges on the online_sales schema.

=> REVOKE USAGE ON SCHEMA online_sales FROM Joe;
REVOKE PRIVILEGE

See Also