MySQL使用者權限設定

MySQL使用者權限資訊用user、db、host、tables_priv和columns_priv表被儲存在mysql資料庫中。

關於使用者權限的相關指令，說明如下

(1)使用 root 進入 MySQL
mysql> mysql -u root -p

(2)遠端登入
mysql> mysql -u root -h remote_host_ip -p
remote_host_ip 指你要登入的遠端MySQL

(3)修改使用者密碼

mysql> SET PASSWORD FOR '目標使用者'@'主機' = PASSWORD('密碼');
mysql> flush privileges;

(4)建立使用者，並給予權限

grant usage on *.* to 'username'@'localhost' identified by 'yourpassword' with grant option; 
grant all privileges on *.* to 'username'@'localhost' identified by 'yourpassword';
flush privileges;

INSERT INTO user(host,user,password) VALUES('%','username',password('userpassword'));
GRANT ALL ON *.* TO 'username'@localhost IDENTIFIED BY 'userpassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE ON dbname.* TO 'username'@localhost IDENTIFIED BY 'userpassword';

(5)刪除mysql的使用者
mysql>delete from mysql.user where user='username' and host='localhost';
mysql>flush privileges;

OR
mysql>DROP USER user@ip_address;

(6)查詢 User 的權限
# 秀出系統現在有哪些使用者
SELECT User,Host FROM mysql.user;
# 下述這些結果都一樣, 都是列出目前使用者的權限.
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

(7)移除 MySQL 帳號權限
revoke all privileges on *.* from 'username'@'localhost';
flush privileges;

範例

#給帳號username'@'localhost對所有資料庫擁有SELECT,INSERT,UPDATE,DELETE的權限
grant SELECT,INSERT,UPDATE,DELETE ON `db`.* TO 'username'@'localhost' IDENTIFIED BY 'password';

#給帳號username'@'localhost對某些資料庫擁有SELECT的權限
grant SELECT ON `dbname`.* TO 'username'@'localhost' IDENTIFIED BY 'password';

#給帳號username'@'localhost對所有資料庫擁有SELECT的權限
grant SELECT ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
flush privileges;

#修改權限~先刪除再給予
SHOW grants for 'username'@'localhost'; #先複製該密碼
revoke all privileges on *.* from 'usernamep'@'localhost';
GRANT SELECT,LOCK TABLES ON *.* TO 'usernamep'@'localhost' IDENTIFIED BY PASSWORD 'password'; #貼上密碼
flush privileges;