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