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,OPTION_TRACKER_OBSERVER,REPLICATION_APPLIER,ROLE_ADMIN,
    SET_ANY_DEFINER,SHOW_ROUTINE,TRANSACTION_GTID_TAG,VECTOR_STORE_LOAD_EXEC,
    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 INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, 
    INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, 
    CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_option`.* FROM `User001`@`%`
    *************************** 6. 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`@`%`
    *************************** 7. row ***************************
    Grants for User001@%: GRANT PROXY ON ``@`` TO `User001`@`%` WITH GRANT OPTION
    *************************** 8. row ***************************
    Grants for User001@%: GRANT `administrator`@`%` TO `User001`@`%` WITH ADMIN OPTION
    8 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.

Global Dynamic Privileges Granted

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

Table 5-10 Global Dynamic Privileges Granted

Global Dynamic Privileges
APPLICATION_PASSWORD_ADMIN AUDIT_ADMIN
BACKUP_ADMIN CONNECTION_ADMIN
FLUSH_OPTIMIZER_COSTS FLUSH_STATUS
FLUSH_TABLES FLUSH_USER_RESOURCES
OPTION_TRACKER_UPDATER (MySQL 9.1.0 or higher only) REPLICATION_APPLIER
ROLE_ADMIN SET_ANY_DEFINER (MySQL 8.2.0 or higher only)
SHOW_ROUTINE TRANSACTION_GTID_TAG (MySQL 8.3.0 or higher only)
VECTOR_STORE_LOAD_EXEC (MySQL 9.0.0 or higher only) XA_RECOVER_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-11 Privileges Revoked from mysql Schema

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-12 Privileges Revoked from mysql_audit Schema

Related Topics

Privileges Revoked from mysql_option Schema

In MySQL 9.1.0 or higher, certain privileges are revoked from the mysql_option schema. You cannot grant these privileges to any DB system user.

Table 5-13 Privileges Revoked from mysql_option Schema

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-14 Privileges Revoked from sys Schema

Related Topics

Roles Granted

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

Table 5-15 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,
OPTION_TRACKER_OBSERVER,REPLICATION_APPLIER,ROLE_ADMIN,SET_ANY_DEFINER,SHOW_ROUTINE,
TRANSACTION_GTID_TAG,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 INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, 
INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, CREATE ROUTINE, 
ALTER ROUTINE, EVENT, TRIGGER ON `mysql_option`.* FROM `administrator`@`%`
*************************** 6. 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`@`%`
6 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,OPTION_TRACKER_OBSERVER,REPLICATION_APPLIER,ROLE_ADMIN,SET_ANY_DEFINER,SHOW_ROUTINE,
    TRANSACTION_GTID_TAG,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 INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, 
    INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, 
    EVENT, TRIGGER ON `mysql_option`.* FROM `<admin_user_name>`@`%`
    *************************** 6. 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>`@`%`
    *************************** 7. row ***************************
    Grants for <admin_user_name>@%: GRANT PROXY ON ``@`` TO `<admin_user_name>`@`%` WITH GRANT OPTION
    *************************** 8. row ***************************
    Grants for <admin_user_name>@%: GRANT `administrator`@`%` TO `<admin_user_name>`@`%`
    8 rows in set (0.00 sec)