# GRANT

The `GRANT` command grants the specified privileges to an existing user. 

All of the privileges and their scope (global, database, table, etc.) are explained in the [Permissions Matrix](https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/permissions-matrix.md).

## Syntax

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

priv_type:
    | SELECT
    | INSERT
    | UPDATE
    | DELETE
    | CREATE
    | DROP
    | RELOAD
    | PROCESS
    | INDEX
    | ALTER
    | SHOW METADATA
    | GRANT OPTION
    | SYSTEM_VARIABLES_ADMIN
    | CREATE TEMPORARY TABLES
    | LOCK TABLES
    | CREATE VIEW
    | SHOW VIEW
    | CREATE USER
    | ALTER USER
    | 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  (SingleStore Helios Enterprise)
    | DROP POOL    (SingleStore Helios Enterprise)
    | CREATE LINK
    | DROP LINK
    | SHOW LINK
    | OUTBOUND
    | ALTER EVENT TRACE
   
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.
  ```sql
  CREATE DATABASE db1;
  CREATE USER test;

  SHOW GRANTS FOR test;

  ```
  ```output

  +----------------------------------+
  | Grants for test@%                |
  +----------------------------------+
  | GRANT USAGE ON *.* TO 'test'@'%' |
  +----------------------------------+
  ```
  ```shell
  singlestore -u test

  ```
  ```output

  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 10195
  Server version: 5.7.32 SingleStoreDB source distribution (compatible; MySQL Enterprise & MySQL Commercial)

  Copyright (c) 2000, 2022, 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.
  ```
  ```sql
  SHOW DATABASES;

  ```
  ```output

  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  +--------------------+
  ```
  ```sql
  USE db1;

  ```
  ```output

  ERROR 1044 (42000): Access denied for user 'test'@'%' to database 'db1'
  ```
* `*.*` - Global privileges are administrative or apply to all databases on a given server.
* `database.*` - Database privileges apply to all objects in a given database. Wildcards (%) are accepted in database names. The underscore character (\_) can also be used as a wildcard to match any single character. A backspace (\\) can precede an underscore to indicate the underscore is a literal part of a database name, e.g. `db_1`. A sharp character (#) must precede the database name if wildcards are used in a `GRANT` command. This will allow users to be granted permissions to any database that fits the pattern. See the following examples.
* `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](https://docs.singlestore.com/cloud/reference/sql-reference/restricted-keywords.md).

`REQUIRE`: `SSL` option ensures that the user connects via SSL. `NONE` specifies that SSL will not be required for the connection. For further information on this clause refer [CREATE USER](https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/create-user.md).

## Remarks

* The creation of users by using `GRANT` is disallowed and `NO_AUTO_CREATE_USER` is enabled by default.
* The [SHOW USERS](https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/show-users.md) command requires `*.*`  privileges on all databases to view all users in the workspace.
* Refer to the [Permissions Matrix](https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/permissions-matrix.md) for the required permissions.
* `USAGE` privilege allows users to connect to the cluster, view some [SHOW Commands](https://docs.singlestore.com/cloud/reference/sql-reference/show-commands.md), and view objects on the `information_schema` database.
* `PROCESS`, `FILE READ`, `FILE WRITE`, `SHOW METADATA`, `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 `SYSTEM_VARIABLES_ADMIN` permission allows users to set certain engine variables. These engine variables can be found by running the `SHOW VARIABLES EXTENDED` command and confirming the value is "Yes" in the `In_Allow_List` column.
* 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](https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/default-mode-permissions-changes.md) for more information.

  **Note:** Different users with `GRANT OPTION` privilege may be able to combine their privileges.
* 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 the `SYSTEM_VARIABLES_ADMIN` 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:
  ```sql
  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).
* 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
  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
  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
  SHOW GRANTS FOR user1;

  ```
  ```output

  +-----------------------------------------------------------------------------------+
  | Grants for user1@%                                                                |
  +-----------------------------------------------------------------------------------+
  | GRANT SELECT, FILE WRITE, BACKUP, CREATE PIPELINE, OUTBOUND ON *.* TO 'user1'@'%' |
  +-----------------------------------------------------------------------------------+
  ```
* The `ALTER USER` permission can be used to execute `ALTER USER` and `SET PASSWORD` commands.
* This command causes implicit commits. Refer to [COMMIT](https://docs.singlestore.com/cloud/reference/sql-reference/data-manipulation-language-dml/commit.md) for more information.
* The `GRANT` command holds a global lock on the cluster until the processing of the command is completed. See [Workspace Level Locks](https://docs.singlestore.com/cloud/reference/sql-reference/operations-that-take-either-a-database-or-a-workspace-level-lock/workspace-level-locks.md) for more info.

## SingleStore Helios

As an example, run the following to create a new user with all of the permissions that are currently available on SingleStore Helios.

```sql
GRANT
  SELECT,
  INSERT,
  UPDATE,
  DELETE,
  CREATE,
  PROCESS,
  INDEX,
  ALTER,
  DROP,
  SHOW METADATA,
  CREATE TEMPORARY TABLES,
  CREATE VIEW,
  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,
  CREATE USER,
  CREATE LINK,
  DROP LINK,
  SHOW LINK,
  EXECUTE,
  USAGE,
  RELOAD,
  LOCK TABLES,
  CREATE POOL,
  DROP POOL,
  SYSTEM_VARIABLES_ADMIN,
  OUTBOUND
ON *.*
TO 'TEST_DBA';

```

For SingleStore Helios, `GRANT` permissions must be scoped to specific actions contained within the database. As a consequence, `GRANT ALL` (an option available in self-hosted SingleStore) is not supported in SingleStore Helios because it includes workspace-wide permissions that are outside the scope of SingleStore Helios.

For example, the following `GRANT` statement is not supported on SingleStore Helios:

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

```

## Examples

**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`.

```sql
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.

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

```

> **📝 Note**: 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 permissions for a resource pool `p1` to a user `u1`:

```sql
GRANT USAGE ON RESOURCE POOL p1 to 'u1';
```

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

```sql
GRANT EXECUTE ON dbname.procname TO username;
```

**Related Topics**

* [GRANT GROUP](https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/grant-group.md)
* [GRANT ROLE](https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/grant-role.md)

***

Modified at: June 12, 2026

Source: [/cloud/reference/sql-reference/security-management-commands/grant/](https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/grant/)

(An index of the documentation is available at /llms.txt)