MySQL用戶管理

賬戶管理的重要性

• 在MySQL中可以通過賬戶控制允許或不允許用戶執(zhí)行操作
• 可以精細分配權(quán)限給不同職能的賬戶
• 避免使用root賬戶
  • 將root賬戶改名，應(yīng)用不要用root
• 應(yīng)用不能直接使用root
• 防止維護期出錯
• 限制特定權(quán)限賬戶確保數(shù)據(jù)完整性
• 允許特定授權(quán)賬戶完成期工作
• 阻止未經(jīng)授權(quán)的用戶訪問超出其特權(quán)的數(shù)據(jù)

理解MySQL連接和查詢流程

查詢MySQL賬戶及驗證

• 5.7以前
  • select user,host,password from mysql.user;
• 5.7以后
  • select user,host,authentication_string from mysql.user;
• MySQL將使用以下三項進行驗證
  • 用戶名
  • 密碼
  • 客戶端主機 -> ip，主機名
• 客戶端主機名，注意事項
  • 創(chuàng)建用戶名時指定來源主機名
  • 連接數(shù)據(jù)庫時，也可以使用主機名
  • user(),current_user的區(qū)別
    • user()當前連接的用戶，current_user()是權(quán)限表中定義的用戶

hetan@127.0.0.1 [(none)]>select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| hetan         | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

hetan@127.0.0.1 [(none)]>select user(),current_user();
+-----------------+----------------+
| user()          | current_user() |
+-----------------+----------------+
| hetan@localhost | hetan@%        |
+-----------------+----------------+
1 row in set (0.00 sec)

創(chuàng)建,修改和刪除賬戶

• 創(chuàng)建用戶
  • 用戶名建議8-16字符，密碼16-32個字符
  • create user 用戶名@主機 identified by '密碼';
  • 主機名是精確匹配，例如兩個用戶名：1、hetan@% 2、hetan@192.168.1.100 現(xiàn)在一個用戶從192.168.1.100機器上連接到服務(wù)器匹配的主機名會是第二個
  • %和區(qū)別：%是匹配一串字符，匹配得是一個字符
  • 使用%的危害
    • 在主機名ip段中某一個ip不讓連接，怎么操作
      • create user 'tdev'@'192.168.1.101' identified by 'otherpwd';
  • 風險提示
    • 不創(chuàng)建無用戶名的賬號
    • 不創(chuàng)建沒有密碼的賬號
    • 在可能的情況下，不要使用主機通配符
• 給賬戶設(shè)置密碼
  • create user ... identified by '密碼';
  • grant ... identified by '密碼';
  • set password for 'hetan'@'%' = '密碼';
  • mysqladmin password （不推薦）
  • update 授權(quán)表（不推薦）
  • alter user 'hetan'@'%' identified by '密碼';
  • 確認密碼
  • 查看沒有密碼的用戶
    • select user,host from mysql.user where password='';
    • select user,host from mysql.user where authentication_string='';
  • 讓用戶口令失效，登錄后必須改密碼
    • alter user 'hetan'@'%' password expire;
    • 要對應(yīng)版本的mysqlclient
• 刪除用戶
  • drop user 'hetan'@'%';
    • 直接刪除該用戶
    • 從授權(quán)表中刪除該用戶記錄
    • 刪除用戶時不帶主機名，刪除得是'用戶名'@'%'
  • rename user 'hetan'@'%' to 'hetan1'@'%';
    • 更改賬號的名稱
    • 保留權(quán)限，可以更改：==用戶名和主機部分==

授權(quán),修改和撤銷用戶權(quán)限

• 授權(quán)
  • 用戶連入mysql后
    • 用戶是誰
    • 用戶的權(quán)限是什么
    • 權(quán)限可以操作什么
  • 合理控制授予權(quán)限也是mysql dba的一個重要職責
  • 如果要創(chuàng)建和root一樣的用戶要加上WITH GRANT OPTION
• 用戶權(quán)限
  • 只讀用戶
    • 全局，數(shù)據(jù)庫或表級別權(quán)限，只用select
  • 一般開發(fā)賬號
    • 業(yè)務(wù)庫，權(quán)限：insert，update，delete，select，call
  • 管理用戶
    • 全局級別，權(quán)限：insert，update，delete，create，alter，drop，file，process，shutdown，super
• 權(quán)限展示
  • 注意以下權(quán)限
    • file：允許用戶指示MySQL服務(wù)器在服務(wù)器主機文件系統(tǒng)中讀取和寫入文件
    • MySQL5.7需要配合
    • 在配置文件中加上secure_file_priv=路徑參數(shù)
  • process：允許用戶使用show processlist語句，管理中常用語句
  • super：允許用戶終止其他客戶機連接，或者更改服務(wù)器的運行時配置。執(zhí)行：kill，set，shutdown
  • ALL：授予所有特權(quán)（但不能向其他用戶授予特權(quán)）
  • grant all ... with grant option 授予所有特權(quán)（可以向其他賬戶授予特權(quán)）
