SingleStore DB

Permissions Matrix
Permissions

The following table describes the permissions that can be granted to users and roles. Permissions are granted in a scope. A scope is the range of objects in the cluster for which the grant applies. For example, in default mode, the SELECT permission can be granted for all databases within a cluster, to a specific database within the cluster, or to a specific table in a database. Compared to default mode, strict mode generally has more restrictive scopes.

Notice

In MemSQL 5.1, significant changes were made to the default mode permissions. These changes are reflected in the table below.

Permission

Allowed Scopes in Default Mode

Allowed Scopes in Strict Mode

Notes

ALL PRIVILEGES

Cluster

Cluster

All permissions

USAGE

Cluster

Cluster

Connect, show variables

SELECT

Cluster, Database, Table

Database, Table

Select rows

INSERT

Cluster, Database, Table

Database, Table

Insert rows

UPDATE

Cluster, Database, Table

Database, Table

Update cells of existing rows

DELETE

Cluster, Database, Table

Database, Table

Delete rows

CREATE

Cluster, Database, Table

Database, Table

Create tables

DROP

Cluster, Database, Table

Database, Table

Drop tables

RELOAD

Cluster, Database

Cluster, Database

Load backups into database

FILE WRITE

Cluster

Cluster

File write access

PROCESS

Cluster

Cluster

View and kill queries

FILE READ

Cluster

Cluster

File read access

WITH GRANT OPTION

<tied to permissions>

(disallowed)

WITH GRANT OPTION disabled in strict mode

INDEX

Cluster, Database, Table

Database, Table

Create and drop indexes

ALTER

Cluster, Database, Table

Database, Table

Alter tables (including indexes)

SHOW METADATA

Cluster

Cluster

Show all metadata

SUPER

Cluster

Cluster

Set global variables, modify resource pool settings

CREATE TEMPORARY TABLES

Cluster, Database

Database

Create temporary tables

LOCK TABLES

Cluster, Database

Cluster, Database

Lock tables (read and write)

REPLICATION

Cluster, Database

Cluster, Database

Read data for replication. This permission cannot be granted directly -- use ALL PRIVILEGES to grant it.

CREATE VIEW

Cluster, Database

Database

Create views

ALTER VIEW

Cluster, Database, View

Database, View

Alter views

DROP VIEW

Cluster, Database, Table

Database, Table

Drop views

SHOW VIEW

Cluster, Database, View

Database, View

Show VIEW definitions

CLUSTER

Cluster

Cluster

Cluster administration, including replication, partition movement, and topology. Includes all permissions in SHOW METADATA.

BACKUP

Cluster, Database

Cluster, Database

Take backups and snapshots

CREATE USER

Cluster

Cluster

Create and drop users (no grants)

GRANT

Cluster, Database

Cluster, Database

Grant / revoke permissions, manage roles & groups. Distinct from WITH GRANT OPTION.

DROP DATABASE

Cluster, Database

Cluster, Database

Drop databases

CREATE DATABASE

Cluster, Database

Cluster, Database

Create databases

CREATE ROUTINE

Cluster, Database, Function

Database, Function

Create extensibility functions or procedures

ALTER ROUTINE

Cluster, Database, Function

Database, Function

Replace or delete extensibility functions or procedures

SHOW ROUTINE

Database

Database

See stored procedure bodies in Information Schema/show commands

EXECUTE

Cluster, Database, Function

Database, Function

Execute extensibility functions or procedures

CREATE PIPELINE

Cluster, Database, Table

Database, Table

Create pipelines

DROP PIPELINE

Cluster, Database, Table

Database, Table

Drop pipelines

START PIPELINE

Cluster, Database, Table

Database, Table

Start pipelines

ALTER PIPELINE

Cluster, Database, Table

Database, Table

Alter pipelines

SHOW PIPELINE

Cluster, Database, Table

Database, Table

Show pipelines

CREATE LINK

Cluster, Database

Database

Create link

DROP LINK

Cluster, Database

Database

Drop link

SHOW LINK

Cluster, Database

Database

