MySQL Static and Dynamic privileges (Part2)
When organizing things helps to simplify life.
In the previous article we start to explore dynamic privileges and the interaction with the static ones. We also saw how to remove SUPER privilege from a DBA account.
What we did was go by subtraction. But in real life, we should act differently. We should ADD only what is really needed for the account to work correctly.
Adding privilege one by one, and for each user is problematic given the level of interaction they may have, and also prone to mistakes.
Instead we can use ROLES to group, assign and revoke the correct privileges in a much easier way.
This is becoming even more important in MySQL with the advent of dynamic privileges.
What should we do to correctly use ROLEs? Well first of all design.
The first step is to identify the ROLES, in doing so we need to keep a basic principle, make it simple, as such let us try to avoid having too many ROLES, or ROLE with too many cross functional privileges.
My proposal:
- DBA (The lord of the databases who can do all)
- MaintenanceAdmin (DBA minions :-) they can perform only some action on the server, and server only)
- UserAdmin (Can create users assign grants and so on)
- MonitorUser (See all process and read from performance_schema)
- DBManager (Can add/drop/modify schemas/tables/triggers/view/routines etc )
- DBDesigner (Can modify specific objects mostly with a clear identification by schema/table)
- ReplicationAdmin (Can add/change/remove start/stop replication also GR)
- BackupAdmin (Can take backup, cannot restore)
We have 8 administrative ROLES and they should cover ALL we need for administrative tasks.
Now let us create them:
CREATE ROLE 'DBA', 'MaintenanceAdmin', 'UserAdmin', 'MonitorUser', 'DBManager', 'DBDesigner', 'ReplicationAdmin', 'BackupAdmin' DC2-1(root@localhost) [mysql]>Select user,host from mysql.user where account_locked ='Y' and password_expired='Y' order by 1; +------------------+------+ | user | host | +------------------+------+ | BackupAdmin | % | | DBA | % | | DBDesigner | % | | DBManager | % | | MaintenanceAdmin | % | | MonitorUser | % | | ReplicationAdmin | % | | UserAdmin | % | +------------------+------+ 8 rows in set (0.00 sec)
Let us check the roles one by one and see what privileges we need to assign.
Our test user do not have any grant:
DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G *************************** 1. row *************************** Grants for secure_test@localhost: GRANT USAGE ON *.* TO `secure_test`@`localhost`
DBA,
well you may say .. easy GRANT ALL.
Wrong! As already indicated in the previous article, doing that will also assign SUPER, which is deprecated from MySQL 8.0.x. Let us start with the right foot and add ONLY what we need:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, 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 TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `DBA`@`%` WITH GRANT OPTION; GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `DBA`@`%` WITH GRANT OPTION;
That should be exactly the same as GRANT ALL, but without SUPER.
To assign the ROLE to our test user:
GRANT `DBA`@`%` TO `secure_test`@`localhost`
Now our user has:
DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G *************************** 1. row *************************** Grants for secure_test@localhost: GRANT USAGE ON *.* TO `secure_test`@`localhost` *************************** 2. row *************************** Grants for secure_test@localhost: GRANT `DBA`@`%` TO `secure_test`@`localhost`
Correct you now see DBA as grant but that is not active:
DC2-2(secure_test@localhost) [(none)]>show grants for DBA@'%'\G ERROR 1142 (42000): SELECT command denied to user 'secure_test'@'localhost' for table 'user'
To ACTIVATE a role you need to do it explicitly:
SET DEFAULT ROLE DBA TO secure_test@'localhost';
And have the user reconnect!
Once a role is activated we can also use:
show grants for current_user()\G
To check which privileges are now active for a specific user.
We can also control which role is active for which user querying the table mysql.default_roles.
To remove the active role:
SET DEFAULT ROLE NONE TO secure_test@'localhost';
Anyhow, NOW we have our DBA role available for all DBA and if we need to change something we can do it just there on the ROLE and not on each single user.
MaintenanceAdmin,
GRANT EVENT, LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, RELOAD, SHUTDOWN ON *.* TO `MaintenanceAdmin`@`%`; GRANT BINLOG_ADMIN, CONNECTION_ADMIN, ENCRYPTION_KEY_ADMIN, GROUP_REPLICATION_ADMIN, REPLICATION_SLAVE_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SYSTEM_VARIABLES_ADMIN ON *.* TO `MaintenanceAdmin`@`%`; GRANT `MaintenanceAdmin`@`%` TO `secure_test`@`localhost` ;
UserAdmin,
GRANT CREATE USER, GRANT OPTION, RELOAD, SHOW DATABASES ON *.* TO `UserAdmin`@`%`; GRANT ROLE_ADMIN ON *.* TO `UserAdmin`@`%`; GRANT `UserAdmin`@`%` TO `secure_test`@`localhost` ;
MonitorUser,
GRANT PROCESS, REPLICATION CLIENT ON *.* TO `MonitorUser`@`%`; GRANT SELECT ON performance_schema.* TO `MonitorUser`@`%`; GRANT `MonitorUser`@`%` TO `secure_test`@`localhost` ;
DBManager,
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, DROP ROLE, EVENT, INDEX, INSERT, LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER, UPDATE ON *.* TO `DBManager`@`%`; GRANT SET_USER_ID, SHOW_ROUTINE ON *.* TO `DBManager`@`%`; GRANT `DBManager`@`%` TO `secure_test`@`localhost` ;
DBDesigner,
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE VIEW, INDEX, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER ON *.* TO `DBDesigner`@`%`; GRANT `DBDesigner`@`%` TO `secure_test`@`localhost` ;
ReplicationAdmin,
GRANT REPLICATION CLIENT ON *.* TO `ReplicationAdmin`@`%`; GRANT REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, GROUP_REPLICATION_ADMIN, SERVICE_CONNECTION_ADMIN ON *.* TO `ReplicationAdmin`@`%`; GRANT SELECT on performance_schema.* TO `ReplicationAdmin`@`%`; GRANT SELECT on mysql.* TO `ReplicationAdmin`@`%`; GRANT `ReplicationAdmin`@`%` TO `secure_test`@`localhost` ;
BackupAdmin,
GRANT EVENT, LOCK TABLES, SELECT, SHOW DATABASES ON *.* TO `BackupAdmin`@`%`; GRANT BACKUP_ADMIN ON *.* TO `BackupAdmin`@`%`; GRANT `BackupAdmin`@`%` TO `secure_test`@`localhost` ;
Once all our ROLES are in, we can test them. For instance we can check our ReplicationAdmin checking the Binary Logs and stopping/starting our Group Replication (or normal Replication):
DC2-2(secure_test@localhost) [(none)]>show binary logs; ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation DC2-2(secure_test@localhost) [(none)]>stop group_replication; ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or GROUP_REPLICATION_ADMIN privilege(s) for this operation
Also if created and assigned the role is not active. Let us now enable the role for the user:
SET DEFAULT ROLE ReplicationAdmin TO secure_test@'localhost';
Remember to reconnect!
DC2-2(secure_test@localhost) [(none)]>show binary logs; +---------------+-----------+-----------+ | Log_name | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000011 | 113802321 | No | | binlog.000012 | 19278 | No | +---------------+-----------+-----------+ 2 rows in set (0.00 sec) DC2-2(secure_test@localhost) [(none)]>stop group_replication; Query OK, 0 rows affected (5.25 sec) DC2-2(secure_test@localhost) [(none)]>select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 7fddf04f-9797-11eb-a193-08002734ed50 | gr5 | 3306 | OFFLINE | | | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 1 row in set (0.00 sec) DC2-2(secure_test@localhost) [(none)]>start group_replication; Query OK, 0 rows affected (3.70 sec) DC2-2(secure_test@localhost) [(none)]>select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 79ede65d-9797-11eb-9963-08002734ed50 | gr4 | 3306 | ONLINE | PRIMARY | 8.0.23 | | group_replication_applier | 7e214802-9797-11eb-a0cf-08002734ed50 | gr6 | 3306 | ONLINE | SECONDARY | 8.0.23 | | group_replication_applier | 7fddf04f-9797-11eb-a193-08002734ed50 | gr5 | 3306 | ONLINE | SECONDARY | 8.0.23 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.01 sec)
And these are the privileges active:
DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G *************************** 1. row *************************** Grants for secure_test@localhost: GRANT REPLICATION CLIENT ON *.* TO `secure_test`@`localhost` *************************** 2. row *************************** Grants for secure_test@localhost: GRANT GROUP_REPLICATION_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,SERVICE_CONNECTION_ADMIN ON *.* TO `secure_test`@`localhost` *************************** 3. row *************************** Grants for secure_test@localhost: GRANT SELECT ON `mysql`.* TO `secure_test`@`localhost` *************************** 4. row *************************** Grants for secure_test@localhost: GRANT SELECT ON `performance_schema`.* TO `secure_test`@`localhost` *************************** 5. row *************************** Grants for secure_test@localhost: GRANT `BackupAdmin`@`%`,`DBA`@`%`,`DBDesigner`@`%`,`DBManager`@`%`,`MaintenanceAdmin`@`%`,`MonitorUser`@`%`,`ReplicationAdmin`@`%`,`UserAdmin`@`%` TO `secure_test`@`localhost` 5 rows in set (0.00 sec)
Conclusions
Using the ROLES allows us to modify the needed privileges in one place, and at the same time will allow us to keep under control the possible proliferation of dynamic privileges due the use of components or plugins, significantly reducing the complexity of having multiple privileges sources.
Roles are normally used in the most common Databases and MySQL had implemented them quite late. But using roles is the natural evolution of how we should deal with user grants when moving from small platforms to medium/large.
The time when we assign single user privileges IS GONE, welcome to 2021 MySQLlers!
For your convenience I am distributing a simple SQL file with all commands to create the Roles as described in this article(link to github)
References
https://dev.mysql.com/doc/refman/8.0/en/roles.html
https://lefred.be/content/some-queries-related-to-mysql-roles/
https://lefred.be/content/mysql-8-0-listing-roles/
https://lefred.be/content/mysql-8-0-roles-and-graphml/
BUT!!! If the primary goes down, the cluster is unable to elect a new primary until a Secondary had completed the operations. Meaning if alter takes 10 hours, we have a possible cluster without Primary for 10 hours. See also bug: