Outdated Version
You are viewing an older version of this section. View current production version.
Permissions Matrix
Info
This describes the permissions matrix for MemSQL 5.1 and later. See Default Mode Permissions Changes for changes from MemSQL 5.0 and earlier.
Permissions
| Permission | Default Scope | Strict Mode Scope | Notes |
|---|---|---|---|
| usage | Cluster | Cluster | Connect, show variables |
| select | Table | Table | Select rows |
| insert | Table | Table | Insert rows |
| update | Table | Table | Update cells of existing rows |
| delete | Table | Table | Delete rows |
| create | Cluster, Database | Database | 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 |
| create temporary tables | Cluster, Database, Table | Database, Table | Create temporary tables |
| lock tables | Cluster, Database | Cluster, Database | Lock tables (read and write) |
| replica | 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 | Database | 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 | Cluster | 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 |
Command permission requirements
This 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 LEAF | cluster | |
| AGGREGATOR SET AS MASTER | 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 | |
| ARRANGE | insert & select | |
| ATTACH LEAF | cluster | |
| ATTACH LEAF ALL | cluster | |
| ATTACH PARTITION | cluster | |
| BACKUP | backup | |
| 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 / alter routine | 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 PROCEDURE | create routine | |
| CREATE OR REPLACE PROCEDURE | create routine / alter routine | 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 | select | insert | update | delete | index | create | drop | alter |
| 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 PROCEDURE | alter routine | |
| DROP ROLE | grant | |
| DROP TABLE | drop | |
| DROP USER | create user | |
| DROP VIEW | alter view | |
| ECHO | execute | |
| EXPLAIN | ||
| EXPLAIN REBALANCE | show metadata | cluster |
| FLUSH TABLES | cluster | |
| GRANT | grant | |
| INSERT | insert | |
| INSERT (ON DUPLICATE KEY UPDATE) | insert & update | |
| INSERT SELECT | insert & select | |
| INSERT SELECT (ON DUPLICATE KEY UPDATE) | insert & select & update | |
| KILL CONNECTION | Any user may kill their own connections. The process permission is required to kill another user’s connection. |
|
| KILLALL CONNECTIONS | With the process permission, kills all connections. Otherwise, kills all connections owned by your user. |
|
| LOAD DATA | insert & file read | |
| LOAD DATA (REPLACE) | insert & file read & delete | |
| LOAD DATA LOCAL | insert | |
| LOAD DATA LOCAL (REPLACE) | insert & delete | |
| LOCK TABLES | select & lock tables | |
| PAUSE REPLICATING (distributed) | create database | |
| PAUSE REPLICATING (partition) | cluster | |
| PROMOTE PARTITION | cluster | |
| REBALANCE PARTITIONS | cluster | |
| REMOVE LEAF | cluster | |
| REPLACE | insert & delete | |
| REPLACE SELECT | insert & select & delete | |
| REPLICATE DATABASE (distributed) | create database | |
| REPLICATE DATABASE (partition) | cluster | |
| RESTORE DATABASE | reload | |
| REVOKE | grant | |
| SELECT | select | |
| SELECT (from TVF) | execute | |
| SELECT GLOBAL | USAGE | |
| SELECT INTO OUTFILE | select & file write | |
| SET GLOBAL | super | |
| SHOW AGGREGATES | create routine | alter routine | execute | |
| SHOW AGGREGATES EXTENDED | create routine | alter routine | execute | |
| SHOW AGGREGATORS | show metadata | cluster |
| SHOW CLUSTER STATUS | show metadata | cluster |
| SHOW COLUMNS | show metadata | select | insert | update | delete | index | create | drop | alter |
| SHOW MERGE STATUS | show metadata | cluster |
| SHOW CREATE AGGREGATE | create routine | alter routine | execute | |
| SHOW CREATE FUNCTION | create routine | alter routine | 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 TABLE | show metadata | select | insert | update | delete | index | create | drop | alter |
| SHOW CREATE VIEW | show metadata | show view |
| SHOW DATABASE STATUS | show metadata | cluster | replica |
| SHOW DATABASES | show metadata | all_db_acls | cluster | show metadata | grant |
| SHOW DATABASES EXTENDED | show metadata | cluster |
| SHOW ERRORS | USAGE | |
| SHOW FUNCTIONS | create routine | alter routine | execute | |
| SHOW GC STATUS | show metadata | cluster |
| SHOW GLOBAL STATUS | USAGE | |
| SHOW GLOBAL VARIABLES | USAGE | |
| SHOW GRANTS | grant | show metadata | |
| SHOW INDEXES | show metadata | select | index |
| SHOW LEAF STATUS | show metadata | cluster |
| SHOW LEAVES | show metadata | cluster |
| SHOW PARTITIONS | show metadata | cluster |
| SHOW PLANCACHE | With the process permission, shows all plans. Otherwise, shows all plans for queries you have permission to run. |
|
| SHOW PRIVILEGES | USAGE | |
| SHOW PROCESSLIST | 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 SESSION STATUS | USAGE | |
| SHOW SESSION VARIABLES | USAGE | |
| SHOW STATUS | USAGE | |
| SHOW STATUS EXTENDED | USAGE | |
| SHOW TABLES | show metadata | select | insert | update | delete | create | drop | index | alter | create view | show view | grant |
| SHOW VARIABLES | USAGE | |
| SHOW WARNINGS | USAGE | |
| SNAPSHOT DATABASE | backup | |
| STOP REPLICATING (distributed) | create database | |
| STOP REPLICATING (partition) | cluster | |
| TRIGGER GC | super | |
| TRUNCATE | drop | |
| UNION ALL | USAGE | |
| UNLOCK TABLES | USAGE | |
| UPDATE | update | |
| USE | show metadata | all_db_acls | show metadata | grant |
