Oracle Cloud Infrastructure Documentation

Default MySQL Privileges

Related Topics

Listing Privileges Using a Command-Line Client

Use a command-line client such as MySQL Client or MySQL Shell to list the privileges granted to a user.

  1. Do one of the following to list the privileges granted to a user:
    • To view the privileges granted to the current user, run the following command:
      SHOW GRANTS \G
    • To view the privileges granted to a named user, run the following command:
      SHOW GRANTS FOR <Username> \G
    If the command was run for an administrator user, User001, the command returns the following:
    *************************** 1. row ***************************
Grants for User001@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, 
PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, 
LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, 
SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, 
CREATE ROLE, DROP ROLE ON *.* TO `User001`@`%` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for User001@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,
CONNECTION_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,
REPLICATION_APPLIER,ROLE_ADMIN,XA_RECOVER_ADMIN ON *.* TO `User001`@`%` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for User001@%: REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, 
INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW,
 CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql`.* FROM `User001`@`%`
*************************** 4. row ***************************
Grants for User001@%: REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, 
CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, 
ALTER ROUTINE, EVENT, TRIGGER ON `mysql_audit`.* FROM `User001`@`%`
*************************** 5. row ***************************
Grants for User001@%: REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, 
CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, 
ALTER ROUTINE, EVENT, TRIGGER ON `sys`.* FROM `User001`@`%`
*************************** 6. row ***************************
Grants for User001@%: GRANT PROXY ON ``@`` TO `User001`@`%` WITH GRANT OPTION
*************************** 7. row ***************************
Grants for User001@%: GRANT `administrator`@`%` TO `User001`@`%` WITH ADMIN OPTION
7 rows in set (0.11 sec)

    The rows correspond to the following:

Global Static Privileges Granted

Certain global static MySQL privileges are granted to the DB system administrator.

Table 5-8 Global Static Privileges Granted

Global Static Privileges Granted
ALTER ALTER ROUTINE
CREATE CREATE ROLE
CREATE ROUTINE CREATE TEMPORARY TABLES
CREATE USER CREATE VIEW
DELETE DROP
EVENT EXECUTE
INDEX INSERT
LOCK TABLES PROCESS
REFERENCES REPLICATION CLIENT
REPLICATION SLAVE SELECT
SHOW DATABASES SHOW VIEW
TRIGGER UPDATE

Global Dynamic Privileges Granted

Certain global dynamic MySQL privileges are granted to the DB system administrator.

Table 5-9 Global Dynamic Privileges Granted

Global Dynamic Privileges
APPLICATION_PASSWORD_ADMIN AUDIT_ADMIN
FLUSH_TABLES CONNECTION_ADMIN
FLUSH_OPTIMIZER_COSTS REPLICATION_APPLIER
FLUSH_USER_RESOURCES FLUSH_STATUS
ROLE_ADMIN XA_RECOVER_ADMIN
BACKUP_ADMIN  

Privileges Revoked from mysql Schema

Certain privileges are revoked from the mysql schema. You cannot grant these privileges to any DB system user.

Table 5-10 Privileges Revoked from mysql Schema

Privileges Revoked from mysql Schema
INSERT UPDATE
DELETE CREATE VIEW
DROP REFERENCES
INDEX ALTER
CREATE TEMPORARY TABLES LOCK TABLES
CREATE VIEW CREATE ROUTINE
EXECUTE ALTER ROUTINE
TRIGGER EVENT

Related Topics

Privileges Revoked from mysql_audit Schema

Certain privileges are revoked from the mysql_audit schema. You cannot grant these privileges to any DB system user.

Table 5-11 Privileges Revoked from mysql_audit Schema

Privileges Revoked from mysql_audit Schema
ALTER ALTER ROUTINE
CREATE CREATE ROUTINE
CREATE TEMPORARY TABLES CREATE VIEW
DROP EVENT
INDEX LOCK TABLES
REFERENCES TRIGGER

Related Topics

Privileges Revoked from sys Schema

Certain privileges are revoked from the sys schema. You cannot grant these privileges to any DB system user.

Table 5-12 Privileges Revoked from sys Schema

Privileges Revoked from sys Schema
ALTER ALTER ROUTINE
CREATE CREATE ROUTINE
CREATE TEMPORARY TABLES CREATE VIEW
DROP EVENT
INDEX LOCK TABLES
REFERENCES TRIGGER

Related Topics

Roles Granted

Certain MySQL user roles are granted to the DB system administrator.

Table 5-13 Roles Granted

Roles Granted
administrator
The administrator role contains all default privileges of the DB system administrator except the PROXY privilege on ``@``. 
SHOW GRANTS FOR administrator \G
*************************** 1. row ***************************
Grants for administrator@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, 
PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, 
LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, 
SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, 
CREATE ROLE, DROP ROLE ON *.* TO `administrator`@`%` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for administrator@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,
BACKUP_ADMIN,CONNECTION_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,
FLUSH_USER_RESOURCES,REPLICATION_APPLIER,ROLE_ADMIN,SET_ANY_DEFINER,SHOW_ROUTINE,
XA_RECOVER_ADMIN ON *.* TO `administrator`@`%` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for administrator@%: REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, 
INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, 
CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql`.* FROM `administrator`@`%`
*************************** 4. row ***************************
Grants for administrator@%: REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, 
CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, 
EVENT, TRIGGER ON `mysql_audit`.* FROM `administrator`@`%`
*************************** 5. row ***************************
Grants for administrator@%: REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, 
CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, 
EVENT, TRIGGER ON `sys`.* FROM `administrator`@`%`
5 rows in set (0.00 sec)

