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这几张表就可以看到很多细节，接下来仔细分析这些表就可以知道权限的奥秘。
5121c5d8c817ad4c086d9118458eb6a9
原博客地址： http://www.php.cn/
原作者：黄杉 (mchdba)
演示过程中需要建立用户来演示，先简单介绍下如何创建用户：
GRANT priv_type ON database.table
TO user[IDENTIFIED BY [PASSWORD] ‘password’]
[,user [IDENTIFIED BY [PASSWORD] ‘password’]…]
示例：
GRANT SELECT, INSERT, UPDATE, DELETE ON d3307.* TO zengxiaoteng@’%’ IDENTIFIED BY ‘0523’;
2.1db表
2.1.1 表结构如下：
mysql> desc mysql.db; +-----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | +-----------------------+---------------+------+-----+---------+-------+22 rows in set (0.02 sec) mysql>
2.1.2分析如下：
db表存储了所有对一个数据库的所有操作权限。创建用户的时候，都会往Host字段，User字段，Password字段录入用户信息；
而当执行 GRANT SELECT,INSERT ON d3307.* TO u4@’%’ IDENTIFIED BY ‘u40523’;类似的授权语句的话，Select_priv和Insert_priv字段的值会变成Y其它字段仍然是N；
当你执行了GRANT ALL ON d3307.* TO u4@’%’ IDENTIFIED BY ‘u40523’;类似的复制语句的话，后面的字段都会变成Y的值；
2.1.3 创建单个select、insert授予权限
创建用户：
GRANT SELECT,INSERT ON d3307.* TO user4@'192.168.52' IDENTIFIED BY 'user0523';
应该除了Host、db、user字段有值，除了Select_priv、Insert_priv值为Y外，其它的都是N。
查看mysql.db表的记录正是如此，如下所示：
mysql> SELECT * FROM mysql.`db` where user='user4'\G;
*************************** 1. row ***************************
Host: 192.168.52
Db: d3307
User: user4
Select_priv: Y
Insert_priv: Y
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: NCreate_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N1 row in set (0.01 sec)
ERROR:
No query specified
mysql>2.1.4 授予ALL权限
执行sql语句建立用户：
GRANT ALL ON d3307.* TO dba5@'192.168.52.1' IDENTIFIED BY 'dba0523';
建立用户的时候，如下所示，除了Host、db、user字段外，所有的*_priv字段记录都会变成Y值，(Grant_priv仍然是N值除非加了WITH* GRANT OPTION执行GRANT ALL ON d3307.* TO dba5@’192.168.52.1’ IDENTIFIED BY ‘dba0523’ WITH GRANT OPTION ;)
如下所示：
mysql> SELECT * FROM mysql.`db` where user='dba5'\G;*************************** 1. row *************************** Host: 192.168.52.1 Db: d3307 User: dba5 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: YCreate_tmp_table_priv: Y Lock_tables_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Execute_priv: Y Event_priv: Y Trigger_priv: Y1 row in set (0.00 sec) ERROR: No query specified mysql>
2.2 user表
2.2.1 表结构：
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.10 sec)
mysql>
2.2.2 分析
存储用户记录的表，存储了用户的信息，每一次创建用户的时候，都会往这个表里录入记录，当你执行了，都会往Host字段，User字段，Password字段录入数据，但是后面的Select_priv、Insert_priv、Update_priv等字段的值，只有赋予GRANT ALL ON . TO timdba@’192.%’ IDENTIFIED BY ‘timdba0523’;类似的对所有库的操作权限的时候才会被记录成Y，否则都记录成N。
2.2.3 创建对库所有表有操作权限的普通用户
创建用户：
GRANT SELECT,UPDATE ON d3307.* TO user6@'192.168.52.1' IDENTIFIED BY 'user0523';
分析结果：存储在mysql.user表里面的记录当中，Host、User、Password是有值的，但是其它的Select_priv等*_priv字段值都是N。
验证结果，去查看表里的存储记录，如下所示：
mysql> SELECT * FROM mysql.user where user='user6'\G;
*************************** 1. row ***************************
Host: 192.168.52.1
User: user6
Password: *A4D1F6ACEBC5D3EB0F6D33C7DCC629E8BE55B75A
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: NCreate_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N1 row in set (0.00 sec)
ERROR:
No query specified
mysql>2.2.4 创建对于所有表有操作权限的用户
创建用户：
mysql> GRANT SELECT,UPDATE ON *.* TO user7@'%' IDENTIFIED BY 'user0523'; Query OK, 0 rows affected (0.00 sec) mysql>
分析：
基本的Host、User、Password字段有记录值，然后grant了select和update所以关于*_priv字段中select和update字段有值为Y，其它*_priv字段值应该是N。
查看记录结果，分享正确，如下所示：
mysql> SELECT * FROM mysql.user where user='user7'\G;
*************************** 1. row ***************************
Host: %
User: user7
Password: *A4D1F6ACEBC5D3EB0F6D33C7DCC629E8BE55B75A
Select_priv: Y
Insert_priv: N
Update_priv: Y
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: NCreate_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N1 row in set (0.00 sec)
ERROR:
No query specified
mysql>2.3 tables_priv表
2.3.1 查看表结构
mysql> desc mysql.tables_priv;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Grantor | char(77) | NO | MUL | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | | | |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)
mysql>
2.3.2 分析：
记录了对一个表的单独授权记录，只有执行grant insert on dbname.tablename to user1@’%’identified by ‘pwd’;类似的授权记录才会在这个表里录入授权信息；其中各个字段涵义如下：
| 字段 | 存储的数据 |
|---|---|
| Host字段 | 用户的登录ip范围 |
| User字段 | 表所在的数据库名称 |
| Table_name字段 | 授权的表的名称 |
| Grantor字段 | 执行grant建立用户的授权者 |
| Timestamp字段 | 0000-00-00 00:00:00 |
| Table_priv字段 | 所授予的操作表的权限，比如select、udate、delete等 |
| Column_priv字段 | 对这个表的某个字段单独授予的权限 |
另外当赋予all在某张表上的时候，Table_priv列会多处所有关于表的授权记录，描述如下：
Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger。
2.3.3 创建单独操作这个表的用户
创建用户：
mysql> GRANT INSERT,SELECT,UPDATE ON d3307.t TO user8@'192.168.52.1' IDENTIFIED BY 'dba0523'; Query OK, 0 rows affected (0.00 sec) mysql>
分析结果：
应该是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv是有值的，但是Column_priv没有值，因为没有单独对某一个列做了授权限制的。
查看权限，如下所示：
mysql> SELECT * FROM mysql.tables_priv where user='user8'\G;*************************** 1. row *************************** Host: 192.168.52.1 Db: d3307 User: user8 Table_name: t Grantor: root@localhost Timestamp: 0000-00-00 00:00:00 Table_priv: Select,Insert,Update Column_priv: 1 row in set (0.00 sec) ERROR: No query specified mysql>
2.3.4 单独为某个列授权
授权语句操作：
mysql> GRANT UPDATE(created_time) ON d3307.t TO user8@'192.168.52.1'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT SELECT(uname) ON d3307.t TO user8@'192.168.52.1'; Query OK, 0 rows affected (0.00 sec) mysql>
分析：
单独为某个列授权，会记录在这个表的Column_priv字段里面，会记录下对单个列的授权操作记录
查看记录：
mysql> SELECT * FROM mysql.tables_priv where user='user8'\G;*************************** 1. row *************************** Host: 192.168.52.1 Db: d3307 User: user8 Table_name: t Grantor: root@localhost Timestamp: 0000-00-00 00:00:00 Table_priv: Select,Insert,Update Column_priv: Select,Update 1 row in set (0.00 sec) ERROR: No query specified mysql>
而且还会在另外一个权限表mysql.columns_priv留下记录单独的授权记录，如下所示：
mysql> SELECT * FROM mysql.columns_priv WHERE USER='user8'; +--------------+-------+-------+------------+--------------+---------------------+-------------+| Host | Db | User | Table_name | Column_name | Timestamp | Column_priv | +--------------+-------+-------+------------+--------------+---------------------+-------------+| 192.168.52.1 | d3307 | user8 | t | created_time | 0000-00-00 00:00:00 | Update | | 192.168.52.1 | d3307 | user8 | t | uname | 0000-00-00 00:00:00 | Select | +--------------+-------+-------+------------+--------------+---------------------+-------------+ 2 rows in set (0.00 sec)mysql>
2.4 columns_priv表
2.4.1 表结构如下：
mysql> desc mysql.columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Column_name | char(64) | NO | PRI | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.04 sec)
mysql>
2.4.2 分析
单独对某一列有操作权限的时候，会将权限信息记录在这个表里面，比如新建立一个账号GRANT UPDATE(uname) ON d3307.t TO user9@’192.168.52.%’ IDENTIFIED BY ‘user0520’; 那么就会在这个表上录入授权信息记录，重点看Column_name字段和Column_priv字段的值。
2.4.3 实际操作
创建用户操作：
mysql> GRANT UPDATE(uname) ON d3307.t TO user9@'192.168.52.%' IDENTIFIED BY 'user0520'; Query OK, 0 rows affected (0.00 sec) mysql>
查看结果，会在这个columns_priv表留下一条记录：
mysql> SELECT * FROM mysql.columns_priv WHERE USER='user9';
+--------------+-------+-------+------------+-------------+---------------------+-------------+
| Host | Db | User | Table_name | Column_name | Timestamp | Column_priv |
+--------------+-------+-------+------------+-------------+---------------------+-------------+
| 192.168.52.% | d3307 | user9 | t | uname | 0000-00-00 00:00:00 | Update |
+--------------+-------+-------+------------+-------------+---------------------+-------------+ 1 row in set (0.00 sec)
mysql>2.5 procs_priv表
2.5.1 表结构
mysql> desc proxies_priv; +--------------+------------+------+-----+-------------------+-----------------------------+| Field | Type | Null | Key | Default | Extra | +--------------+------------+------+-----+-------------------+-----------------------------+| Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Proxied_host | char(60) | NO | PRI | | | | Proxied_user | char(16) | NO | PRI | | | | With_grant | tinyint(1) | NO | | 0 | | | Grantor | char(77) | NO | MUL | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +--------------+------------+------+-----+-------------------+-----------------------------+ 7 rows in set (0.04 sec)mysql>
2.6.2分析：
procs_priv表可以对存储过程和存储函数进行权限设置。主要字段：proc_priv。
3，创建用户
3.1、CREATE USER创建用户
使用CREATE USER语句创建用户，必须要拥有CREATE USER权限。其格式如下：
CREATE USER user[IDENTIFIED BY [PASSWORD] 'password'], [user[IDENTIFIED BY [PASSWORD] 'password']]...
其中，user参数表示新建用户的账户，user由用户名(User)和主机名(Host)构成；IDENTIFIED BY关键字用来设置用户的密码；password参数表示用户的密码；如果密码是一个普通的字符串，就不需要使用PASSWORD关键字。可以没有初始密码。
例如
CREATE USER 'sys'@'%' IDENTIFIED BY 'sys';
执行之后user表会增加一行记录，但权限暂时全部为‘N’。
3.2、用INSERT语句新建普通用户
可以使用INSERT语句直接将用户的信息添加到mysql.user表。但必须拥有mysql.user表的INSERT权限。
另外，ssl_cipher、x509_issuer、x509_subject等必须要设置值，否则INSERT语句无法执行。
示例：
INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) VALUES(‘%’,’newuser1’,PASSWORD(‘123456’),”,”,”)
执行INSERT之后，要使用命令：FLUSH PRIVILEGES;命令来使用户生效。
3.3、用GRANT语句来新建普通用户
用GRANT来创建新的用户时，能够在创建用户时为用户授权。但需要拥有GRANT权限。
语法如下：
GRANT priv_type ON database.table TO user[IDENTIFIED BY [PASSWORD] 'password'] [,user [IDENTIFIED BY [PASSWORD] 'password']...]
priv_type：参数表示新yoghurt的权限；
databse.table：参数表示新用户的权限范围；
user：参数新用户的账户，由用户名和主机构成；
IDENTIFIED BY关键字用来设置密码；
password：新用户密码；
PS：GRANT语句可以同时创建多个用户。.与db.*的区别在于。.对所有数据库生效，所以user表的SELECT会变为Y。而db.*user表为’N’，更改的是Db表。
4，删除用户
4.1 drop user删除用户
DROP USER语句删除普通用户，需要拥有DROP USER权限。
语法如下：
DROP USER user[,user]...
user是需要删除的用户，由用户名(User)和主机名(Host)构成。
4.2 DELETE语句删除普通用户
可以使用DELETE语句直接将用户的信息从mysql.user表中删除。但必须拥有对mysql.user表的DELETE权限。DELETE FROM mysql.user WHERE Host = ‘%’ AND User = ‘admin’; 删除完成后，一样要FLUSH PRIVILEGES才生效。
5，修改用户密码
5.1 使用mysqladmin命令来修改root用户的密码
语法：
mysqladmin -u -username -p password "new_password"
新密码(new_password)必须用括号括起来，单引号会报错。
示例，修改中要输入旧的密码来验证：
[root@data02 ~]# mysqladmin -u timman -p password "tim" --socket=/usr/local/mysql3307/mysql.sock Enter password: [root@data02 ~]# [root@data02 ~]# mysql --socket=/usr/local/mysql3307/mysql.sock -utimman -ptim -e "select @@port";+--------+| @@port | +--------+| 3307 | +--------+[root@data02 ~]#
5.2 修改user表
UPDATE user表的passwor字段的值，也可以达到修改密码的目的；
UPDATE user SET Password = PASSWORD('123') WHERE USER = 'myuser';FLUSH PRIVILEGES;
刷新后生效。
5.3 使用SET语句来修改密码
使用root用户登录到MySQL服务器后，可以使用SET语句来修改密码：
修改自己的密码，不需要用户名
SET PASSWORD = PASSWORD("123");修改其他用户密码：
SET PASSWORD FOR 'myuser'@'%'=PASSWORD("123456") FOR 用户名@主机名5.4 GRANT语句来修改普通用户的密码
使用GRANT语句修改普通用户的密码，必须拥有GRANT权限。
GRANT priv_type ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password']
示例：
GRANT SELECT ON *.* TO 'user10'@'%' IDENTIFIED BY '123'
5.5 忘记用户密码的解决办法
普通用户，直接用root超级管理员登录进去修改密码就可以了，但是如果root密码丢失了，怎么办呢？
5.5.1 msyqld_saft方式找回密码
停止mysql：service mysqld stop;
安全模式启动：mysqld_safe –skip-grant-tables &
无密码回车键登录：mysql -uroot –p
重置密码：use mysql; update user set password=password(“”) where user=’root’ and host=’localhost’; flush privileges;
正常启动：service mysql restart
再使用mysqladmin： mysqladmin password ‘123456’
5.5.2 使用普通账号来找回密码
–>(1)：有一个修改test库的用户：grant create,delete,update,insert,select on d3307.* to test@’%’ identified by ‘t1’;
–>(2)：复制user表文件到test库下并且赋予mysql用户访问权限：
cp /home/data/mysql/data/mysql/user.* /home/data/mysql/data/test/;chown mysql.mysql /home/data/mysql/data/test/user.*
–>(3)：mysql -utest -pt1登录修改root密码：
–>(4)：将test库的user表文件覆盖 mysql库的user表文件
cp /home/data/mysql/data/mysql/user.* /tmp/; mv /home/data/mysql/data/test/user.* /home/data/mysql/data/mysql/ ; chown mysql.mysql /home/data/mysql/data/mysql/user.*;
–>(5):查找mysql进程号，并且发送SIGHUP信号，重新加载权限表。
pgrep -n mysql; kill -SIGHUP 12234;
–>(6):无密码登录，再使用mysqladmin重新设置密码。
PS：请参考第20课的视频，那里有详细的记录整个过修改密码的过程。
6，收回用户权限
查看权限：
SHOW GRANTS; SHOW GRANTS FOR user10@'%';
或者直接执行sql命令去mysql数据库下的user表中查看存储着用户的基本权限：
SELECT * FROM mysql.user WHERE USER='user10' AND HOST='%';
使用revoke关键字来收回权限：
REVOKE priv_type[(column_list)]ON database.tableFROM user[,user]
示例：
REVOKE EXECUTE ON d3307.* FROM user10@'%';
7，数据库用户划分
7.1 普通数据管理用户：
赋予对业务表的查询维护权限即可，授权sql如下：
GRANT SELECT, INSERT, UPDATE, DELETE ON d3307.* TO zengxiaoteng@'%' IDENTIFIED BY '0523';
7.2 开发人员账户：
赋予增删改查的权限，授权sql如下：
GRANT SELECT,INSERT,DELETE,UPDATE ON d3307.* TO huyan@'%' IDENTIFIED BY '0523';
授予创建、修改、删除 MySQL 数据表结构权限。
GRANT CREATE ON d3307.* TO huyan@’192.168.52.11’;GRANT ALTER ON d3307.* TO huyan@’192.168.52.11’;GRANT DROP ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 外键权限：
GRANT REFERENCES ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 临时表权限:
GRANT CREATE TEMPORARY TABLES ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 索引权限:
GRANT INDEX ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 视图、查看视图源代码 权限：
GRANT CREATE VIEW ON d3307.* TO huyan@’192.168.52.11’;GRANT SHOW VIEW ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 存储过程、函数 权限：
GRANT CREATE ROUTINE ON d3307.* TO huyan@’192.168.52.11’;GRANT ALTER ROUTINE ON d3307.* TO huyan@’192.168.52.11’;GRANT EXECUTE ON d3307.* TO huyan@’192.168.52.11’;
7.3 DBA人员账户
授予普通DBA管理某个MySQL数据库(test)的权限：
GRANT ALL PRIVILEGES ON test TO sysdba@'192.168.52.%';
授予高级 DBA 管理 MySQL 中所有数据库的权限：
GRANT ALL ON *.* TO sysdba@'192.168.52.%';
7.4 数据分析人员只读账号
只需要分配只读的权限：
GRANT SELECT ON d3307.* TO dataquery@'192.168.52.129' IDENTIFIED BY '20150523';
甚至有些用户，可以只分配读取某些表列的权限，如下所示：
GRANT SELECT ON test.* TO dataquery@’192.168.52.%’ IDENTIFIED BY ‘20150523’;
GRANT SELECT(id,uname) ON d3307.t TO dataquery@’192.168.52.%’ ;
示列权限登录操作：
[root@data02 ~]# mysql --socket=/usr/local/mysql3307/mysql.sock -u dataquery -p20150523 -h192.168.52.130 -P3307Welcome TO the MySQL monitor. Commands END WITH ; OR \g. Your MySQL CONNECTION id IS 18SERVER VERSION: 5.6.12-LOG Source distribution Copyright (c) 2000, 2013, Oracle AND/OR its affiliates. ALL rights reserved. Oracle IS a registered trademark of Oracle Corporation AND/OR its affiliates. Other NAMES may be trademarks of their respective owners.TYPE 'help;' OR '\h' FOR help. TYPE '\c' TO clear the current input statement. mysql> SELECT * FROM d3307.t; ERROR 1142 (42000): SELECT command denied TO USER 'dataquery'@'data02' FOR TABLE 't' mysql> mysql> SELECT id,uname FROM d3307.t; +----+-------+| id | uname | +----+-------+| 1 | a | +----+-------+1 ROW IN SET (0.00 sec) mysql>
8，权限划分一般原则
数据库一般划分为线上库，测试库，开发库。
8.1对于线上库：
DBA：有所有权限，超级管理员权限
应用程序：分配insert、delete、update、select、execute、events、jobs权限。
测试人员：select某些业务表权限
开发人员：select某些业务表权限
原则：所有对线上表的操作，除了应用程序之外，都必须经由DBA来决定是否执行、已经什么时候执行等。
8.2 测试库
DBA：所有权限。
测试人员：有insert、delete、update、select、execute、jobs权限。
数据分析人员：只有select查询权限
开发人员：有select权限。
原则：DBA有所有权限，而且严格控制表结构的变更，不允许除了dba之外的人对测试环境的库环境进行修改，以免影响测试人员测试。所有对测试库的表结构进行的修改必须由测试人员和DBA一起审核过后才能操作。
8.3 开发库
DBA：所有权限
测试人员：有库表结构以及数据的所有操作权限。
开发人员：有库表结构以及数据的所有操作权限。
数据分析人员：有库表结构以及数据的所有操作权限。
这里大家可以愉快的玩耍了，只要不mysql服务不hang不downtime都OK了。
参考文章资料：
http://www.php.cn/
http://www.php.cn/
http://www.php.cn/
http://www.php.cn/
1，MySQL权限体系
mysql 的权限体系大致分为5个层级:
全局层级：
全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON .和REVOKE ALL ON .只授予和撤销全局权限。
数据库层级：
数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db表中。GRANT ALL ON db_name.和REVOKE ALL ON db_name.只授予和撤销数据库权限。
表层级：
表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。
列层级：
列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，您必须指定与被授权列相同的列。
子程序层级：
CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。
这些权限信息存储在下面的系统表中:
mysql.user
mysql.db
mysql.host
mysql.table_priv
mysql.column_priv
mysql. procs_priv
当用户连接进来，mysqld会通过上面的这些表对用户权限进行验证！
2， 千里追踪之5表
相对于oracle来说，mysql的特性是可以限制ip，用户user、ip地址host、密码passwd这3个是用户管理的基础，权限的细节基本在mysql.user、mysql.db、mysql.host、mysql.table_priv、mysql.column_priv这几张表就可以看到很多细节，接下来仔细分析这些表就可以知道权限的奥秘。
5121c5d8c817ad4c086d9118458eb6a9
原博客地址： http://www.php.cn/
原作者：黄杉 (mchdba)
演示过程中需要建立用户来演示，先简单介绍下如何创建用户：
GRANT priv_type ON database.table
TO user[IDENTIFIED BY [PASSWORD] ‘password’]
[,user [IDENTIFIED BY [PASSWORD] ‘password’]…]
示例：
GRANT SELECT, INSERT, UPDATE, DELETE ON d3307.* TO zengxiaoteng@’%’ IDENTIFIED BY ‘0523’;
2.1db表
2.1.1 表结构如下：
mysql> desc mysql.db; +-----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | +-----------------------+---------------+------+-----+---------+-------+22 rows in set (0.02 sec) mysql>
2.1.2分析如下：
db表存储了所有对一个数据库的所有操作权限。创建用户的时候，都会往Host字段，User字段，Password字段录入用户信息；
而当执行 GRANT SELECT,INSERT ON d3307.* TO u4@’%’ IDENTIFIED BY ‘u40523’;类似的授权语句的话，Select_priv和Insert_priv字段的值会变成Y其它字段仍然是N；
当你执行了GRANT ALL ON d3307.* TO u4@’%’ IDENTIFIED BY ‘u40523’;类似的复制语句的话，后面的字段都会变成Y的值；
2.1.3 创建单个select、insert授予权限
创建用户：
GRANT SELECT,INSERT ON d3307.* TO user4@'192.168.52' IDENTIFIED BY 'user0523';
应该除了Host、db、user字段有值，除了Select_priv、Insert_priv值为Y外，其它的都是N。
查看mysql.db表的记录正是如此，如下所示：
mysql> SELECT * FROM mysql.`db` where user='user4'\G;
*************************** 1. row ***************************
Host: 192.168.52
Db: d3307
User: user4
Select_priv: Y
Insert_priv: Y
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: NCreate_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N1 row in set (0.01 sec)
ERROR:
No query specified
mysql>2.1.4 授予ALL权限
执行sql语句建立用户：
GRANT ALL ON d3307.* TO dba5@'192.168.52.1' IDENTIFIED BY 'dba0523';
建立用户的时候，如下所示，除了Host、db、user字段外，所有的*_priv字段记录都会变成Y值，(Grant_priv仍然是N值除非加了WITH* GRANT OPTION执行GRANT ALL ON d3307.* TO dba5@’192.168.52.1’ IDENTIFIED BY ‘dba0523’ WITH GRANT OPTION ;)
如下所示：
mysql> SELECT * FROM mysql.`db` where user='dba5'\G;*************************** 1. row *************************** Host: 192.168.52.1 Db: d3307 User: dba5 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: YCreate_tmp_table_priv: Y Lock_tables_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Execute_priv: Y Event_priv: Y Trigger_priv: Y1 row in set (0.00 sec) ERROR: No query specified mysql>
2.2 user表
2.2.1 表结构：
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.10 sec)
mysql>
2.2.2 分析
存储用户记录的表，存储了用户的信息，每一次创建用户的时候，都会往这个表里录入记录，当你执行了，都会往Host字段，User字段，Password字段录入数据，但是后面的Select_priv、Insert_priv、Update_priv等字段的值，只有赋予GRANT ALL ON . TO timdba@’192.%’ IDENTIFIED BY ‘timdba0523’;类似的对所有库的操作权限的时候才会被记录成Y，否则都记录成N。
2.2.3 创建对库所有表有操作权限的普通用户
创建用户：
GRANT SELECT,UPDATE ON d3307.* TO user6@'192.168.52.1' IDENTIFIED BY 'user0523';
分析结果：存储在mysql.user表里面的记录当中，Host、User、Password是有值的，但是其它的Select_priv等*_priv字段值都是N。
验证结果，去查看表里的存储记录，如下所示：
mysql> SELECT * FROM mysql.user where user='user6'\G;
*************************** 1. row ***************************
Host: 192.168.52.1
User: user6
Password: *A4D1F6ACEBC5D3EB0F6D33C7DCC629E8BE55B75A
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: NCreate_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N1 row in set (0.00 sec)
ERROR:
No query specified
mysql>2.2.4 创建对于所有表有操作权限的用户
创建用户：
mysql> GRANT SELECT,UPDATE ON *.* TO user7@'%' IDENTIFIED BY 'user0523'; Query OK, 0 rows affected (0.00 sec) mysql>
分析：
基本的Host、User、Password字段有记录值，然后grant了select和update所以关于*_priv字段中select和update字段有值为Y，其它*_priv字段值应该是N。
查看记录结果，分享正确，如下所示：
mysql> SELECT * FROM mysql.user where user='user7'\G;
*************************** 1. row ***************************
Host: %
User: user7
Password: *A4D1F6ACEBC5D3EB0F6D33C7DCC629E8BE55B75A
Select_priv: Y
Insert_priv: N
Update_priv: Y
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: NCreate_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N1 row in set (0.00 sec)
ERROR:
No query specified
mysql>2.3 tables_priv表
2.3.1 查看表结构
mysql> desc mysql.tables_priv;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Grantor | char(77) | NO | MUL | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | | | |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)
mysql>
2.3.2 分析：
记录了对一个表的单独授权记录，只有执行grant insert on dbname.tablename to user1@’%’identified by ‘pwd’;类似的授权记录才会在这个表里录入授权信息；其中各个字段涵义如下：
| 字段 | 存储的数据 |
|---|---|
| Host字段 | 用户的登录ip范围 |
| User字段 | 表所在的数据库名称 |
| Table_name字段 | 授权的表的名称 |
| Grantor字段 | 执行grant建立用户的授权者 |
| Timestamp字段 | 0000-00-00 00:00:00 |
| Table_priv字段 | 所授予的操作表的权限，比如select、udate、delete等 |
| Column_priv字段 | 对这个表的某个字段单独授予的权限 |
另外当赋予all在某张表上的时候，Table_priv列会多处所有关于表的授权记录，描述如下：
Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger。
2.3.3 创建单独操作这个表的用户
创建用户：
mysql> GRANT INSERT,SELECT,UPDATE ON d3307.t TO user8@'192.168.52.1' IDENTIFIED BY 'dba0523'; Query OK, 0 rows affected (0.00 sec) mysql>
分析结果：
应该是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv是有值的，但是Column_priv没有值，因为没有单独对某一个列做了授权限制的。
查看权限，如下所示：
mysql> SELECT * FROM mysql.tables_priv where user='user8'\G;*************************** 1. row *************************** Host: 192.168.52.1 Db: d3307 User: user8 Table_name: t Grantor: root@localhost Timestamp: 0000-00-00 00:00:00 Table_priv: Select,Insert,Update Column_priv: 1 row in set (0.00 sec) ERROR: No query specified mysql>
2.3.4 单独为某个列授权
授权语句操作：
mysql> GRANT UPDATE(created_time) ON d3307.t TO user8@'192.168.52.1'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT SELECT(uname) ON d3307.t TO user8@'192.168.52.1'; Query OK, 0 rows affected (0.00 sec) mysql>
分析：
单独为某个列授权，会记录在这个表的Column_priv字段里面，会记录下对单个列的授权操作记录
查看记录：
mysql> SELECT * FROM mysql.tables_priv where user='user8'\G;*************************** 1. row *************************** Host: 192.168.52.1 Db: d3307 User: user8 Table_name: t Grantor: root@localhost Timestamp: 0000-00-00 00:00:00 Table_priv: Select,Insert,Update Column_priv: Select,Update 1 row in set (0.00 sec) ERROR: No query specified mysql>
而且还会在另外一个权限表mysql.columns_priv留下记录单独的授权记录，如下所示：
mysql> SELECT * FROM mysql.columns_priv WHERE USER='user8'; +--------------+-------+-------+------------+--------------+---------------------+-------------+| Host | Db | User | Table_name | Column_name | Timestamp | Column_priv | +--------------+-------+-------+------------+--------------+---------------------+-------------+| 192.168.52.1 | d3307 | user8 | t | created_time | 0000-00-00 00:00:00 | Update | | 192.168.52.1 | d3307 | user8 | t | uname | 0000-00-00 00:00:00 | Select | +--------------+-------+-------+------------+--------------+---------------------+-------------+ 2 rows in set (0.00 sec)mysql>
2.4 columns_priv表
2.4.1 表结构如下：
mysql> desc mysql.columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Column_name | char(64) | NO | PRI | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.04 sec)
mysql>
2.4.2 分析
单独对某一列有操作权限的时候，会将权限信息记录在这个表里面，比如新建立一个账号GRANT UPDATE(uname) ON d3307.t TO user9@’192.168.52.%’ IDENTIFIED BY ‘user0520’; 那么就会在这个表上录入授权信息记录，重点看Column_name字段和Column_priv字段的值。
2.4.3 实际操作
创建用户操作：
mysql> GRANT UPDATE(uname) ON d3307.t TO user9@'192.168.52.%' IDENTIFIED BY 'user0520'; Query OK, 0 rows affected (0.00 sec) mysql>
查看结果，会在这个columns_priv表留下一条记录：
mysql> SELECT * FROM mysql.columns_priv WHERE USER='user9';
+--------------+-------+-------+------------+-------------+---------------------+-------------+
| Host | Db | User | Table_name | Column_name | Timestamp | Column_priv |
+--------------+-------+-------+------------+-------------+---------------------+-------------+
| 192.168.52.% | d3307 | user9 | t | uname | 0000-00-00 00:00:00 | Update |
+--------------+-------+-------+------------+-------------+---------------------+-------------+ 1 row in set (0.00 sec)
mysql>2.5 procs_priv表
2.5.1 表结构
mysql> desc proxies_priv; +--------------+------------+------+-----+-------------------+-----------------------------+| Field | Type | Null | Key | Default | Extra | +--------------+------------+------+-----+-------------------+-----------------------------+| Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Proxied_host | char(60) | NO | PRI | | | | Proxied_user | char(16) | NO | PRI | | | | With_grant | tinyint(1) | NO | | 0 | | | Grantor | char(77) | NO | MUL | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +--------------+------------+------+-----+-------------------+-----------------------------+ 7 rows in set (0.04 sec)mysql>
2.6.2分析：
procs_priv表可以对存储过程和存储函数进行权限设置。主要字段：proc_priv。
3，创建用户
3.1、CREATE USER创建用户
使用CREATE USER语句创建用户，必须要拥有CREATE USER权限。其格式如下：
CREATE USER user[IDENTIFIED BY [PASSWORD] 'password'], [user[IDENTIFIED BY [PASSWORD] 'password']]...
其中，user参数表示新建用户的账户，user由用户名(User)和主机名(Host)构成；IDENTIFIED BY关键字用来设置用户的密码；password参数表示用户的密码；如果密码是一个普通的字符串，就不需要使用PASSWORD关键字。可以没有初始密码。
例如
CREATE USER 'sys'@'%' IDENTIFIED BY 'sys';
执行之后user表会增加一行记录，但权限暂时全部为‘N’。
3.2、用INSERT语句新建普通用户
可以使用INSERT语句直接将用户的信息添加到mysql.user表。但必须拥有mysql.user表的INSERT权限。
另外，ssl_cipher、x509_issuer、x509_subject等必须要设置值，否则INSERT语句无法执行。
示例：
INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) VALUES(‘%’,’newuser1’,PASSWORD(‘123456’),”,”,”)
执行INSERT之后，要使用命令：FLUSH PRIVILEGES;命令来使用户生效。
3.3、用GRANT语句来新建普通用户
用GRANT来创建新的用户时，能够在创建用户时为用户授权。但需要拥有GRANT权限。
语法如下：
GRANT priv_type ON database.table TO user[IDENTIFIED BY [PASSWORD] 'password'] [,user [IDENTIFIED BY [PASSWORD] 'password']...]
priv_type：参数表示新yoghurt的权限；
databse.table：参数表示新用户的权限范围；
user：参数新用户的账户，由用户名和主机构成；
IDENTIFIED BY关键字用来设置密码；
password：新用户密码；
PS：GRANT语句可以同时创建多个用户。.与db.*的区别在于。.对所有数据库生效，所以user表的SELECT会变为Y。而db.*user表为’N’，更改的是Db表。
4，删除用户
4.1 drop user删除用户
DROP USER语句删除普通用户，需要拥有DROP USER权限。
语法如下：
DROP USER user[,user]...
user是需要删除的用户，由用户名(User)和主机名(Host)构成。
4.2 DELETE语句删除普通用户
可以使用DELETE语句直接将用户的信息从mysql.user表中删除。但必须拥有对mysql.user表的DELETE权限。DELETE FROM mysql.user WHERE Host = ‘%’ AND User = ‘admin’; 删除完成后，一样要FLUSH PRIVILEGES才生效。
5，修改用户密码
5.1 使用mysqladmin命令来修改root用户的密码
语法：
mysqladmin -u -username -p password "new_password"
新密码(new_password)必须用括号括起来，单引号会报错。
示例，修改中要输入旧的密码来验证：
[root@data02 ~]# mysqladmin -u timman -p password "tim" --socket=/usr/local/mysql3307/mysql.sock Enter password: [root@data02 ~]# [root@data02 ~]# mysql --socket=/usr/local/mysql3307/mysql.sock -utimman -ptim -e "select @@port";+--------+| @@port | +--------+| 3307 | +--------+[root@data02 ~]#
5.2 修改user表
UPDATE user表的passwor字段的值，也可以达到修改密码的目的；
UPDATE user SET Password = PASSWORD('123') WHERE USER = 'myuser';FLUSH PRIVILEGES;
刷新后生效。
5.3 使用SET语句来修改密码
使用root用户登录到MySQL服务器后，可以使用SET语句来修改密码：
修改自己的密码，不需要用户名
SET PASSWORD = PASSWORD("123");修改其他用户密码：
SET PASSWORD FOR 'myuser'@'%'=PASSWORD("123456") FOR 用户名@主机名5.4 GRANT语句来修改普通用户的密码
使用GRANT语句修改普通用户的密码，必须拥有GRANT权限。
GRANT priv_type ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password']
示例：
GRANT SELECT ON *.* TO 'user10'@'%' IDENTIFIED BY '123'
5.5 忘记用户密码的解决办法
普通用户，直接用root超级管理员登录进去修改密码就可以了，但是如果root密码丢失了，怎么办呢？
5.5.1 msyqld_saft方式找回密码
停止mysql：service mysqld stop;
安全模式启动：mysqld_safe –skip-grant-tables &
无密码回车键登录：mysql -uroot –p
重置密码：use mysql; update user set password=password(“”) where user=’root’ and host=’localhost’; flush privileges;
正常启动：service mysql restart
再使用mysqladmin： mysqladmin password ‘123456’
5.5.2 使用普通账号来找回密码
–>(1)：有一个修改test库的用户：grant create,delete,update,insert,select on d3307.* to test@’%’ identified by ‘t1’;
–>(2)：复制user表文件到test库下并且赋予mysql用户访问权限：
cp /home/data/mysql/data/mysql/user.* /home/data/mysql/data/test/;chown mysql.mysql /home/data/mysql/data/test/user.*
–>(3)：mysql -utest -pt1登录修改root密码：
–>(4)：将test库的user表文件覆盖 mysql库的user表文件
cp /home/data/mysql/data/mysql/user.* /tmp/; mv /home/data/mysql/data/test/user.* /home/data/mysql/data/mysql/ ; chown mysql.mysql /home/data/mysql/data/mysql/user.*;
–>(5):查找mysql进程号，并且发送SIGHUP信号，重新加载权限表。
pgrep -n mysql; kill -SIGHUP 12234;
–>(6):无密码登录，再使用mysqladmin重新设置密码。
PS：请参考第20课的视频，那里有详细的记录整个过修改密码的过程。
6，收回用户权限
查看权限：
SHOW GRANTS; SHOW GRANTS FOR user10@'%';
或者直接执行sql命令去mysql数据库下的user表中查看存储着用户的基本权限：
SELECT * FROM mysql.user WHERE USER='user10' AND HOST='%';
使用revoke关键字来收回权限：
REVOKE priv_type[(column_list)]ON database.tableFROM user[,user]
示例：
REVOKE EXECUTE ON d3307.* FROM user10@'%';
7，数据库用户划分
7.1 普通数据管理用户：
赋予对业务表的查询维护权限即可，授权sql如下：
GRANT SELECT, INSERT, UPDATE, DELETE ON d3307.* TO zengxiaoteng@'%' IDENTIFIED BY '0523';
7.2 开发人员账户：
赋予增删改查的权限，授权sql如下：
GRANT SELECT,INSERT,DELETE,UPDATE ON d3307.* TO huyan@'%' IDENTIFIED BY '0523';
授予创建、修改、删除 MySQL 数据表结构权限。
GRANT CREATE ON d3307.* TO huyan@’192.168.52.11’;GRANT ALTER ON d3307.* TO huyan@’192.168.52.11’;GRANT DROP ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 外键权限：
GRANT REFERENCES ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 临时表权限:
GRANT CREATE TEMPORARY TABLES ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 索引权限:
GRANT INDEX ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 视图、查看视图源代码 权限：
GRANT CREATE VIEW ON d3307.* TO huyan@’192.168.52.11’;GRANT SHOW VIEW ON d3307.* TO huyan@’192.168.52.11’;
授予操作 MySQL 存储过程、函数 权限：
GRANT CREATE ROUTINE ON d3307.* TO huyan@’192.168.52.11’;GRANT ALTER ROUTINE ON d3307.* TO huyan@’192.168.52.11’;GRANT EXECUTE ON d3307.* TO huyan@’192.168.52.11’;
7.3 DBA人员账户
授予普通DBA管理某个MySQL数据库(test)的权限：
GRANT ALL PRIVILEGES ON test TO sysdba@'192.168.52.%';
授予高级 DBA 管理 MySQL 中所有数据库的权限：
GRANT ALL ON *.* TO sysdba@'192.168.52.%';
7.4 数据分析人员只读账号
只需要分配只读的权限：
GRANT SELECT ON d3307.* TO dataquery@'192.168.52.129' IDENTIFIED BY '20150523';
甚至有些用户，可以只分配读取某些表列的权限，如下所示：
GRANT SELECT ON test.* TO dataquery@’192.168.52.%’ IDENTIFIED BY ‘20150523’;
GRANT SELECT(id,uname) ON d3307.t TO dataquery@’192.168.52.%’ ;
示列权限登录操作：
[root@data02 ~]# mysql --socket=/usr/local/mysql3307/mysql.sock -u dataquery -p20150523 -h192.168.52.130 -P3307Welcome TO the MySQL monitor. Commands END WITH ; OR \g. Your MySQL CONNECTION id IS 18SERVER VERSION: 5.6.12-LOG Source distribution Copyright (c) 2000, 2013, Oracle AND/OR its affiliates. ALL rights reserved. Oracle IS a registered trademark of Oracle Corporation AND/OR its affiliates. Other NAMES may be trademarks of their respective owners.TYPE 'help;' OR '\h' FOR help. TYPE '\c' TO clear the current input statement. mysql> SELECT * FROM d3307.t; ERROR 1142 (42000): SELECT command denied TO USER 'dataquery'@'data02' FOR TABLE 't' mysql> mysql> SELECT id,uname FROM d3307.t; +----+-------+| id | uname | +----+-------+| 1 | a | +----+-------+1 ROW IN SET (0.00 sec) mysql>
8，权限划分一般原则
数据库一般划分为线上库，测试库，开发库。
8.1对于线上库：
DBA：有所有权限，超级管理员权限
应用程序：分配insert、delete、update、select、execute、events、jobs权限。
测试人员：select某些业务表权限
开发人员：select某些业务表权限
原则：所有对线上表的操作，除了应用程序之外，都必须经由DBA来决定是否执行、已经什么时候执行等。
8.2 测试库
DBA：所有权限。
测试人员：有insert、delete、update、select、execute、jobs权限。
数据分析人员：只有select查询权限
开发人员：有select权限。
原则：DBA有所有权限，而且严格控制表结构的变更，不允许除了dba之外的人对测试环境的库环境进行修改，以免影响测试人员测试。所有对测试库的表结构进行的修改必须由测试人员和DBA一起审核过后才能操作。
8.3 开发库
DBA：所有权限
测试人员：有库表结构以及数据的所有操作权限。
开发人员：有库表结构以及数据的所有操作权限。
数据分析人员：有库表结构以及数据的所有操作权限。
这里大家可以愉快的玩耍了，只要不mysql服务不hang不downtime都OK了。
以上就是MySQL 用户权限详细汇总的内容，更多相关内容请关注PHP中文网（www.php.cn）！
MySQL은 sqlite와 어떻게 다릅니 까?Apr 24, 2025 am 12:12 AMMySQL과 Sqlite의 주요 차이점은 설계 개념 및 사용 시나리오입니다. 1. MySQL은 대규모 응용 프로그램 및 엔터프라이즈 수준의 솔루션에 적합하며 고성능 및 동시성을 지원합니다. 2. SQLITE는 모바일 애플리케이션 및 데스크탑 소프트웨어에 적합하며 가볍고 내부질이 쉽습니다.
MySQL의 색인이란 무엇이며 성능을 어떻게 향상 시키는가?Apr 24, 2025 am 12:09 AMMySQL의 인덱스는 데이터 검색 속도를 높이는 데 사용되는 데이터베이스 테이블에서 하나 이상의 열의 주문 구조입니다. 1) 인덱스는 스캔 한 데이터의 양을 줄임으로써 쿼리 속도를 향상시킵니다. 2) B-Tree Index는 균형 잡힌 트리 구조를 사용하여 범위 쿼리 및 정렬에 적합합니다. 3) CreateIndex 문을 사용하여 CreateIndexIdx_customer_idonorders (customer_id)와 같은 인덱스를 작성하십시오. 4) Composite Indexes는 CreateIndexIdx_customer_orderOders (Customer_id, Order_Date)와 같은 다중 열 쿼리를 최적화 할 수 있습니다. 5) 설명을 사용하여 쿼리 계획을 분석하고 피하십시오
MySQL에서 트랜잭션을 사용하여 데이터 일관성을 보장하는 방법을 설명하십시오.Apr 24, 2025 am 12:09 AMMySQL에서 트랜잭션을 사용하면 데이터 일관성이 보장됩니다. 1) STARTTRANSACTION을 통해 트랜잭션을 시작한 다음 SQL 작업을 실행하고 커밋 또는 롤백으로 제출하십시오. 2) SavePoint를 사용하여 부분 롤백을 허용하는 저장 지점을 설정하십시오. 3) 성능 최적화 제안에는 트랜잭션 시간 단축, 대규모 쿼리 방지 및 격리 수준을 합리적으로 사용하는 것이 포함됩니다.
MySQL을 통해 어떤 시나리오에서 PostgreSQL을 선택할 수 있습니까?Apr 24, 2025 am 12:07 AMMySQL 대신 PostgreSQL을 선택한 시나리오에는 다음이 포함됩니다. 1) 복잡한 쿼리 및 고급 SQL 기능, 2) 엄격한 데이터 무결성 및 산 준수, 3) 고급 공간 기능이 필요하며 4) 큰 데이터 세트를 처리 할 때 고성능이 필요합니다. PostgreSQL은 이러한 측면에서 잘 수행되며 복잡한 데이터 처리 및 높은 데이터 무결성이 필요한 프로젝트에 적합합니다.
MySQL 데이터베이스를 어떻게 보호 할 수 있습니까?Apr 24, 2025 am 12:04 AMMySQL 데이터베이스의 보안은 다음 조치를 통해 달성 할 수 있습니다. 1. 사용자 권한 관리 : CreateUser 및 Grant 명령을 통한 액세스 권한을 엄격히 제어합니다. 2. 암호화 된 전송 : 데이터 전송 보안을 보장하기 위해 SSL/TLS를 구성합니다. 3. 데이터베이스 백업 및 복구 : MySQLDump 또는 MySQLPump를 사용하여 정기적으로 백업 데이터를 사용하십시오. 4. 고급 보안 정책 : 방화벽을 사용하여 액세스를 제한하고 감사 로깅 작업을 가능하게합니다. 5. 성능 최적화 및 모범 사례 : 인덱싱 및 쿼리 최적화 및 정기 유지 보수를 통한 안전 및 성능을 모두 고려하십시오.
MySQL 성능을 모니터링하는 데 사용할 수있는 몇 가지 도구는 무엇입니까?Apr 23, 2025 am 12:21 AMMySQL 성능을 효과적으로 모니터링하는 방법은 무엇입니까? Mysqladmin, Showglobalstatus, Perconamonitoring and Management (PMM) 및 MySQL Enterprisemonitor와 같은 도구를 사용하십시오. 1. MySQLADMIN을 사용하여 연결 수를보십시오. 2. showglobalstatus를 사용하여 쿼리 번호를보십시오. 3.pmm은 자세한 성능 데이터 및 그래픽 인터페이스를 제공합니다. 4. MySQLENTERPRISOMITOR는 풍부한 모니터링 기능 및 경보 메커니즘을 제공합니다.
MySQL은 SQL Server와 어떻게 다릅니 까?Apr 23, 2025 am 12:20 AMMySQL과 SqlServer의 차이점은 1) MySQL은 오픈 소스이며 웹 및 임베디드 시스템에 적합합니다. 2) SQLServer는 Microsoft의 상용 제품이며 엔터프라이즈 수준 애플리케이션에 적합합니다. 스토리지 엔진의 두 가지, 성능 최적화 및 응용 시나리오에는 상당한 차이가 있습니다. 선택할 때는 프로젝트 규모와 향후 확장 성을 고려해야합니다.
MySQL을 통해 어떤 시나리오에서 SQL Server를 선택할 수 있습니까?Apr 23, 2025 am 12:20 AM고 가용성, 고급 보안 및 우수한 통합이 필요한 엔터프라이즈 수준의 응용 프로그램 시나리오에서는 MySQL 대신 SQLServer를 선택해야합니다. 1) SQLServer는 고 가용성 및 고급 보안과 같은 엔터프라이즈 수준의 기능을 제공합니다. 2) VisualStudio 및 Powerbi와 같은 Microsoft Ecosystems와 밀접하게 통합되어 있습니다. 3) SQLSERVER는 성능 최적화에서 우수한 성능을 발휘하며 메모리 최적화 된 테이블 및 열 스토리지 인덱스를 지원합니다.
핫 AI 도구
Undresser.AI Undress
사실적인 누드 사진을 만들기 위한 AI 기반 앱
AI Clothes Remover
사진에서 옷을 제거하는 온라인 AI 도구입니다.
Undress AI Tool
무료로 이미지를 벗다
Clothoff.io
AI 옷 제거제
Video Face Swap
완전히 무료인 AI 얼굴 교환 도구를 사용하여 모든 비디오의 얼굴을 쉽게 바꾸세요!
인기 기사
뜨거운 도구
맨티스BT
Mantis는 제품 결함 추적을 돕기 위해 설계된 배포하기 쉬운 웹 기반 결함 추적 도구입니다. PHP, MySQL 및 웹 서버가 필요합니다. 데모 및 호스팅 서비스를 확인해 보세요.
에디트플러스 중국어 크랙 버전
작은 크기, 구문 강조, 코드 프롬프트 기능을 지원하지 않음
ZendStudio 13.5.1 맥
강력한 PHP 통합 개발 환경
안전한 시험 브라우저
안전한 시험 브라우저는 온라인 시험을 안전하게 치르기 위한 보안 브라우저 환경입니다. 이 소프트웨어는 모든 컴퓨터를 안전한 워크스테이션으로 바꿔줍니다. 이는 모든 유틸리티에 대한 액세스를 제어하고 학생들이 승인되지 않은 리소스를 사용하는 것을 방지합니다.
SublimeText3 Mac 버전
신 수준의 코드 편집 소프트웨어(SublimeText3)
