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.
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. …