我有这个:
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'
用户'root'@'%'被拒绝访问数据库'Company'
现在,我正在审查我所拥有的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)
之前,我进行了本地测试,结果良好!
现在需要远程特权:
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
不起作用!!! 我认为它必须起作用,因为:"ON *.* TO 'root'@'%'"
寻找区别: 'root'@'%'没有CREATE TABLESPACE、EVENT和TRIGGER
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>
但是,我认为这并不是问题的根源...
也许解决方案是使用:GRANT ALL PRIVILEGES ON *.* TO 'root'@'%',但我认为“所有权限”不仅仅代表“一定数量的权限”。