權限生效小知識

MySQL的授權用戶由兩部分組成：'用戶名'@'主機名/IP/IP段'

注意：包含特殊字符則必須加上引號

• 執(zhí)行grant,revoke,setpassword,renameuser命令修改權限之后线罕，MySQL會自動將修改后的權限信息同步加載到系統(tǒng)內存中止潮；執(zhí)行insert/update/delete系統(tǒng)權限表之后，須執(zhí)行刷新權限命令才能同步系統(tǒng)內存中：flush privileges/mysqladmin flush-privileges/mysqladmin reload
• 修改tables和columns級別權限钞楼，客戶端下次操作新權限就會生效
• 修改database級別權限喇闸，新權限在客戶端執(zhí)行use database命令后生效
• 修改global級別權限，需要重新創(chuàng)建連接新權限才能生效
• --skip-grant-tables 可以跳過所有系統(tǒng)權限表允許所有用戶登錄，只在特殊情況下暫時使用

連接MySQL常見報錯問題

• 網(wǎng)絡問題

can not connect to mysql server on 'xxxxx'
#檢查防火墻是否限制了MySQL的端口
#檢查MySQL服務是否關閉
#檢查selinux 是否強制

• 權限表中有賬號沒地址

host 'xxxxx'  is not allowed to connect to this mysql server

• 權限表中沒有賬號或密碼錯誤

Access denied for user 'dba'@'xx.xx.xx.x'

• 權限表中沒有具體權限

'DROP' command denied to user 'dba'@'localhost' for table 'xxx';
Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

授權原則

• 生產系統(tǒng)中MySQL用戶創(chuàng)建由DBA統(tǒng)一協(xié)調按需創(chuàng)建燃乍，DBA通常直接使用root用戶來管理數(shù)據(jù)庫唆樊；
• 通常會創(chuàng)建指定業(yè)務數(shù)據(jù)庫上的增刪改查、臨時表刻蟹、執(zhí)行存儲過程的權限給應用程序來連接數(shù)據(jù)庫逗旁；
• 通常會創(chuàng)建指定業(yè)務數(shù)據(jù)庫上的只讀權限給特定應用程序或某些高級別人員來查詢數(shù)據(jù)，防止數(shù)據(jù)被修改舆瘪；
• 修改mysql的端口片效，通過防火墻限制訪問mysql端口的ip
• root賬號設置強密碼并指定只容許本地登錄，其他用戶設置強密碼并嚴格指定對應賬號的訪問ip
• 如果需要記錄登錄信息英古，可以開啟查詢日志淀衣，查詢日志會記錄登錄和查詢語句
• mysql進程運行賬號 :

linux : 新建mysql賬號，安裝時指定以mysql賬戶運行召调，給程序所在目錄讀取權限膨桥，data所在目錄讀取和寫入權限；
windows : 禁止使用local system運行mysql賬戶唠叛，新建帳號給mysql程序所在目錄讀取權限和data目錄讀取和寫入權限

• 用戶授權權限最小原則 : 單數(shù)據(jù)庫授權只嚣，權限夠用即可，單數(shù)據(jù)庫賬戶不能擁有super/process/file等全局權限玻墅，可避免安全隱患
• 刪除無用數(shù)據(jù)庫 : test數(shù)據(jù)庫對新建的賬戶默認有權限

常用操作需要的權限舉例

-- 執(zhí)行show processlist
    只有連接權限時环础，只能查看自己的連接
    具有process權限時，可以看到所有連接
-- 查看information_schema庫下表
    process (innodb相關表需要process權限)  
-- mysql邏輯備份mysqldump所需權限
    select+lock tables        
    【--single-transaction 參數(shù)下不需要鎖表剩拢，所以不需要lock tables權限】
    【要備份觸發(fā)器存儲過程等线得，需要添加TRIGGER、EVENT徐伐、EXECUTE 等權限】
    【MySQL版本5.7測試】
        賬號權限：只有l(wèi)ock tables 
        備份語句 mysqldump -u -p -A> 
        備份結果：文件內容只有創(chuàng)建數(shù)據(jù)庫語句
-- mysql邏輯恢復最小權限
    GRANT SELECT, INSERT, CREATE, DROP, ALTER ON *.*
    如果有外鍵關聯(lián)：REFERENCES
    如果有觸發(fā)器或者存儲過程:??待測試   
-- rename
    INSERT, CREATE, DROP, ALTER
-- truncat
清空表需要有drop權限（授權后需要重新使用表或重新登錄）
    
-- usage  
可以執(zhí)行show databases看到information_schema的所有表結構贯钩，能看到除innodb表之外的表數(shù)據(jù)；
執(zhí)行select sleep(10);等內置函數(shù)办素；
show processlist 查看該用戶所有會話連接角雷；
可以執(zhí)行kill命令 殺掉該用戶所有連接；

-- select *.*  
可以執(zhí)行show databases看到所有庫表,查看所有庫表結構和數(shù)據(jù)

