Programmatically setting MySQL user roles with Perl

MySQL 5.0 does not support user roles.   Try Googling ‘mysql roles’ and you’ll see what I mean.  The permissions available to a user are configured by GRANT statements that apply only to single ‘user’@'host’ combination. There are third party tools too support an overly of roles to MySQL such as Darren Cassar’s Open-source Securich and Google’s MySQL patches. I didn’t actually come across these tools until after I wrote the scripts documented here, but I frankly prefef what I’ve come up with

• No patches to MySQL are required
• Everthing is entirely outside of MySQL
• The definition files are very easy for humans to read
• The definition files can be version-controlled, providing a clear audit trail (provided you only manage perms via this process)
• It’s appropriate in scope for the handful of roles and dozens of users we need to support

Grants are applied to MySQL servers via a script provision_mysql.pl (). and two configuration files.

The two configuration files are roles.yml, and a server-specific users.cfg file. The roles.yml is YAML-formatted file that defines the grants that apply to specific role. The grants are standard MySQL GRANT commands with ‘%s@%s’ being the placeholder for ‘user’@'host’ to be defined later. E.g.:

---
default:
  - GRANT USAGE ON *.* TO %s@%s WITH MAX_QUERIES_PER_HOUR 100;
community:
  - GRANT SELECT, EXECUTE ON `aarp_main`.* to %s@%s
  - GRANT SELECT ON `mysql`.`proc` to %s@%s
24x7:
  - GRANT SELECT on *.* to %s@%s
# Root gets no queries per hour limit
root:
  - GRANT ALL PRIVILEGES ON *.* TO %s@%s WITH GRANT OPTION WITH MAX_QUERIES_PER_HOUR 0;

The users.cfg file specifes the user, host, and roles that are required. In the example below, user1 has ‘root’ grants on localhost, but ’24×7′ grants when connecting from a remote host. user2 has only ‘community’ when connecting remotely or on localhost, and no other grants.

user1 : localhost : root
user1 : % : 24x7
user2 : % localhost : community

These grants are applied by running ./provision_mysql users.cfg with the following prerequisites:

1. The roles.yml file must be in the same directory
2. The $HOME/.my.cnf must define ‘root’ level access to the mysql server
3. The RPMs/CPAN modules for perl-DBD-MySQL, perl-YAML, and perl-Term-ReadKey

The script then takes the following actions:

1. Creates ‘user’@'host’ if that user does not yet exist
   • If a password is found for another ‘user’@ anyhost, that password is reused
   • Otherwise, you are prompted to enter a new password
2. Revokes all privileges for the user
3. Applies the list of grants defined for the user’s roles

Shortly after I first set this up, Matt emailed me that he could not list the stored procedures in mysql.proc. Oops, I had set the wrong grants for the ’24×7′ role. I updated the ‘roles.yml’ file, ran./provision_mysql.pl w-x_users.cfg and all the users in the role had their grants corrected.

There are a few features that would be nice to have:

• Support for dropping all users (except root) for which ‘user’@'host’ are not defined
• Generating a ‘.my.cnf’ file for new users with their password

But that’s a job for another day. When I make an update I’ll post a git project for this.