When trying to make things better, make it very complicated.
I was working on a Security Threat Tool script when I had to learn more about the interaction between static and dynamic privileges in MySQL 8.
Dynamic privileges is a “new” thing added in MySQL 8 to easily extend the privileges definition, and at the same time to provide more granularity. For instance, the FLUSH operation now has dedicated Privileges and by scope.
Dynamic privileges are assigned at runtime. Most of them are active when the server starts. But they can also change with respect to the components or plugin when activated. (https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/privileges-provided.html#privileges-provided-dynamic)
Static privileges are the classical privileges available in MySQL. (https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/privileges-provided.html#privileges-provided-static)
Those are built into the server and cannot be changed.
So far, all is good. If we can give more flexibility to the security mechanism existing in MySQL, well, I am all for it.
My first step was to deal with the abuse of SUPER.
About that – the manual comes to help with a section called Migrating Accounts from SUPER to Dynamic Privileges.
Woo perfect!
Let us play a bit. First, let me create a user:
1 2 3 4 5 6 7 | create user secure_test@'localhost' identified by 'secret'; DC2-2(secure_test@localhost) [(none)]>show grants for current_user(); +-------------------------------------------------+ | Grants for secure_test@localhost | +-------------------------------------------------+ | GRANT USAGE ON *.* TO `secure_test`@`localhost` | +-------------------------------------------------+ |
As you can see I can connect, but have no permissions.
On another terminal with an administrative account, let us do the classical operation to create a DBA:
1 | GRANT ALL on *.* to secure_test@'localhost' WITH GRANT OPTION; |
And now I have:
1 2 3 4 5 | DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G *************************** 1. row *************************** Grants for secure_test@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, 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 `secure_test`@`localhost` WITH GRANT OPTION *************************** 2. row *************************** Grants for secure_test@localhost: 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 `secure_test`@`localhost` WITH GRANT OPTION |
As you can see, I have a bunch of privileges assigned.
To be honest, to have to identify exactly what each privilege does and how it interacts with the others is challenging.
Anyhow, the manual tells us:
“For each account identified by the preceding query, determine the operations for which it needs SUPER. Then grant the dynamic privileges corresponding to those operations, and revoke SUPER.“
In our case:
1 | revoke SUPER on *.* from secure_test@'localhost'; |
Which will remove the SUPER privileges, but what else will remain active? Let us try one of the easiest things, let us modify the variable super_read_only.
With super I can change the value of the variable without problems, but if I remove the SUPER privileges, what will happen?
1 2 3 4 5 | DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G *************************** 1. row *************************** Grants for secure_test@localhost: 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 `secure_test`@`localhost` WITH GRANT OPTION *************************** 2. row *************************** Grants for secure_test@localhost: 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 `secure_test`@`localhost` WITH GRANT OPTION |
As you can see SUPER is gone.
1 2 | DC2-2(secure_test@localhost) [(none)]>set global super_read_only=0; Query OK, 0 rows affected (0.00 sec) |
And I can still modify the global variable. WHY?
The manual says that SYSTEM_VARIABLES_ADMIN from the dynamic privileges allow us to modify “Enables system variable changes at runtime“. Well, what if I revoke it?
1 2 3 4 | revoke SYSTEM_VARIABLES_ADMIN on *.* from secure_test@'localhost'; DC2-2(secure_test@localhost) [(none)]>set global super_read_only=0; ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation |
Great! So in order to really remove/limit super, I need to also remove SYSTEM_VARIABLES_ADMIN. But is that all?
Well to make it short, no it is not.
Checking the manual you can see that SUPER is affecting all these:
- BINLOG_ADMIN,
- CONNECTION_ADMIN,
- ENCRYPTION_KEY_ADMIN,
- GROUP_REPLICATION_ADMIN,
- REPLICATION_SLAVE_ADMIN,
- SESSION_VARIABLES_ADMIN,
- SET_USER_ID,
- SYSTEM_VARIABLES_ADMIN
And these are the ones by default. But we can also have others depending on the plugins we have active.
So in theory to be sure we are removing all SUPER related privileges, we should:
1 | REVOKE SUPER, BINLOG_ADMIN, CONNECTION_ADMIN, ENCRYPTION_KEY_ADMIN, GROUP_REPLICATION_ADMIN, REPLICATION_SLAVE_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SYSTEM_VARIABLES_ADMIN on *.* from secure_test@'localhost'; |
This should leave us with the equivalent of a user without SUPER:
1 2 3 4 5 | DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G *************************** 1. row *************************** Grants for secure_test@localhost: 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 `secure_test`@`localhost` WITH GRANT OPTION *************************** 2. row *************************** Grants for secure_test@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SHOW_ROUTINE,SYSTEM_USER,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `secure_test`@`localhost` WITH GRANT OPTION |
CONCLUSION
In this first blog, we have started to explore the usage of Dynamic privileges, and what we need to do to remove the SUPER privilege.
Nevertheless, the list above is still a bit chaotic and unsafe. We still have SHUTDOWN or RELOAD or FILE, all of them are insecure and should be assigned with great care. In the next article in this series, we see how to deal with Dynamic and Static privileges by Role and we also try to have clearer how they affect one another.
Percona Distribution for MySQL is the most complete, stable, scalable, and secure, open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
I implemented dynamic privileges in TiDB earlier this year. I was actually not that familiar with the feature prior since security is not something I’ve previously focused on. My opinion working on it has been that it is a incredibly well thought out spec that considers the upgrade case of users very carefully.
The purpose of dynamic privileges is not clear from your text, but it is to *replace* the SUPER user with fine grained privileges. SUPER itself is deprecated, but for backward compatibility is still granted by GRANT ALL. You’ve trimmed the output of your SQL statements, but you will actually get a warning when doing this:
mysql [localhost:8024] {root} ((none)) > revoke SUPER on *.* from secure_test@’localhost’;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql [localhost:8024] {root} ((none)) > show warnings;
+———+——+———————————————-+
| Level | Code | Message |
+———+——+———————————————-+
| Warning | 1287 | The SUPER privilege identifier is deprecated |
+———+——+———————————————-+
1 row in set (0.00 sec)
The problem is the semantic “ALL”:
a) Should it allocate only the “new privileges”?
b) Should it allocate only the “old privileges”?
c) Should it allocate both types of privileges?
I don’t think the answer is that straight forward. In the end the MySQL team chose (c) for 8.0, and actually for TiDB we chose (b), but there were other factors that led to our decision. I assume later down the road we will both switch to (a).
One of the subtleties I like about dynamic privileges is that the names of the privileges are already “role-oriented”. So typically you can allocate just one privilege to a user (such as CLONE_ADMIN) and not have to figure out from a long list what is required. This helps prevent over-allocating privileges, which is a real issue in the privilege design of MySQL. It’s not always clear from error messages what privileges will be required for a certain operation.
Morgan, I agree with all you just said. 😀
BTW I consider ONE of the scope of dynamic privileges to replace super with fine grained security (FINALLY).
Anyhow for me the main point is that we should STOP to assign privileges to the users and instead use ROLEs and assign them as many of the more mature RDBMS do.
The other problem can be the proliferation of dynamic privileges, that we all do not start to use ROLESs can be the next nightmare for DBAs. Who already have bad sleeping with the security as it is.
I am immagine the need to assign proper privileges by table/column to different users. Or different admin role by application owner …
just to start