1

I have 30 plus MySQL users their permissions vary from one another.

I want to GRANT SELECT privilege to all of the 30 users. Is there any one command that I can use that can grant select to all 30 users in one shot!

2 Answers 2

0

Try this:

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| eroom            | localhost |
| musr             | localhost |
| musr             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

mysql> select concat('GRANT SELECT ON *.* to "',user,'"@"',host,'";') into outfile '/tmp/select_script.sql' FROM mysql.user;
Query OK, 4 rows affected (0.00 sec)

mysql> source /tmp/select_script.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
2
  • 1
    Would something like 'GRANT SELECT ON *.My_Database...." be better - i.e. specify the database, rather than granting SELECT on everything?
    – Vérace
    Aug 8, 2015 at 1:13
  • Always. Only providing the minimal and most granular is erring on the side of caution and security best practice.
    – eroomydna
    Aug 8, 2015 at 7:07
0

I want to GRANT SELECT privilege to all of the 30 users. Is there any one command that I can use that can grant select to all 30 users in one shot!

Yes.

grant select on *.* to user_group ; 

... where every user who should have this access is a member of this group.

Working with lots of individual users opens up the risk of getting into exactly the situation you're currently in - inconsistency. Use groups/roles to make your security landscape consistent, safer and easier to manage.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.