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

Please explain what you want in details. To grant one more privilege (UPDATE)? Or just create new user and grant some privileges? ... — Devart, Oct 07 '11 at 14:53
I want to _set_ (in contrast to _add_, which is what grant does) the privileges for user A (the user might already exist and have privileges in other databases, which must not be touched, so drop user is not allowed) in database B with preferably a single statement. — michael667, Oct 07 '11 at 19:24
Grant and Revoke work fine. You make the statement that you don't believe that Revoke works right, yet it does work when you revoke all privileges for that user? — gview, Oct 14 '11 at 07:37
Revoke works right, but only if the user already exists. That's why I have to call the first `grant`, in case it does not exist yet. What I would prefer: `revoke if exists`, but it seems MySQL cannot do that. — michael667, Oct 14 '11 at 07:46

Devart · Accepted Answer · 2011-10-14T06:16:59.450

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

You could do `SELECT 1 INTO @exist FROM mysql.user ...` instead of `SELECT COUNT(*) INTO @exist FROM mysql.user ...` to avoid the unnecessary count() call. — breiti, Oct 13 '11 at 17:36
I didn't know about server-side prepared statements in MySQL yet. This seems to be a good idea for my _problem_. Instead of your `select`/`create user`/`grant`, however, I will just use my `grant`/`revoke`/`grant` approach. — michael667, Oct 14 '11 at 07:55

score 3 · Answer 2 · answered Oct 07 '11 at 18:33

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.

Thank you for your suggestions. While the `insert` method requires only one step, it is much more verbose than my existing approach -- I already thought about generating my three-statement method from some other, but rejected it for being overkill for my needs. — michael667, Oct 11 '11 at 11:42

palacsint · Answer 3 · 2011-10-14T10:23:09.837

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 want a single solution that works also when the user currently has _more_ privileges in `my_database` than he should have after the changes. So your fixture must be `GRANT SELECT, UPDATE ON 'my_database'.* TO 'my_user'@'%'`, and now the user should have _only_ `select` privileges — michael667, Oct 14 '11 at 08:18

score 0 · Answer 4 · answered Oct 07 '11 at 13:33

0

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

answered Oct 07 '11 at 13:33

palacsint

28,416
10
82
109

Yes, at the end of my grant/revoke blocks. The script is working perfectly, but it is so _verbose_ to achieve something so trivial. – michael667 Oct 07 '11 at 13:40
FLUSH PRIVILEGES in this case has no sence. – Devart Oct 07 '11 at 14:08
1

Running `GRANT` or `REVOKE` automatically calls `FLUSH PRIVILEGES`. It is only necessary when you make `INSERT`, `UPDATE`, or `DELETE` queries directly on the `mysql` tables. – desertwebdesigns Oct 07 '11 at 23:19
You are right, thanks guys for pointing that out. It's in the documentation in case of somebody needs the link: http://dev.mysql.com/doc/refman/5.1/en/adding-users.html – palacsint Oct 10 '11 at 17:08

How to write good Mysql grants scripts

4 Answers4

Case #1, the user does not exist:

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

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