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