If you’re like me, you love the granular permissions capabilities of MySQL but hate the work that goes into managing them.
Recently, I’ve been dealing with MySQL permissions a lot and most of the time I’m creating very similar permissions over and over again. It got me thinking that I could really use MySQL groups. Unfortunately, there doesn’t seem to be anything like groups in MySQL and according to plans won’t be added officially until MySQL 7.0 (WL#988). Considering they originally planned to include Role support in MySQL 5.0, I’m not sure I’m holding my breath.
While searching around, I found Securich – a project about 6 months old which uses stored procedures to create a much more capable and easy to manage permissions system on top of MySQL’s existing permissions. DISCLAIMER: I have not actually tried this so everything I say is based on what I’ve understood from the documentation.
On the simplest level, it gives you the ability to define roles as a set of privilege types. This isn’t what I had in mind but it does help. For example, there might be a role called ‘readonly’ which only has SELECT permissions and there may be another role called ‘readwrite’ which has ‘SELECT,INSERT,UPDATE,DELETE’ etc. Then you can grant a role on tables or databases to users. Any time you update the permissions in the role, you update the permissions for all the users on every database/table where they have those permissions.
An interesting feature is the ability to define the tables in the grant privileges procedure using a regexp so if you use a prefix like ‘dev_ ‘ to indicate tables used by developers, you might create a role called ‘developer’ and apply it to all tables beginning with ‘dev_’.
Additional features I like are:
- The ability to search for users with specific permissions.
- The ability to clone users (ie. add another developer)
- Storing meta-data on the users like contact email address.
- Password aging, history, and complexity requirements – although it seems like these are only enforced if the passwords aren’t modified using standard MySQL commands.
- Auditing – Securich stores a history of when permissions are granted and to whom, etc.
What I don’t see:
- I don’t see column level permissions
- Preferably there would be a way to combine the permissions and the DB/Table set into the ‘Role’ but the ability to clone users is pretty close.
- Promised support – I’m a fan of open source but a one man project is not something for production. It would be nice if this were adopted by someone bigger.
To summarize, I don’t think I’ll be deploying this but it looks promising. I hope Oracle will prioritize Roles and deliver them before 7.0. If not, maybe someone in MySQL will implement something similar to Securich (without the major architecture changes planned for 7.0 (pluggable authentication, new privilege table structures, etc.) to give us the quick win.