REVOKE

Reference and syntax for the REVOKE command.

Revokes permissions from a role. REVOKE can also be used to revoke a role from another role or a user.

For more information, see Role-based access control.

REVOKE PRIVILEGE

Revokes a permission from a role.

Only account_admin or a role owner can revoke a permission to a role.

Syntax

REVOKE <permission> ON <object_type> <object_name> [IN <object_type> <object_name>] FROM <role_name>
REVOKE <account_permission> FROM <role_name>

Account-level permissions such as CREATE DATABASE or USAGE ANY DATABASE apply to the whole deployment and take no ON clause. For the list, see Account permissions.

To revoke SELECT on specific columns of a table, use the column-list syntax:

REVOKE SELECT (<column1> [, <column2>, ...]) ON TABLE <table_name> FROM <role_name>

Column-list REVOKE subtracts only from the column-scoped grant. It does not affect table-level SELECT grants. A table-level REVOKE SELECT ON TABLE (without a column list) removes all SELECT on that table, including any column-scoped grants. For more information, see Column-level security.

Parameters

Parameter	Description
<permission>	The name of the permission to revoke from a role. Permissions that can be revoked vary depending on the object that they apply to. For a full list, see Permissions.
<object_type>	The type of the object to revoke permissions from.
<object_name>	The name of the object to revoke permissions from.
<role_name>	The name of the role from which the permission will be revoked.

Examples

Revoke MODIFY permission on a database

The following code example revokes the MODIFY permission on the db database from the role user_role, preventing it from making changes to the database:

REVOKE MODIFY ON DATABASE db FROM user_role;

Revoke all permissions on a database

The following code example revokes the all permissions on the db database from the role user_role, preventing all operations on it:

REVOKE ALL ON DATABASE db FROM user_role;

Revoke USAGE permissions on all databases

The following code example revokes USAGE permissions on all current and future databases from the role user_role, preventing it from accessing metadata or using those databases:

REVOKE USAGE ANY DATABASE FROM user_role;

Revoke SELECT permission on a specific table

The following code example sets the active database to db and revokes user_role’s permission to read data from the my_table table in the public schema.:

USE DATABASE db;
REVOKE SELECT ON TABLE my_table IN SCHEMA public FROM user_role;

Revoke SELECT on specific columns of a table

The following code example revokes user_role’s permission to read the email column from my_table, while retaining access to any other granted columns:

REVOKE SELECT (email) ON TABLE my_table FROM user_role;

For more information, see Column-level security.

Revoke SELECT permission on all tables in a schema

The following code revokes user_role’s permission to read data from all existing and future tables in the public schema of the db database:

REVOKE SELECT ANY ON SCHEMA public  IN DATABASE db FROM user_role;

Revoke MODIFY permission on a database while retaining it on all other databases

The following code example revokes the MODIFY permission on the db database from the role user_role, while keeping it for any other existing and future databases:

GRANT MODIFY ANY DATABASE TO user_role;
REVOKE MODIFY ON DATABASE db FROM user_role;

REVOKE ROLE

Revokes a role from a user or from another role.

Syntax

REVOKE ROLE <role_name> FROM { USER <user_name> | ROLE <role_name_2> }

Parameters

Parameter	Description
<role_name>	The name of the role to revoke.
<user_name>	The name of the user from which to revoke the <role_name> role.
<role_name_2>	The name of the role from which to revoke the role.

Examples

Revoke a role from another role

The following code example removes the role_name role from role_name_2, revoking access to permissions granted to role_name:

REVOKE ROLE role_name FROM ROLE role_name_2;

Revoke a role from a user

The following command revokes role role_name from user user_name, removing the user’s access to the permissions granted to role_name:

REVOKE ROLE role_name FROM USER user_name;