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.

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)	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.
`SYSTEM_VARIABLES_ADMIN`	Cluster	Cluster	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`	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.
`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.
`OUTBOUND`	Cluster, Database	Database	The `OUTBOUND` privilege is required to create an outbound connection between SingleStore and an a 3rd party object store.

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`	`CLUSTER`
`ALTER NODE`	`CLUSTER`
`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 LEAF`	`CLUSTER`
`ATTACH LEAF ALL`	`CLUSTER`
`ATTACH PARTITION`	`CLUSTER`
`BACKUP`	`BACKUP`
`BOOTSTRAP AGGREGATOR`	`CLUSTER`
`CALL`	`EXECUTE`
`CONTINUE REPLICATING`	`CREATE DATABASE`
`COPY PARTITION`	`CLUSTER`
`CREATE DATABASE`	`CREATE DATABASE`
`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 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 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 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 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`	`CREATE DATABASE`
`PROFILE PIPELINE`	`START PIPELINE`
`PROFILE PIPELINE JSON`	`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`	`CREATE DATABASE`
`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 TVF)	`EXECUTE`
`SELECT GLOBAL`	`USAGE`
`SELECT INTO OBJECTSTORE (S3/KAFKA)`	`SELECT`
`SELECT INTO OUTFILE/FS`	`SELECT` and `FILE WRITE`
`SET GLOBAL`	`SUPER`
`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 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 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`	`SUPER` 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`	`CREATE DATABASE`
`TEST PIPELINE`	`START PIPELINE`
`TRIGGER GC`	`SUPER`
`TRUNCATE`	`DROP`, `DELETE`	`DELETE` added as of version 7.6.10.
`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`.