REVOKE

Syntax

REVOKE priv_type [, priv_type] ... ON priv_level
    FROM user_or_role [, user_or_role [ ... ]] ...

priv_level:
    *
  | *.*
  | database.*

user_or_role:
  user [, user]
  | role

user:
  'user_name'[@'host_name']

role:
  ROLE 'role_name'

priv_type:
    SELECT
  | INSERT
  | UPDATE
  | DELETE
  | CREATE
  | DROP
  | RELOAD
  | PROCESS
  | FILE READ
  | FILE WRITE
  | GRANT OPTION
  | INDEX
  | ALTER
  | SHOW METADATA
  | SUPER
  | CREATE TEMPORARY TABLES
  | LOCK TABLES
  | REPLICATION
  | CREATE VIEW
  | SHOW VIEW
  | CREATE USER
  | CLUSTER
  | ALTER VIEW
  | DROP VIEW
  | BACKUP
  | CREATE DATABASE
  | DROP DATABASE

REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM user_or_role [, user_or_role] ...

Remarks

priv_level - privilege level
priv_type - privilege type
user_or_role: One or more user attributes or a role attribute. The @'host_name' part is optional. If a hostname is not specified, @'%' will be appended to the user string. For example, 'demo-user' is equivalent to 'demo-user'@'%'.The '%' wildcard matches any hostname. user and host_name need not be quoted if they are legal as unquoted identifiers. Quotes must be used if a host_name string contains wildcard characters (such as %), or a user string contains special characters (such as space or -) or is a restricted keyword.
This command can be run on any SingleStore node.
This command causes implicit commits. Refer to COMMIT for more information.
GRANT OPTION must be revoked separately from any other privileges.
The REVOKE command holds a global lock on the cluster until the processing of the command is completed. See Cluster Level Locks for more info.
A user is granted the USAGE privilege by default. This privilege cannot be removed by using REVOKE [ALL]. The USAGEprivilege is automatically removed when the user is dropped.
Refer to the Permissions Matrix for the required permissions.

Required Privileges

The permissions required to execute a REVOKE statement depend on the value of the privilege_transfer_mode engine variable. The default is grant_option; set privilege_transfer_mode to per_privilege to enable per-privilege delegation.

Exact scope match is not required. The revoking user's scope must cover the target scope: Global (*.*) > Database (db.*) > Table (db.t).

If a user has SUPER and GRANT OPTION on *.*, they can revoke any permission from any user or role.

Note

A user can hold a permission directly, or inherit it through a role assigned to a group (permission → role → group → user). You cannot grant permissions to groups directly; assign permissions to a role and grant the role to the group. Refer to Role-Based Access Control (RBAC) at Database Level for more information.

Operation	`GRANT OPTION` mode (default)	`PER PRIVILEGE` mode
`REVOKE <permission> ON <scope> FROM <user>`	`GRANT OPTION` covers `<scope> + <permission>` covers `<scope>`	`TRANSFERABLE <permission>` covers `<scope>`
`REVOKE <permission> ON <scope> FROM <role>`	`GRANT OPTION` covers `<scope> + <permission>` covers `<scope>`	`TRANSFERABLE <permission>` covers `<scope>`
`REVOKE GROUP <group> FROM <user>`	`GRANT OPTION on .`	`GRANT OPTION on .`
`REVOKE ROLE <role> FROM <group>`	`GRANT OPTION on .`	`GRANT OPTION on .`
`DROP ROLE <role>`	`GRANT OPTION on .`	`GRANT OPTION on .`
`DROP GROUP <group>`	`GRANT OPTION on .`	`GRANT OPTION on .`

Examples

SQL

REVOKE INSERT ON *.* FROM 'user';

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user';

REVOKE SELECT ON dbTable FROM ROLE 'r1';

Revoke SELECT in grant_option mode:

SQL

-- Revoking user must have GRANT OPTION and SELECT on a scope that covers db1.*
REVOKE SELECT ON db1.* FROM 'user1'@'%';

Revoke SELECT in per_privilege mode:

SQL

-- Revoking user must have TRANSFERABLE SELECT on a covering scope
REVOKE SELECT ON db1.* FROM 'user1'@'%';

Revoke SELECT from a user-defined role:

SQL

-- Revoking user must have GRANT OPTION and SELECT on a scope that covers db1.*
REVOKE SELECT ON db1.* FROM ROLE 'role1';

Related Topics

On this page