All geek questions in one place

How to Write Good Mysql Grant Scripts

I use scripts to create Mysql databases and tables. These scripts contain permission sections, as shown below:

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 the Q privilege from the user and re-run this script, the user still had this privilege in the database. So I added a review line to the grant line.

Then I ran into the following problem: whenever I ran the script in a “new” Mysql installation, the cancellation failed because the user did not exist yet. So before the review, I added a grant to 'dummy'.

Question: Is there a better way to achieve this? My “real” scripts contain many users and many databases and are difficult to read because I need three lines for each set of privileges that I want to assign. I would like to use only one line.

Edit (based on feedback from responses 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 can

  • already exists (but may be new)
  • there are currently privileges extending those that I want him to have
  • have privileges in other databases that should remain unaffected.
+6
source share
4 answers

You can use the procedure to create a new user, if necessary, and provide privileges for the database. I used prepared statements and GRANT statements. The prepared statements in MySQL 5.5 support GRANT, if you are using a lower version, then you can rewrite the 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');
+5
source

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 make grants and revoke in one step, you may need to directly access the internal permissions storage table:

 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, which is less portable, it requires more permissions and does not create a user account when necessary, so you will probably be better off using a three-task method.

To help with the readability problem, you can create some kind of CSV with accounts and permissions by creating an SQL script from this.

+3
source

Sorry for the long answer, which is actually a comment, but I do not understand it. The third-line GRANT command works well for me. Here are two cases that should work. It would be great if you could post some test commands that reproduce the error. At least I could learn from this :)

Case No. 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 SELECT permission.

Case No. 2, the user exists and has the right to 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 test equipment, and now we give the user a new UPDATE permission:

 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 did not change to other_database , and he received a new UPDATE permission on my_database and in the previous SELECT .

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

Unfortunately, with current versions of MySQL this cannot be done with just one command. GRANT does not have a REMOVE EXISTING .

I think the best solution is @eswald GRANT USAGE ON ... , but there are 3 more teams. Another solution is

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

but he needs FLUSH PRIVILEGES , so he also has 3 teams.

A workaround could be a bash script that generates three commands that are in 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"

He 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 changed the first GRANT SELECT to USAGE .)

+3
source

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

0
source

Source: https://habr.com/ru/post/898668/

More articles:


All Articles