GRANT

Grants the specified privileges to an existing user. The user will be created if it does not exist. All of the privileges and their scope (global, database, table, etc.) are explained in the Permissions Matrix.

Syntax

GRANT [TRANSFERABLE] priv_type [, priv_type [ ... ]] ON priv_level
    TO user_or_role [, user_or_role [ ... ]]
    [WITH GRANT OPTION]
    [REQUIRE {SSL | NONE}]

priv_type:
      ALL PRIVILEGES
    | SELECT
    | INSERT
    | UPDATE
    | DELETE
    | CREATE
    | DROP
    | RELOAD
    | PROCESS
    | FILE READ
    | FILE WRITE
    | INDEX
    | ALTER
    | SHOW METADATA
    | GRANT OPTION
    | SUPER
    | SYSTEM_VARIABLE_ADMIN
    | CREATE TEMPORARY TABLES
    | LOCK TABLES
    | CREATE VIEW
    | SHOW VIEW
    | CREATE USER
    | CLUSTER
    | ALTER VIEW
    | DROP VIEW
    | BACKUP
    | CREATE DATABASE
    | DROP DATABASE
    | CREATE PIPELINE
    | DROP PIPELINE
    | ALTER PIPELINE
    | START PIPELINE
    | SHOW PIPELINE
    | EXECUTE
    | CREATE ROUTINE
    | ALTER ROUTINE
    | SHOW ROUTINE
    | CREATE POOL
    | DROP POOL
    | CREATE LINK
    | DROP LINK
    | SHOW LINK
    
priv_level:
      *
    | *.*
    | database.*
    | database.table

user_or_role:
      user [, user]
    | role

user:
   'user_name'[@'host_name'] [IDENTIFIED BY 'password']

role:
   ROLE 'role_name'

Arguments

priv_type: The privileges to grant the specified user.

priv_level: The resource on which to grant privileges.

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.

Remarks

  • The GRANT command creates a new user if the specified username does not exist.

  • A user with the SUPER permission can perform the following actions:

    • Set global variables, run administrative commands, and change passwords for other users.

    • Access all available resource pools. Permissions for additional resource pools can be given using the GRANT command.

  • PROCESS, FILE READ, FILE WRITE, SHOW METADATA, SUPER, CREATE USER, SYSTEM_VARIABLES_ADMIN, CLUSTER, CREATE POOL, and DROP POOL are global privileges. They can be granted only on *.* and not on specific databases or tables.

  • The WITH GRANT OPTION clause grants the GRANT OPTION privilege, which allows a user to grant all privileges that the user already has to other users within a scoped database. The privilege_transfer_mode engine variable must be left on the default value grant_option for the WITH GRANT OPTION clause to work as expected. The GRANT ... WITH GRANT OPTION and GRANT GRANT OPTION ... statements are synonymous. See Default Mode Permissions Changes for more information.

    Note: Different users with GRANT OPTION privilege may be able to combine their privileges.

    A user with both the GRANT OPTION and SUPER privileges can grant any privilege to the users (including themselves) within a scoped database.

  • The TRANSFERABLE clause allows a user to grant individual privileges to another user. The privilege_transfer_mode engine variable must be set to per_privilege for the TRANSFERABLE clause to work as expected. If privilege_transfer_mode is left on the default value grant_option and the TRANSFERABLE clause is attempted, the user will receive an error. If a non-transferable privilege is granted to a user with the same privilege level as the grantee, then transferability is revoked.

    Note: The user must have SYSTEM_VARIABLES_ADMIN or SUPER privilege to change the value of privilege_transfer_mode.

    For example, after this statement, both SELECT and INSERT on db.* will be available to 'user'@'%' and 'user'@'%' will be able to grant them to others:

    GRANT TRANSFERABLE SELECT, INSERT ON db.* TO 'user'@'%'
  • The EXECUTE permission allows a user to execute an extensibility object. For example, having this permission for UDFs, UDAFs, and TVFs allows the user to run them in SELECT queries. Similarly, a user with EXECUTE privileges for a stored procedure can run the SP using the CALL or ECHO statements.

  • The CREATE ROUTINE privilege allows a user to create an extensibility object (for example, a UDF, UDAF, TVF, or SP).

  • The ALTER ROUTINE privilege allows a user to replace or delete an extensibility object (for example, a UDF, UDAF, TVF, or SP).

  • This command can be run on any SingleStore DB node. For more information, see the Node Requirements for SingleStore DB Commands.

Examples

The following examples demonstrates how to grant privileges to the existing singlestore_admin user:

GRANT ALL PRIVILEGES ON *.* TO 'singlestore_admin' IDENTIFIED BY 'password' WITH GRANT OPTION;

Or

GRANT * ON *.* TO 'singlestore_admin' IDENTIFIED BY 'password' WITH GRANT OPTION;

Note: The privilege_transfer_mode engine variable must be left on the default value grant_option for the WITH GRANT OPTION clause to work as expected. The user must have SYSTEM_VARIABLES_ADMIN privilege to be able to change the value of privilege_transfer_mode.

GRANT TRANSFERABLE SELECT, INSERT ON db.* TO 'singlestore_admin';

Note: The privilege_transfer_mode engine variable must be set to value per_privilege to grant transferable privileges. The user must have SYSTEM_VARIABLES_ADMIN privilege to be able to change the value of privilege_transfer_mode.

The following example demonstrates how to grant the specified privileges to all databases to the full_backup_role role.

GRANT BACKUP, RELOAD ON *.* TO ROLE 'full_backup_role';

Notice

If you removed ROLE from the example above, the GRANT command would apply privileges to the user 'full_backup_role'.

The following example demonstrates how to grant EXECUTE and ALTER ROUTINE privileges for the stored procedure recStock to user 'adam'.

GRANT EXECUTE, ALTER ROUTINE ON db_tmp1.recStock TO 'adam';

The following example demonstrates how to grant permissions for a resource pool p1 to a user u1:

GRANT USAGE ON RESOURCE POOL p1 to 'u1';

Related Topics