mysql - 使用命令行创建mysql用户

Question

我在mysql命令行中用这个命令创建了一个用户

GRANT ALL PRIVILEGES ON `db_name.*` to 'db_name_admin'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

我用

连接到数据库

mysql -u db_name_admin -p -h localhost

检查我是否连接

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_name            |
+--------------------+
2 rows in set (0.00 sec)

然后我更改为使用正确的数据库

use db_name

但是当我这样做的时候

show tables

我明白了

empty set (0.00 sec)

当我使用 root 帐户登录时，我可以看到 db_name 中的所有表和记录。所以我猜我的 GRANT 查询有问题，但我不知道是什么。

当我检查我看到的 TABLE_PRIVILEGES 时

+-----------------------------+---------------+--------------+--------------+----------------+--------------+
| GRANTEE                     | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME   | PRIVILEGE_TYPE | IS_GRANTABLE |
+-----------------------------+---------------+--------------+--------------+----------------+--------------+
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | SELECT         | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | INSERT         | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | UPDATE         | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | DELETE         | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | CREATE         | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | DROP           | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | REFERENCES     | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | INDEX          | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | ALTER          | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | CREATE VIEW    | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | SHOW VIEW      | YES          |
| 'db_name_admin'@'localhost' | def           | db_name      | db_name.*    | TRIGGER        | YES          |
+-----------------------------+---------------+--------------+--------------+----------------+--------------+

我的数据库名称使用 - 符号命令应该是

GRANT ALL PRIVILEGES ON `db-name`.* to 'db_name_admin'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

Answer 1

执行:GRANT ALL PRIVILEGES ON db_name.* ... 不带反引号

反引号“卡住”您在此处键入的内容，因此您仅获得名称为“db_name.*”的表的权限