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 workspace for which the grant applies. For example, in default mode, the SELECT permission can be granted for all databases within a workspace, to a specific database within the workspace, or to a specific table in a database. Compared to default mode, strict mode generally has more restrictive scopes.

Permission

Allowed Scopes in Default Mode

Allowed Scopes in Strict Mode

Notes

SELECT

Workspace, Database, Table

Database, Table

Select rows.

INSERT

Workspace, Database, Table

Database, Table

Insert rows.

UPDATE

Workspace, Database, Table

Database, Table

Update cells of existing rows.

DELETE

Workspace, Database, Table

Database, Table

Delete rows.

CREATE

Workspace, Database, Table

Database, Table

Create tables.

DROP

Workspace, Database, Table

Database, Table

Drop tables.

RELOAD

Workspace, Database

Workspace, Database

Load backups into database.

PROCESS

Workspace

Workspace

View and kill queries.

Required to query the MV_TRACE_EVENTS information schema.

Required to query MV_TRACE_EVENTS_STATUS (if user does not have ALTER TRACE)

WITH GRANT OPTION

<tied to permissions>

(disallowed)

WITH GRANT OPTION disabled in strict mode.

INDEX

Workspace, Database, Table

Database, Table

Create and drop indexes.

ALTER

Workspace, Database, Table

Database, Table

Alter tables (including indexes).

SHOW METADATA

Workspace

Workspace

Show all metadata.

SYSTEM_VARIABLES_ADMIN

Workspace

Workspace

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

Allow users to change the value of the privilege_transfer_mode engine variable. Setting this variable to per_privilege allows users to transfer specific privileges to other users via the TRANSFERABLE argument.

CREATE TEMPORARY TABLES

Workspace, Database

Database

Create temporary tables.

LOCK TABLES

Workspace, Database

Workspace, Database

Lock tables (read and write).

CREATE VIEW

Workspace, Database

Database

Create views.

ALTER VIEW

Workspace, Database, View

Database, View

Alter views.

DROP VIEW

Workspace, Database, Table

Database, Table

Drop views.

SHOW VIEW

Workspace, Database, View

Database, View

Show VIEW definitions.

BACKUP

Workspace, Database

Workspace, Database

Take backups and snapshots.

CREATE USER

Workspace

Workspace

Create and drop users (no grants).

ALTER USER

Workspace

Workspace

Alter user profiles with ALTER USER or SET PASSWORD commands.

GRANT

Workspace, Database

Workspace, Database

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

DROP DATABASE

Workspace, Database

Workspace, Database

Drop databases.

CREATE DATABASE

Workspace, Database

Workspace, Database

Create databases.

CREATE ROUTINE

Workspace, Database, Function

Database, Function

Create extensibility functions or procedures.

ALTER ROUTINE

Workspace, 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

Workspace, Database, Function

Database, Function

Execute extensibility functions or procedures.

CREATE PIPELINE

Workspace, Database, Table

Database, Table

Create pipelines.

DROP PIPELINE

Workspace, Database, Table

Database, Table

Drop pipelines.

START PIPELINE

Workspace, Database, Table

Database, Table

Start pipelines.

ALTER PIPELINE

Workspace, Database, Table

Database, Table

Alter pipelines.

SHOW PIPELINE

Workspace, Database, Table

Database, Table

Show pipelines.

CREATE LINK

Workspace, Database

Database

Create link.

DROP LINK

Workspace, Database

Database

Drop link.

SHOW LINK

Workspace, Database

Database

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

CREATE POOL

Workspace

Workspace

Create resource pool.

DROP POOL

Workspace

Workspace

Drop resource pool.

ALTER TRACE

Workspace

Workspace

Required to create or drop trace events

Required to query MV_TRACE_EVENTS_STATUS (if user does not have PROCESS)

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

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 or ALTER USER

ANALYZE TABLE

SELECT and (INSERT or ALTER)

ATTACH DATABASE

SUPER if Unlimited Storage. Otherwise CREATE DATABASE.

BACKUP

BACKUP

CALL

EXECUTE

CONTINUE REPLICATING

CREATE DATABASE

CREATE DATABASE

CREATE DATABASE

CREATE EVENT TRACE

ALTER TRACE

CREATE FUNCTION

CREATE ROUTINE

CREATE LINK

CREATE LINK

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

DROP ... FROM PLANCACHE

PROCESS

DROP DATABASE

DROP DATABASE

DROP EVENT TRACE

ALTER TRACE

DROP FUNCTION

ALTER ROUTINE

DROP GROUP

GRANT

DROP INDEX

INDEX

ALTER

DROP LINK

DROP LINK

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>

EXTRACT PIPELINE

FILE WRITE and START PIPELINE

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

CREATE DATABASE

PROFILE PIPELINE

START PIPELINE

PROFILE PIPELINE JSON

START PIPELINE

REPAIR DATABASE

CREATE

REPLACE

INSERT and DELETE

REPLACE SELECT

INSERT and SELECT and DELETE

REPLICATE DATABASE

CREATE DATABASE

RESTORE DATABASE

RELOAD

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 INTO OBJECTSTORE (S3/KAFKA)

SELECT

SET PASSWORD

SUPER or ALTER USER

SHOW AGGREGATES

CREATE ROUTINE or ALTER ROUTINE or EXECUTE

SHOW AGGREGATES EXTENDED

CREATE ROUTINE or ALTER ROUTINE or EXECUTE

SHOW COLUMNS

SHOW METADATA

One or more of the show and create table permissions.

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 DATABASES

SHOW METADATA

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

SHOW FUNCTIONS

CREATE ROUTINE or ALTER ROUTINE or EXECUTE

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 LINKS

SHOW LINK

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

SHOW PLANCACHE

PROCESS

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

SHOW PROCESSLIST

PROCESS

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

SHOW RESOURCE POOL

SUPER

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

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.

SNAPSHOT DATABASE

BACKUP

START PIPELINE

START PIPELINE

STOP PIPELINE

START PIPELINE

STOP REPLICATING

CREATE DATABASE

TEST PIPELINE

START PIPELINE

TRUNCATE

DROP, DELETE

DELETE added as of version 7.6.10.

UPDATE

UPDATE

USE

SHOW METADATA

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

Last modified: March 14, 2024

Was this article helpful?