主页 / dba / 问题 / 98949
Accepted
Ahmad Abuhasna
Asked: 2015-04-28 04:54:04 +0800 CST

对除一个 mysql 之外的所有数据库授予选择权限

  • 772

我在 MS Windows 2008R2 下test_user创建了一个名为的用户MySQL 5.5.17，我想授予该用户对除 MySQL 数据库之外的所有数据库的选择权限，注意我在这个实例中有大约 200 个数据库。

编辑：

在此处输入图像描述

编辑2：

在此处输入图像描述

mysql mysql-5.5

3 个回答

  1. Best Answer

    运行以下查询的输出：

    SELECT CONCAT("GRANT SELECT ON ",SCHEMA_NAME,".* TO 'test_user'@'localhost';")
FROM information_schema.SCHEMATA 
WHERE SCHEMA_NAME NOT LIKE 'mysql';
    • 9

  2. 由于您有 200 个数据库并且您不想一一授予。最快的方法是

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

    然后只需撤销 mysql db 中的权限

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

    但是当我在 mysql.* 上选择性地 GRANT 然后 REVOKE 时。然后它正在工作

    在此处输入图像描述

    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'
    • 5

  3. 多年后，在 MariaDB 中。我所做的：

    • 授予对所有数据库的访问权限
    • 在 mysql 上仅授予“LOCK TABLES”。*

    用户可以看到 mysql，但看不到表。锁定表不起作用，因为 te 用户无法访问任何表。

    • 0

相关问题