Related Topics

Creating Additional Administrators Using a Command-Line Client

Use a command-line client such as MySQL Client or MySQL Shell to create additional DB system administrators.

  1. Connect to the DB system with the credential of a DB system administrator using a command-line client such as MySQL client or MySQL Shell.
  2. Run the following command to create another DB system administrator:
    CREATE USER <admin_user_name> IDENTIFIED BY '<password>' DEFAULT ROLE 'administrator';
GRANT PROXY ON ``@`` TO <admin_user_name> WITH GRANT OPTION;
  3. (Optional) Run the following command to list the privileges granted to the new user account: 
    SHOW GRANTS FOR <admin_user_name> \G
    The output shows:
    *************************** 1. row ***************************
Grants for <admin_user_name>@%: GRANT USAGE ON *.* TO `<admin_user_name>`@`%` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for <admin_user_name>@%: GRANT PROXY ON ``@`` TO `<admin_user_name>`@`%` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for <admin_user_name>@%: GRANT `administrator`@`%` TO `<admin_user_name>`@`%`
3 rows in set (0.00 sec)
  4. (Optional) Connect to the DB system with the credential of the new administrator using a command-line client and run the following to list the privileges:
    SHOW GRANTS \G
    The output shows:
    *************************** 1. row ***************************
Grants for <admin_user_name>@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, 
PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, 
EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, 
ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE ROLE, DROP ROLE ON *.* 
TO `<admin_user_name>`@`%` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for <admin_user_name>@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,
CONNECTION_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,
REPLICATION_APPLIER,ROLE_ADMIN,SET_ANY_DEFINER,SHOW_ROUTINE,XA_RECOVER_ADMIN ON *.* 
TO `<admin_user_name>`@`%` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for <admin_user_name>@%: REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, 
INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, CREATE ROUTINE, 
ALTER ROUTINE, EVENT, TRIGGER ON `mysql`.* FROM `<admin_user_name>`@`%`
*************************** 4. row ***************************
Grants for <admin_user_name>@%: REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, 
CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, 
EVENT, TRIGGER ON `mysql_audit`.* FROM `<admin_user_name>`@`%`
*************************** 5. row ***************************
Grants for <admin_user_name>@%: REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, 
CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, 
EVENT, TRIGGER ON `sys`.* FROM `<admin_user_name>`@`%`
*************************** 6. row ***************************
Grants for <admin_user_name>@%: GRANT PROXY ON ``@`` TO `<admin_user_name>`@`%` WITH GRANT OPTION
*************************** 7. row ***************************
Grants for <admin_user_name>@%: GRANT `administrator`@`%` TO `<admin_user_name>`@`%`
7 rows in set (0.00 sec)