Outdated Version

You are viewing an older version of this section. View current production version.

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.

Info

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
`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`		(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
`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

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`
`AGGREGATOR SET AS MASTER`	`CLUSTER`
`ALTER RESOURCE POOL`	`SUPER`
`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`

`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 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`	`SUPER`
`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 PARTITION`	`CLUSTER`
`DROP PIPELINE`	`DROP PIPELINE`
`DROP PROCEDURE`	`ALTER ROUTINE`
`DROP RESOURCE POOL`	`SUPER`
`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`
`PAUSE REPLICATING` (distributed)	`CREATE DATABASE`
`PAUSE REPLICATING` (partition)	`CLUSTER`
`PROFILE PIPELINE`	`START PIPELINE`
`PROMOTE PARTITION`	`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 TVF)	`EXECUTE`
`SELECT GLOBAL`	`USAGE`
`SELECT INTO OUTFILE`	`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`	`CREATE ROUTINE` or `ALTER 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.
`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 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`
`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`.

Was this Article Helpful?

Questions?

Ask the Community, find solutions in the Troubleshooting Overview, get Self-Paced Training or learn more about our Support Plans.