From time to time people wonder how to implement roles in MySQL. This can be useful for companies having to deal with many user accounts or for companies with tight security requirements (PCI or HIPAA for instance). Roles do not exist in regular MySQL but here is an example on how to emulate them using Percona Server, the PAM plugin and proxy users.

The goal

Say we have 2 databases: db1 and db2, and we want to be able to create 3 roles:

• db1_dev: can read and write on db1 only.
• db2_dev: can read and write on db2 only.
• stats: can read on db1 and db2

For each role, we will create one user: joe (db1_dev), mike (db2_dev) and tom (stats).

Setting up the Percona PAM plugin

The Percona PAM plugin is distributed with Percona Server 5.5 and 5.6. I will be using Percona Server 5.6 in this post and I will authenticate users with /etc/shadow. As explained here, the setup is easy:

• Make sure /etc/shadow can be read by the mysql user:
  

  # chgrp mysql /etc/shadow
  # chmod g+r /etc/shadow

• Install the plugin:
  

  mysql> INSTALL PLUGIN auth_pam SONAME 'auth_pam.so';

• Create a /etc/pam.d/mysqld file containing:
  

  auth       required     pam_warn.so
  auth       required     pam_unix.so audit
  account    required     pam_unix.so audit

Tinkering with the permissions of /etc/shadow may a security concern. Authenticating users against an LDAP server may be a better option. The configuration of the PAM plugin is similar (replace pam_unix.so with pam_ldap.so and forget the part about /etc/shadow).

Testing authentication with the PAM plugin

Now let’s create a user:

# adduser test_pam
# passwd test_pam
mysql> GRANT ALL PRIVILEGES ON db1.* TO test_pam@localhost IDENTIFIED WITH auth_pam;

And let’s check that the authentication is working as we expect:

mysql -utest_pam -p
Enter password:
mysql> show grants;
+-----------------------------------------------------------+
| Grants for test_pam@localhost                             |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_pam'@'localhost'              |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'test_pam'@'localhost' |
+-----------------------------------------------------------+

That works! We can delete the user and go to the next step.

Creating proxy user

The key to emulate a role is to create a MySQL account for which nobody will know the password (then nobody will be able to use it directly). Instead we will use the PROXY privilege to make sure we map an anonymous account that will match any incoming user to the right MySQL user.

So the first step is to create an anonymous user:

mysql> CREATE USER ''@'' IDENTIFIED WITH auth_pam AS 'mysqld, pam_db1=db1_dev, pam_db2=db2_dev, pam_stats=stats';

The goal of this user is simply to map Unix users in the pam_db1 group to the db1_dev MySQL user, Unix users in the pam_db2 group to the db2_dev MySQL user and Unix users in the pam_stats group to the stats MySQL user.

Creating the proxied accounts

Now we can create the MySQL users corresponding to each of the roles we want to create:

mysql> GRANT SELECT, INSERT ON db1.* TO 'db1_dev'@localhost IDENTIFIED BY 'XXXXX';
mysql> GRANT PROXY ON 'db1_dev'@'localhost' TO ''@'';
mysql> GRANT SELECT, INSERT ON db2.* TO 'db2_dev'@localhost IDENTIFIED BY 'YYYYY';
mysql> GRANT PROXY ON 'db2_dev'@'localhost' TO ''@'';
mysql> GRANT SELECT ON db1.* TO 'stats'@localhost IDENTIFIED BY 'ZZZZZ';
mysql> GRANT SELECT ON db2.* TO 'stats'@localhost;
mysql> GRANT PROXY ON 'stats'@'localhost' TO ''@'';

Creating the Unix user accounts

The last step is to create the Unix users joe, mike and tom and assign them the correct group:

# useradd joe
# passwd joe
# groupadd pam_db1
# usermod -g pam_db1 joe
# useradd mike
# passwd mike
# groupadd pam_db2
# usermod -g pam_db2 mike
# useradd tom
# passwd tom
# groupadd pam_stats
# usermod -g pam_stats tom

Again you may prefer using an LDAP server to avoid creating the users at the OS level.

Testing it out!

Let’s try to connect as mike:

# mysql -umike -p
Enter password:
mysql> show grants;
+----------------------------------------------------------------------------------------------------------------+
| Grants for db2_dev@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'db2_dev'@'localhost' IDENTIFIED BY PASSWORD '*C1DDB6E980040762275B29A316FD993B4A19C108' |
| GRANT SELECT, INSERT ON `db2`.* TO 'db2_dev'@'localhost'                                                       |
+----------------------------------------------------------------------------------------------------------------+

Not bad!

Alternatives

The Percona PAM plugin is not the only option to use roles:

• MariaDB 10 supports roles from version 10.0.5
• Oracle distributes a PAM plugin for MySQL 5.5 and MySQL 5.6 as part of the MySQL Enterprise subscription
• Securich is a set of stored procedures that has many features regarding user management
• Google has been offering support for roles through its google-mysql-tools for a long time.

Conclusion

Even if they are not officially supported, roles can be emulated with an authentication plugin and a proxy user. Let’s hope that roles will be added in MySQL 5.7!