權限生效小知識
MySQL的授權使用者由兩部分組成：'使用者名'@'主機名/IP/IP段'
注意：包含特殊字元則必須加上引号
字元
含義
%和_
%代表所有主機，'10.10.1.%'代表所有來自10.10.1網段的主機
域名
'%.mysql.com'代表來自mysql.com這個域名下的所有主機
localhost/127.0.0.1/::1
本機/ipv4/ipv6的本機位址
同一個使用者@IP
IP是包含關系，小範圍IP的權限生效
同一個使用者@IP
權限是包含關系，最大範圍生效，存在的權限都可以使用
dba@%
本地執行[mysql -udba -p -P -h 本機IP位址]可以登入；
所有賬号本地登入都依賴sock檔案
賬号有密碼情況下僅指定sock檔案不能登入；删除sock檔案使用賬号密碼本地登入也會失敗。
執行grant,revoke,setpassword,renameuser指令修改權限之後，MySQL會自動将修改後的權限資訊同步加載到系統記憶體中；執行insert/update/delete系統權限表之後，須執行重新整理權限指令才能同步系統記憶體中：flush privileges/mysqladmin flush-privileges/mysqladmin reload
修改tables和columns級别權限，用戶端下次操作新權限就會生效
修改database級别權限，新權限在用戶端執行use database指令後生效
修改global級别權限，需要重新建立連接配接新權限才能生效
--skip-grant-tables 可以跳過所有系統權限表允許所有使用者登入，隻在特殊情況下暫時使用
連接配接MySQL常見報錯問題
網絡問題
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
授權原則
生産系統中MySQL使用者建立由DBA統一協調按需建立，DBA通常直接使用root使用者來管理資料庫；
通常會建立指定業務資料庫上的增删改查、臨時表、執行存儲過程的權限給應用程式來連接配接資料庫；
通常會建立指定業務資料庫上的隻讀權限給特定應用程式或某些進階别人員來查詢資料，防止資料被修改；
修改mysql的端口，通過防火牆限制通路mysql端口的ip
root賬号設定強密碼并指定隻容許本地登入，其他使用者設定強密碼并嚴格指定對應賬号的通路ip
如果需要記錄登入資訊，可以開啟查詢日志，查詢日志會記錄登入和查詢語句
mysql程序運作賬号 :
linux : 建立mysql賬号，安裝時指定以mysql賬戶運作，給程式所在目錄讀取權限，data所在目錄讀取和寫入權限；
windows : 禁止使用local system運作mysql賬戶，建立帳号給mysql程式所在目錄讀取權限和data目錄讀取和寫入權限
使用者授權權限最小原則 : 單資料庫授權，權限夠用即可，單資料庫賬戶不能擁有super/process/file等全局權限，可避免安全隐患
删除無用資料庫 : test資料庫對建立的賬戶預設有權限
授權語句關鍵點
含義
通路位址
要通路的資料庫伺服器ip和port
權限
具體需要的權限，開發一般授予 select,update,insert,delete,create temporary tables,execute
使用者名
具體通路人dev_name 、op_name、 test_name等
通路源位址
通路人的用戶端ip 或者 應用程式ip
常用操作需要的權限舉例
-- 執行show processlist
隻有連接配接權限時，隻能檢視自己的連接配接
具有process權限時，可以看到所有連接配接
-- 檢視information_schema庫下表
process (innodb相關表需要process權限)
-- mysql邏輯備份mysqldump所需權限
select+lock tables
【--single-transaction 參數下不需要鎖表，是以不需要lock tables權限】
【要備份觸發器存儲過程等，需要添加TRIGGER、EVENT、EXECUTE 等權限】
【MySQL版本5.7測試】
賬号權限：隻有lock tables
備份語句 mysqldump -u -p -A>
備份結果：檔案内容隻有建立資料庫語句
-- mysql邏輯恢複最小權限
GRANT SELECT, INSERT, CREATE, DROP, ALTER ON *.*
如果有外鍵關聯：REFERENCES
如果有觸發器或者存儲過程:??待測試
-- rename
INSERT, CREATE, DROP, ALTER
-- truncat
清空表需要有drop權限(授權後需要重新使用表或重新登入)
-- usage
可以執行show databases看到information_schema的所有表結構，能看到除innodb表之外的表資料；
執行select sleep(10);等内置函數；
show processlist 檢視該使用者所有會話連接配接；
可以執行kill指令 殺掉該使用者所有連接配接；
-- select *.*
可以執行show databases看到所有庫表,檢視所有庫表結構和資料
-- references
字段級 使用者可以将其它表的一個字段作為某一個表的外鍵限制
使用者建立和删除舉例
--建立使用者
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直接建立賬戶
-- 使用者重命名
RENAME USER [email protected] TO [email protected];
-- 删除使用者
DROP USER [email protected];
--如果使用者已存在：ERROR 1396 (HY000): Operation CREATE USER failed for 'dba'@'localhost'
授權和回收舉例
-- 列授權
GRANT select(id, num, class) ON testdb.log TO [email protected];
-- 存儲過程授權
GRANT execute ON procedure testdb.pr_add TO 'dba'@'localhost';
-- 函數授權
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使用者密碼過期政策【系統參數，作用于所有使用者】
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 [email protected];
使用者資源限制
-- 通過執行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：一個使用者在一個小時内可以執行查詢的次數(基本包含所有語句)
• MAX_UPDATES_PER_HOUR：一個使用者在一個小時内可以執行修改的次數(僅包含修改資料庫或表的語句)
• MAX_CONNECTIONS_PER_HOUR：一個使用者在一個小時内可以連接配接MySQL的時間
• MAX_USER_CONNECTIONS：一個使用者可以在同一時間連接配接MySQL執行個體的數量
-- 取消某項資源限制既是把原先的值修改成0
ALTER USER 'username'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 0;
-- 當針對某個使用者的max_user_connections非0時，則忽略全局系統參數 max_user_connections，反之則全局系統參數生效
修改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
--通過執行create user/alter user指令中帶account lock/unlock子句設定使用者的lock狀态
--create user語句預設的使用者是unlock狀态
create user [email protected] identified by 'xxx' account lock;
--alter user語句預設不會修改使用者的lock/unlock狀态
alter user 'mysql.sys'@localhost account lock;
alter user 'mysql.sys'@localhost account unlock;
-- 當用戶端使用lock狀态的使用者登入MySQL時，會收到報錯
Access denied for user 'user_name'@'host_name'. Account is locked.
權限統計
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