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” separates 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 only gives 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 only allows 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 OPTION: 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. The GRANT OPTION permission allows a user to:

   1. Have a GRANT capability scoped to users and objects in a named database
   2. Grant permissions (that the user already has) to other users within the scoped database

Was this Article Helpful?

Thanks For Your Feedback!