向 MySQL 中的用户（远程 root 登录）授予权限

Grant Privileges to an user (remotely root logged) in MySQL

提问by

I have this:

我有这个：

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@%         |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT USER();
+------------------+
| USER()           |
+------------------+
| root@CQ2404LA-PC |
+------------------+
1 row in set (0.00 sec)

mysql>
mysql> GRANT ALL PRIVILEGES ON `Company`.* TO 'TheUser'@'%' IDENTIFIED BY PASS
WORD '*3814FFAFF303C7DBB5511684314B57577D754FF9';
ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'Company'

Access denied for user 'root'@'%' to database 'Company'

用户 'root'@'%' 对数据库 'Company' 的访问被拒绝

Now reviewing the root privileges I have:

现在查看我拥有的 root 权限：

mysql> show grants for 'root'@'localhost';
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| Grants for root@localhost
                                                         |
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*158
FB31F24156B52B2408974EF221C5100001544' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
                                                         |
+-------------------------------------------------------------------------------
---------------------------------------------------------+
2 rows in set (0.00 sec)

Before, I tested (Locally) And Works fine!.

之前，我测试过（本地）并且工作正常！。

Now Remotely Privileges:

现在远程特权：

mysql> show grants for 'root'@'%';
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------------------------------+
| Grants for root@%



                                                                           |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS,
 FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES,
 LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW
VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER ON *.* TO 'root'@'%' IDENTIFIED
 BY PASSWORD '*158FB31F24156B52B2408974EF221C5100001544' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'%' WITH GRANT OPTION

Doesn't work!!!, I think that it must work because: "ON *.* TO 'root'@'%'"

不起作用！！！，我认为它必须起作用，因为：“ ON *.* TO 'root'@'%'”

Looking for the difference:'root'@'%' haven't CREATE TABLESPACE, EVENT and TRIGGER

寻找差异：'root'@'%' 没有创建表空间、事件和触发器

mysql> SELECT Host,     Event_priv,     Trigger_priv,   Create_tablespace_priv,
authentication_string   FROM mysql.user WHERE USER = "root";
+-----------+------------+--------------+------------------------+--------------
---------+
| Host      | Event_priv | Trigger_priv | Create_tablespace_priv | authenticatio
n_string |
+-----------+------------+--------------+------------------------+--------------
---------+
| localhost | Y          | Y            | Y                      |
         |
| %         | N          | N            | N                      | NULL
         |
+-----------+------------+--------------+------------------------+--------------
---------+
2 rows in set (0.01 sec)

mysql>

But, I think that is not root of problem...

但是，我认为这不是问题的根源......

Maybe The solution will be, to use: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%', but I think "all privileges" have other thing than "an amount of privileges".

也许解决方案是，使用：GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'，但我认为“所有特权”除了“一定数量的特权”之外还有其他东西。

回答by Michael - sqlbot

To use?GRANT, you must have the?GRANT OPTION?privilege, and you must have the privileges that you are granting.

— http://dev.mysql.com/doc/refman/5.6/en/grant.html

要使用？GRANT，你一定有吗？GRANT OPTION?privilege，并且您必须拥有您授予的特权。

— http://dev.mysql.com/doc/refman/5.6/en/grant.html

If you don't hold "all privileges," you can't grant "all privileges."

如果您不拥有“所有权限”，则无法授予“所有权限”。

Fix the root@% user's missing privileges and the problem will be resolved, although you really should understand what each privilege does and only grant the appropriate ones to each user.

修复 root@% 用户缺少的权限，问题将得到解决，尽管您确实应该了解每个权限的作用，并且只将适当的权限授予每个用户。