Default MySQL Privileges
Certain MySQL privileges are granted to the DB system administrator and
certain privileges are revoked from the mysql
and sys
schemas.
- Listing Privileges Using a Command-Line Client
- Global Static Privileges Granted
- Global Dynamic Privileges Granted
- Privileges Revoked from mysql Schema
- Privileges Revoked from mysql_audit Schema
- Privileges Revoked from mysql_option Schema
- Privileges Revoked from sys Schema
- Roles Granted
- Creating Additional Administrators Using a Command-Line Client
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.
Global Static Privileges Granted
Certain global static MySQL privileges are granted to the DB system administrator.
Table 5-9 Global Static Privileges Granted
Related Topics
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 |
Related Topics
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
Privileges Revoked from mysql_option Schema | |
---|---|
INSERT |
UPDATE |
DELETE |
CREATE |
DROP |
REFERENCES |
INDEX |
ALTER |
CREATE TEMPORARY TABLES |
LOCK TABLES |
EXECUTE |
CREATE VIEW |
CREATE ROUTINE |
ALTER ROUTINE |
EVENT |
TRIGGER |
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
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 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
Privileges Revoked from mysql_option Schema | |
---|---|
INSERT |
UPDATE |
DELETE |
CREATE |
DROP |
REFERENCES |
INDEX |
ALTER |
CREATE TEMPORARY TABLES |
LOCK TABLES |
EXECUTE |
CREATE VIEW |
CREATE ROUTINE |
ALTER ROUTINE |
EVENT |
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-14 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-15 Roles Granted
Roles Granted | |
---|---|
administrator |
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