Show links. This permission applies for commands such as BACKUP, RESTORE, CREATE PIPELINE, and SELECT that use connection links.

CREATE POOL

Cluster

Cluster

Create resource pool

DROP POOL

Cluster

Cluster

Drop resource pool

Permission Lists

The following lists are referenced by the Command Permission Requirements section.

Show and create table permissions

SELECT, INSERT , UPDATE, DELETE, INDEX, CREATE, DROP, ALTER

Database and Table Permissions

CREATE TEMPORARY TABLE, LOCK TABLES, RELOAD, BACKUP, CREATE DATABASE, DROP DATABASE, SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER, ALTER VIEW, DROP VIEW, CREATE PIPELINE, START PIPELINE, ALTER PIPELINE, SHOW PIPELINE, DROP PIPELINE, EXECUTE, CREATE ROUTINE, ALTER ROUTINE

Command Permission Requirements

The following table describes the permissions required to run each command.

Some commands are allowed if you have any of a list of permissions - for example, the CREATE INDEX command is allowed if you have either the INDEX permission or the ALTER permission. For these, generally the more specific permission is listed under Min. Permission and any other permissions that enable the command are listed under Additional Permissions.

Command

Min. Permission

Additional Permissions, Notes

ADD AGGREGATOR

CLUSTER

ADD LEAF

CLUSTER and WITH GRANT OPTION

AGGREGATOR SET AS MASTER

CLUSTER

AGGREGATOR SYNC AUTO_INCREMENT

CREATE, INSERT, UPDATE, DELETE, INDEX, DROP, or ALTER

Applies on a table-by-table basis.

ALTER RESOURCE POOL

CREATE POOL or DROP POOL

ALTER TABLE ADD COLUMNS

ALTER

ALTER TABLE ADD INDEX

INDEX

ALTER

ALTER TABLE DROP COLUMNS

ALTER

ALTER TABLE DROP INDEX

INDEX

ALTER

ALTER VIEW

ALTER VIEW

ALTER PIPELINE

ALTER PIPELINE

Requires SUPER if using a transform and the URI in the WITH TRANSFORM clause does not have the prefix memsql://.

ALTER USER

GRANT

ANALYZE TABLE

SELECT and (INSERT or ALTER)

ATTACH DATABASE

CREATE DATABASE

ATTACH LEAF

CLUSTER

ATTACH LEAF ALL

CLUSTER

ATTACH PARTITION

CLUSTER

BACKUP

BACKUP

BOOTSTRAP AGGREGATOR

CLUSTER

CALL

EXECUTE

CONTINUE REPLICATING (distributed)

CREATE DATABASE

CONTINUE REPLICATING (partition)

CLUSTER

COPY PARTITION

CLUSTER

CREATE DATABASE

CREATE DATABASE

CREATE FUNCTION

CREATE ROUTINE

CREATE LINK

CREATE LINK

CREATE MILESTONE

CREATE TABLE

CREATE OR REPLACE FUNCTION

CREATE ROUTINE is required if the function currently does not exist, ALTER ROUTINE is required if the function currently exists.

CREATE GROUP

GRANT

CREATE INDEX

INDEX

ALTER

CREATE PARTITION

CLUSTER

CREATE PIPELINE

CREATE PIPELINE

Requires EXECUTE if using INTO PROCEDURE. Requires SUPER if using a transform and the URI in the WITH TRANSFORM clause does not have the prefix memsql://.

CREATE OR REPLACE PIPELINE

CREATE PIPELINE and ALTER PIPELINE

Requires EXECUTE if using INTO PROCEDURE. Requires SUPER if using a transform and the URI in the WITH TRANSFORM clause does not have the prefix memsql://.

CREATE PROCEDURE

CREATE ROUTINE

CREATE OR REPLACE PROCEDURE

CREATE ROUTINE is required if the procedure currently does not exist, ALTER ROUTINE is required if the procedure currently exists.

CREATE RESOURCE POOL

CREATE POOL

CREATE ROLE

GRANT

CREATE TABLE

CREATE

CREATE TEMPORARY TABLE

CREATE TEMPORARY TABLES

CREATE USER

CREATE USER

