Ever since Oracle became the owner of MySQL when it acquired Sun Microsystems in 2010, improvements to the software haven’t been as forthcoming as one might hope. In fact, some critics have gone so far as to contend that Oracle is willfully “trying to make it as difficult as possible to use MySQL”. I can’t say for sure, but I do know that some features have been a long time coming. Case in point, User Role support was originally slated for the MySQL 5.0 release. Fast-forward to the present, there still doesn’t seem to be anything like groups in MySQL. According to Oracle, we can expect it to arrive for MySQL 7.0 (WL#988)! Until then, this article presents a few software offerings that may help tide you over.
Where there are gaps in application functionality, you can always expect someone to step up and fill the void. MySQL User Roles is no exception. Here are a just a few that I was able to find.
Is there any pie that Google doesn’t have a couple of fingers in? (Apparently not.) Google-mysql-tools contain several tools for managing, maintaining, and improving the performance of MySQL databases. User Roles support is bundled in the permissions_lib package. Others include pylib/db.py, which allows parallel query execution, and parser_lib, a library that can parse SQL statements and apply rules based on the parsed output. The tools are written in python, so they require the MySQLdb MySQL database connector to run.
Google-mysql-tools add a new table called mysql.mapped_user to define mapped users. This table does not have a privileges column per se. Instead, each row references an account name from the mysql.user table that provides the privileges. Entries in the mysql.user table are then reassigned to represent roles when there are entries from mysql.mapped_user that reference them. Hence, one row in mysql.user may be referenced by multiple rows in the mysql.mapped_user table. In a nutshell, entries in the mysql.user table are reused for roles when there are entries from mysql.mapped_user that reference them.
Here is some sample python code that illustrates how to utilize user privileges using google-mysql-tools:
SetAllowedFields(['ssl_cipher', 'x509_issuer', 'x509_subject']) base_tmpl = (Account(ssl_cipher='', x509_issuer='', x509_subject='') .GrantPrivileges(database='test', privileges=SELECT | INSERT | UPDATE | DELETE | CREATE | DROP) .AddAllowedHost(hostname_pattern='%')) (base_tmpl.Clone(username='root', # password='test', password_hash='*F3A2A51A9B0F2BE2468926B4132313728C250DBF') .GrantPrivileges(privileges=ALL_PRIVILEGES) .Export(set_name='primary') .Export(set_name='replica'))
SecuRich is an Open Source project licensed under GPLv2 that provides customizable user roles, password expiry and password history. The product consists of a database that contains some pre-defined tables and stored procedures that manage roles.
The first step is to switch to the securich database:
mysql> use securich;
To add a new role, call the create_update_role stored procedure with the command, name, and role:
mysql> call create_update_role('add','role1','select');
Once a new role has been defined, we can add more privileges to it:
mysql> call create_update_role('add','role1','insert'); mysql> call create_update_role('add','role1','update');
Now we’re ready to assign the new role to a user:
mysql> call grant_privileges('rob', 'sys1.robgravelle.com', 'employees', '', 'alltables', 'role1', 'firstname.lastname@example.org');
The show_full_user_entries proc displays the roles assigned to a particular user and on which database, table and from which host those privileges can be used:
mysql> call show_full_user_entries('rob'); +----------+----------+--------------+-----------+-------+-----------+-------+ | USERNAME | HOSTNAME | DATABASENAME | TABLENAME | ROLE | PRIVILEGE | STATE | +----------+----------+--------------+-----------+-------+-----------+-------+ | rob | 10.0.0.2 | employees | writers | role1 | INSERT | A | | rob | 10.0.0.2 | employees | writers | role1 | SELECT | A | | rob | 10.0.0.2 | employees | writers | role1 | UPDATE | A | +----------+----------+--------------+-----------+-------+-----------+-------+
Other procs such as set_password, rename_user, and clone_user are geared towards user data rather than roles, but since users and their roles are closely linked, user management must also be performed via the SecuRich interface:
mysql> call set_password('rob', '10.0.0.2', '2f76754c4ef43ded7a', 'password123'); mysql> call rename_user('rob' , 'rod' , 'email@example.com'); mysql> call clone_user('rob', '10.0.0.2', 'robert' , '10.0.0.2', 'firstname.lastname@example.org');
SecuRich is available for MySQL 5.1 or higher on any MySQL platform.
Like google-mysql-tools, the MySQL Workbench doesn't create roles as such but provides a role-like interface to the model. Hence, the underlying code isn't creating roles but making direct grants to users in batches.
User roles fall under the Server Administration heading on the right side of the main screen.
- Right-click the Server Instance from the list and select Manage Instance from the popup menu to open the Admin tab. You’ll have to click on New Server Instance below the listbox if you don’t have any active instances.
Note that your mysqld.exe daemon must be running as a service in order to use the MySQL Workbench Server Administration feature.
- On the Admin screen, click on Users and Privileges under the Security heading on the left and click on the first tab, named “Server Access Management” to view the User Accounts information.
You can Add or Delete users as well as assign roles to them from here:
MySQL Workbench Admin Screen
Select any user and there are three sub tabs on the right hand side: Login, Administrative Roles, and Account Limits.
Clicking on the Administrative Roles tab shows the ten available Administrative Roles:
1. DBA: Grants rights to perform all the tasks.
2. MaintenanceAdmin: Grants rights only related to maintaining the server. MaintenanceAdmin may also have the rights of ProcessAdmin.
3. ProcessAdmin: Grants rights only to access monitor and kill user processes.
4. UserAdmin: Grants rights to create users login and reset passwords.
5. SecurityAdmin: Grants rights to manage logins and grant and revoke server privileges. SecurityAdmin can also have the rights of UserAdmin.
6. MonitorAdmin: Grants rights only to monitor the server.
7. DBManager: Grants full rights to manage all databases. DBManager also inherits the rights of BackupAdmin.
8. DBDesigner: Grants rights to create and reverse engineer any database schema.
9. ReplicationAdmin: Grants rights to setup and manage replication.
10. BackupAdmin: Grants required rights to back up databases.
Today we looked at three tools that bring User Roles to MySQL while we wait for Oracle to include them in an up-coming release. Of course, there is nothing stopping you from implementing your own solution, but that route is not advisable unless you have a really strong grasp of User Roles and have the skill at designing stored procedures and/or applications in order to be able to implement a robust solution.