How to write good Mysql grants scripts

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 lo开发者_JAVA技巧ts 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

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');

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.

Sorry for the long answer which actually a comment but I don't get it. Your "third line" GRANT command works well for me. Here is the two cases which should work. It would be great if you could post some test commands which reproduce the bug. At least I could learn from it :)

Case #1, the user does not exist:

mysql> SHOW GRANTS FOR my_user@"%";
ERROR 1141 (42000): There is no such grant defined for user 'my_user' on host '%'

OK, the user does not exist.

mysql> create database my_database;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT SELECT ON my_database.* TO my_user@"%" IDENTIFIED BY 'my_password';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR my_user@"%";
+-----------------------------------------------------------------------+
| Grants for my_user@%                                                  |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'my_user'@'%' IDENTIFIED BY PASSWORD '*CC...18' | 
| GRANT SELECT ON `my_database`.* TO 'my_user'@'%'                      | 
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)

OK, he has the SELECT permission.

Case #2, the user exists and has right on other_database and my_database too:

mysql> SHOW GRANTS FOR my_user@"%";
ERROR 1141 (42000): There is no such grant defined for user 'my_user' on host '%'

OK, the user does not exist.

mysql> create database my_database;
Query OK, 1 row affected (0.00 sec)

mysql> create database other_database;
Query OK, 1 row affected (0.01 sec)

mysql> GRANT SELECT ON my_database.* TO my_user@"%" IDENTIFIED BY 'my_password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON other_database.* TO my_user@"%" IDENTIFIED BY 'my_password';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR my_user@"%";
+-----------------------------------------------------------------------+
| Grants for my_user@%                                                  |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'my_user'@'%' IDENTIFIED BY PASSWORD '*CC...18' | 
| GRANT SELECT ON `other_database`.* TO 'my_user'@'%'                   | 
| GRANT SELECT ON `my_database`.* TO 'my_user'@'%'                      | 
+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)

The above is the test fixture and now we grant a new UPDATE permission to the user:

mysql> GRANT UPDATE ON my_database.* TO my_user@"%" IDENTIFIED BY 'my_password';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR my_user@"%";
+-----------------------------------------------------------------------+
| Grants for my_user@%                                                  |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'my_user'@'%' IDENTIFIED BY PASSWORD '*CC...18' | 
| GRANT SELECT ON `other_database`.* TO 'my_user'@'%'                   | 
| GRANT SELECT, UPDATE ON `my_database`.* TO 'my_user'@'%'              | 
+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)

His permission haven't changed on the other_database and he got the new UPDATE permission on my_database and the former SELECT as well.

Based on the comments it should be only UPDATE without SELECT.

Unfortunately with the current MySQL versions it's not possible to do that with only one command. GRANT does not have REMOVE EXISTING clause.

I think the best solution is @eswald's GRANT USAGE ON ... but it still 3 commands. Another solution is a

DELETE FROM mysql.db WHERE user = 'my_user' AND host ='%' AND db = 'my_database'

but it needs a FLUSH PRIVILEGES so it's also 3 commands.

A workaround could be a bash script which generates the three commands which is is in the question:

#!/bin/bash

function grant {
    USER=$1
    PASSWORD=$2
    DB=$3
    PERMISSIONS=$4

    echo "GRANT USAGE ON $DB TO $USER IDENTIFIED BY '$PASSWORD';"
    echo "REVOKE ALL PRIVILEGES ON $DB FROM $USER;"
    echo "GRANT $PERMISSIONS ON $DB TO $USER IDENTIFIED BY '$PASSWORD';"
}

grant "my_user@'%'" "my_password" "my_database.*" "SELECT, UPDATE"

It prints:

GRANT USAGE 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';

(I've changed the first GRANT SELECT to USAGE.)

I'm just wondering, have you run FLUSH PRIVILEGES?