2

I have user called test_user created under MySQL 5.5.17 under MS Windows 2008R2, I want to grant this user select privileges on all databases except MySQL database, note I have around 200 database inside this instance.

EDIT:

enter image description here

EDIT2:

enter image description here

6

Run the output of the following query:

SELECT CONCAT("GRANT SELECT ON ",SCHEMA_NAME,".* TO 'test_user'@'localhost';")
FROM information_schema.SCHEMATA 
WHERE SCHEMA_NAME NOT LIKE 'mysql';
  • thanks I did not think about this solution (upvote) – Ahmad Abuhasna Apr 27 '15 at 16:47
3

Since you have 200 databases and you do not want to grant one by one. Fastest way to do that would be

GRANT SELECT ON *.* TO 'test_user'@'localhost';
FLUSH PRIVILEGES;

And then just revoke the privilege in mysql db

REVOKE SELECT ON mysql.* FROM 'test_user'@'localhost' ;
FLUSH PRIVILEGES;

But when I selectively GRANT and then REVOKE on mysql.* . Then it is working

enter image description here

user@ubuntu:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 150
Server version: 5.5.41-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER 'mysqlrockstar'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'mysqlrockstar'@'localhost';
+---------------------------------------------------+
| Grants for mysqlrockstar@localhost                |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO 'mysqlrockstar'@'localhost' |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> GRANT SELECT ON mysql.* TO 'mysqlrockstar'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye
user@ubuntu:~$ mysql -u mysqlrockstar
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 151
Server version: 5.5.41-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.06 sec)

mysql> exit;
Bye
user@ubuntu:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 152
Server version: 5.5.41-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> REVOKE SELECT  ON mysql.* FROM 'mysqlrockstar'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye
user@ubuntu:~$ mysql -u mysqlrockstar
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 153
Server version: 5.5.41-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> use mysql;
ERROR 1044 (42000): Access denied for user 'mysqlrockstar'@'localhost' to database 'mysql'
  • I tried this but I found this error message as in the screenshot in the edit section. – Ahmad Abuhasna Apr 27 '15 at 13:35
  • Check SHOW GRANTS FOR 'test_user'@'aabuhasna-pc' ; – Infra DBA Apr 27 '15 at 13:40
  • I added the result of the command in edit2 section – Ahmad Abuhasna Apr 27 '15 at 13:54
  • On doing GRANT and then REVOKE explicitly for mysql.* It is working – Infra DBA Apr 27 '15 at 14:12
  • 2
    Not good enough -- That only removes the entry from mysql.db. The entry in mysql.user still lets the user have access. (Downvote) – Rick James Apr 27 '15 at 14:22
0

Years later and in MariaDB. What I do:

  • grant access to all databases
  • grant only 'LOCK TABLES' on mysql.*

User can see mysql, but no tables. Lock table does not work, because te user cannot access any tables.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

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