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 MemSQL:

1. CLUSTER: a new permission called “CLUSTER” will be created to separate partition and cluster topology commands from the “SUPER” permission. SUPER is still required set global variables, reload the license, etc.

2. INDEX: previously, creating indexes on a table required ALTER permission, which also allowed the user to add and delete columns.

   1. A separate permission called INDEX may be granted to create and drop indexes.
   2. Users with only the INDEX permission are not allowed to affect or read columns.

3. SHOW METADATA: previously, there was no single permission that allowed read-only access to all of the metadata in the information_schema database.

   1. 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.
   2. Some commands are not covered under this permission: SHOW PLANCACHE, SHOW VIEW, and SHOW GRANTS.

4. 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.
     1. An operator or automated process may manage backups without having permission to read the data itself.
     2. Users with SELECT permissions cannot trigger backups.
     3. This permission cannot overwrite existing files.
     4. The MemSQL cluster itself may from time to time automatically take snapshots of a database in order to compact the log files used for durability and replication.

5. RESTORE: previously, a user could RESTORE a database from disk if they possessed the CREATE permission.

   1. A user must possess the RELOAD permission in order to restore a database from backups.
   2. The RELOAD permission does not allow the user to
      1. read data in tables
      2. drop or overwrite an existing database
      3. create an arbitrary database
      4. directly read or write files

6. CREATE, ALTER, and DROP: Previously, these permissions applied to both databases and tables. Under the new regime:

   1. CREATE, ALTER, and DROP will only give the user permission to create, alter, and drop tables.
   2. In order to create databases, a user must possess the separate CREATE DATABASE permission.
   3. 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.
   4. The ALTER permission implies the INDEX permission.

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

   1. The FILE [READ] permission will only allow read-only LOAD DATA access.
   2. To run SELECT INTO OUTFILE, the user must possess the new FILE WRITE permission.
   3. The user must also possess the INSERT / REPLACE (for LOAD INFILE) or SELECT (for SELECT OUTFILE) permission on the tables in question.

8. GRANT: Previously, a user that held a given permission plus the “grant option” could grant that permission to another user. In “Default Mode”, this behavior will be preserved. However, a new, distinct permission called GRANT will be added to the security model, allowing a user to:

   1. Have a GRANT capability scoped to users and objects in a named database
   2. Grant other users a permission without necessarily possessing that permission
   3. Grant any permission (NEEDS FEEDBACK)

Was this Article Helpful?

Thanks For Your Feedback!