MySQL 用户权限详细汇总
来源：互联网 发布：魔神英雄传 知乎 编辑：程序博客网 时间：2024/05/21 17:01
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://blog.csdn.net/mchdba/article/details/45921045
原作者：黄杉 (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 表结构如下：
<code class="hljs objectivec has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">mysql> desc mysql<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">.db</span>;+-----------------------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------------------+---------------+------+-----+---------+-------+| Host | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">char</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">60</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | PRI | | || Db | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">char</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">64</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | PRI | | || User | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">char</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">16</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | PRI | | || Select_priv | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">enum</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'N'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Y'</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | | N | || Insert_priv | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">enum</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'N'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Y'</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | | N | || Update_priv | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">enum</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'N'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Y'</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | | N | || Delete_priv | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">enum</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'N'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Y'</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | | N | || Create_priv | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">enum</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'N'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Y'</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | | N | || Drop_priv | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">enum</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'N'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Y'</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | | N | || Grant_priv | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">enum</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'N'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Y'</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | | N | || References_priv | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">enum</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'N'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Y'</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | | N | || Index_priv | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">enum</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'N'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Y'</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | | N | || Alter_priv | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">enum</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'N'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Y'</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | | N | || Create_tmp_table_priv | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">enum</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'N'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Y'</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | | N | || Lock_tables_priv | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">enum</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'N'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Y'</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | | N | || Create_view_priv | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">enum</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'N'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Y'</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | | N | || Show_view_priv | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">enum</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'N'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Y'</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | | N | || Create_routine_priv | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">enum</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'N'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Y'</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | | N | || Alter_routine_priv | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">enum</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'N'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Y'</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | | N | || Execute_priv | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">enum</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'N'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Y'</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | | N | || Event_priv | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">enum</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'N'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Y'</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | | N | || Trigger_priv | <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">enum</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'N'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Y'</span>) | <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NO</span> | | N | |+-----------------------+---------------+------+-----+---------+-------+<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">22</span> rows in set (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0.02</span> sec)mysql></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li><li style="box-sizing: border-box; padding: 0px 5px;">28</li><li style="box-sizing: border-box; padding: 0px 5px;">29</li><li style="box-sizing: border-box; padding: 0px 5px;">30</li></ul>
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授予权限
创建用户：
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span>,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> user4@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'192.168.52'</span> IDENTIFIED <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">BY</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'user0523'</span>;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
应该除了Host、db、user字段有值，除了Select_priv、Insert_priv值为Y外，其它的都是N。
查看mysql.db表的记录正是如此，如下所示：
<code class="hljs mathematica has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">mysql> SELECT * FROM mysql.`db` where user=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'user4'</span>\G;*************************** <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1.</span> row *************************** Host: <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">192.168</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.52</span> Db: d3307 User: user4 Select_priv: Y Insert_priv: Y Update_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Delete_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Create_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Drop_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Grant_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> References_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Index_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Alter_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span>Create_tmp_table_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Lock_tables_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Create_view_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Show_view_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Create_routine_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Alter_routine_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Execute_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Event_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Trigger_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> row in set (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0.01</span> sec)ERROR: No query specifiedmysql></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li><li style="box-sizing: border-box; padding: 0px 5px;">28</li><li style="box-sizing: border-box; padding: 0px 5px;">29</li><li style="box-sizing: border-box; padding: 0px 5px;">30</li></ul>
2.1.4 授予ALL权限
执行sql语句建立用户：
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ALL</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> dba5@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'192.168.52.1'</span> IDENTIFIED <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">BY</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'dba0523'</span>;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
建立用户的时候，如下所示，除了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 ;)
如下所示：
<code class="hljs markdown has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">mysql> SELECT * FROM mysql.<span class="hljs-code" style="box-sizing: border-box;">`db`</span> where user='dba5'\G;<span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">** 1. row **</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-code" style="box-sizing: border-box;"> Host: 192.168.52.1</span><span class="hljs-code" style="box-sizing: border-box;"> Db: d3307</span><span class="hljs-code" style="box-sizing: border-box;"> User: dba5</span><span class="hljs-code" style="box-sizing: border-box;"> Select_priv: Y</span><span class="hljs-code" style="box-sizing: border-box;"> Insert_priv: Y</span><span class="hljs-code" style="box-sizing: border-box;"> Update_priv: Y</span><span class="hljs-code" style="box-sizing: border-box;"> Delete_priv: Y</span><span class="hljs-code" style="box-sizing: border-box;"> Create_priv: Y</span><span class="hljs-code" style="box-sizing: border-box;"> Drop_priv: Y</span><span class="hljs-code" style="box-sizing: border-box;"> Grant_priv: N</span><span class="hljs-code" style="box-sizing: border-box;"> References_priv: Y</span><span class="hljs-code" style="box-sizing: border-box;"> Index_priv: Y</span><span class="hljs-code" style="box-sizing: border-box;"> Alter_priv: Y</span>Create<span class="hljs-emphasis" style="box-sizing: border-box;">_tmp_</span>table_priv: Y<span class="hljs-code" style="box-sizing: border-box;"> Lock_tables_priv: Y</span><span class="hljs-code" style="box-sizing: border-box;"> Create_view_priv: Y</span><span class="hljs-code" style="box-sizing: border-box;"> Show_view_priv: Y</span> Create<span class="hljs-emphasis" style="box-sizing: border-box;">_routine_</span>priv: Y Alter<span class="hljs-emphasis" style="box-sizing: border-box;">_routine_</span>priv: Y<span class="hljs-code" style="box-sizing: border-box;"> Execute_priv: Y</span><span class="hljs-code" style="box-sizing: border-box;"> Event_priv: Y</span><span class="hljs-code" style="box-sizing: border-box;"> Trigger_priv: Y</span>1 row in set (0.00 sec)ERROR: No query specifiedmysql></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li><li style="box-sizing: border-box; padding: 0px 5px;">28</li><li style="box-sizing: border-box; padding: 0px 5px;">29</li><li style="box-sizing: border-box; padding: 0px 5px;">30</li></ul>
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 创建对库所有表有操作权限的普通用户
创建用户：
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span>,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">UPDATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> user6@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'192.168.52.1'</span> IDENTIFIED <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">BY</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'user0523'</span>;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
分析结果：存储在mysql.user表里面的记录当中，Host、User、Password是有值的，但是其它的Select_priv等*_priv字段值都是N。
验证结果，去查看表里的存储记录，如下所示：
<code class="hljs mathematica has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">mysql> SELECT * FROM mysql.user where user=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'user6'</span>\G;*************************** <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1.</span> row *************************** Host: <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">192.168</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.52</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.1</span> User: user6 Password: *A4D1F6ACEBC5D3EB0F6D33C7DCC629E8BE55B75A Select_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Insert_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Update_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Delete_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Create_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Drop_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Reload_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Shutdown_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Process_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> File_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Grant_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> References_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Index_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Alter_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Show_db_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Super_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Create_tmp_table_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Lock_tables_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Execute_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Repl_slave_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Repl_client_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Create_view_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Show_view_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Create_routine_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Alter_routine_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Create_user_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Event_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Trigger_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span>Create_tablespace_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span> max_updates: <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span> max_connections: <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span> max_user_connections: <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span> plugin: mysql_native_password authentication_string: password_expired: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> row in set (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0.00</span> sec)ERROR: No query specifiedmysql></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li><li style="box-sizing: border-box; padding: 0px 5px;">28</li><li style="box-sizing: border-box; padding: 0px 5px;">29</li><li style="box-sizing: border-box; padding: 0px 5px;">30</li><li style="box-sizing: border-box; padding: 0px 5px;">31</li><li style="box-sizing: border-box; padding: 0px 5px;">32</li><li style="box-sizing: border-box; padding: 0px 5px;">33</li><li style="box-sizing: border-box; padding: 0px 5px;">34</li><li style="box-sizing: border-box; padding: 0px 5px;">35</li><li style="box-sizing: border-box; padding: 0px 5px;">36</li><li style="box-sizing: border-box; padding: 0px 5px;">37</li><li style="box-sizing: border-box; padding: 0px 5px;">38</li><li style="box-sizing: border-box; padding: 0px 5px;">39</li><li style="box-sizing: border-box; padding: 0px 5px;">40</li><li style="box-sizing: border-box; padding: 0px 5px;">41</li><li style="box-sizing: border-box; padding: 0px 5px;">42</li><li style="box-sizing: border-box; padding: 0px 5px;">43</li><li style="box-sizing: border-box; padding: 0px 5px;">44</li><li style="box-sizing: border-box; padding: 0px 5px;">45</li><li style="box-sizing: border-box; padding: 0px 5px;">46</li><li style="box-sizing: border-box; padding: 0px 5px;">47</li><li style="box-sizing: border-box; padding: 0px 5px;">48</li><li style="box-sizing: border-box; padding: 0px 5px;">49</li><li style="box-sizing: border-box; padding: 0px 5px;">50</li><li style="box-sizing: border-box; padding: 0px 5px;">51</li></ul>
2.2.4 创建对于所有表有操作权限的用户
创建用户：
<code class="hljs lasso has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">mysql<span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">></span> GRANT <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span>,UPDATE <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> <span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">*</span><span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">.</span><span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">*</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> user7@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%'</span> IDENTIFIED <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">BY</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'user0523'</span>;Query OK, <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">rows</span> affected (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0.00</span> sec)mysql<span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">></span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li></ul>
分析：
基本的Host、User、Password字段有记录值，然后grant了select和update所以关于*_priv字段中select和update字段有值为Y，其它*_priv字段值应该是N。
查看记录结果，分享正确，如下所示：
<code class="hljs mathematica has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">mysql> SELECT * FROM mysql.user where user=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'user7'</span>\G;*************************** <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1.</span> row *************************** Host: % User: user7 Password: *A4D1F6ACEBC5D3EB0F6D33C7DCC629E8BE55B75A Select_priv: Y Insert_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Update_priv: Y Delete_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Create_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Drop_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Reload_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Shutdown_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Process_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> File_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Grant_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> References_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Index_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Alter_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Show_db_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Super_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Create_tmp_table_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Lock_tables_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Execute_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Repl_slave_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Repl_client_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Create_view_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Show_view_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Create_routine_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Alter_routine_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Create_user_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Event_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> Trigger_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span>Create_tablespace_priv: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span> ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span> max_updates: <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span> max_connections: <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span> max_user_connections: <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span> plugin: mysql_native_password authentication_string: password_expired: <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">N</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> row in set (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0.00</span> sec)ERROR: No query specifiedmysql></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li><li style="box-sizing: border-box; padding: 0px 5px;">28</li><li style="box-sizing: border-box; padding: 0px 5px;">29</li><li style="box-sizing: border-box; padding: 0px 5px;">30</li><li style="box-sizing: border-box; padding: 0px 5px;">31</li><li style="box-sizing: border-box; padding: 0px 5px;">32</li><li style="box-sizing: border-box; padding: 0px 5px;">33</li><li style="box-sizing: border-box; padding: 0px 5px;">34</li><li style="box-sizing: border-box; padding: 0px 5px;">35</li><li style="box-sizing: border-box; padding: 0px 5px;">36</li><li style="box-sizing: border-box; padding: 0px 5px;">37</li><li style="box-sizing: border-box; padding: 0px 5px;">38</li><li style="box-sizing: border-box; padding: 0px 5px;">39</li><li style="box-sizing: border-box; padding: 0px 5px;">40</li><li style="box-sizing: border-box; padding: 0px 5px;">41</li><li style="box-sizing: border-box; padding: 0px 5px;">42</li><li style="box-sizing: border-box; padding: 0px 5px;">43</li><li style="box-sizing: border-box; padding: 0px 5px;">44</li><li style="box-sizing: border-box; padding: 0px 5px;">45</li><li style="box-sizing: border-box; padding: 0px 5px;">46</li><li style="box-sizing: border-box; padding: 0px 5px;">47</li><li style="box-sizing: border-box; padding: 0px 5px;">48</li><li style="box-sizing: border-box; padding: 0px 5px;">49</li><li style="box-sizing: border-box; padding: 0px 5px;">50</li><li style="box-sizing: border-box; padding: 0px 5px;">51</li></ul>
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’;类似的授权记录才会在这个表里录入授权信息；其中各个字段涵义如下：
另外当赋予all在某张表上的时候，Table_priv列会多处所有关于表的授权记录，描述如下：
Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger。
2.3.3 创建单独操作这个表的用户
创建用户：
<code class="hljs lasso has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">mysql<span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">></span> GRANT INSERT,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span>,UPDATE <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">.</span>t <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> user8@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'192.168.52.1'</span> IDENTIFIED <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">BY</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'dba0523'</span>;Query OK, <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">rows</span> affected (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0.00</span> sec)mysql<span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">></span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li></ul>
分析结果：
应该是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv是有值的，但是Column_priv没有值，因为没有单独对某一个列做了授权限制的。
查看权限，如下所示：
<code class="hljs markdown has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">mysql> SELECT * FROM mysql.tables_priv where user='user8'\G;<span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">** 1. row **</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-code" style="box-sizing: border-box;"> Host: 192.168.52.1</span><span class="hljs-code" style="box-sizing: border-box;"> Db: d3307</span><span class="hljs-code" style="box-sizing: border-box;"> User: user8</span> Table_name: t<span class="hljs-code" style="box-sizing: border-box;"> Grantor: root@localhost</span> Timestamp: 0000-00-00 00:00:00 Table_priv: Select,Insert,UpdateColumn_priv: 1 row in set (0.00 sec)ERROR: No query specifiedmysql></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li></ul>
2.3.4 单独为某个列授权
授权语句操作：
<code class="hljs lasso has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">mysql<span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">></span> GRANT UPDATE(created_time) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">.</span>t <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> user8@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'192.168.52.1'</span>;Query OK, <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">rows</span> affected (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0.00</span> sec)mysql<span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">></span> GRANT <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span>(uname) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">.</span>t <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> user8@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'192.168.52.1'</span>;Query OK, <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">rows</span> affected (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0.00</span> sec)mysql<span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">></span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li></ul>
分析：
单独为某个列授权，会记录在这个表的Column_priv字段里面，会记录下对单个列的授权操作记录
查看记录：
<code class="hljs markdown has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">mysql> SELECT * FROM mysql.tables_priv where user='user8'\G;<span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">** 1. row **</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-strong" style="box-sizing: border-box;">*****</span><span class="hljs-code" style="box-sizing: border-box;"> Host: 192.168.52.1</span><span class="hljs-code" style="box-sizing: border-box;"> Db: d3307</span><span class="hljs-code" style="box-sizing: border-box;"> User: user8</span> Table_name: t<span class="hljs-code" style="box-sizing: border-box;"> Grantor: root@localhost</span> Timestamp: 0000-00-00 00:00:00 Table_priv: Select,Insert,UpdateColumn_priv: Select,Update1 row in set (0.00 sec)ERROR: No query specifiedmysql></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li></ul>
而且还会在另外一个权限表mysql.columns_priv留下记录单独的授权记录，如下所示：
<code class="hljs asciidoc has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-header" style="box-sizing: border-box;">mysql> SELECT * FROM mysql.columns_priv WHERE USER='user8';+--------------+-------+-------+------------+--------------+---------------------+-------------+</span><span class="hljs-header" style="box-sizing: border-box;">| Host | Db | User | Table_name | Column_name | Timestamp | Column_priv |+--------------+-------+-------+------------+--------------+---------------------+-------------+</span>| 192.168.52.1 | d3307 | user8 | t | created<span class="hljs-emphasis" style="box-sizing: border-box;">_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)</span>mysql></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li></ul>
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 实际操作
创建用户操作：
<code class="hljs lasso has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">mysql<span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">></span> GRANT UPDATE(uname) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">.</span>t <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> user9@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'192.168.52.%'</span> IDENTIFIED <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">BY</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'user0520'</span>;Query OK, <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">rows</span> affected (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0.00</span> sec)mysql<span class="hljs-subst" style="color: rgb(0, 0, 0); box-sizing: border-box;">></span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li></ul>
查看结果，会在这个columns_priv表留下一条记录：
<code class="hljs smalltalk has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">mysql> <span class="hljs-class" style="box-sizing: border-box; color: rgb(102, 0, 102);">SELECT</span> * <span class="hljs-class" style="box-sizing: border-box; color: rgb(102, 0, 102);">FROM</span> mysql.columns_priv <span class="hljs-class" style="box-sizing: border-box; color: rgb(102, 0, 102);">WHERE</span> <span class="hljs-class" style="box-sizing: border-box; color: rgb(102, 0, 102);">USER</span>=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'user9'</span>; +--------------+-------+-------+------------+-------------+---------------------+-------------+ | <span class="hljs-class" style="box-sizing: border-box; color: rgb(102, 0, 102);">Host</span> | <span class="hljs-class" style="box-sizing: border-box; color: rgb(102, 0, 102);">Db</span> | <span class="hljs-class" style="box-sizing: border-box; color: rgb(102, 0, 102);">User</span> | <span class="hljs-class" style="box-sizing: border-box; color: rgb(102, 0, 102);">Table_name</span> | <span class="hljs-class" style="box-sizing: border-box; color: rgb(102, 0, 102);">Column_name</span> | <span class="hljs-class" style="box-sizing: border-box; color: rgb(102, 0, 102);">Timestamp</span> | <span class="hljs-class" style="box-sizing: border-box; color: rgb(102, 0, 102);">Column_priv</span> | +--------------+-------+-------+------------+-------------+---------------------+-------------+ | <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">192.168</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.52</span>.% <span class="hljs-localvars" style="box-sizing: border-box;">| d3307 | user9 | t | uname |</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0000</span>-<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">00</span>-<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">00</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">00</span>:<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">00</span>:<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">00</span> | <span class="hljs-class" style="box-sizing: border-box; color: rgb(102, 0, 102);">Update</span> | +--------------+-------+-------+------------+-------------+---------------------+-------------+ <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> row in set (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0.00</span> sec) mysql></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li></ul>
2.5 procs_priv表
2.5.1 表结构
<code class="hljs asciidoc has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-header" style="box-sizing: border-box;">mysql> desc proxies_priv;+--------------+------------+------+-----+-------------------+-----------------------------+</span><span class="hljs-header" style="box-sizing: border-box;">| Field | Type | Null | Key | Default | Extra |+--------------+------------+------+-----+-------------------+-----------------------------+</span>| Host | char(60) | NO | PRI | | || User | char(16) | NO | PRI | | || Proxied<span class="hljs-emphasis" style="box-sizing: border-box;">_host | char(60) | NO | PRI | | || Proxied_</span>user | char(16) | NO | PRI | | || With<span class="hljs-emphasis" style="box-sizing: border-box;">_grant | tinyint(1) | NO | | 0 | || Grantor | char(77) | NO | MUL | | || Timestamp | timestamp | NO | | CURRENT_</span>TIMESTAMP | on update CURRENT<span class="hljs-emphasis" style="box-sizing: border-box;">_TIMESTAMP |+--------------+------------+------+-----+-------------------+-----------------------------+7 rows in set (0.04 sec)</span>mysql></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li></ul>
2.6.2分析：
procs_priv表可以对存储过程和存储函数进行权限设置。主要字段：proc_priv。
3，创建用户
3.1、CREATE USER创建用户
使用CREATE USER语句创建用户，必须要拥有CREATE USER权限。其格式如下：
<code class="hljs r has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">CREATE USER user[IDENTIFIED BY [PASSWORD] <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'password'</span>],[user[IDENTIFIED BY [PASSWORD] <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'password'</span>]]<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">...</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li></ul>
其中，user参数表示新建用户的账户，user由用户名(User)和主机名(Host)构成；IDENTIFIED BY关键字用来设置用户的密码；password参数表示用户的密码；如果密码是一个普通的字符串，就不需要使用PASSWORD关键字。可以没有初始密码。
例如
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CREATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">USER</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'sys'</span>@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%'</span> IDENTIFIED <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">BY</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'sys'</span>;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
执行之后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权限。
语法如下：
<code class="hljs r has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">GRANT priv_type ON database.tableTO user[IDENTIFIED BY [PASSWORD] <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'password'</span>][,user [IDENTIFIED BY [PASSWORD] <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'password'</span>]<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">...</span>]</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul>
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权限。
语法如下：
<code class="hljs r has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">DROP USER user[,user]<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">...</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
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用户的密码
语法：
<code class="hljs lasso has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">mysqladmin <span class="hljs-attribute" style="box-sizing: border-box;">-u</span> <span class="hljs-attribute" style="box-sizing: border-box;">-username</span> <span class="hljs-attribute" style="box-sizing: border-box;">-p</span> password <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"new_password"</span> </code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
新密码(new_password)必须用括号括起来，单引号会报错。
示例，修改中要输入旧的密码来验证：
<code class="hljs asciidoc has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">[root@data02 ~]# mysqladmin -u timman -p password "tim" --socket=/usr/local/mysql3307/mysql.sockEnter password: [root@data02 ~]#[root@data02 ~]# mysql --socket=/usr/local/mysql3307/mysql.sock -utimman -ptim -e "select @@port";<span class="hljs-code" style="box-sizing: border-box;">+--------+</span><span class="hljs-header" style="box-sizing: border-box;">| @@port |+--------+</span><span class="hljs-header" style="box-sizing: border-box;">| 3307 |+--------+</span>[root@data02 ~]#</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li></ul>
5.2 修改user表
UPDATE user表的passwor字段的值，也可以达到修改密码的目的；
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">UPDATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">user</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SET</span> Password = PASSWORD(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'123'</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHERE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">USER</span> = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'myuser'</span>;</span>FLUSH PRIVILEGES;</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li></ul>
刷新后生效。
5.3 使用SET语句来修改密码
使用root用户登录到MySQL服务器后，可以使用SET语句来修改密码：
修改自己的密码，不需要用户名
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SET</span> PASSWORD = PASSWORD(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"123"</span>);</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
修改其他用户密码：
<code class="hljs ruleslanguage has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SET</span> PASSWORD <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FOR</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'myuser'</span>@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%'</span>=PASSWORD(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"123456"</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FOR</span> 用户名@主机名</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
5.4 GRANT语句来修改普通用户的密码
使用GRANT语句修改普通用户的密码，必须拥有GRANT权限。
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> priv_type <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">database</span>.<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">table</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">user</span> [IDENTIFIED <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">BY</span> [PASSWORD] <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'password'</span>]</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
示例：
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> *.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'user10'</span>@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%'</span> IDENTIFIED <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">BY</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'123'</span></span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
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，收回用户权限
查看权限：
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SHOW</span> GRANTS;</span> <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SHOW</span> GRANTS <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FOR</span> user10@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%'</span>;</span> </code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
或者直接执行sql命令去mysql数据库下的user表中查看存储着用户的基本权限：
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span> * <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FROM</span> mysql.<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">user</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHERE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">USER</span>=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'user10'</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AND</span> HOST=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%'</span>;</span> </code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
使用revoke关键字来收回权限：
<code class="hljs css has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-tag" style="color: rgb(0, 0, 0); box-sizing: border-box;">REVOKE</span> <span class="hljs-tag" style="color: rgb(0, 0, 0); box-sizing: border-box;">priv_type</span><span class="hljs-attr_selector" style="color: rgb(0, 136, 0); box-sizing: border-box;">[(column_list)]</span><span class="hljs-tag" style="color: rgb(0, 0, 0); box-sizing: border-box;">ON</span> <span class="hljs-tag" style="color: rgb(0, 0, 0); box-sizing: border-box;">database</span><span class="hljs-class" style="box-sizing: border-box; color: rgb(155, 112, 63);">.table</span><span class="hljs-tag" style="color: rgb(0, 0, 0); box-sizing: border-box;">FROM</span> <span class="hljs-tag" style="color: rgb(0, 0, 0); box-sizing: border-box;">user</span><span class="hljs-attr_selector" style="color: rgb(0, 136, 0); box-sizing: border-box;">[,user]</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul>
示例：
<code class="hljs vbnet has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">REVOKE EXECUTE <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FROM</span> user10@<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">'%';</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
7，数据库用户划分
7.1 普通数据管理用户：
赋予对业务表的查询维护权限即可，授权sql如下：
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span>, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span>, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">UPDATE</span>, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">DELETE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> zengxiaoteng@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%'</span> IDENTIFIED <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">BY</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'0523'</span>;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
7.2 开发人员账户：
赋予增删改查的权限，授权sql如下：
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span>,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span>,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">DELETE</span>,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">UPDATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> huyan@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%'</span> IDENTIFIED <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">BY</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'0523'</span>;</span> </code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
授予创建、修改、删除 MySQL 数据表结构权限。
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CREATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> huyan@’<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">192.168</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.52</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.11</span>’;</span><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ALTER</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> huyan@’<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">192.168</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.52</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.11</span>’;</span><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">DROP</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> huyan@’<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">192.168</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.52</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.11</span>’;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul>
授予操作 MySQL 外键权限：
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">REFERENCES</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> huyan@’<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">192.168</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.52</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.11</span>’;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
授予操作 MySQL 临时表权限:
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CREATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TEMPORARY</span> TABLES <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> huyan@’<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">192.168</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.52</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.11</span>’;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
授予操作 MySQL 索引权限:
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> INDEX <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> huyan@’<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">192.168</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.52</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.11</span>’;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
授予操作 MySQL 视图、查看视图源代码 权限：
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CREATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VIEW</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> huyan@’<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">192.168</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.52</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.11</span>’;</span><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SHOW</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VIEW</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> huyan@’<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">192.168</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.52</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.11</span>’;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li></ul>
授予操作 MySQL 存储过程、函数 权限：
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CREATE</span> ROUTINE <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> huyan@’<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">192.168</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.52</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.11</span>’;</span><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ALTER</span> ROUTINE <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> huyan@’<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">192.168</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.52</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.11</span>’;</span><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">EXECUTE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> huyan@’<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">192.168</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.52</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.11</span>’;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul>
7.3 DBA人员账户
授予普通DBA管理某个MySQL数据库(test)的权限：
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ALL</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">PRIVILEGES</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> test <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> sysdba@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'192.168.52.%'</span>;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
授予高级 DBA 管理 MySQL 中所有数据库的权限：
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ALL</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> *.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> sysdba@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'192.168.52.%'</span>;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
7.4 数据分析人员只读账号
只需要分配只读的权限：
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">GRANT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> d3307.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> dataquery@<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'192.168.52.129'</span> IDENTIFIED <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">BY</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'20150523'</span>;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
甚至有些用户，可以只分配读取某些表列的权限，如下所示：
GRANT SELECT ON test.* TO dataquery@’192.168.52.%’ IDENTIFIED BY ‘20150523’;
GRANT SELECT(id,uname) ON d3307.t TO dataquery@’192.168.52.%’ ;
示列权限登录操作：
<code class="hljs vhdl has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">[root@data02 ~]# mysql <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">--socket=/usr/local/mysql3307/mysql.sock -u dataquery -p20150523 -h192.168.52.130 -P3307</span>Welcome <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> the MySQL monitor. Commands <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">END</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WITH</span> ; <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">OR</span> \g.Your MySQL CONNECTION id <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">IS</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">18</span>SERVER VERSION: <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">5.6</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">.12</span>-LOG Source distributionCopyright (c) <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2000</span>, <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2013</span>, Oracle <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AND</span>/<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">OR</span> its affiliates. <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ALL</span> rights reserved.Oracle <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">IS</span> a registered trademark <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">of</span> Oracle Corporation <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AND</span>/<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">OR</span> itsaffiliates. Other NAMES may be trademarks <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">of</span> their respectiveowners.<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TYPE</span> <span class="hljs-attribute" style="box-sizing: border-box;">'help</span>;' <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">OR</span> '\h' <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FOR</span> help. <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TYPE</span> '\c' <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> clear the current input statement.mysql> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span> * FROM d3307.t;ERROR <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1142</span> (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">42000</span>): <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span> command denied <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TO</span> USER <span class="hljs-attribute" style="box-sizing: border-box;">'dataquery</span>'@<span class="hljs-attribute" style="box-sizing: border-box;">'data02</span>' <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FOR</span> TABLE <span class="hljs-attribute" style="box-sizing: border-box;">'t</span>'mysql>mysql> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span> id,uname FROM d3307.t;+<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">----+-------+</span>| id | uname |+<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">----+-------+</span>| <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> | a |+<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">----+-------+</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> ROW <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">IN</span> SET (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0.00</span> sec)mysql></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li></ul>
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了。
0 0
- MySQL 用户权限详细汇总
- MySQL 用户权限详细汇总
- MySQL 用户权限详细汇总
- MySQL 用户权限详细汇总
- MySQL用户权限
- MySQL用户权限
- mysql用户权限
- MySql 用户权限
- Mysql 用户权限
- mysql 用户权限
- mysql 用户权限
- MySQL用户权限
- mysql 用户权限
- MySQL用户权限
- MySQL 用户权限
- Mysql用户权限
- Mysql 用户权限
- mysql-用户权限
- 【PHP学习】输出数据方面的方法
- 2015.7.14 树状数组及其初步应用
- hdu5289Assignment
- mybatis异常
- Android自定义弹窗效果
- MySQL 用户权限详细汇总
- oc003-set和get方法
- Palindrome Linked List
- IOS--UI--LessonParse 数据解析 XML JSON
- STL--关于权重问题的解决
- EventBus使用详解
- Socket编程《三》
- 【POJ】【3624】
- [leetcode] Trapping Rain Water
