MySQL權限級別介紹
- MySQL權限級別
- 全局性的管理權限，作用於整個MySQL實例級別
- 數據庫級別的權限，作用於某個指定的數據庫上或者所有的數據庫上
- 數據庫對象級別的權限，作用於指定的數據庫對象上(表、視圖等)或 者所有的數據庫對象上
- 權限存儲在mysql庫的user, db, tables_priv, columns_priv, and procs_priv這幾個系統表中，待MySQL實例啟動後就加載到內存中
MySQL權限級別介紹
對比root用戶在幾個權限系統表中的數據
mysql> select * from user where user=‘root’ and host=‘localhost’; ##都是’Y’
mysql> select * from db where user=‘root’ and host=‘localhost’; ##無記錄
mysql> select * from tables_priv where host=‘localhost’ and user=‘root’; ##無記錄
mysql> select * from columns_priv where user=‘root’ and host=‘localhost’; ##無記錄
mysql> select * from procs_priv where user=‘root’ and host=‘localhost’;
MySQL權限詳解(1)
- All/All Privileges權限代表全局或者全數據庫對象級別的所有權限
- Alter權限代表允許修改表結構的權限，但必須要求有create和insert權 限配合。如果是rename表名，則要求有alter和drop原表，create和 insert新表的權限
- Alter routine權限代表允許修改或者刪除存儲過程、函數的權限
- Create權限代表允許創建新的數據庫和表的權限
- Createroutine權限代表允許創建存儲過程、函數的權限
- Createtablespace權限代表允許創建、修改、刪除表空間和日誌組的權 限
- Create temporary tables權限代表允許創建臨時表的權限
- Createuser權限代表允許創建、修改、刪除、重命名user的權限
- Createview權限代表允許創建視圖的權限
MySQL權限詳解(2)
• Delete權限代表允許刪除行數據的權限
• Drop權限代表允許刪除數據庫、表、視圖的權限，包括truncatetable命令
• Event權限代表允許查詢，創建，修改，刪除MySQL事件
• Execute權限代表允許執行存儲過程和函數的權限
• File權限代表允許在MySQL可以訪問的目錄進行讀寫磁盤文件操作，可使用 的命令包括load data infile,select ... into outfile,load file()函數
• Grant option權限代表是否允許此用戶授權或者收回給其他用戶你給予的權 限
• Index權限代表是否允許創建和刪除索引
• Insert權限代表是否允許在表裡插入數據，同時在執行analyze table,optimize table,repair table語句的時候也需要insert權限
• Lock權限代表允許對擁有select權限的表進行鎖定，以防止其他鏈接對此表 的讀或寫
MySQL權限詳解(3)
• Process權限代表允許查看MySQL中的進程信息，比如執行showprocesslist,
• Reference權限是在5.7.6版本之後引入，代表是否允許創建外鍵
• Reload權限代表允許執行flush命令，指明重新加載權限表到系統內存中， refresh命令代表關閉和重新開啟日誌文件並刷新所有的表
• Replication client權限代表允許執行show master status,show slave status,show binary logs命令
• Replication slave權限代表允許slave主機通過此用戶連接master以便建立主從 複製關係
• Select權限代表允許從表中查看數據，某些不查詢表數據的select執行則不需 要此權限，如Select 1+1，Select PI()+2;而且select權限在執行update/delete 語句中含有where條件的情況下也是需要的
• Showdatabases權限代表通過執行showdatabases命令查看所有的數據庫名
• Show view權限代表通過執行show create view命令查看視圖創建的語句mysqladmin processlist, show engine等命令
MySQL權限詳解(4)
• Shutdown權限代表允許關閉數據庫實例，執行語句包括mysqladmin shutdown
• Super權限代表允許執行一系列數據庫管理命令，包括kill強制關閉某個連接 命令，change master to創建複製關係命令，以及create/alter/drop server等命 令
• Trigger權限代表允許創建，刪除，執行，顯示觸發器的權限
• Update權限代表允許修改表中的數據的權限
• Usage權限是創建一個用戶之後的默認權限，其本身代表連接登錄權限
系統權限表
• 權限存儲在mysql庫的user,db, tables_priv, columns_priv, and procs_priv這幾個系統表中，待MySQL實例啟動後就加載到內存中
• User表:存放用戶賬戶信息以及全局級別(所有數據庫)權限，決定了 來自哪些主機的哪些用戶可以訪問數據庫實例，如果有全局權限則意味
著對所有數據庫都有此權限
• Db表:存放數據庫級別的權限，決定了來自哪些主機的哪些用戶可以訪 問此數據庫
• Tables_priv表:存放表級別的權限，決定了來自哪些主機的哪些用戶可以 訪問數據庫的這個表
• Columns_priv表:存放列級別的權限，決定了來自哪些主機的哪些用戶可 以訪問數據庫表的這個字段
• Procs_priv表:存放存儲過程和函數級別的權限
• User和db權限表結構
• User權限表結構中的特殊字段
• Plugin,password,authentication_string三個字段存放用戶認證信息
• Password_expired設置成’Y’則表明允許DBA將此用戶的密碼設置成過期而 且過期後要求用戶的使用者重置密碼(alter user/set password重置密碼)
• Password_last_changed作為一個時間戳字段代表密碼上次修改時間，執 行create user/alter user/set password/grant等命令創建用戶或修改用戶密 碼時此數值自動更新
• Password_lifetime代表從password_last_changed時間開始此密碼過期的天 數
• Account_locked代表此用戶被鎖住，無法使用
• Tables_priv和columns_priv權限表結構
• Timestamp和grantor兩個字段暫時沒用
• Tables_priv和columns_priv權限值
• procs_priv權限表結構
• Routine_type是枚舉類型，代表是存儲過程還是函數
• Timestamp和grantor兩個字段暫時沒用
• 系統權限表字段長度限制表
• 權限認證中的大小寫敏感問題
• 字段user,password,authencation_string,db,table_name大小寫敏感
• 字段host,column_name,routine_name大小寫不敏感
• User用戶大小寫敏感
mysql> create user abc@localhost;
ERROR 1396 (HY000): Operation CREATE USER failed for 'abc'@'localhost'
mysql> create user Abc@localhost;
Query OK, 0 rows affected (0.01 sec)
• Host主機名大小寫不敏感
mysql> create user abc@Localhost;
ERROR 1396 (HY000): Operation CREATE USER failed for 'abc'@'localhost'
• MySQL的授權用戶由兩部分組成:用戶名和登錄主機名
• 表達用戶的語法為‘user_name’@‘host_name’
• 單引號不是必須，但如果其中包含特殊字符則是必須的
• ‘’@‘localhost’代表匿名登錄的用戶
• Host_name可以使主機名或者ipv4/ipv6的地址。Localhost代表本機，127.0.0.1代表ipv4的 本機地址，::1代表ipv6的本機地址
• Host_name字段允許使用%和_兩個匹配字符，比如’%’代表所有主機，’%.mysql.com’代表 來自mysql.com這個域名下的所有主機，‘192.168.1.%’代表所有來自192.168.1網段的主機
MySQL修改權限的生效
• 執行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用戶連接
mysql
mysql -u finley -p db_name
mysql
shell> mysql -u finley -ppassword db_name
創建MySQL用戶
• 有兩種方式創建MySQL授權用戶
• 執行createuser/grant命令(推薦方式)
• 通過insert語句直接操作MySQL系統權限表
mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass';
mysql>GRANTALLPRIVILEGESON*.*TO'finley'@'localhost' WITH
GRANT OPTION;
mysql> CREATE USER 'finley'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'%‘ WITH GRANT OPTION;
mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin_pass';
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> grant select(id) on test.temp to cdq@localhost;
創建MySQL用戶
mysql> CREATE USER 'custom'@'localhost' IDENTIFIED BY 'obscure'; mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON bankaccount.*
-> TO 'custom'@'localhost';
mysql> CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'obscure'; mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON expenses.*
-> TO 'custom'@'host47.example.com';
mysql> CREATE USER 'custom'@'%.example.com' IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
回收MySQL用戶權限
• 通過revoke命令收回用戶權限
mysql> revoke select on `sys`.`sys_config` from 'mysql.sys'@localhost;
刪除MySQL用戶
• 通過執行drop user命令刪除MySQL用戶
mysql> DROP USER 'jeffrey'@'localhost';
設置MySQL用戶資源限制
• 通過設置全局變量max_user_connections可以限制所有用戶在同一時 間連接MySQL實例的數量，但此參數無法對每個用戶區別對待，所以 MySQL提供了對每個用戶的資源限制管理
• MAX_QUERIES_PER_HOUR:一個用戶在一個小時內可以執行查詢的次 數(基本包含所有語句)
• MAX_UPDATES_PER_HOUR:一個用戶在一個小時內可以執行修改的次 數(僅包含修改數據庫或表的語句)
• MAX_CONNECTIONS_PER_HOUR:一個用戶在一個小時內可以連接 MySQL的時間
• MAX_USER_CONNECTIONS:一個用戶可以在同一時間連接MySQL實例 的數量
• 從5.0.3版本開始，對用戶‘user’@‘%.example.com’的資源限制是指所有 通過example.com域名主機連接user用戶的連接，而不是分別指從 host1.example.com和host2.example.com主機過來的連接
設置MySQL用戶資源限制
• 通過執行createuser/alteruser設置/修改用戶的資源限制
mysql> CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank'
-> -> -> ->
WITH MAX_QUERIES_PER_HOUR 20 MAX_UPDATES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 5 MAX_USER_CONNECTIONS 2;
mysql> ALTER USER 'francis'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;
• 取消某項資源限制既是把原先的值修改成0
mysql> ALTER USER 'francis'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 0;
• 當針對某個用戶的max_user_connections非0時，則忽略全局系統參數 max_user_connections，反之則全局系統參數生效
設置MySQL用戶的密碼
• 執行create user創建用戶和密碼
mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
• 修改用戶密碼的方式包括:
mysql> ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
mysql> SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');
mysql> GRANT USAGE ON *.* TO 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
shell> mysqladmin -u user_name -h host_name password "new_password";
• 修改本身用戶密碼的方式包括:
mysql> ALTER USER USER() IDENTIFIED BY 'mypass';
mysql> SET PASSWORD = PASSWORD('mypass');
設置MySQL用戶密碼過期策略
• 設置系統參數default_password_lifetime作用於所有的用戶賬戶
• default_password_lifetime=180 設置180天過期
• default_password_lifetime=0 設置密碼不過期
• 如果為每個用戶設置了密碼過期策略，則會覆蓋上述系統參數
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL90DAY;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INEVER;密碼不過期
ALTER USER‘jeffrey’@‘localhost’ PASSWORD EXPIRE DEFAULT;默認過期策略
• 手動強制某個用戶密碼過期
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;
mysql> SELECT 1;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> ALTER USER USER() IDENTIFIED BY 'new_password';
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT 1;
|1|
MySQL用戶lock
通過執行create user/alter user命令中帶account lock/unlock子句設 置用戶的lock狀態
Createuser語句默認的用戶是unlock狀態
mysql>create user abc2@localhost identified by 'mysql' account lock;
QueryOK,0rowsaffected(0.01sec)
Alter user語句默認不會修改用戶的lock/unlock狀態
mysql>alter user 'mysql.sys'@localhost account lock;
Query OK,0 row saffected(0.00sec)
mysql>alter user 'mysql.sys'@localhost account unlock;
Query OK,0 row saffected(0.00sec)
Account is locked.
企業應用中的常規MySQL用戶
• 企業生產系統中MySQL用戶的創建通常由DBA統一協調創建，而且按需
創建
• DBA通常直接使用root用戶來管理數據庫
• 通常會創建指定業務數據庫上的增刪改查、臨時表、執行存儲過程的權限給應 用程序來連接數據庫
Create user app_full identified by ‘mysql’;
Grant select,update,insert,delete,create temporary tables,execute on esn.* to
app_full@’10.0.0.%’;
mysql>show grants for app_full@'10.0.0.%';
+
|[email protected].% |
+
|GRANTUSAGEON*.*TO'app_full'@'10.0.0.%' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE ON `esn`.* TO 'app_full'@'10.0.0.%' |
• 通常也會創建指定業務數據庫上的只讀權限給特定應用程序或某些高級別人員 來查詢數據，
防止數據被修改
Create user app_readonly identified by ‘mysql’;
Grant select on esn.* to app_readonly identified by ‘mysq’;
閱讀更多 我要報告 的文章
