MySQL 用户权限详细汇总

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

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 表结构如下:

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>123456789101112131415161718192021222324252627282930

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';1

应该除了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>123456789101112131415161718192021222324252627282930

2.1.4 授予ALL权限

执行sql语句建立用户:

GRANT ALL ON d3307.* TO dba5@'192.168.52.1' IDENTIFIED BY 'dba0523';1

建立用户的时候,如下所示,除了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>123456789101112131415161718192021222324252627282930

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';1

分析结果:存储在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>123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051

2.2.4 创建对于所有表有操作权限的用户

创建用户:

mysql> GRANT SELECT,UPDATE ON *.* TO user7@'%' IDENTIFIED BY 'user0523'; Query OK, 0 rows affected (0.00 sec) mysql>1234

分析: 
基本的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>123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051

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>1234

分析结果: 
应该是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>12345678910111213141516


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>1234567

分析: 
单独为某个列授权,会记录在这个表的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>12345678910111213141516


而且还会在另外一个权限表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>12345678910


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>1234

查看结果,会在这个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>123456789


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>123456789101112131415


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']]...12

  其中,user参数表示新建用户的账户,user由用户名(User)和主机名(Host)构成;IDENTIFIED BY关键字用来设置用户的密码;password参数表示用户的密码;如果密码是一个普通的字符串,就不需要使用PASSWORD关键字。可以没有初始密码。

例如

CREATE USER 'sys'@'%' IDENTIFIED BY 'sys';1

执行之后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']...]123

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]...1

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" 1

新密码(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 ~]#12345678910


5.2 修改user表

UPDATE user表的passwor字段的值,也可以达到修改密码的目的;

UPDATE user SET Password = PASSWORD('123') WHERE USER = 'myuser';FLUSH PRIVILEGES;12

刷新后生效。


5.3 使用SET语句来修改密码

使用root用户登录到MySQL服务器后,可以使用SET语句来修改密码: 
修改自己的密码,不需要用户名

SET PASSWORD = PASSWORD("123");1

修改其他用户密码:

SET PASSWORD FOR 'myuser'@'%'=PASSWORD("123456") FOR 用户名@主机名1


5.4 GRANT语句来修改普通用户的密码

使用GRANT语句修改普通用户的密码,必须拥有GRANT权限。

GRANT priv_type ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password']1

示例:

GRANT SELECT ON *.* TO 'user10'@'%' IDENTIFIED BY '123'1


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@'%'; 1

或者直接执行sql命令去mysql数据库下的user表中查看存储着用户的基本权限:

SELECT * FROM mysql.user WHERE USER='user10' AND HOST='%'; 1

使用revoke关键字来收回权限:

REVOKE priv_type[(column_list)]ON database.tableFROM user[,user]123

示例:

REVOKE EXECUTE ON d3307.* FROM user10@'%';1


7,数据库用户划分

7.1 普通数据管理用户:

赋予对业务表的查询维护权限即可,授权sql如下:

GRANT SELECT, INSERT, UPDATE, DELETE ON d3307.* TO zengxiaoteng@'%' IDENTIFIED BY '0523';1


7.2 开发人员账户:

赋予增删改查的权限,授权sql如下:

GRANT SELECT,INSERT,DELETE,UPDATE ON d3307.* TO huyan@'%' IDENTIFIED BY '0523'; 1

授予创建、修改、删除 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’;123

授予操作 MySQL 外键权限:

GRANT REFERENCES ON d3307.* TO huyan@’192.168.52.11’;1

授予操作 MySQL 临时表权限:

GRANT CREATE TEMPORARY TABLES ON d3307.* TO huyan@’192.168.52.11’;1

授予操作 MySQL 索引权限:

GRANT INDEX ON d3307.* TO huyan@’192.168.52.11’;1

授予操作 MySQL 视图、查看视图源代码 权限:

GRANT CREATE VIEW ON d3307.* TO huyan@’192.168.52.11’;GRANT SHOW   VIEW ON d3307.* TO huyan@’192.168.52.11’;12

授予操作 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’;123


7.3 DBA人员账户

授予普通DBA管理某个MySQL数据库(test)的权限:

GRANT ALL PRIVILEGES ON test TO sysdba@'192.168.52.%';1

授予高级 DBA 管理 MySQL 中所有数据库的权限:

GRANT ALL ON *.* TO sysdba@'192.168.52.%';1


7.4 数据分析人员只读账号

只需要分配只读的权限:

GRANT SELECT ON d3307.* TO dataquery@'192.168.52.129' IDENTIFIED BY '20150523';1

甚至有些用户,可以只分配读取某些表列的权限,如下所示:

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>12345678910111213141516171819202122232425


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了。










本文转自 chengxuyonghu 51CTO博客,原文链接:http://blog.51cto.com/6226001001/1676972,如需转载请自行联系原作者
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
SQL 关系型数据库 MySQL
mysql用户权限设置
mysql用户权限设置
108 1
|
2月前
|
存储 关系型数据库 MySQL
mysql(三)用户权限管理
为什么要设置用户权限?MySQL设置用户管理权限的主要目的是为了确保数据库的安全性和数据的机密性。以下是一些原因。
121 1
mysql(三)用户权限管理
|
1月前
|
关系型数据库 MySQL 数据库
5.Mysql 用户权限管理
5.Mysql 用户权限管理
23 0
|
9月前
|
SQL 关系型数据库 MySQL
MySQL 数据控制语言(DCL):管理用户权限
MySQL 是一个强大的关系型数据库管理系统,提供了丰富的功能和选项来管理数据库和用户。数据库管理员(DBA)通常使用数据控制语言(Data Control Language,简称 DCL)来管理用户的权限和访问。 本文将详细介绍 MySQL DCL 的基本概念,包括如何创建用户、授权和撤销权限等,同时提供示例代码以帮助您更好地理解。
159 2
|
2月前
|
SQL 关系型数据库 MySQL
MySQL用户管理 用户权限控制(DCL语句)
MySQL用户管理 用户权限控制(DCL语句)
40 0
|
关系型数据库 MySQL 数据安全/隐私保护
mysql权限管理查看用户权限
mysql权限管理查看用户权限
105 0
|
11月前
|
存储 关系型数据库 MySQL
MySQL触发器以及用户权限
MySQL触发器以及用户权限
161 0
|
存储 编解码 关系型数据库
案例分享:Qt激光加工焊接设备信息化软件研发(西门子PLC,mysql数据库,用户权限控制,界面设计,参数定制,播放器,二维图,rgv小车,期限控制,参数调试等)
国产大型机床中Qt上位机激光焊接系统软件案例分享,介绍了Qt在国产化机床上的各种应用案例,并附上案例的具体功能界面供大家学习
案例分享:Qt激光加工焊接设备信息化软件研发(西门子PLC,mysql数据库,用户权限控制,界面设计,参数定制,播放器,二维图,rgv小车,期限控制,参数调试等)
|
安全 关系型数据库 MySQL
mysql:MySQL数据库修改用户权限(远程访问权限、操作权限)
mysql:MySQL数据库修改用户权限(远程访问权限、操作权限)
2061 0
mysql:MySQL数据库修改用户权限(远程访问权限、操作权限)
|
关系型数据库 MySQL 数据库
MySQL配置用户权限(mysql5.*及mysql8.*+)
MySQL配置用户权限(mysql5.*及mysql8.*+)
195 0