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.

Permission	Allowed Scopes in Default Mode	Notes
`USAGE`	Workspace	Connect, show variables.
`SELECT`	Workspace, Database, Table	Select rows.
`INSERT`	Workspace, Database, Table	Insert rows.
`UPDATE`	Workspace, Database, Table	Update cells of existing rows.
`DELETE`	Workspace, Database, Table	Delete rows.
`CREATE`	Workspace, Database, Table	Create tables.
`DROP`	Workspace, Database, Table	Drop tables.
`RELOAD`	Workspace, Database	Load backups into database.
`PROCESS`	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>
`INDEX`	Workspace, Database, Table	Create and drop indexes.
`ALTER`	Workspace, Database, Table	Alter tables (including indexes).
`SHOW METADATA`	Workspace	Show all metadata.
`SYSTEM_VARIABLES_ADMIN`	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	Create temporary tables.
`LOCK TABLES`	Workspace, Database	Lock tables (read and write).
`CREATE VIEW`	Workspace, Database	Create views.
`ALTER VIEW`	Workspace, Database, View	Alter views.
`DROP VIEW`	Workspace, Database, Table	Drop views.
`SHOW VIEW`	Workspace, Database, View	Show `VIEW` definitions.
`BACKUP`	Workspace, Database	Take backups and snapshots.
`CREATE USER`	Workspace	Create and drop users (no grants).
`ALTER USER`	Workspace	Alter user profiles with `ALTER USER` or `SET PASSWORD` commands.
`GRANT`	Workspace, Database	Grant / revoke permissions, manage roles & groups. Distinct from `WITH GRANT OPTION`.
`DROP DATABASE`	Workspace, Database	Drop databases.
`CREATE DATABASE`	Workspace, Database	Create databases.
`CREATE ROUTINE`	Workspace, Database, Function	Create extensibility functions or procedures.
`ALTER ROUTINE`	Workspace, Database, Function	Replace or delete extensibility functions or procedures.
`SHOW ROUTINE`	Database	See stored procedure bodies in Information Schema/show commands.
`EXECUTE`	Workspace, Database, Function	Execute extensibility functions or procedures.
`CREATE PIPELINE`	Workspace, Database, Table	Create pipelines.
`DROP PIPELINE`	Workspace, Database, Table	Drop pipelines.
`START PIPELINE`	Workspace, Database, Table	Start pipelines.
`ALTER PIPELINE`	Workspace, Database, Table	Alter pipelines.
`SHOW PIPELINE`	Workspace, Database, Table	Show pipelines.
`CREATE LINK`	Workspace, Database	Create link.
`DROP LINK`	Workspace, Database	Drop link.
`SHOW LINK`	Workspace, Database	Show links. This permission applies for commands such as `BACKUP`, `RESTORE`, `CREATE PIPELINE`, and `SELECT` that use connection links.
`OUTBOUND`	Workspace, Database	The `OUTBOUND` privilege is required to create an outbound connection between SingleStore and an a 3rd party object store.
`CREATE POOL`	Workspace	Create resource pool.
`DROP POOL`	Workspace	Drop resource pool.
`ALTER TRACE`	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 EXTENSION`	`CREATE EXTENSION`, `SHOW EXTENSION`, `ALTER EXTENSION`, `DROP EXTENSION`
`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 EXTENSION`	`DROP EXTENSION`
`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`
`FULLTEXT SERVICE STOP`	`PROCESS`
`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`
`REOPTIMIZE`	<can `EXECUTE` q>
`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`.