MySQL Database Service: administrator user

On MySQL Database Service (aka MDS) on Oracle Cloud Infrastructure (aka OCI), when you create a new instance, you also need to set the credentials for the administrator:

Let’s focus on that account…

Please don’t forget the password, it cannot be recovered !!

First of all that account’s username has some limitations. Indeed, some names are reserved as it’s mentioned in the manual. So you could not use the following usernames:

• administrator
• ociadmin
• ocirpl
• mysql.sys
• mysql.session
• mysql.infoschema

This list can evolve, you can get the list directly from the MySQL DB System creation’s screen:

In fact, those accounts are already existing in the MySQL DB Instance:

MySQL  select User from mysql.user where user not like 'admin';
+------------------+
| User             |
+------------------+
| administrator    |
| ocirpl           |
| ociadmin         |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
+------------------+
6 rows in set (0.0006 sec)

Some people like to use ‘root‘ for their administrator username but I prefer ‘admin‘ as ‘root‘ doesn’t sound correct for an account with some limitations.

Because yes, this administrator account has some limitations. You should not forget that MDS is a managed service and therefore, the real administrator of the database is the MySQL Team operating it for you.

Compare to the ‘root‘ user on a local instance (as MySQL 8.0.26), the following privileges are not granted to the new created administrator (‘admin‘) user:

• RELOAD
• SHUTDOWN
• FILE
• SUPER
• CREATE TABLESPACE
• AUDIT_ADMIN
• BINLOG_ADMIN
• BINLOG_ENCRYPTION_ADMIN
• CLONE_ADMIN
• CONNECTION_ADMIN
• ENCRYPTION_KEY_ADMIN
• FLUSH_OPTIMIZER_COSTS
• FLUSH_USER_RESOURCES
• GROUP_REPLICATION_ADMIN
• INNODB_REDO_LOG_ARCHIVE
• INNODB_REDO_LOG_ENABLE,
• PERSIST_RO_VARIABLES_ADMIN
• REPLICATION_SLAVE_ADMIN
• RESOURCE_GROUP_ADMIN
• RESOURCE_GROUP_USER
• ROLE_ADMIN
• SERVICE_CONNECTION_ADMIN
• SESSION_VARIABLES_ADMIN
• SET_USER_ID
• SHOW_ROUTINE (we will discuss this one in a future article)
• SYSTEM_USER
• SYSTEM_VARIABLES_ADMIN
• TABLE_ENCRYPTION_ADMIN

Quite a large list. This is exactly the reason I prefer not to call this administrator user ‘root‘.

If you want to list all available privileges, just run show privileges;

Some privileges are also revoked in MDS for this ‘admin’ user as mentioned again in the manual.

Create another administrator account

So what is the best practice to create a second administrator on my MDS instance ?

You don’t need to remember all the available and authorized privileges, the MySQL Team provided exactly all is needed: the administrator role !

If you need to create a new administrator user, you just need to assign him the administrator role and it will have all the same privileges as the first administrator account at the time of its creation:

MySQL  create user admin2 identified by 'MySQL8isGr3at!' 
        default role 'administrator';

MySQL  show grants for admin2;
+-------------------------------------------+
| Grants for admin2@%                       |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `admin2`@`%`        |
| GRANT `administrator`@`%` TO `admin2`@`%` |
+-------------------------------------------+

And if you connect to your MySQL instance with the new created user you will see all the privileges granted and revoked like the first created administrator:

 SQL  show grants\G
*************************** 1. row ***************************
Grants for admin2@%: 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 `admin2`@`%` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for admin2@%: GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,CONNECTION_ADMIN,FLUSH_TABLES,REPLICATION_APPLIER,ROLE_ADMIN,XA_RECOVER_ADMIN ON *.* TO `admin2`@`%` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for admin2@%: 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 `admin2`@`%`
*************************** 4. row ***************************
Grants for admin2@%: REVOKE CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `sys`.* FROM `admin2`@`%`
*************************** 5. row ***************************
Grants for admin2@%: GRANT `administrator`@`%` TO `admin2`@`%`

Summary

To summarize, it’s important to remember that:

• you should not forget the administrator account’s password
• the administrator account has all the required privileges to deal with daily MySQL DBA tasks (create users, tables, get statistics out of performance_schema, …)
• but the administrator account cannot “operate” the instance, the operations (binlogs, tablespaces, …) are MySQL Team’s responsibility
• you can easily create other administrator accounts using the specific role

Thank you for using MySQL and MDS !