Create a user account with the specified privileges.

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


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.

Remarks

• The GRANT command creates a new user if the specified username does not exist.
• 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).
• A user with SUPER permission can perform the following actions:
  • Set global variables, run administrative commands, and change password for other users.
  • Access all available resource pools. Permissions for additional resource pools can be given using the GRANT command.
• The WITH GRANT OPTION clause grants the GRANT OPTION privilege, which allows a user to grant any privilege that the user already has to other users within a scoped database. The GRANT ... WITH GRANT OPTION and GRANT GRANT OPTION ... statements are synonymous to each other. See GRANT 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.
• This command can be run on any SingleStore node. For more information, see the Node Requirements for SingleStore DB Commands.

SingleStore Managed Service

Run the following to create a new user with all of the permissions that are currently available on SingleStore Managed Service.

GRANT
  SELECT,
  CREATE USER,
  INSERT,
  UPDATE,
  DELETE,
  CREATE,
  INDEX,
  ALTER,
  DROP,
  CREATE TEMPORARY TABLES,
  CREATE,
  SHOW VIEW,
  ALTER VIEW,
  DROP VIEW,
  BACKUP,
  CREATE DATABASE,
  DROP DATABASE,
  CREATE PIPELINE,
  DROP PIPELINE,
  ALTER PIPELINE,
  START PIPELINE,
  SHOW PIPELINE,
  CREATE ROUTINE,
  ALTER ROUTINE,
  EXECUTE,
  USAGE,
  RELOAD,
  LOCK TABLES
ON TEST_DM.*
TO 'TEST_DBA';

Note that the following permissions are currently not available with SingleStore Managed Service.

priv_type:
      ALL PRIVILEGES
    | PROCESS
    | FILE READ
    | FILE WRITE
    | SHOW METADATA
    | CLUSTER

For SingleStore Managed Service, GRANT permissions must be scoped to specific actions contained within the database. As a consequence, GRANT ALL is not supported in SingleStore Managed Service because it includes cluster-wide permissions that are outside the scope of the managed service.

For example:

GRANT ALL PRIVILEGES ON *.* TO 'memsql_admin'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

In addition, cluster-level statements are not supported as SingleStore Managed Service clusters are managed by SingleStore DB.

Examples

The following example demonstrates how to grant privileges to the existing memsql_admin user:

GRANT ALL PRIVILEGES ON *.* TO 'memsql_admin'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

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

GRANT BACKUP, RELOAD ON *.* TO ROLE 'full_backups_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 to a user:

GRANT USAGE ON RESOURCE POOL <resource_pool_name> to '<user_name>'@'%';

Related Topics

• GRANT GROUP
• GRANT ROLE

Was this Article Helpful?

Thanks For Your Feedback!