1，MySQL權限體系
mysql 的權限體系大致分為5個層級:
全局層級：
全局權限適用于一個給定服務器中的所有數據庫。這些權限存儲在mysql.user表中。GRANT ALL ON .和REVOKE ALL ON .只授予和撤銷全局權限。
數據庫層級：
數據庫權限適用于一個給定數據庫中的所有目標。這些權限存儲在mysql.db表中。GRANT ALL ON db_name.和REVOKE ALL ON db_name.只授予和撤銷數據庫權限。
表層級：
表權限適用于一個給定表中的所有列。這些權限存儲在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤銷表權限。
列層級：
列權限適用于一個給定表中的單一列。這些權限存儲在mysql.columns_priv表中。當使用REVOKE時，您必須指定與被授權列相同的列。
子程序層級：
CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT權限適用于已存儲的子程序。這些權限可以被授予為全局層級和數據庫層級。而且，除了CREATE ROUTINE外，這些權限可以被授予為子程序層級，并存儲在mysql.procs_priv表中。
這些權限信息存儲在下面的系統表中:
mysql.user
mysql.db
mysql.host
mysql.table_priv
mysql.column_priv
mysql. procs_priv
當用戶連接進來，mysqld會通過上面的這些表對用戶權限進行驗證！
2， 千里追蹤之5表
相對于oracle來說，mysql的特性是可以限制ip，用戶user、ip地址host、密碼passwd這3個是用戶管理的基礎，權限的細節基本在mysql.user、mysql.db、mysql.host、mysql.table_priv、mysql.column_priv這幾張表就可以看到很多細節，接下來仔細分析這些表就可以知道權限的奧秘。
原博客地址： ? ?http://www.php.cn/
原作者：黃杉 (mchdba)
演示過程中需要建立用戶來演示，先簡單介紹下如何創建用戶：
GRANT priv_type ON database.table
TO user[IDENTIFIED BY [PASSWORD] ‘password’]
[,user [IDENTIFIED BY [PASSWORD] ‘password’]…]
示例：
GRANT SELECT, INSERT, UPDATE, DELETE ON d3307.* TO zengxiaoteng@’%’ IDENTIFIED BY ‘0523’;
2.1db表
2.1.1 表結構如下：
mysql>?desc?mysql.db; +-----------------------+---------------+------+-----+---------+-------+ |?Field?????????????????|?Type??????????|?Null?|?Key?|?Default?|?Extra?| +-----------------------+---------------+------+-----+---------+-------+ |?Host??????????????????|?char(60)??????|?NO???|?PRI?|?????????|???????| |?Db????????????????????|?char(64)??????|?NO???|?PRI?|?????????|???????| |?User??????????????????|?char(16)??????|?NO???|?PRI?|?????????|???????| |?Select_priv???????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Insert_priv???????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Update_priv???????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Delete_priv???????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Create_priv???????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Drop_priv?????????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Grant_priv????????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?References_priv???????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Index_priv????????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Alter_priv????????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Create_tmp_table_priv?|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Lock_tables_priv??????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Create_view_priv??????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Show_view_priv????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Create_routine_priv???|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Alter_routine_priv????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Execute_priv??????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Event_priv????????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Trigger_priv??????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| +-----------------------+---------------+------+-----+---------+-------+22?rows?in?set?(0.02?sec) mysql>
2.1.2分析如下：
db表存儲了所有對一個數據庫的所有操作權限。創建用戶的時候，都會往Host字段，User字段，Password字段錄入用戶信息；
而當執行 GRANT SELECT,INSERT ON d3307.* TO u4@’%’ IDENTIFIED BY ‘u40523’;類似的授權語句的話，Select_priv和Insert_priv字段的值會變成Y其它字段仍然是N；
當你執行了GRANT ALL ON d3307.* TO u4@’%’ IDENTIFIED BY ‘u40523’;類似的復制語句的話，后面的字段都會變成Y的值；
2.1.3 創建單個select、insert授予權限
創建用戶：
GRANT?SELECT,INSERT?ON?d3307.*?TO?user4@'192.168.52'?IDENTIFIED?BY?'user0523';
應該除了Host、db、user字段有值，除了Select_priv、Insert_priv值為Y外，其它的都是N。
查看mysql.db表的記錄正是如此，如下所示：
mysql>?SELECT?*?FROM?mysql.`db`?where?user='user4'G; ***************************?1.?row?*************************** ?????????????????Host:?192.168.52 ???????????????????Db:?d3307 ?????????????????User:?user4 ??????????Select_priv:?Y ??????????Insert_priv:?Y ??????????Update_priv:?N ??????????Delete_priv:?N ??????????Create_priv:?N ????????????Drop_priv:?N ???????????Grant_priv:?N ??????References_priv:?N ???????????Index_priv:?N ???????????Alter_priv:?NCreate_tmp_table_priv:?N ?????Lock_tables_priv:?N ?????Create_view_priv:?N ???????Show_view_priv:?N ??Create_routine_priv:?N ???Alter_routine_priv:?N ?????????Execute_priv:?N ???????????Event_priv:?N ?????????Trigger_priv:?N1?row?in?set?(0.01?sec) ERROR:? No?query?specified mysql>
2.1.4 授予ALL權限
執行sql語句建立用戶：
GRANT?ALL?ON?d3307.*?TO?dba5@'192.168.52.1'?IDENTIFIED?BY?'dba0523';
建立用戶的時候，如下所示，除了Host、db、user字段外，所有的*_priv字段記錄都會變成Y值，(Grant_priv仍然是N值除非加了WITH* GRANT OPTION執行GRANT ALL ON d3307.* TO dba5@’192.168.52.1’ IDENTIFIED BY ‘dba0523’ WITH GRANT OPTION ??
如下所示：
mysql>?SELECT?*?FROM?mysql.`db`?where?user='dba5'G;***************************?1.?row?***************************?????????????????Host:?192.168.52.1???????????????????Db:?d3307?????????????????User:?dba5??????????Select_priv:?Y??????????Insert_priv:?Y??????????Update_priv:?Y??????????Delete_priv:?Y??????????Create_priv:?Y????????????Drop_priv:?Y???????????Grant_priv:?N??????References_priv:?Y???????????Index_priv:?Y???????????Alter_priv:?YCreate_tmp_table_priv:?Y?????Lock_tables_priv:?Y?????Create_view_priv:?Y???????Show_view_priv:?Y ??Create_routine_priv:?Y ???Alter_routine_priv:?Y?????????Execute_priv:?Y???????????Event_priv:?Y?????????Trigger_priv:?Y1?row?in?set?(0.00?sec) ERROR:? No?query?specified mysql>
2.2 user表
2.2.1 表結構：
mysql> desc mysql.user; +------------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | | plugin | char(64) | YES | | | | | authentication_string | text | YES | | NULL | | | password_expired | enum('N','Y') | NO | | N | | +------------------------+-----------------------------------+------+-----+---------+-------+ 43 rows in set (0.10 sec) mysql>
2.2.2 分析
存儲用戶記錄的表，存儲了用戶的信息，每一次創建用戶的時候，都會往這個表里錄入記錄，當你執行了，都會往Host字段，User字段，Password字段錄入數據，但是后面的Select_priv、Insert_priv、Update_priv等字段的值，只有賦予GRANT ALL ON . TO timdba@’192.%’ IDENTIFIED BY ‘timdba0523’;類似的對所有庫的操作權限的時候才會被記錄成Y，否則都記錄成N。
2.2.3 創建對庫所有表有操作權限的普通用戶
創建用戶：
GRANT?SELECT,UPDATE?ON?d3307.*?TO?user6@'192.168.52.1'?IDENTIFIED?BY?'user0523';
分析結果：存儲在mysql.user表里面的記錄當中，Host、User、Password是有值的，但是其它的Select_priv等*_priv字段值都是N。
驗證結果，去查看表里的存儲記錄，如下所示：
mysql>?SELECT?*?FROM?mysql.user?where?user='user6'G; ***************************?1.?row?*************************** ??????????????????Host:?192.168.52.1 ??????????????????User:?user6 ??????????????Password:?*A4D1F6ACEBC5D3EB0F6D33C7DCC629E8BE55B75A ???????????Select_priv:?N ???????????Insert_priv:?N ???????????Update_priv:?N ???????????Delete_priv:?N ???????????Create_priv:?N ?????????????Drop_priv:?N ???????????Reload_priv:?N ?????????Shutdown_priv:?N ??????????Process_priv:?N ?????????????File_priv:?N ????????????Grant_priv:?N ???????References_priv:?N ????????????Index_priv:?N ????????????Alter_priv:?N ??????????Show_db_priv:?N ????????????Super_priv:?N ?Create_tmp_table_priv:?N ??????Lock_tables_priv:?N ??????????Execute_priv:?N ???????Repl_slave_priv:?N ??????Repl_client_priv:?N ??????Create_view_priv:?N ????????Show_view_priv:?N ???Create_routine_priv:?N ????Alter_routine_priv:?N ??????Create_user_priv:?N ????????????Event_priv:?N ??????????Trigger_priv:?NCreate_tablespace_priv:?N ??????????????ssl_type:? ????????????ssl_cipher:? ???????????x509_issuer:? ??????????x509_subject:? ?????????max_questions:?0 ???????????max_updates:?0 ???????max_connections:?0 ??max_user_connections:?0 ????????????????plugin:?mysql_native_password ?authentication_string:? ??????password_expired:?N1?row?in?set?(0.00?sec) ERROR:? No?query?specified mysql>
2.2.4 創建對于所有表有操作權限的用戶
創建用戶：
mysql>?GRANT?SELECT,UPDATE?ON?*.*?TO?user7@'%'?IDENTIFIED?BY?'user0523'; Query?OK,?0?rows?affected?(0.00?sec) mysql>
分析：
基本的Host、User、Password字段有記錄值，然后grant了select和update所以關于*_priv字段中select和update字段有值為Y，其它*_priv字段值應該是N。
查看記錄結果，分享正確，如下所示：
mysql>?SELECT?*?FROM?mysql.user?where?user='user7'G; ***************************?1.?row?*************************** ??????????????????Host:?% ??????????????????User:?user7 ??????????????Password:?*A4D1F6ACEBC5D3EB0F6D33C7DCC629E8BE55B75A ???????????Select_priv:?Y ???????????Insert_priv:?N ???????????Update_priv:?Y ???????????Delete_priv:?N ???????????Create_priv:?N ?????????????Drop_priv:?N ???????????Reload_priv:?N ?????????Shutdown_priv:?N ??????????Process_priv:?N ?????????????File_priv:?N ????????????Grant_priv:?N ???????References_priv:?N ????????????Index_priv:?N ????????????Alter_priv:?N ??????????Show_db_priv:?N ????????????Super_priv:?N ?Create_tmp_table_priv:?N ??????Lock_tables_priv:?N ??????????Execute_priv:?N ???????Repl_slave_priv:?N ??????Repl_client_priv:?N ??????Create_view_priv:?N ????????Show_view_priv:?N ???Create_routine_priv:?N ????Alter_routine_priv:?N ??????Create_user_priv:?N ????????????Event_priv:?N ??????????Trigger_priv:?NCreate_tablespace_priv:?N ??????????????ssl_type:? ????????????ssl_cipher:? ???????????x509_issuer:? ??????????x509_subject:? ?????????max_questions:?0 ???????????max_updates:?0 ???????max_connections:?0 ??max_user_connections:?0 ????????????????plugin:?mysql_native_password ?authentication_string:? ??????password_expired:?N1?row?in?set?(0.00?sec) ERROR:? No?query?specified mysql>
2.3 tables_priv表
2.3.1 查看表結構
mysql> desc mysql.tables_priv; +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Table_name | char(64) | NO | PRI | | | | Grantor | char(77) | NO | MUL | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | | | | | Column_priv | set('Select','Insert','Update','References') | NO | | | | +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+ 8 rows in set (0.00 sec) mysql>
2.3.2 分析：
記錄了對一個表的單獨授權記錄，只有執行grant insert on dbname.tablename to user1@’%’identified by ‘pwd’;類似的授權記錄才會在這個表里錄入授權信息；其中各個字段涵義如下：
| 字段 | 存儲的數據 |
|---|---|
| Host字段 | 用戶的登錄ip范圍 |
| User字段 | 表所在的數據庫名稱 |
| Table_name字段 | 授權的表的名稱 |
| Grantor字段 | 執行grant建立用戶的授權者 |
| Timestamp字段 | 0000-00-00 00:00:00 |
| Table_priv字段 | 所授予的操作表的權限，比如select、udate、delete等 |
| Column_priv字段 | 對這個表的某個字段單獨授予的權限 |
另外當賦予all在某張表上的時候，Table_priv列會多處所有關于表的授權記錄，描述如下：
Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger。
2.3.3 創建單獨操作這個表的用戶
創建用戶：
mysql>?GRANT?INSERT,SELECT,UPDATE?ON?d3307.t?TO?user8@'192.168.52.1'?IDENTIFIED?BY?'dba0523'; Query?OK,?0?rows?affected?(0.00?sec) mysql>
分析結果：
應該是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv是有值的，但是Column_priv沒有值，因為沒有單獨對某一個列做了授權限制的。
查看權限，如下所示：
mysql>?SELECT?*?FROM?mysql.tables_priv?where?user='user8'G;***************************?1.?row?***************************???????Host:?192.168.52.1?????????Db:?d3307???????User:?user8 ?Table_name:?t????Grantor:?root@localhost ??Timestamp:?0000-00-00?00:00:00 ?Table_priv:?Select,Insert,Update Column_priv:? 1?row?in?set?(0.00?sec) ERROR:? No?query?specified mysql>
2.3.4 單獨為某個列授權
授權語句操作：
mysql>?GRANT?UPDATE(created_time)?ON?d3307.t??TO?user8@'192.168.52.1'; Query?OK,?0?rows?affected?(0.00?sec) mysql>?GRANT?SELECT(uname)??ON?d3307.t??TO?user8@'192.168.52.1'; Query?OK,?0?rows?affected?(0.00?sec) mysql>
分析：
單獨為某個列授權，會記錄在這個表的Column_priv字段里面，會記錄下對單個列的授權操作記錄
查看記錄：
mysql>?SELECT?*?FROM?mysql.tables_priv?where?user='user8'G;***************************?1.?row?***************************???????Host:?192.168.52.1?????????Db:?d3307???????User:?user8 ?Table_name:?t????Grantor:?root@localhost ??Timestamp:?0000-00-00?00:00:00 ?Table_priv:?Select,Insert,Update Column_priv:?Select,Update 1?row?in?set?(0.00?sec) ERROR:? No?query?specified mysql>
而且還會在另外一個權限表mysql.columns_priv留下記錄單獨的授權記錄，如下所示：
mysql>?SELECT?*?FROM?mysql.columns_priv?WHERE?USER='user8'; +--------------+-------+-------+------------+--------------+---------------------+-------------+|?Host?????????|?Db????|?User??|?Table_name?|?Column_name??|?Timestamp???????????|?Column_priv?| +--------------+-------+-------+------------+--------------+---------------------+-------------+|?192.168.52.1?|?d3307?|?user8?|?t??????????|?created_time?|?0000-00-00?00:00:00?|?Update??????| |?192.168.52.1?|?d3307?|?user8?|?t??????????|?uname????????|?0000-00-00?00:00:00?|?Select??????| +--------------+-------+-------+------------+--------------+---------------------+-------------+ 2?rows?in?set?(0.00?sec)mysql>
2.4 columns_priv表
2.4.1 表結構如下：
mysql> desc mysql.columns_priv; +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Table_name | char(64) | NO | PRI | | | | Column_name | char(64) | NO | PRI | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | Column_priv | set('Select','Insert','Update','References') | NO | | | | +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ 7 rows in set (0.04 sec) mysql>
2.4.2 分析
單獨對某一列有操作權限的時候，會將權限信息記錄在這個表里面，比如新建立一個賬號GRANT UPDATE(uname) ON d3307.t TO user9@’192.168.52.%’ IDENTIFIED BY ‘user0520’; 那么就會在這個表上錄入授權信息記錄，重點看Column_name字段和Column_priv字段的值。
2.4.3 實際操作
創建用戶操作：
mysql>?GRANT?UPDATE(uname)?ON?d3307.t?TO?user9@'192.168.52.%'?IDENTIFIED?BY?'user0520'; Query?OK,?0?rows?affected?(0.00?sec) mysql>
查看結果，會在這個columns_priv表留下一條記錄：
mysql>?SELECT?*?FROM?mysql.columns_priv?WHERE?USER='user9'; ????????+--------------+-------+-------+------------+-------------+---------------------+-------------+ ????????|?Host?????????|?Db????|?User??|?Table_name?|?Column_name?|?Timestamp???????????|?Column_priv?| ????????+--------------+-------+-------+------------+-------------+---------------------+-------------+ ????????|?192.168.52.%?|?d3307?|?user9?|?t??????????|?uname???????|?0000-00-00?00:00:00?|?Update??????| ????????+--------------+-------+-------+------------+-------------+---------------------+-------------+????????1?row?in?set?(0.00?sec) ????????mysql>
2.5 procs_priv表
2.5.1 表結構
mysql>?desc?proxies_priv; +--------------+------------+------+-----+-------------------+-----------------------------+|?Field????????|?Type???????|?Null?|?Key?|?Default???????????|?Extra???????????????????????| +--------------+------------+------+-----+-------------------+-----------------------------+|?Host?????????|?char(60)???|?NO???|?PRI?|???????????????????|?????????????????????????????| |?User?????????|?char(16)???|?NO???|?PRI?|???????????????????|?????????????????????????????| |?Proxied_host?|?char(60)???|?NO???|?PRI?|???????????????????|?????????????????????????????| |?Proxied_user?|?char(16)???|?NO???|?PRI?|???????????????????|?????????????????????????????| |?With_grant???|?tinyint(1)?|?NO???|?????|?0?????????????????|?????????????????????????????| |?Grantor??????|?char(77)???|?NO???|?MUL?|???????????????????|?????????????????????????????| |?Timestamp????|?timestamp??|?NO???|?????|?CURRENT_TIMESTAMP?|?on?update?CURRENT_TIMESTAMP?| +--------------+------------+------+-----+-------------------+-----------------------------+ 7?rows?in?set?(0.04?sec)mysql>
2.6.2分析：
procs_priv表可以對存儲過程和存儲函數進行權限設置。主要字段：proc_priv。
3，創建用戶
3.1、CREATE USER創建用戶
使用CREATE USER語句創建用戶，必須要擁有CREATE USER權限。其格式如下：
CREATE?USER?user[IDENTIFIED?BY?[PASSWORD]?'password'], [user[IDENTIFIED?BY?[PASSWORD]?'password']]...
其中，user參數表示新建用戶的賬戶，user由用戶名(User)和主機名(Host)構成；IDENTIFIED BY關鍵字用來設置用戶的密碼；password參數表示用戶的密碼；如果密碼是一個普通的字符串，就不需要使用PASSWORD關鍵字。可以沒有初始密碼。
例如
CREATE?USER?'sys'@'%'?IDENTIFIED?BY?'sys';
執行之后user表會增加一行記錄，但權限暫時全部為‘N’。
3.2、用INSERT語句新建普通用戶
可以使用INSERT語句直接將用戶的信息添加到mysql.user表。但必須擁有mysql.user表的INSERT權限。
另外，ssl_cipher、x509_issuer、x509_subject等必須要設置值，否則INSERT語句無法執行。
示例：
INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) VALUES(‘%’,’newuser1’,PASSWORD(‘123456’),”,”,”)
執行INSERT之后，要使用命令：FLUSH PRIVILEGES;命令來使用戶生效。
3.3、用GRANT語句來新建普通用戶
用GRANT來創建新的用戶時，能夠在創建用戶時為用戶授權。但需要擁有GRANT權限。
語法如下：
GRANT?priv_type?ON?database.table TO?user[IDENTIFIED?BY?[PASSWORD]?'password'] [,user?[IDENTIFIED?BY?[PASSWORD]?'password']...]
priv_type：參數表示新yoghurt的權限；
databse.table：參數表示新用戶的權限范圍；
user：參數新用戶的賬戶，由用戶名和主機構成；
IDENTIFIED BY關鍵字用來設置密碼；
password：新用戶密碼；
PS：GRANT語句可以同時創建多個用戶。.與db.*的區別在于。.對所有數據庫生效，所以user表的SELECT會變為Y。而db.*user表為’N’，更改的是Db表。
4，刪除用戶
4.1 drop user刪除用戶
DROP USER語句刪除普通用戶，需要擁有DROP USER權限。
語法如下：
DROP?USER?user[,user]...
user是需要刪除的用戶，由用戶名(User)和主機名(Host)構成。
4.2 DELETE語句刪除普通用戶
可以使用DELETE語句直接將用戶的信息從mysql.user表中刪除。但必須擁有對mysql.user表的DELETE權限。DELETE FROM mysql.user WHERE Host = ‘%’ AND User = ‘admin’; 刪除完成后，一樣要FLUSH PRIVILEGES才生效。
5，修改用戶密碼
5.1 使用mysqladmin命令來修改root用戶的密碼
語法：
mysqladmin?-u?-username?-p?password?"new_password"
新密碼(new_password)必須用括號括起來，單引號會報錯。
示例，修改中要輸入舊的密碼來驗證：
[root@data02?~]#?mysqladmin?-u?timman?-p?password?"tim"?--socket=/usr/local/mysql3307/mysql.sock Enter?password:? [root@data02?~]# [root@data02?~]#?mysql?--socket=/usr/local/mysql3307/mysql.sock?-utimman?-ptim?-e?"select?@@port";+--------+|?@@port?| +--------+|???3307?| +--------+[root@data02?~]#
5.2 修改user表
UPDATE user表的passwor字段的值，也可以達到修改密碼的目的；
UPDATE?user?SET?Password?=?PASSWORD('123')?WHERE?USER?=?'myuser';FLUSH?PRIVILEGES;
刷新后生效。
5.3 使用SET語句來修改密碼
使用root用戶登錄到MySQL服務器后，可以使用SET語句來修改密碼：
修改自己的密碼，不需要用戶名
SET?PASSWORD?=?PASSWORD("123");
修改其他用戶密碼：
SET?PASSWORD?FOR?'myuser'@'%'=PASSWORD("123456")?FOR?用戶名@主機名
5.4 GRANT語句來修改普通用戶的密碼
使用GRANT語句修改普通用戶的密碼，必須擁有GRANT權限。
GRANT?priv_type?ON?database.table?TO?user?[IDENTIFIED?BY?[PASSWORD]?'password']
示例：
GRANT?SELECT?ON?*.*?TO?'user10'@'%'?IDENTIFIED?BY?'123'
5.5 忘記用戶密碼的解決辦法
普通用戶，直接用root超級管理員登錄進去修改密碼就可以了，但是如果root密碼丟失了，怎么辦呢？
5.5.1 msyqld_saft方式找回密碼
停止mysql：service mysqld stop;
? ? 安全模式啟動：mysqld_safe –skip-grant-tables &
? ?無密碼回車鍵登錄：mysql -uroot –p
? ?重置密碼：use mysql; update user set password=password(“”) where user=’root’ and host=’localhost’; flush privileges;
? ?正常啟動：service mysql restart
? ?再使用mysqladmin： mysqladmin ?password ‘123456’ ?
5.5.2 ?使用普通賬號來找回密碼
–>(1)：有一個修改test庫的用戶：grant create,delete,update,insert,select on d3307.* to test@’%’ identified by ‘t1’;
–>(2)：復制user表文件到test庫下并且賦予mysql用戶訪問權限： ?
? ?cp /home/data/mysql/data/mysql/user.* /home/data/mysql/data/test/;chown mysql.mysql /home/data/mysql/data/test/user.*
–>(3)：mysql -utest -pt1登錄修改root密碼：
–>(4)：將test庫的user表文件覆蓋 mysql庫的user表文件 ?
? ?cp /home/data/mysql/data/mysql/user.* /tmp/; mv /home/data/mysql/data/test/user.* /home/data/mysql/data/mysql/ ; ? ?chown mysql.mysql /home/data/mysql/data/mysql/user.*;
–>(5):查找mysql進程號，并且發送SIGHUP信號，重新加載權限表。 ?
pgrep -n mysql; kill -SIGHUP 12234;
–>(6):無密碼登錄，再使用mysqladmin重新設置密碼。
PS：請參考第20課的視頻，那里有詳細的記錄整個過修改密碼的過程。
6，收回用戶權限
查看權限：
SHOW?GRANTS;??SHOW?GRANTS?FOR?user10@'%';
或者直接執行sql命令去mysql數據庫下的user表中查看存儲著用戶的基本權限：
SELECT?*?FROM?mysql.user?WHERE?USER='user10'?AND?HOST='%';
使用revoke關鍵字來收回權限：
REVOKE?priv_type[(column_list)]ON?database.tableFROM?user[,user]
示例：
REVOKE?EXECUTE?ON?d3307.*?FROM?user10@'%';
7，數據庫用戶劃分
7.1 普通數據管理用戶：
賦予對業務表的查詢維護權限即可，授權sql如下：
GRANT?SELECT,?INSERT,?UPDATE,?DELETE?ON?d3307.*?TO?zengxiaoteng@'%'?IDENTIFIED?BY?'0523';
7.2 開發人員賬戶：
賦予增刪改查的權限，授權sql如下：
GRANT?SELECT,INSERT,DELETE,UPDATE?ON?d3307.*?TO?huyan@'%'?IDENTIFIED?BY?'0523';
授予創建、修改、刪除 MySQL 數據表結構權限。
GRANT?CREATE?ON?d3307.*??TO?huyan@’192.168.52.11’;GRANT?ALTER??ON?d3307.*?TO?huyan@’192.168.52.11’;GRANT?DROP???ON?d3307.*?TO?huyan@’192.168.52.11’;
授予操作 MySQL 外鍵權限：
GRANT?REFERENCES?ON?d3307.*?TO?huyan@’192.168.52.11’;
授予操作 MySQL 臨時表權限:
GRANT?CREATE?TEMPORARY?TABLES?ON?d3307.*?TO?huyan@’192.168.52.11’;
授予操作 MySQL 索引權限:
GRANT?INDEX?ON?d3307.*?TO?huyan@’192.168.52.11’;
授予操作 MySQL 視圖、查看視圖源代碼 權限：
GRANT?CREATE?VIEW?ON?d3307.*?TO?huyan@’192.168.52.11’;GRANT?SHOW???VIEW?ON?d3307.*?TO?huyan@’192.168.52.11’;
授予操作 MySQL 存儲過程、函數 權限：
GRANT?CREATE?ROUTINE?ON?d3307.*?TO?huyan@’192.168.52.11’;GRANT?ALTER?ROUTINE?ON?d3307.*?TO?huyan@’192.168.52.11’;GRANT?EXECUTE????????ON?d3307.*?TO?huyan@’192.168.52.11’;
7.3 DBA人員賬戶
授予普通DBA管理某個MySQL數據庫(test)的權限：
GRANT?ALL?PRIVILEGES?ON?test?TO?sysdba@'192.168.52.%';
授予高級 DBA 管理 MySQL 中所有數據庫的權限：
GRANT?ALL?ON?*.*?TO?sysdba@'192.168.52.%';
7.4 數據分析人員只讀賬號
只需要分配只讀的權限：
GRANT?SELECT?ON?d3307.*?TO?dataquery@'192.168.52.129'?IDENTIFIED?BY?'20150523';
甚至有些用戶，可以只分配讀取某些表列的權限，如下所示：
GRANT SELECT ON test.* TO dataquery@’192.168.52.%’ IDENTIFIED BY ‘20150523’;
GRANT SELECT(id,uname) ON d3307.t TO dataquery@’192.168.52.%’ ;
示列權限登錄操作：
[root@data02?~]#?mysql?--socket=/usr/local/mysql3307/mysql.sock?-u?dataquery?-p20150523?-h192.168.52.130?-P3307Welcome?TO?the?MySQL?monitor.??Commands?END?WITH?;?OR?g. Your?MySQL?CONNECTION?id?IS?18SERVER?VERSION:?5.6.12-LOG?Source?distribution Copyright?(c)?2000,?2013,?Oracle?AND/OR?its?affiliates.?ALL?rights?reserved. Oracle?IS?a?registered?trademark?of?Oracle?Corporation?AND/OR?its affiliates.?Other?NAMES?may?be?trademarks?of?their?respective owners.TYPE?'help;'?OR?'h'?FOR?help.?TYPE?'c'?TO?clear?the?current?input?statement. mysql>?SELECT?*?FROM?d3307.t; ERROR?1142?(42000):?SELECT?command?denied?TO?USER?'dataquery'@'data02'?FOR?TABLE?'t' mysql> mysql>?SELECT?id,uname?FROM?d3307.t; +----+-------+|?id?|?uname?| +----+-------+|??1?|?a?????| +----+-------+1?ROW?IN?SET?(0.00?sec) mysql>
8，權限劃分一般原則
數據庫一般劃分為線上庫，測試庫，開發庫。
8.1對于線上庫：
DBA：有所有權限，超級管理員權限
應用程序：分配insert、delete、update、select、execute、events、jobs權限。
測試人員：select某些業務表權限 ?
開發人員：select某些業務表權限
原則：所有對線上表的操作，除了應用程序之外，都必須經由DBA來決定是否執行、已經什么時候執行等。
8.2 測試庫
DBA：所有權限。 ?
測試人員：有insert、delete、update、select、execute、jobs權限。
數據分析人員：只有select查詢權限
開發人員：有select權限。
原則：DBA有所有權限，而且嚴格控制表結構的變更，不允許除了dba之外的人對測試環境的庫環境進行修改，以免影響測試人員測試。所有對測試庫的表結構進行的修改必須由測試人員和DBA一起審核過后才能操作。
8.3 開發庫
DBA：所有權限
測試人員：有庫表結構以及數據的所有操作權限。
開發人員：有庫表結構以及數據的所有操作權限。
數據分析人員：有庫表結構以及數據的所有操作權限。
這里大家可以愉快的玩耍了，只要不mysql服務不hang不downtime都OK了。
參考文章資料：
http://www.php.cn/
http://www.php.cn/
http://www.php.cn/
http://www.php.cn/
1，MySQL權限體系
mysql 的權限體系大致分為5個層級:
全局層級：
全局權限適用于一個給定服務器中的所有數據庫。這些權限存儲在mysql.user表中。GRANT ALL ON .和REVOKE ALL ON .只授予和撤銷全局權限。
數據庫層級：
數據庫權限適用于一個給定數據庫中的所有目標。這些權限存儲在mysql.db表中。GRANT ALL ON db_name.和REVOKE ALL ON db_name.只授予和撤銷數據庫權限。
表層級：
表權限適用于一個給定表中的所有列。這些權限存儲在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤銷表權限。
列層級：
列權限適用于一個給定表中的單一列。這些權限存儲在mysql.columns_priv表中。當使用REVOKE時，您必須指定與被授權列相同的列。
子程序層級：
CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT權限適用于已存儲的子程序。這些權限可以被授予為全局層級和數據庫層級。而且，除了CREATE ROUTINE外，這些權限可以被授予為子程序層級，并存儲在mysql.procs_priv表中。
這些權限信息存儲在下面的系統表中:
mysql.user
mysql.db
mysql.host
mysql.table_priv
mysql.column_priv
mysql. procs_priv
當用戶連接進來，mysqld會通過上面的這些表對用戶權限進行驗證！
2， 千里追蹤之5表
相對于oracle來說，mysql的特性是可以限制ip，用戶user、ip地址host、密碼passwd這3個是用戶管理的基礎，權限的細節基本在mysql.user、mysql.db、mysql.host、mysql.table_priv、mysql.column_priv這幾張表就可以看到很多細節，接下來仔細分析這些表就可以知道權限的奧秘。
原博客地址： ? ?http://www.php.cn/
原作者：黃杉 (mchdba)
演示過程中需要建立用戶來演示，先簡單介紹下如何創建用戶：
GRANT priv_type ON database.table
TO user[IDENTIFIED BY [PASSWORD] ‘password’]
[,user [IDENTIFIED BY [PASSWORD] ‘password’]…]
示例：
GRANT SELECT, INSERT, UPDATE, DELETE ON d3307.* TO zengxiaoteng@’%’ IDENTIFIED BY ‘0523’;
2.1db表
2.1.1 表結構如下：
mysql>?desc?mysql.db; +-----------------------+---------------+------+-----+---------+-------+ |?Field?????????????????|?Type??????????|?Null?|?Key?|?Default?|?Extra?| +-----------------------+---------------+------+-----+---------+-------+ |?Host??????????????????|?char(60)??????|?NO???|?PRI?|?????????|???????| |?Db????????????????????|?char(64)??????|?NO???|?PRI?|?????????|???????| |?User??????????????????|?char(16)??????|?NO???|?PRI?|?????????|???????| |?Select_priv???????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Insert_priv???????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Update_priv???????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Delete_priv???????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Create_priv???????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Drop_priv?????????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Grant_priv????????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?References_priv???????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Index_priv????????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Alter_priv????????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Create_tmp_table_priv?|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Lock_tables_priv??????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Create_view_priv??????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Show_view_priv????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Create_routine_priv???|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Alter_routine_priv????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Execute_priv??????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Event_priv????????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| |?Trigger_priv??????????|?enum('N','Y')?|?NO???|?????|?N???????|???????| +-----------------------+---------------+------+-----+---------+-------+22?rows?in?set?(0.02?sec) mysql>
2.1.2分析如下：
db表存儲了所有對一個數據庫的所有操作權限。創建用戶的時候，都會往Host字段，User字段，Password字段錄入用戶信息；
而當執行 GRANT SELECT,INSERT ON d3307.* TO u4@’%’ IDENTIFIED BY ‘u40523’;類似的授權語句的話，Select_priv和Insert_priv字段的值會變成Y其它字段仍然是N；
當你執行了GRANT ALL ON d3307.* TO u4@’%’ IDENTIFIED BY ‘u40523’;類似的復制語句的話，后面的字段都會變成Y的值；
2.1.3 創建單個select、insert授予權限
創建用戶：
GRANT?SELECT,INSERT?ON?d3307.*?TO?user4@'192.168.52'?IDENTIFIED?BY?'user0523';
應該除了Host、db、user字段有值，除了Select_priv、Insert_priv值為Y外，其它的都是N。
查看mysql.db表的記錄正是如此，如下所示：
mysql>?SELECT?*?FROM?mysql.`db`?where?user='user4'G; ***************************?1.?row?*************************** ?????????????????Host:?192.168.52 ???????????????????Db:?d3307 ?????????????????User:?user4 ??????????Select_priv:?Y ??????????Insert_priv:?Y ??????????Update_priv:?N ??????????Delete_priv:?N ??????????Create_priv:?N ????????????Drop_priv:?N ???????????Grant_priv:?N ??????References_priv:?N ???????????Index_priv:?N ???????????Alter_priv:?NCreate_tmp_table_priv:?N ?????Lock_tables_priv:?N ?????Create_view_priv:?N ???????Show_view_priv:?N ??Create_routine_priv:?N ???Alter_routine_priv:?N ?????????Execute_priv:?N ???????????Event_priv:?N ?????????Trigger_priv:?N1?row?in?set?(0.01?sec) ERROR:? No?query?specified mysql>
2.1.4 授予ALL權限
執行sql語句建立用戶：
GRANT?ALL?ON?d3307.*?TO?dba5@'192.168.52.1'?IDENTIFIED?BY?'dba0523';
建立用戶的時候，如下所示，除了Host、db、user字段外，所有的*_priv字段記錄都會變成Y值，(Grant_priv仍然是N值除非加了WITH* GRANT OPTION執行GRANT ALL ON d3307.* TO dba5@’192.168.52.1’ IDENTIFIED BY ‘dba0523’ WITH GRANT OPTION ??
如下所示：
mysql>?SELECT?*?FROM?mysql.`db`?where?user='dba5'G;***************************?1.?row?***************************?????????????????Host:?192.168.52.1???????????????????Db:?d3307?????????????????User:?dba5??????????Select_priv:?Y??????????Insert_priv:?Y??????????Update_priv:?Y??????????Delete_priv:?Y??????????Create_priv:?Y????????????Drop_priv:?Y???????????Grant_priv:?N??????References_priv:?Y???????????Index_priv:?Y???????????Alter_priv:?YCreate_tmp_table_priv:?Y?????Lock_tables_priv:?Y?????Create_view_priv:?Y???????Show_view_priv:?Y ??Create_routine_priv:?Y ???Alter_routine_priv:?Y?????????Execute_priv:?Y???????????Event_priv:?Y?????????Trigger_priv:?Y1?row?in?set?(0.00?sec) ERROR:? No?query?specified mysql>
2.2 user表
2.2.1 表結構：
mysql> desc mysql.user; +------------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | | plugin | char(64) | YES | | | | | authentication_string | text | YES | | NULL | | | password_expired | enum('N','Y') | NO | | N | | +------------------------+-----------------------------------+------+-----+---------+-------+ 43 rows in set (0.10 sec) mysql>
2.2.2 分析
存儲用戶記錄的表，存儲了用戶的信息，每一次創建用戶的時候，都會往這個表里錄入記錄，當你執行了，都會往Host字段，User字段，Password字段錄入數據，但是后面的Select_priv、Insert_priv、Update_priv等字段的值，只有賦予GRANT ALL ON . TO timdba@’192.%’ IDENTIFIED BY ‘timdba0523’;類似的對所有庫的操作權限的時候才會被記錄成Y，否則都記錄成N。
2.2.3 創建對庫所有表有操作權限的普通用戶
創建用戶：
GRANT?SELECT,UPDATE?ON?d3307.*?TO?user6@'192.168.52.1'?IDENTIFIED?BY?'user0523';
分析結果：存儲在mysql.user表里面的記錄當中，Host、User、Password是有值的，但是其它的Select_priv等*_priv字段值都是N。
驗證結果，去查看表里的存儲記錄，如下所示：
mysql>?SELECT?*?FROM?mysql.user?where?user='user6'G; ***************************?1.?row?*************************** ??????????????????Host:?192.168.52.1 ??????????????????User:?user6 ??????????????Password:?*A4D1F6ACEBC5D3EB0F6D33C7DCC629E8BE55B75A ???????????Select_priv:?N ???????????Insert_priv:?N ???????????Update_priv:?N ???????????Delete_priv:?N ???????????Create_priv:?N ?????????????Drop_priv:?N ???????????Reload_priv:?N ?????????Shutdown_priv:?N ??????????Process_priv:?N ?????????????File_priv:?N ????????????Grant_priv:?N ???????References_priv:?N ????????????Index_priv:?N ????????????Alter_priv:?N ??????????Show_db_priv:?N ????????????Super_priv:?N ?Create_tmp_table_priv:?N ??????Lock_tables_priv:?N ??????????Execute_priv:?N ???????Repl_slave_priv:?N ??????Repl_client_priv:?N ??????Create_view_priv:?N ????????Show_view_priv:?N ???Create_routine_priv:?N ????Alter_routine_priv:?N ??????Create_user_priv:?N ????????????Event_priv:?N ??????????Trigger_priv:?NCreate_tablespace_priv:?N ??????????????ssl_type:? ????????????ssl_cipher:? ???????????x509_issuer:? ??????????x509_subject:? ?????????max_questions:?0 ???????????max_updates:?0 ???????max_connections:?0 ??max_user_connections:?0 ????????????????plugin:?mysql_native_password ?authentication_string:? ??????password_expired:?N1?row?in?set?(0.00?sec) ERROR:? No?query?specified mysql>
2.2.4 創建對于所有表有操作權限的用戶
創建用戶：
mysql>?GRANT?SELECT,UPDATE?ON?*.*?TO?user7@'%'?IDENTIFIED?BY?'user0523'; Query?OK,?0?rows?affected?(0.00?sec) mysql>
分析：
基本的Host、User、Password字段有記錄值，然后grant了select和update所以關于*_priv字段中select和update字段有值為Y，其它*_priv字段值應該是N。
查看記錄結果，分享正確，如下所示：
mysql>?SELECT?*?FROM?mysql.user?where?user='user7'G; ***************************?1.?row?*************************** ??????????????????Host:?% ??????????????????User:?user7 ??????????????Password:?*A4D1F6ACEBC5D3EB0F6D33C7DCC629E8BE55B75A ???????????Select_priv:?Y ???????????Insert_priv:?N ???????????Update_priv:?Y ???????????Delete_priv:?N ???????????Create_priv:?N ?????????????Drop_priv:?N ???????????Reload_priv:?N ?????????Shutdown_priv:?N ??????????Process_priv:?N ?????????????File_priv:?N ????????????Grant_priv:?N ???????References_priv:?N ????????????Index_priv:?N ????????????Alter_priv:?N ??????????Show_db_priv:?N ????????????Super_priv:?N ?Create_tmp_table_priv:?N ??????Lock_tables_priv:?N ??????????Execute_priv:?N ???????Repl_slave_priv:?N ??????Repl_client_priv:?N ??????Create_view_priv:?N ????????Show_view_priv:?N ???Create_routine_priv:?N ????Alter_routine_priv:?N ??????Create_user_priv:?N ????????????Event_priv:?N ??????????Trigger_priv:?NCreate_tablespace_priv:?N ??????????????ssl_type:? ????????????ssl_cipher:? ???????????x509_issuer:? ??????????x509_subject:? ?????????max_questions:?0 ???????????max_updates:?0 ???????max_connections:?0 ??max_user_connections:?0 ????????????????plugin:?mysql_native_password ?authentication_string:? ??????password_expired:?N1?row?in?set?(0.00?sec) ERROR:? No?query?specified mysql>
2.3 tables_priv表
2.3.1 查看表結構
mysql> desc mysql.tables_priv; +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Table_name | char(64) | NO | PRI | | | | Grantor | char(77) | NO | MUL | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | | | | | Column_priv | set('Select','Insert','Update','References') | NO | | | | +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+ 8 rows in set (0.00 sec) mysql>
2.3.2 分析：
記錄了對一個表的單獨授權記錄，只有執行grant insert on dbname.tablename to user1@’%’identified by ‘pwd’;類似的授權記錄才會在這個表里錄入授權信息；其中各個字段涵義如下：
| 字段 | 存儲的數據 |
|---|---|
| Host字段 | 用戶的登錄ip范圍 |
| User字段 | 表所在的數據庫名稱 |
| Table_name字段 | 授權的表的名稱 |
| Grantor字段 | 執行grant建立用戶的授權者 |
| Timestamp字段 | 0000-00-00 00:00:00 |
| Table_priv字段 | 所授予的操作表的權限，比如select、udate、delete等 |
| Column_priv字段 | 對這個表的某個字段單獨授予的權限 |
另外當賦予all在某張表上的時候，Table_priv列會多處所有關于表的授權記錄，描述如下：
Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger。
2.3.3 創建單獨操作這個表的用戶
創建用戶：
mysql>?GRANT?INSERT,SELECT,UPDATE?ON?d3307.t?TO?user8@'192.168.52.1'?IDENTIFIED?BY?'dba0523'; Query?OK,?0?rows?affected?(0.00?sec) mysql>
分析結果：
應該是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv是有值的，但是Column_priv沒有值，因為沒有單獨對某一個列做了授權限制的。
查看權限，如下所示：
mysql>?SELECT?*?FROM?mysql.tables_priv?where?user='user8'G;***************************?1.?row?***************************???????Host:?192.168.52.1?????????Db:?d3307???????User:?user8 ?Table_name:?t????Grantor:?root@localhost ??Timestamp:?0000-00-00?00:00:00 ?Table_priv:?Select,Insert,Update Column_priv:? 1?row?in?set?(0.00?sec) ERROR:? No?query?specified mysql>
2.3.4 單獨為某個列授權
授權語句操作：
mysql>?GRANT?UPDATE(created_time)?ON?d3307.t??TO?user8@'192.168.52.1'; Query?OK,?0?rows?affected?(0.00?sec) mysql>?GRANT?SELECT(uname)??ON?d3307.t??TO?user8@'192.168.52.1'; Query?OK,?0?rows?affected?(0.00?sec) mysql>
分析：
單獨為某個列授權，會記錄在這個表的Column_priv字段里面，會記錄下對單個列的授權操作記錄
查看記錄：
mysql>?SELECT?*?FROM?mysql.tables_priv?where?user='user8'G;***************************?1.?row?***************************???????Host:?192.168.52.1?????????Db:?d3307???????User:?user8 ?Table_name:?t????Grantor:?root@localhost ??Timestamp:?0000-00-00?00:00:00 ?Table_priv:?Select,Insert,Update Column_priv:?Select,Update 1?row?in?set?(0.00?sec) ERROR:? No?query?specified mysql>
而且還會在另外一個權限表mysql.columns_priv留下記錄單獨的授權記錄，如下所示：
mysql>?SELECT?*?FROM?mysql.columns_priv?WHERE?USER='user8'; +--------------+-------+-------+------------+--------------+---------------------+-------------+|?Host?????????|?Db????|?User??|?Table_name?|?Column_name??|?Timestamp???????????|?Column_priv?| +--------------+-------+-------+------------+--------------+---------------------+-------------+|?192.168.52.1?|?d3307?|?user8?|?t??????????|?created_time?|?0000-00-00?00:00:00?|?Update??????| |?192.168.52.1?|?d3307?|?user8?|?t??????????|?uname????????|?0000-00-00?00:00:00?|?Select??????| +--------------+-------+-------+------------+--------------+---------------------+-------------+ 2?rows?in?set?(0.00?sec)mysql>
2.4 columns_priv表
2.4.1 表結構如下：
mysql> desc mysql.columns_priv; +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Table_name | char(64) | NO | PRI | | | | Column_name | char(64) | NO | PRI | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | Column_priv | set('Select','Insert','Update','References') | NO | | | | +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ 7 rows in set (0.04 sec) mysql>
2.4.2 分析
單獨對某一列有操作權限的時候，會將權限信息記錄在這個表里面，比如新建立一個賬號GRANT UPDATE(uname) ON d3307.t TO user9@’192.168.52.%’ IDENTIFIED BY ‘user0520’; 那么就會在這個表上錄入授權信息記錄，重點看Column_name字段和Column_priv字段的值。
2.4.3 實際操作
創建用戶操作：
mysql>?GRANT?UPDATE(uname)?ON?d3307.t?TO?user9@'192.168.52.%'?IDENTIFIED?BY?'user0520'; Query?OK,?0?rows?affected?(0.00?sec) mysql>
查看結果，會在這個columns_priv表留下一條記錄：
mysql>?SELECT?*?FROM?mysql.columns_priv?WHERE?USER='user9'; ????????+--------------+-------+-------+------------+-------------+---------------------+-------------+ ????????|?Host?????????|?Db????|?User??|?Table_name?|?Column_name?|?Timestamp???????????|?Column_priv?| ????????+--------------+-------+-------+------------+-------------+---------------------+-------------+ ????????|?192.168.52.%?|?d3307?|?user9?|?t??????????|?uname???????|?0000-00-00?00:00:00?|?Update??????| ????????+--------------+-------+-------+------------+-------------+---------------------+-------------+????????1?row?in?set?(0.00?sec) ????????mysql>
2.5 procs_priv表
2.5.1 表結構
mysql>?desc?proxies_priv; +--------------+------------+------+-----+-------------------+-----------------------------+|?Field????????|?Type???????|?Null?|?Key?|?Default???????????|?Extra???????????????????????| +--------------+------------+------+-----+-------------------+-----------------------------+|?Host?????????|?char(60)???|?NO???|?PRI?|???????????????????|?????????????????????????????| |?User?????????|?char(16)???|?NO???|?PRI?|???????????????????|?????????????????????????????| |?Proxied_host?|?char(60)???|?NO???|?PRI?|???????????????????|?????????????????????????????| |?Proxied_user?|?char(16)???|?NO???|?PRI?|???????????????????|?????????????????????????????| |?With_grant???|?tinyint(1)?|?NO???|?????|?0?????????????????|?????????????????????????????| |?Grantor??????|?char(77)???|?NO???|?MUL?|???????????????????|?????????????????????????????| |?Timestamp????|?timestamp??|?NO???|?????|?CURRENT_TIMESTAMP?|?on?update?CURRENT_TIMESTAMP?| +--------------+------------+------+-----+-------------------+-----------------------------+ 7?rows?in?set?(0.04?sec)mysql>
2.6.2分析：
procs_priv表可以對存儲過程和存儲函數進行權限設置。主要字段：proc_priv。
3，創建用戶
3.1、CREATE USER創建用戶
使用CREATE USER語句創建用戶，必須要擁有CREATE USER權限。其格式如下：
CREATE?USER?user[IDENTIFIED?BY?[PASSWORD]?'password'], [user[IDENTIFIED?BY?[PASSWORD]?'password']]...
其中，user參數表示新建用戶的賬戶，user由用戶名(User)和主機名(Host)構成；IDENTIFIED BY關鍵字用來設置用戶的密碼；password參數表示用戶的密碼；如果密碼是一個普通的字符串，就不需要使用PASSWORD關鍵字。可以沒有初始密碼。
例如
CREATE?USER?'sys'@'%'?IDENTIFIED?BY?'sys';
執行之后user表會增加一行記錄，但權限暫時全部為‘N’。
3.2、用INSERT語句新建普通用戶
可以使用INSERT語句直接將用戶的信息添加到mysql.user表。但必須擁有mysql.user表的INSERT權限。
另外，ssl_cipher、x509_issuer、x509_subject等必須要設置值，否則INSERT語句無法執行。
示例：
INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) VALUES(‘%’,’newuser1’,PASSWORD(‘123456’),”,”,”)
執行INSERT之后，要使用命令：FLUSH PRIVILEGES;命令來使用戶生效。
3.3、用GRANT語句來新建普通用戶
用GRANT來創建新的用戶時，能夠在創建用戶時為用戶授權。但需要擁有GRANT權限。
語法如下：
GRANT?priv_type?ON?database.table TO?user[IDENTIFIED?BY?[PASSWORD]?'password'] [,user?[IDENTIFIED?BY?[PASSWORD]?'password']...]
priv_type：參數表示新yoghurt的權限；
databse.table：參數表示新用戶的權限范圍；
user：參數新用戶的賬戶，由用戶名和主機構成；
IDENTIFIED BY關鍵字用來設置密碼；
password：新用戶密碼；
PS：GRANT語句可以同時創建多個用戶。.與db.*的區別在于。.對所有數據庫生效，所以user表的SELECT會變為Y。而db.*user表為’N’，更改的是Db表。
4，刪除用戶
4.1 drop user刪除用戶
DROP USER語句刪除普通用戶，需要擁有DROP USER權限。
語法如下：
DROP?USER?user[,user]...
user是需要刪除的用戶，由用戶名(User)和主機名(Host)構成。
4.2 DELETE語句刪除普通用戶
可以使用DELETE語句直接將用戶的信息從mysql.user表中刪除。但必須擁有對mysql.user表的DELETE權限。DELETE FROM mysql.user WHERE Host = ‘%’ AND User = ‘admin’; 刪除完成后，一樣要FLUSH PRIVILEGES才生效。
5，修改用戶密碼
5.1 使用mysqladmin命令來修改root用戶的密碼
語法：
mysqladmin?-u?-username?-p?password?"new_password"
新密碼(new_password)必須用括號括起來，單引號會報錯。
示例，修改中要輸入舊的密碼來驗證：
[root@data02?~]#?mysqladmin?-u?timman?-p?password?"tim"?--socket=/usr/local/mysql3307/mysql.sock Enter?password:? [root@data02?~]# [root@data02?~]#?mysql?--socket=/usr/local/mysql3307/mysql.sock?-utimman?-ptim?-e?"select?@@port";+--------+|?@@port?| +--------+|???3307?| +--------+[root@data02?~]#
5.2 修改user表
UPDATE user表的passwor字段的值，也可以達到修改密碼的目的；
UPDATE?user?SET?Password?=?PASSWORD('123')?WHERE?USER?=?'myuser';FLUSH?PRIVILEGES;
刷新后生效。
5.3 使用SET語句來修改密碼
使用root用戶登錄到MySQL服務器后，可以使用SET語句來修改密碼：
修改自己的密碼，不需要用戶名
SET?PASSWORD?=?PASSWORD("123");
修改其他用戶密碼：
SET?PASSWORD?FOR?'myuser'@'%'=PASSWORD("123456")?FOR?用戶名@主機名
5.4 GRANT語句來修改普通用戶的密碼
使用GRANT語句修改普通用戶的密碼，必須擁有GRANT權限。
GRANT?priv_type?ON?database.table?TO?user?[IDENTIFIED?BY?[PASSWORD]?'password']
示例：
GRANT?SELECT?ON?*.*?TO?'user10'@'%'?IDENTIFIED?BY?'123'
5.5 忘記用戶密碼的解決辦法
普通用戶，直接用root超級管理員登錄進去修改密碼就可以了，但是如果root密碼丟失了，怎么辦呢？
5.5.1 msyqld_saft方式找回密碼
停止mysql：service mysqld stop;
? ? 安全模式啟動：mysqld_safe –skip-grant-tables &
? ?無密碼回車鍵登錄：mysql -uroot –p
? ?重置密碼：use mysql; update user set password=password(“”) where user=’root’ and host=’localhost’; flush privileges;
? ?正常啟動：service mysql restart
? ?再使用mysqladmin： mysqladmin ?password ‘123456’ ?
5.5.2 ?使用普通賬號來找回密碼
–>(1)：有一個修改test庫的用戶：grant create,delete,update,insert,select on d3307.* to test@’%’ identified by ‘t1’;
–>(2)：復制user表文件到test庫下并且賦予mysql用戶訪問權限： ?
? ?cp /home/data/mysql/data/mysql/user.* /home/data/mysql/data/test/;chown mysql.mysql /home/data/mysql/data/test/user.*
–>(3)：mysql -utest -pt1登錄修改root密碼：
–>(4)：將test庫的user表文件覆蓋 mysql庫的user表文件 ?
? ?cp /home/data/mysql/data/mysql/user.* /tmp/; mv /home/data/mysql/data/test/user.* /home/data/mysql/data/mysql/ ; ? ?chown mysql.mysql /home/data/mysql/data/mysql/user.*;
–>(5):查找mysql進程號，并且發送SIGHUP信號，重新加載權限表。 ?
pgrep -n mysql; kill -SIGHUP 12234;
–>(6):無密碼登錄，再使用mysqladmin重新設置密碼。
PS：請參考第20課的視頻，那里有詳細的記錄整個過修改密碼的過程。
6，收回用戶權限
查看權限：
SHOW?GRANTS;??SHOW?GRANTS?FOR?user10@'%';
或者直接執行sql命令去mysql數據庫下的user表中查看存儲著用戶的基本權限：
SELECT?*?FROM?mysql.user?WHERE?USER='user10'?AND?HOST='%';
使用revoke關鍵字來收回權限：
REVOKE?priv_type[(column_list)]ON?database.tableFROM?user[,user]
示例：
REVOKE?EXECUTE?ON?d3307.*?FROM?user10@'%';
7，數據庫用戶劃分
7.1 普通數據管理用戶：
賦予對業務表的查詢維護權限即可，授權sql如下：
GRANT?SELECT,?INSERT,?UPDATE,?DELETE?ON?d3307.*?TO?zengxiaoteng@'%'?IDENTIFIED?BY?'0523';
7.2 開發人員賬戶：
賦予增刪改查的權限，授權sql如下：
GRANT?SELECT,INSERT,DELETE,UPDATE?ON?d3307.*?TO?huyan@'%'?IDENTIFIED?BY?'0523';
授予創建、修改、刪除 MySQL 數據表結構權限。
GRANT?CREATE?ON?d3307.*??TO?huyan@’192.168.52.11’;GRANT?ALTER??ON?d3307.*?TO?huyan@’192.168.52.11’;GRANT?DROP???ON?d3307.*?TO?huyan@’192.168.52.11’;
授予操作 MySQL 外鍵權限：
GRANT?REFERENCES?ON?d3307.*?TO?huyan@’192.168.52.11’;
授予操作 MySQL 臨時表權限:
GRANT?CREATE?TEMPORARY?TABLES?ON?d3307.*?TO?huyan@’192.168.52.11’;
授予操作 MySQL 索引權限:
GRANT?INDEX?ON?d3307.*?TO?huyan@’192.168.52.11’;
授予操作 MySQL 視圖、查看視圖源代碼 權限：
GRANT?CREATE?VIEW?ON?d3307.*?TO?huyan@’192.168.52.11’;GRANT?SHOW???VIEW?ON?d3307.*?TO?huyan@’192.168.52.11’;
授予操作 MySQL 存儲過程、函數 權限：
GRANT?CREATE?ROUTINE?ON?d3307.*?TO?huyan@’192.168.52.11’;GRANT?ALTER?ROUTINE?ON?d3307.*?TO?huyan@’192.168.52.11’;GRANT?EXECUTE????????ON?d3307.*?TO?huyan@’192.168.52.11’;
7.3 DBA人員賬戶
授予普通DBA管理某個MySQL數據庫(test)的權限：
GRANT?ALL?PRIVILEGES?ON?test?TO?sysdba@'192.168.52.%';
授予高級 DBA 管理 MySQL 中所有數據庫的權限：
GRANT?ALL?ON?*.*?TO?sysdba@'192.168.52.%';
7.4 數據分析人員只讀賬號
只需要分配只讀的權限：
GRANT?SELECT?ON?d3307.*?TO?dataquery@'192.168.52.129'?IDENTIFIED?BY?'20150523';
甚至有些用戶，可以只分配讀取某些表列的權限，如下所示：
GRANT SELECT ON test.* TO dataquery@’192.168.52.%’ IDENTIFIED BY ‘20150523’;
GRANT SELECT(id,uname) ON d3307.t TO dataquery@’192.168.52.%’ ;
示列權限登錄操作：
[root@data02?~]#?mysql?--socket=/usr/local/mysql3307/mysql.sock?-u?dataquery?-p20150523?-h192.168.52.130?-P3307Welcome?TO?the?MySQL?monitor.??Commands?END?WITH?;?OR?g. Your?MySQL?CONNECTION?id?IS?18SERVER?VERSION:?5.6.12-LOG?Source?distribution Copyright?(c)?2000,?2013,?Oracle?AND/OR?its?affiliates.?ALL?rights?reserved. Oracle?IS?a?registered?trademark?of?Oracle?Corporation?AND/OR?its affiliates.?Other?NAMES?may?be?trademarks?of?their?respective owners.TYPE?'help;'?OR?'h'?FOR?help.?TYPE?'c'?TO?clear?the?current?input?statement. mysql>?SELECT?*?FROM?d3307.t; ERROR?1142?(42000):?SELECT?command?denied?TO?USER?'dataquery'@'data02'?FOR?TABLE?'t' mysql> mysql>?SELECT?id,uname?FROM?d3307.t; +----+-------+|?id?|?uname?| +----+-------+|??1?|?a?????| +----+-------+1?ROW?IN?SET?(0.00?sec) mysql>
8，權限劃分一般原則
數據庫一般劃分為線上庫，測試庫，開發庫。
8.1對于線上庫：
DBA：有所有權限，超級管理員權限
應用程序：分配insert、delete、update、select、execute、events、jobs權限。
測試人員：select某些業務表權限 ?
開發人員：select某些業務表權限
原則：所有對線上表的操作，除了應用程序之外，都必須經由DBA來決定是否執行、已經什么時候執行等。
8.2 測試庫
DBA：所有權限。 ?
測試人員：有insert、delete、update、select、execute、jobs權限。
數據分析人員：只有select查詢權限
開發人員：有select權限。
原則：DBA有所有權限，而且嚴格控制表結構的變更，不允許除了dba之外的人對測試環境的庫環境進行修改，以免影響測試人員測試。所有對測試庫的表結構進行的修改必須由測試人員和DBA一起審核過后才能操作。
8.3 開發庫
DBA：所有權限
測試人員：有庫表結構以及數據的所有操作權限。
開發人員：有庫表結構以及數據的所有操作權限。
數據分析人員：有庫表結構以及數據的所有操作權限。
這里大家可以愉快的玩耍了，只要不mysql服務不hang不downtime都OK了。
以上就是MySQL 用戶權限詳細匯總的內容，更多相關內容請關注PHP中文網（www.php.cn）！
