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:
  • Install the plugin:
  • Create a /etc/pam.d/mysqld file containing:

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!

5 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Steve Jackson

Hei Stephane.

You mention that its just to swap out pam_unix.so with pam_ldap.so. Have you guys actually tested this?

I have followed the MySQL/Percona documentation, and this blog post religiously to try and get LDAP working.

In the authentication string i map the relevant group to the proxy user “pam_test”

I have no errors in auth_log for LDAP authentication, yet still I get access denied.

In the general log, I see the following, which indicates that the group mapping is working (I have no steve user defined in the user table, only the ”@” capture user, and the pam_test proxy user.

The ”@” capture user has the following config auth_pam | mysql,steves_group=pam_test

and /etc/pam.d/mysql has of course

auth required pam_ldap.so
account required pam_ldap.so

150616 9:45:47
118 Connect [email protected] as pam_test on
118 Connect Access denied for user ‘steve’@’157.xxx.xx.xx’ (using password: YES)

Its almost like it’s not trying to use the ”@” user

0
Stephane Combaudon

Hi Steve,

I have not tested the setup with LDAP, so there may be some additional steps.
Check this post which is about how to configure the MySQL PAM plugin with LDAP:
http://www.arubin.org/blog/2012/07/16/mysql-pam-ldap-authentication-module-configuration/

0
Evgeniy Sudyr

I got it working with LDAP and had same issues as Steve. Make sure you granted access to correct hosts – In my case using client on localhost make no sense at all.

For LDAP checks I would recommend use id to make sure user belongs to group you want map to proxy user.

CREATE USER ”@” IDENTIFIED WITH auth_pam AS ‘mysqld, mysql_dev=dev_proxy’;
GRANT SELECT on mydb1.* to ‘dev_proxy’@’%’ identified by ‘XXXX’;
GRANT PROXY ON ‘dev_proxy’@’%’ TO ”@”;

Hope this save couple hours to someone 😉

0
Vitaly Karasik

It seems that there is a bug into Percona module – all LDAP users, not only from mapped group, is able to login to MySQL.
These users have access only to “test” and “information_scheme” DBs, but anyway.

mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
Current database: information_schema

+——————+—————-+————–+
| USER() | CURRENT_USER() | @@proxy_user |
+——————+—————-+————–+
| test22@localhost | @ | NULL |
+——————+—————-+————–+
1 row in set (0.07 sec)

MySQL plugin doesn’t allow to such users to login.

0
neunoum

Is this supposed to work on Percona XtraDB Cluster 5.6.28-25.14.1?

mysql> GRANT PROXY ON ‘dba’@’localhost’ TO ”@”;
ERROR 1698 (28000): Access denied for user ‘root’@’localhost’

The same process worked on all Percona Server databases.

0