• grant語句
  • grant語句用于創(chuàng)建或是給現(xiàn)有的用戶添加權(quán)限
  • grant select on world_innodb.* to 'hetan'@'localhost' indentified by 'zhishutang';
  • 關(guān)鍵點描述
    • 權(quán)限列表，多個權(quán)限可以用逗號分隔如：insert，update（不區(qū)別大小寫）
    • 授權(quán)對象：
      • 所有對象，全局：*.*
      • 數(shù)據(jù)庫級別:<db_name>.*
      • 表級別：<db_name>.<table_name>
    • 要創(chuàng)建或是授權(quán)的用戶：'user_name'@'hostname'
    • 密碼：可選
• 查看用戶權(quán)限
  • show grants;
  • show grants for current_user();
  • show grangs for 'root'@'localhost';
• 查看可以授予哪些權(quán)限
  • show privileges;
• 權(quán)限控制表
  • mysql.user:每個創(chuàng)建的用戶都會有一條記錄
  • mysql.db:限制用戶作用于特定的DB
  • mysql.tables_priv:用于表級別的權(quán)限控制
  • mysql.procs_priv:用于存儲過程和函數(shù)權(quán)限控制
  • MySQL啟動時從mysql庫中把權(quán)限讀取加載到內(nèi)存中；如果通過DML更新權(quán)限表需要借助于：flush privileges;生效
  • ==特別提示：不要對權(quán)限表進行DML操作==
• 撤銷用戶權(quán)限
  • 使用revoke語句撤銷對用戶的權(quán)限
  • revoke delete，insert，update，on world_innodb.* from 'zst'@'%';
  • revoke all privileges,grant option from 'zst'@'%';
  • revoke語法
    • revoke關(guān)鍵字：指定要撤銷的特權(quán)列表
    • on子句：指定要撤銷特權(quán)的級別【全局級時可以不用帶】
    • from子句：指定賬戶名稱

禁用驗證控制

• 場景：密碼忘了，升級過程中
  • --skip-grant-tables，加到配置文件mysqld中
  • 無需用戶和密碼登錄，登錄后禁止使用create user，grant，revoke，set，password
  • 建議配合：禁止提供網(wǎng)絡(luò)驗證使用
  • 禁止提供網(wǎng)絡(luò)驗證使用
    • --skip-networking
  • 還有別的方法沒?
    • 可以將user表復(fù)制到另一個數(shù)據(jù)庫中,在表中復(fù)制添加一個用戶,將密碼改成新密碼,在將user表考回原數(shù)據(jù)庫,再用kill -HUP `pidof mysqld`重新加載文件,就可以用新用戶新密碼登錄了

MySQL常用client命令及GUI工具推薦

• 重要的mysql客戶端程序
  • mysql:將SQL語句發(fā)送到服務(wù)器
  • mysqladmin:用于管理MySQL服務(wù)器,在shell層次交互
  • mysqldump:備份數(shù)據(jù)庫
  • mysqlbinlog:解析mysql的binary log以及重放binary log
  • 不需要知道:mysqlimport 將文件加載到數(shù)據(jù)庫(有點load data的感覺);mysqlsalp MySQL官方自帶的一個簡單的壓力測試工具
• mysql提示符
  • prompt
    • mysql>prompt \u@\h [\d]>
  • 也可以把參數(shù)寫到mysql可以讀到的配置文件中
    • /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
    • 忘記了可以用這個命令看mysql --help|grep my.cnf
• mysql安全更新
  • --safe-update
    • 阻止mysql客戶端使用沒有帶where條件的語句
    • update和delete僅在包含where子句(該子句通過鍵值對明確標識了要更新或刪除的記錄)或limit子句時才允許使用
    • 將單表select語句中的輸出限制為不超過1000行,但語句包含limit子句時除外
    • 僅當MySQL為處理查詢所檢查的行不超過1000000時,才允許使用多表select語句
• mysqladmin
  • DBA的命令行客戶機
  • 多項功能
    • "強制回應(yīng)(ping)"服務(wù)器
    • 關(guān)閉服務(wù)器
    • 創(chuàng)建和刪除數(shù)據(jù)庫
    • 顯示服務(wù)器和版本信息
    • 顯示或重置服務(wù)器狀態(tài)變量
    • 設(shè)置口令
    • 重新裝入授權(quán)表
    • 刷新日志文件和高速緩存
    • 啟動和停止復(fù)制
    • 顯示客戶機信息

實戰(zhàn)部分

用戶名忘了怎么處理?

實際工作中用戶名和密碼這塊需要注意什么?

• 推薦做法
  • 應(yīng)用程序和用戶名密碼分開存儲
  • 用戶名長度8-16個字符
  • 密碼長度推薦:16-32個字符,復(fù)雜密碼策略
  • 可能的情況下實現(xiàn)各組配置加密
  • 每個業(yè)務(wù)系統(tǒng),只能看自己的配置
  • 訪問別人的配置只能通過API調(diào)用