MySQL權限管理

在連docker container ( IP: 172.17.0.2 )的mysql時發現抓不到newdatabase的資料
mysql.php：

?

1 2 3 4 5 6 7 8 9 10

mysql_connect("172.17.0.2", "root", "password"); mysql_select_db("newdatabase");   $result = mysql_query("SELECT database();");   while ($row = mysql_fetch_array($result,  MYSQL_ASSOC)) {     echo $row['database()']; }   mysql_free_result($result);

結果：
$ php mysql.php
(空)

連進去檢查( 注意，下面每次更改權限後，必須重新登錄才有效 )
$ mysql -u root -p -h 172.17.0.2
然後發現找不到table ( 但是在172.17.0.2上直接連正常 )
mysql> show databases;

?

1 2 3 4 5

+--------------------+ | Database           | +--------------------+ | information_schema | +--------------------+

檢查權限
mysql> SHOW GRANTS FOR 'root'@'localhost';

?

1 2 3 4 5 6 7

+--------------------------------------------------------------------------------------------------+ | Grants for root@localhost                                                                        | +--------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'xxx' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                     | +--------------------------------------------------------------------------------------------------+<span style="font-family: Times New Roman;"><span style="white-space: normal;"> </span></span>

mysql> SHOW GRANTS FOR 'root'@'%';

?

1 2 3 4 5

+----------------------------------------------------------------+ | Grants for root@%                                              | +----------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*xxx' | +----------------------------------------------------------------+

原來是newdatabase表沒開放權限給其他ip

開放(GRANT)權限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' ;
這樣Grants for root@% 就會變成：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*xxx'

亦可以通過特定的權限
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'root'@'%';

移除權限
mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'%' ;
這樣Grants for root@% 就會變成：
GRANT USAGE ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*xxx'

秀出系統現在有哪些 user
mysql> SELECT User,Host FROM mysql.user;

參考資料：
http://blog.longwin.com.tw/2009/06/query-mysql-show-grant-permission-2009/