Emulating MySQL roles with the Percona PAM plugin and proxy users

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<br># chmod g+r /etc/shadow<br>

  1	# chgrp mysql /etc/shadow<br># chmod g+r /etc/shadow<br>

• Install the plugin:
  
  mysql&gt; INSTALL PLUGIN auth_pam SONAME 'auth_pam.so';<br>

  1	mysql&gt; INSTALL PLUGIN auth_pam SONAME 'auth_pam.so';<br>

• Create a /etc/pam.d/mysqld file containing:
  
  auth required pam_warn.so<br>auth required pam_unix.so audit<br>account required pam_unix.so audit<br>

  1	auth       required     pam_warn.so<br>auth       required     pam_unix.so audit<br>account    required     pam_unix.so audit<br>

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:

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

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:

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:

Creating the Unix user accounts

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

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:

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!