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>

Parameters

ParameterDescription
<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 in an account The following code example revokes USAGE permissions on all databases in the dev account from the role user_role, preventing it from accessing metadata or using those databases:
REVOKE USAGE ANY DATABASE ON ACCOUNT dev 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 TO user_role;
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 TO user_role;
Revoke MODIFY permission on a database while retaining it on all other databases in the dev account 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 of the account:
GRANT MODIFY ANY DATABASE ON ACCOUNT dev 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

ParameterDescription
<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;