How to write good Mysql grants scripts

Question

I'm using scripts to create Mysql databases and tables. Those scripts contain grant sections like the following:

GRANT SELECT ON my_database.* TO my_user@"%" IDENTIFIED BY 'my_password';
REVOKE ALL PRIVILEGES ON my_database.* FROM my_user@"%";
GRANT SELECT, UPDATE ON my_database.* TO my_user@"%" IDENTIFIED BY 'my_password';

Initially, I used only the third line, but ran into the following problem: Whenever I removed privilege Q from a user and re-ran that script, the user still had that privilege in the database. So I added the revoke line before the grant line.

Then I ran into the following problem: Whenever I ran the script on a 'fresh' Mysql installation, the revoke failed because the user was not yet existing. So I added a 'dummy' grant before the revoke.

Question: Is there any better way to accomplish this? My 'real' scripts contain lots of users and lots of databases and are hard to read, because I need three lines for each set of privileges I want to assign. I'd like to use only one line.

Edit (based on feedback from answers and comments):

I'm looking for the shortest way to say something like

SET PRIVILEGES SELECT, UPDATE
ON my_database.*
TO my_user@"%"
IDENTIFIED BY 'my_password';

where my_user might

• already exists (but could be new)
• currently have privileges extending the ones I want him to have
• have privileges on other databases, which must remain unaffected

Answer 1

You can use a procedure to create new user if necessary and grant privileges to database. I used prepared statements and GRANT statements. Prepared statements in MySQL 5.5 supports GRANT, if you are using lower version, then you can rewrite GRANT command to INSERT INTO.

USE test;

DELIMITER $$

CREATE PROCEDURE procedure_user(
  IN host_name VARCHAR(60),  IN user_name VARCHAR(60),
  IN db_name   VARCHAR(255),
  IN db_privs  VARCHAR(255))
BEGIN
  SELECT 1 INTO @exist FROM mysql.user WHERE user = user_name AND host = host_name;

  -- Create new user, generate command like this: CREATE USER 'user1'@'%';;
  IF @exist IS NULL THEN

    SET @sql = CONCAT('CREATE USER ''', user_name, '''@''', host_name, '''');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;

  -- Generate command like this: GRANT INSERT, UPDATE ON database1.* TO 'user1'@'%';
  SET @sql = CONCAT('GRANT ', db_privs, ' ON ', db_name, '.* TO ''', user_name, '''@''', host_name, '''');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END
$$

DELIMITER ;

Using examples:

-- First command will create new user user1@% and will grant SELECT, INSERT, UPDATE privileges to database1.
CALL procedure_user('%', 'user1', 'database1', 'SELECT, INSERT, UPDATE');

-- Second command just will grant SELECT, INSERT, UPDATE privileges to database2 to that user.
CALL procedure_user('%', 'user1', 'database2', 'SELECT, INSERT, UPDATE');

Answer 2

To ensure that the user exists without granting any privileges:

GRANT USAGE ON *.* TO my_user@"%" IDENTIFIED BY 'my_password';

If you really want to do the grants and revokes in one step, you may have to muck with the internal permissions storage table directly:

INSERT INTO `mysql`.`db` (
    `Host`, `Db`, `User`,
    `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`,
    `Create_priv`, `Drop_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`,
    `Create_tmp_table_priv`, `Lock_tables_priv`, `Create_view_priv`, `Show_view_priv`,
    `Create_routine_priv`, `Alter_routine_priv`, `Execute_priv`)
VALUES (
    'my_user', '%', 'my_database',
    'Y', 'N', 'Y', 'N',
    'N', 'N', 'N', 'N', 'N', 'N',
    'N', 'N', 'N', 'N',
    'N', 'N', 'N')
ON DUPLICATE KEY UPDATE
    `Select_priv` = 'Y', `Insert_priv` = 'N', `Update_priv` = 'Y', `Delete_priv` = 'N',
    `Create_priv` = 'N', `Drop_priv` = 'N', `Grant_priv` = 'N', `References_priv` = 'N', `Index_priv` = 'N', `Alter_priv` = 'N',
    `Create_tmp_table_priv` = 'N', `Lock_tables_priv` = 'N', `Create_view_priv` = 'N', `Show_view_priv` = 'N',
    `Create_routine_priv` = 'N', `Alter_routine_priv` = 'N', `Execute_priv` = 'N';

However, that's less portable, requires more permissions, and doesn't create the user account when necessary, so you're probably better off with the three-statement method.

To help with the readability issue, you could create some sort of CSV with accounts and permissions, generating the SQL script from that.