MySQL權限管理

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

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;

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

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

+--------------------------------------------------------------------------------------------------+
| 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                                     |
+--------------------------------------------------------------------------------------------------+

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

+----------------------------------------------------------------+
| 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/