CREATE VIEW

CREATE VIEW

DELETE

DELETE

DESCRIBE TABLE

SHOW METADATA

One or more of the show and create table permissions.

DETACH DATABASE

DROP DATABASE

DETACH LEAF

CLUSTER

DETACH PARTITION

CLUSTER

DROP ... FROM PLANCACHE

PROCESS

DROP DATABASE

DROP DATABASE

DROP FUNCTION

ALTER ROUTINE

DROP GROUP

GRANT

DROP INDEX

INDEX

ALTER

DROP LINK

DROP LINK

DROP PARTITION

CLUSTER

DROP PIPELINE

DROP PIPELINE

DROP PROCEDURE

ALTER ROUTINE

DROP RESOURCE POOL

DROP POOL

DROP ROLE

GRANT

DROP TABLE

DROP

DROP USER

CREATE USER

DROP VIEW

DROP VIEW

ECHO

EXECUTE

EXPLAIN

<can EXECUTE q>

EXPLAIN REBALANCE

SHOW METADATA

CLUSTER

EXTRACT PIPELINE

FILE WRITE and START PIPELINE

FLUSH TABLES

CLUSTER

GRANT

GRANT

GRANT GROUP

GRANT

GRANT ROLE

GRANT

INSERT

INSERT

INSERT ... ON DUPLICATE KEY UPDATE

INSERT and UPDATE

INSERT SELECT

INSERT and SELECT

INSERT SELECT ... ON DUPLICATE KEY UPDATE

INSERT and SELECT and UPDATE

KILL CONNECTION

PROCESS

Any user may kill their own connections. The PROCESS permission is required to kill another user’s connection.

KILLALL CONNECTIONS

PROCESS

With the PROCESS permission, kills all connections. Otherwise, kills all connections owned by your user.

LOAD DATA

INSERT and FILE READ

LOAD DATA ... REPLACE

INSERT and FILE READ and DELETE

LOAD DATA ... LOCAL

INSERT

LOAD DATA ... LOCAL ... REPLACE

INSERT and DELETE

LOCK TABLES

SELECT and LOCK TABLES

OPTIMIZE PIPELINE

START PIPELINE

OPTIMIZE TABLE

INSERT

PAUSE REPLICATING (distributed)

CREATE DATABASE

PAUSE REPLICATING (partition)

CLUSTER

PROFILE PIPELINE

START PIPELINE

PROMOTE PARTITION

CLUSTER

PROMOTE AGGREGATOR

CLUSTER

REBALANCE PARTITIONS

CLUSTER

REMOVE AGGREGATOR

CLUSTER

REMOVE LEAF

CLUSTER

REPAIR DATABASE

CREATE

REPLACE

INSERT and DELETE

REPLACE SELECT

INSERT and SELECT and DELETE

REPLICATE DATABASE (distributed)

CREATE DATABASE

REPLICATE DATABASE (partition)

CLUSTER

RESTORE DATABASE

RELOAD

RESTORE REDUNDANCY

CLUSTER

REVOKE

GRANT

SELECT

SELECT

SELECT * FROM information_schema.LOAD_DATA_ERRORS or any pipeline information_schema table

INSERT and SHOW METADATA

For more information, see the Information Schema Introduction.

SELECT * FROM information_schema.MV_QUERIES or any workload profiling information_schema table

PROCESS and SHOW METADATA

For more information, see the Information Schema Introduction.

SELECT * FROM information_schema.<TABLE_NAME>

SHOW METADATA

For more information, see the Information Schema Introduction.

SELECT * FROM information_schema.parameters

SHOW ROUTINE

Allows the user to see the body of the Procedure/Routine without the permissions required to be able to edit it.

SELECT * FROM information_schema.routines

SHOW ROUTINE

SELECT (from TVF)

EXECUTE

SELECT GLOBAL

USAGE

SELECT INTO OBJECTSTORE (S3/KAFKA)

SELECT

SELECT INTO OUTFILE/FS

SELECT and FILE WRITE

SET GLOBAL

SUPER

SHOW AGGREGATES

CREATE ROUTINE or ALTER ROUTINE or EXECUTE