-- references   
字段級   用戶可以將其它表的一個字段作為某一個表的外鍵約束

用戶創(chuàng)建和刪除舉例

--創(chuàng)建用戶
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin';
CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'obscure';
CREATE USER 'custom'@'%.example.com' IDENTIFIED BY 'obscure'; 
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON expenses.* TO 'custom'@'host47.exa.com';       
-- 5.7之前可以使用grant直接創(chuàng)建賬戶
-- 用戶重命名
RENAME USER mhh@localhost TO miao@localhost;
-- 刪除用戶
DROP USER test@localhost;
--如果用戶已存在：ERROR 1396 (HY000): Operation CREATE USER failed for 'dba'@'localhost'

授權和回收舉例

-- 列授權
GRANT select(id, num, class) ON testdb.log TO dba@localhost;
-- 存儲過程授權
GRANT execute ON procedure testdb.pr_add TO 'dba'@'localhost';
-- 函數(shù)授權
GRANT execute ON function testdb.fn_add TO 'dba'@'localhost';
-- 加期限授權
ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE INTERVAL 30 DAY; 密碼30天后過期
ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE INEVER;  密碼不過期
ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE DEFAULT; 默認過期策略
-- 設置MySQL用戶密碼過期策略【系統(tǒng)參數(shù)性穿，作用于所有用戶】
default_password_lifetime=180 設置180天過期
default_password_lifetime=0 設置密碼不過期
--手動強制某個用戶密碼過期
  ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE;
  SELECT 1;
  ERROR 1820 (HY000): You must SET PASSWORD before executing this statement 
  ALTER USER USER() IDENTIFIED BY 'new_password';
  Query OK, 0 rows affected (0.01 sec)
  SELECT 1;
  |1|

-- 回收權限
REVOKE drop ON test.* FROM test@localhost;

用戶資源限制

-- 通過執(zhí)行create user/alter user設置/修改用戶的資源限制
CREATE USER 'username'@'localhost' IDENTIFIED BY 'xxxxx' WITH MAX_QUERIES_PER_HOUR 20 MAX_UPDATES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 5 MAX_USER_CONNECTIONS 2;
ALTER USER 'username'@'localhost' WITH MAX_USER_CONNECTIONS 10;
? MAX_QUERIES_PER_HOUR：一個用戶在一個小時內可以執(zhí)行查詢的次數(shù)(基本包含所有語句)
? MAX_UPDATES_PER_HOUR：一個用戶在一個小時內可以執(zhí)行修改的次數(shù)(僅包含修改數(shù)據(jù)庫或表的語句)
? MAX_CONNECTIONS_PER_HOUR：一個用戶在一個小時內可以連接MySQL的時間
? MAX_USER_CONNECTIONS：一個用戶可以在同一時間連接MySQL實例的數(shù)量
-- 取消某項資源限制既是把原先的值修改成0
ALTER USER 'username'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 0;
-- 當針對某個用戶的max_user_connections非0時勺三，則忽略全局系統(tǒng)參數(shù) max_user_connections，反之則全局系統(tǒng)參數(shù)生效

修改root密碼

--V5.6 
update mysql.user set password=password('xxxx') where user='root' and host = 'localhost';flush privileges;
--V5.7 
update mysql.user set authentication_string=password('pwd ') where user='root' and host = 'localhost';flush privileges;
--V5.8 
alter user 'root'@'localhost' identified by '';
ALTER USER 'dba'@'localhost' IDENTIFIED BY 'dba140';--需要alter權限
SET PASSWORD FOR 'dba'@'localhost' = PASSWORD('xxx');--不需要具體權限
GRANT USAGE ON *.* TO 'dba'@'localhost' IDENTIFIED BY 'xxx'; --需要grant權限
mysqladmin -u user_name -h host_name password "new_password";
--修改本身用戶密碼方式:
  ALTER USER USER() IDENTIFIED BY 'mypass'; 
  SET PASSWORD = PASSWORD('mypass');

MySQL用戶lock

--通過執(zhí)行create user/alter user命令中帶account lock/unlock子句設置用戶的lock狀態(tài)
--create user語句默認的用戶是unlock狀態(tài)
create user username@localhost identified by 'xxx' account lock; 
--alter user語句默認不會修改用戶的lock/unlock狀態(tài) 
alter user 'mysql.sys'@localhost account lock;
alter user 'mysql.sys'@localhost account unlock;
-- 當客戶端使用lock狀態(tài)的用戶登錄MySQL時需曾，會收到報錯 
Access denied for user 'user_name'@'host_name'. Account is locked.

權限統(tǒng)計

mysql -uxxx -pxxx -NBe "select concat(\"'\",user,\"'\",'@',\"'\",host,\"'\") from mysql.user order by user desc;" |while read userhost ;do mysql -uxxx -pxxx -NBe "show grants for ${userhost};";done >/tmp/pri.sql