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.