SHOW AGGREGATES EXTENDED

CREATE ROUTINE or ALTER ROUTINE or EXECUTE

SHOW AGGREGATORS

SHOW METADATA

CLUSTER

SHOW CLUSTER STATUS

SHOW METADATA

CLUSTER

SHOW COLUMNS

SHOW METADATA

One or more of the show and create table permissions.

SHOW MERGE STATUS

SHOW METADATA

CLUSTER

SHOW CREATE AGGREGATE

CREATE ROUTINE or ALTER ROUTINE or EXECUTE

SHOW CREATE FUNCTION / SHOW CREATE PROCEDURE

CREATE ROUTINESHOW ROUTINEALTER ROUTINE or EXECUTE.

If the user only has EXECUTE permissions, then only the function header will be shown and the function body will be hidden.

The SHOW ROUTINE permission allows a user to view but not edit the bodies of procedures.

SHOW CREATE PIPELINE

SHOW METADATA

SHOW PIPELINE or CREATE PIPELINE or ALTER PIPELINE or DROP PIPELINE or START PIPELINE

SHOW CREATE TABLE

SHOW METADATA

One or more of the show and create table permissions.

SHOW CREATE VIEW

SHOW METADATA

SHOW VIEW

SHOW DATABASE STATUS

SHOW METADATA

CLUSTER or REPLICATION

SHOW DATABASES

SHOW METADATA

One or more of the database and table permissions or CLUSTER or SHOW METADATA or GRANT.

SHOW DATABASES EXTENDED

SHOW METADATA

CLUSTER

SHOW ERRORS

USAGE

SHOW FUNCTIONS

CREATE ROUTINE or ALTER ROUTINE or EXECUTE

SHOW GC STATUS

SHOW METADATA

CLUSTER

SHOW GLOBAL STATUS

USAGE

SHOW GLOBAL VARIABLES

USAGE

SHOW GRANTS

GRANT

GRANT if showing grants for groups or roles. GRANT or SHOW METADATA if showing grants for users.

SHOW INDEXES

SHOW METADATA

SELECT or INDEX

SHOW LEAF STATUS

SHOW METADATA

CLUSTER

SHOW LEAVES

SHOW METADATA

CLUSTER

SHOW LINKS

SHOW LINK

The SHOW LINK permission applies for commands such as BACKUP, RESTORE, CREATE PIPELINE, and SELECT that use connection links.

SHOW PARTITIONS

SHOW METADATA

CLUSTER

SHOW PLANCACHE

PROCESS

With the PROCESS permission, shows all plans. Otherwise, shows all plans for queries you have permission to run.

SHOW PRIVILEGES

USAGE

SHOW PROCESSLIST

PROCESS

With the PROCESS permission, shows all processes. Otherwise, shows all processes owned by your user.

SHOW REBALANCE STATUS

SHOW METADATA

CLUSTER

SHOW REPLICATION STATUS

SHOW METADATA

CLUSTER

SHOW RESOURCE POOL

SUPER

CREATE POOL is required unless the user has access to the pool.

SHOW SESSION STATUS

USAGE

SHOW SESSION VARIABLES

USAGE

SHOW STATUS

USAGE

SHOW STATUS EXTENDED

USAGE

SHOW TABLES

SHOW METADATA

SELECT or INSERT or UPDATE or DELETE or CREATE or DROP or INDEX or ALTER or CREATE VIEW or SHOW VIEW or GRANT.

SHOW VARIABLES

USAGE

SHOW WARNINGS

USAGE

SNAPSHOT DATABASE

BACKUP

START PIPELINE

START PIPELINE

STOP PIPELINE

START PIPELINE

STOP REPLICATING (distributed)

CREATE DATABASE

STOP REPLICATING (partition)

CLUSTER

TEST PIPELINE

START PIPELINE

TRIGGER GC

SUPER

TRUNCATE

DROP

UNION ALL

USAGE

UNLOCK TABLES

USAGE

UPDATE

UPDATE

USE

SHOW METADATA

One or more of the database and table permissions or SHOW METADATA or GRANT.