GRANT

Syntax

GRANT  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
    | 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
    | OUTBOUND
   
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.

• USAGE privilege is given by default when a user is created. It is a synonym for “no privileges”. All privileges must be given separately using the GRANT command.

  Copy

  memsql> CREATE DATABASE db1;
  Query OK, 1 row affected (1.33 sec)
  
  memsql> CREATE USER test;
  Query OK, 0 rows affected (0.01 sec)
  
  memsql> SHOW GRANTS FOR test;
  +----------------------------------+
  | Grants for test@%                |
  +----------------------------------+
  | GRANT USAGE ON *.* TO 'test'@'%' |
  +----------------------------------+
  1 row in set (0.00 sec)
  
  memsql> exit
  
  $ memsql -u test
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 4
  Server version: 5.7.32-debug SingleStoreDB source distribution (compatible; MySQL Enterprise & MySQL Commercial)
  
  Copyright (c) 2000, 2024, Oracle and/or its affiliates.
  
  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.
  
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  
  memsql> SHOW DATABASES;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  +--------------------+
  1 row in set (0.00 sec)
  
  memsql> USE db1;
  ERROR 1044 (42000): Access denied for user 'test'@'%' to database 'db1'
  memsql> 

• *.* - Global privileges are administrative or apply to all databases on a given server.

• database.* - Database privileges apply to all objects in a given database.

• database.table - Table privileges apply to all columns in a given table in a given database.

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 and if the engine variable sql_mode value doesn’t contain NO_AUTO_CREATE_USER.

• 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.

• Refer to the Permission Matrix for the required permission.

• USAGE privilege allows users to connect to the cluster, view some SHOW Commands, and view objects on the information_schema database.

• 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_modeengine 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 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).

• GRANT syntax for the following authentication users as follows:

  • PAM

    SQL

    Copy

    GRANT ALL ON *.* TO 'singlestore-db_user'@'127.0.0.1'IDENTIFIED WITH authentication_pam AS 'pam_service';

  • Kerberos

    SQL

    Copy

    GRANT ALL ON *.* TO 'user1'@'%' IDENTIFIED WITH 'authentication_gss' AS 'user1@EXAMPLE.COM';

  • SAML

    Swl

    Copy

    GRANT ALL ON *.* TO 'user1'@'localhost' IDENTIFIED WITH 'authentication_saml';

• The OUTBOUND privilege is required to create an outbound connection between SingleStore and an a 3rd party object store via the following commands:

  • SELECT ... INTO S3, HDFS, Azure Blob, GCS, or Kafka.

    Note

    SELECT ... INTO a file system storage requires both SELECT and FILE WRITE privileges. Users will still be able to execute SELECT ... FROM, but only if outbound connectivity is not created.

  • BACKUP ... TO S3, Azure Blob, or GCS.

  • CREATE PIPELINE with S3, HDFS, Azure Blob, GCS, or Kafka.

  Without the required privileges, an error will be generated. For example:

  SQL

  Copy

  SELECT ... INTO:
  singlestore> SELECT * FROM t1 
      INTO S3 'testing/output' 
      CONFIG '{"region":"us-east-1"}' 
      CREDENTIALS '{"aws_access_key_id":"your_access_key_id","aws_secret_access_key":"your_secret_access_key"}';
  ERROR 1142 (42000): OUTBOUND command denied to user 'user1'@'%' for table 't1'
  
  BACKUP ... INTO:
  singlestore> BACKUP t1 INTO TO S3 'testing/output' 
      CONFIG '{"region":"us-east-1"}' 
      CREDENTIALS '{"aws_access_key_id":"your_access_key_id","aws_secret_access_key":"your_secret_access_key"}';
  ERROR 1227 (42000): Access denied; you need (at least one of) the OUTBOUND privilege(s) for this operation
  
  CREATE PIPELINE:
  singlestore> CREATE PIPELINE pipeline1 
      AS LOAD DATA S3 'my-bucket-name' 
      CONFIG '{"region": "us-east-1"}'
      CREDENTIALS '{"aws_access_key_id": "your_access_key_id", "aws_secret_access_key": "your_secret_access_key", "aws_session_token": "your_session_token"}'
      INTO TABLE 't1'
      FIELDS TERMINATED BY ',';
  ERROR 1227 (42000): Access denied; you need (at least one of) the OUTBOUND privilege(s) for this operation 

  Here's how to set the privileges for a user to execute these commands:

  SQL

  Copy

  SELECT ... INTO:
  singlestore> GRANT SELECT, FILE WRITE, OUTBOUND on *.* to user1;
  
  BACKUP ... INTO:
  singlestore> GRANT SELECT, FILE WRITE, BACKUP, OUTBOUND on *.* to user1;
  
  CREATE PIPELINE:
  singlestore> GRANT SELECT, FILE WRITE, CREATE PIPELINE, OUTBOUND on *.* to user1;

  The OUTBOUND privilege will be displayed in the SHOW GRANTS command:

  SQL

  Copy

  SHOW GRANTS FOR user1;

  +-----------------------------------------------------------------------------------+
  | Grants for user1@%                                                                |
  +-----------------------------------------------------------------------------------+
  | GRANT SELECT, FILE WRITE, BACKUP, CREATE PIPELINE, OUTBOUND ON *.* TO 'user1'@'%' |
  +-----------------------------------------------------------------------------------+

• This command causes implicit commits. Refer to COMMIT for more information.

• This command can be run on any SingleStore node. For more information, see the Cluster Management Commands.

• The GRANT command holds a global lock on the cluster until the processing of the command is completed. See Cluster Level Locks for more info.

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;

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

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

The following example demonstrates how to grant the execute permission for a stored procedure or function:

GRANT EXCUTE ON dbname.procname TO username;

Related Topics

• GRANT GROUP

• GRANT ROLE