MySQL User Groups

Most operating systems have a user privilege granting system where privileges are given to groups, and users are asigned to groups, thereby inheriting all the privileges of this group. Unfortunately this is not currently (using MySQL server version 5.1) natively possible: the privileges must be defined for every user. Managing a MySQL installation with many users can become a very laborious, and non transparent process, and many admins opt to have fewer MySQL users, with many real users using one MySQL user with the desired privileges. This increases difficulty in managing the server, because you never have just the right set of privileges for a given person, nor can you quickly determine which processes are being carried out by which person.

Following is a work around to implement a kind of group privilege management in MySQL.

Setup

See attached procedure and creates to set it up the tables on your system.

Two additional tables must be defined to hold the groups.  These are created in a new `mysql_meta` database (must be created first). The first table contains a list of the users which should be managed, and the groups which they are in. The second table contains a list of the privileges given for each group.

After entering the users, groups, the procedure updateMysqlUserPrivileges when called will first revoke all privileges from all users listed, and then proceed to grant the privileges anew. This is not very elegant… but if it was, I wouldn’t call it a workaround!

It should be noted at this point that every user listed will be processed. That means that if a user is defined, but he has no group, or no privileges defined, all his privileges will be revoked, and he will be left with nothing but USAGE on the server.

Adding users

Users must be defined manually before adding them to the mysql_meta tables:

CREATE USER newuser@localhost IDENTIFIED BY 'password';

You can then add your user to the mysql_meta.user_groups table. If the user should belong to multiple groups, make multiple entries in this table, one for each group.

In the mysql_meta.user_group_privileges you can now define the privileges which should be given to each group. Please note that I have simplified by using one column `schema` to contain the database/table definition. As a result, it is not currently possible to grant permissions to specific columns. For example:

INSERT INTO `mysql_meta`.`user_groups` (`user`,`host`,`user_group`)
   VALUES( 'newuser','localhost','newgroup');
INSERT INTO `mysql_meta`.`user_group_privileges`
  (`user_group`,`schema`,`privilege_type`,`isa`)
  VALUES ( 'newgroup','mydb.*' ,'SELECT','TABLE'),
  ('newgroup','mydb.*','UPDATE'),
  ('newgroup','mydb.*','INSERT','TABLE'),
  ('newgroup','mydb.fancyProcedure','EXECUTE','PROCEDURE');

Would be equivalent to:

GRANT UPDATE, INSERT, SELECT ON mydb.* TO newuser@localhost;
GRANT EXECUTE PROCEDURE ON mydb.fancyProcedure TO newuser@localhost

Committing new privileges

Once the new privilege tables are completed, simply call the procedure to update:

CALL mysql_meta.updateMysqlUserPrivileges;

And here we stumble on another shortcoming of MySQL: there is no real way to do error handling in procedures or functions… I have opted to return a row with two columns: “Errors” and “Warnings”. If any users are defined in the user_groups table, but are not yet created as MySQL users, these will be listed as Errors, and if there are users which are defined, but which have no privileges, they will be listed in the Warnings column.

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.