REVOKE

The REVOKE command revokes privileges from a SingleStore user or role.

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

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:

-- 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:

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

Revoke SELECT from a user-defined role:

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

Related Topics

Last modified:

Was this article helpful?