Default Mode Permissions Changes
The contents of this page pertains to MemSQL versions 5.1 and up, as compared to MemSQL versions 5.0 and below. Here are the changes to the “default mode” of permissions in SingleStoreDB Cloud:
Default mode permissions are enabled when SingleStore is installed. To change that, you need to enable Strict Mode Permissions.
CLUSTER: a new permission called “CLUSTER” separates partition and workspace topology commands from the “SUPER” permission. SUPER is still required set global variables, reload the license, etc.
INDEX: previously, creating indexes on a table required ALTER permission, which also allowed the user to add and delete columns.
A separate permission called INDEX may be granted to create and drop indexes.
Users with only the INDEX permission are not allowed to affect or read columns.
SHOW METADATA: previously, there was no single permission that allowed read-only access to all of the metadata in the information_schema database.
SHOW METADATA allows this read-only access, as well as access to related commands such as SHOW DATABASES, DESCRIBE TABLE, SHOW CLUSTER STATUS, SHOW AGGREGATORS, SHOW LEAVES, etc.
Some commands are not covered under this permission: SHOW PLANCACHE, SHOW VIEW, and SHOW GRANTS.
BACKUP and SNAPSHOT: previously, a user could take a backup or snapshot of a database as long as they possessed SELECT permission on the entire database.
The user must now possess the BACKUP permission to run backups and manual snapshots.
An operator or automated process may manage backups without having permission to read the data itself.
Users with SELECT permissions cannot trigger backups.
This permission cannot overwrite existing files.
The SingleStoreDB Cloud workspace itself may from time to time automatically take snapshots of a database in order to compact the log files used for durability and replication.
RESTORE: previously, a user could RESTORE a database from disk if they possessed the CREATE permission.
A user must possess the RELOAD permission in order to restore a database from backups.
The RELOAD permission does not allow the user to
read data in tables
drop or overwrite an existing database
create an arbitrary database
directly read or write files
CREATE, ALTER, and DROP: Previously, these permissions applied to both databases and tables. Under the new regime:
CREATE, ALTER, and DROP only gives the user permission to create, alter, and drop tables.
In order to create databases, a user must possess the separate CREATE DATABASE permission.
In order to drop databases, a user must possess the DROP DATABASE permission. In order to create, drop, or alter views, the user must possess the CREATE VIEW, DROP VIEW, or ALTER VIEW permission, respectively.
The ALTER permission implies the INDEX permission.
FILE permission is read-only: previously, the FILE permission allowed a user to both read files (via LOAD DATA INFILE) and write files (via SELECT INTO OUTFILE) local to the aggregator.
The FILE [READ] permission only allows read-only LOAD DATA access.
To run SELECT INTO OUTFILE, the user must possess the new FILE WRITE permission.
The user must also possess the INSERT / REPLACE (for LOAD INFILE) or SELECT (for SELECT OUTFILE) permission on the tables in question.
GRANT: A user that holds a given permission plus the
GRANT OPTION
permission can grant that permission to another user. In "Default Mode", this behavior is preserved. TheGRANT OPTION
permission allows a user to:Have a
GRANT
capability scoped to users and objects in a named databaseGrant permissions (that the user already has) to other users within the scoped database