## 通過grant [a?'d?nt?fa?d] 命令創建用戶并授權 * grant命令簡單語法 ``` grant all privileges on dbname.* to username@localhost identified by ‘password’; # 創建用戶并授權 ``` 列表說明參數: | grant | all privileges | on dbname.* | to username@localhost | identified by ‘password’ | | -- | -- | -- | -- | -- | | 授權命令 | 對應權限 | 目標:庫和表 | 用戶名和客戶端主機 | 用戶密碼 | > 說明:上述命令是授權localhost主機上通過username管理dbname數據庫的所有權限,密碼為password。其中username，dbname，password根據業務情況自行修改。 * create和grant配合方法創建用戶并授權 ``` CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password'; # 創建用戶 grant all privileges on test.* to 'jeffrey'@'localhost'; # 給用戶授權 show grants for 'jeffrey'@'localhost'; # 查看用戶權限明細 ``` * 授權局域網內主機遠程連接數據庫: 根據grant命令語法，我們知道'jeffrey'@'localhost'位置為授權訪問數據庫的主機,localhost可以使用域名,IP地址或者IP段來替代,因此，要授權局域網內主機可以通過如下方法實現: ``` grant all on *.* to username@'192.168.1.%' identified by 'password'; # 百分號匹配 grant all on *.* to username@'192.168.1.0/255.255.255.0' identified by 'password'; # 子網掩碼配置 ``` * 刪除MySQL用戶 ``` DROP USER 'jeffrey'@'localhost'; ``` ## MySQL用戶可以授權的權限有哪些? ``` insert, select, update, delete, create, drop, references, index, alter, create temporary tables, lock tables, execute, create view, show view, create routine, alter routine, event, trigger ``` ## 創建一個數據庫備份用戶權限 ``` grant SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, REPLICATION CLIENT, EVENT,TRIGGER ON *.* TO 'back_user'@'Host' IDENTIFIED BY 'Password'; flush privileges; ``` 權限列表 1. SELECT 讀取 2. SHOW DATABASES 允許訪問完整的數據庫列表 4. LOCK TABLES 允許鎖定表 5. RELOAD 允許載入和刷新服務器緩存 6. REPLICATION CLIENT 允許用戶詢問從屬服務器或主服務器的地址 7. EVENT 允許執行事務 8. TRIGGER 觸發器相關的權限