MySQL Privileges 快速權限設定

MySQL系統資料表
1.mysql.user 登入帳戶與全域設定權限
2.mysql.tables_priv 資料表權限

# 查詢現有的MySQL帳戶
select host,user from mysql.user;

# 新增帳戶指定來源IP,並給予資料表所有權限
grant all privileges on Database.TableName to 'NewUser'@'192.168.1.1' Identified by 'Password';

# 新增帳戶不指定來源IP,並給予資料表所有權限,任何IP都可以連線到這台MySQL
grant all privileges on Database.TableName to 'NewUser'@'%' Identified by 'Password';

# 已經有record在mysql.user裡則不用在指定密碼,直接給予權限
grant select, insert,update,delete on Database.TableName to 'NewUser'@'%';

# 移除權限,此命令無法刪除帳戶,欲刪除帳戶必須使用delete
revoke delete on Database.TableName from 'NewUser'@'%'

# 移除帳戶
delete from mysql.user where user='NewUser'@'%'

# 更新系統的權限設定,修改或新增任務權限設定都必須要下一次flush privileges才能讓設定生效
flush privileges;