MySQL的用户管理

　　　　　　　　　　　　　　　MySQL的用户管理

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰

一.MySQL的用户管理

1>.用户账号的组成

　　MySQL在使用用户时比较独特，它的用户账号由两部分组成，即用户名和主机（username@hostname），这个username我们可以自定义，而hostname可以是一个网段，也可以是一个主机名，hostname支持通配符。

2>.用户账号管理的常用命令。

　　create user 　　　 
　　　　#创建用户
　　drop user 　　　　
　　　　#删除用户
　　rename user 　　  
　　　　#修改用户账号名称
　　set password 　　 
　　　　#设置用户密码

3>.权限管理的命令

　　grant 　　　　　　
　　　　#授权
　　revoke 　　　　　 
　　　　#取消权限

4>.查看用户能够使用的权限：

mysql>show grants for username@'hostname'

以下是MySQL实例：

 1 mysql> create user yinzhengjie@'10.0.0.1__' identified by '123';                #注意，这里使用的是允许网段10.0.0.100~10.0.0.199的IP可以访问哟！
 2 Query OK, 0 rows affected (0.02 sec)
 3 
 4 mysql> flush privileges;         #通知MySQL服务器重读授权表
 5 Query OK, 0 rows affected (0.00 sec)
 6 
 7 mysql>     
 8 
 9 
10 
11 
12 
13 
14 #进行以上操作后，我们新建一个终端进行以下验证：
15 [root@yinzhengjie ~]# mysql -uyinzhengjie -h 10.0.0.115 -p123
16 Welcome to the MySQL monitor.  Commands end with ; or \g.
17 Your MySQL connection id is 19
18 Server version: 5.1.73 Source distribution
19 
20 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
21 
22 Oracle is a registered trademark of Oracle Corporation and/or its
23 affiliates. Other names may be trademarks of their respective
24 owners.
25 
26 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
27 
28 mysql> show databases;
29 +--------------------+
30 | Database           |
31 +--------------------+
32 | information_schema |
33 | test               |
34 | yinzhengjie        |
35 +--------------------+
36 3 rows in set (0.00 sec)
37 
38 mysql> create database testdb;                                                                   #很明显，我们用户并没有权限创建数据库，只有一些查看的权限。
39 ERROR 1044 (42000): Access denied for user 'yinzhengjie'@'10.0.0.1__' to database 'testdb'
40 mysql>

MySQL用户的创建案例（create user）展示

 1 mysql> create user yinzhengjie@'10.0.0.1__' identified by '123';
 2 Query OK, 0 rows affected (0.02 sec)
 3 
 4 mysql> flush privileges;
 5 Query OK, 0 rows affected (0.00 sec)
 6 
 7 mysql> 
 8 mysql> 
 9 mysql> 
10 mysql> rename user yinzhengjie@'10.0.0.1__' to 'yzj'@'10.0.0.%';　　#由于MySQL用户是有用户和主机名组成，因此我们在修改MySQL用户的时候，既可以修改用户的字符也可以修改mysql的字符哟~
11 Query OK, 0 rows affected (0.00 sec)
12 
13 mysql> 
14 
15 
16 
17 
18 #进行以上操作后，我们新建一个终端进行以下验证：
19 [root@yinzhengjie ~]# mysql -uyinzhengjie -h 10.0.0.115 -p123　　　　　　　　　　　　　　#我们会发现当你再次用你之前创建的用户登录数据库是会报错，但是更换新的修改后的用户，即可成功登录哟！
20 ERROR 1045 (28000): Access denied for user 'yinzhengjie'@'www.yinzhengjie.com' (using password: YES)
21 [root@yinzhengjie ~]# mysql -uyzj -h 10.0.0.115 -p123　　　　　　　　　　　　　　　　　　#使用我们修改的用户即可正常登录
22 Welcome to the MySQL monitor.  Commands end with ; or \g.
23 Your MySQL connection id is 21
24 Server version: 5.1.73 Source distribution
25 
26 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
27 
28 Oracle is a registered trademark of Oracle Corporation and/or its
29 affiliates. Other names may be trademarks of their respective
30 owners.
31 
32 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
33 
34 mysql>

MySQL的改名（rename user ）案例展示

 1 mysql> rename user yinzhengjie@'10.0.0.1__' to 'yzj'@'10.0.0.%';　　　　　　　　#改名操作
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 mysql> set password for 'yzj'@'10.0.0.%' = password('666');　　　　　　　　　　#对改名后的MySQL用户重新设置登录密码
 5 Query OK, 0 rows affected (0.00 sec)
 6 
 7 mysql> 
 8 
 9 
10 
11 
12 
13 
14 
15 
16 
17 #进行以上操作后，我们新建一个终端进行以下验证：
18 mysql> \q　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#退出数据库
19 Bye
20 [root@yinzhengjie ~]# mysql -uyzj -h 10.0.0.115 -p123　　　　　　#发现用之前的数据库密码无法登录
21 ERROR 1045 (28000): Access denied for user 'yzj'@'www.yinzhengjie.com' (using password: YES)
22 [root@yinzhengjie ~]# mysql -uyzj -h 10.0.0.115 -p666　　　　　　#但是我们用修改后的密码就可以完成登录操作
23 Welcome to the MySQL monitor.  Commands end with ; or \g.
24 Your MySQL connection id is 23
25 Server version: 5.1.73 Source distribution
26 
27 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
28 
29 Oracle is a registered trademark of Oracle Corporation and/or its
30 affiliates. Other names may be trademarks of their respective
31 owners.
32 
33 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
34 
35 mysql>

MySQL用户重置密码（set password）案例展示

二.Mysql的权限类型

1>.管理类权限

 1          create temporary tables              #临时表
 2          create user                    　　　 #创建用户
 3          file                                 #允许用户读或者写某些文件
 4          lock tables                     　　　#添加显式锁
 5          process:                             #查看用户的线程
 6          reload：                    　　　　　 #相当于执行flush和reset
 7          replication client             　　　 #查询有哪些复制客户端
 8          replication slave             　　　　#赋予用户复制权限
 9          show databases                　　　　#查看数据库权限
10          shutdown                　　　　　　　 #关闭MySQL服务
11          super                    　　　　　　  #杂项管理类命令

2>. 数据库访问权限（库级别）

 1  　　　　　alter                         　　　　#修改表的权限
 2          alter routine                     　 #修改存储历程
 3          create                        　　　　#可以穿件表和库的
 4          create routine                 　　　 #创建存储过程，存储函数
 5          create view                    　　　 #创建视图
 6          delete                        　　　　#删除表中的行
 7          drop                        　　　　  #删除数据库或者表的
 8          execute                    　　　　　　#是否能执行存储过程或存储函数的
 9          grant option                     　 　#将自己的权限复制给别的用户
10          index                         　　　　#创建或删除索引
11          show view                    　　　　 #查看一个视图是如何被创建的

3>.数据操作类权限（表级别）

1         select                        　　　　#在表中执行查询操作
2         insert                        　　　　#在表中插入数据
3         update                        　　　　#修改表中的数据
4         delete                        　　　　#删除表中的数据

4>.字段级别

1        select（col1,....）                #查询
2        update（col1,....）                #修改字符
3        insert（col1,....）                #插入字符

5>.所有权限

1 　　ALL      [PRIVILEGES ]　　　　　　　　　　　　　　　　　　　　　　#用户的所有权限

三.MySQL的权限操作

1.查看授权的（grant）帮助信息

 1 mysql> help grant
 2 Name: 'GRANT'
 3 Description:
 4 Syntax:
 5 
 6 GRANT
 7     priv_type [(column_list)]
 8       [, priv_type [(column_list)]] ...
 9     ON [object_type] priv_level                    　　　　　 #如果[object_type] 不指定，默认类型为表；
10     TO user_specification [, user_specification] ...        #指定授权用户，可以一次性指定多个，用逗号（,）分隔；
11     [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]    #要求基于ssl_option进行连接，需要在前面加REQUIRE关键字，如果不加默认不启用该功能；
12     [WITH with_option ...]
13 
14 GRANT PROXY ON user_specification
15     TO user_specification [, user_specification] ...
16     [WITH GRANT OPTION]
17 
18 object_type:（指定授权类型，如果不指定，默认为表）
19     TABLE　　　　　　#表
20   | FUNCTION　　　　#函数
21   | PROCEDURE　　　　#过程
22 
23 priv_level: （指定授权对象）
24     *                　　　　　　　　 #指定所有数据对象
25   | *.*                　　　　　　  #指定所有库的所有表
26   | db_name.*            　　　　　 #指定库的所有表
27   | db_name.tbl_name        　　　　#指定库的某个表
28   | tbl_name            　　　　　　#指定表名
29   | db_name.routine_name    　　　　#指定库的存储历程
30 
31 user_specification:
32     user            　　　　　　　　　　　　　　　　#可以直接跟用户，不需要指定密码，但是前提是你之前已经创建过该用户并配置的有密码
33     [
34         IDENTIFIED BY [PASSWORD] 'password'    #如果之前创建过用户，则这里是为该用户修改密码哟
35       | IDENTIFIED WITH auth_plugin [AS 'auth_string']
36     ]
37 
38 ssl_option:    
39     SSL            　　#基于SSL协议，该选项用的较频繁
40   | X509        　　　　#基于X509的证书格式
41   | CIPHER 'cipher'    #指定使用的加密机制
42   | ISSUER 'issuer'    #指定颁发的证书
43   | SUBJECT 'subject'    #指定证书的承受着
44 
45 with_option:
46     GRANT OPTION             　　　　　#将自己的权限转赠给别人，不建议使用，这种操作很危险；
47   | MAX_QUERIES_PER_HOUR count        #每小时允许执行的最大查询次数
48   | MAX_UPDATES_PER_HOUR count        #每小时允许执行的最大更新次数
49   | MAX_CONNECTIONS_PER_HOUR count    #每小时允许执行的最大连接次数
50   | MAX_USER_CONNECTIONS count        #使用同一个账号可以同时连接的次数

以下是授权的实例仅供参考：

 1 mysql> grant create on yinzhengjie.tb1 to 'yzj'@'10.0.0.%';   #值运行MySQL用户'yzj'@'10.0.0.%'在yinzhengjie这个库中创建名字为tb1的表名。
 2 Query OK, 0 rows affected (0.01 sec)
 3 
 4 mysql> 
 5 
 6 
 7 
 8 
 9 #进行以上操作后，我们新建一个终端进行以下验证：
10 [root@yinzhengjie ~]# 
11 [root@yinzhengjie ~]# mysql -uyzj -h 10.0.0.115 -p666   #登录数据库
12 Welcome to the MySQL monitor.  Commands end with ; or \g.
13 Your MySQL connection id is 27
14 Server version: 5.1.73 Source distribution
15 
16 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
17 
18 Oracle is a registered trademark of Oracle Corporation and/or its
19 affiliates. Other names may be trademarks of their respective
20 owners.
21 
22 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
23 
24 mysql> 
25 mysql> use yinzhengjie
26 Database changed
27 mysql> 
28 mysql> 
29 mysql> 
30 mysql> show grants for 'yzj'@'10.0.0.%';   #查看授权信心
31 +-----------------------------------------------------------------------------------------------------------+
32 | Grants for yzj@10.0.0.%                                                                                   |
33 +-----------------------------------------------------------------------------------------------------------+
34 | GRANT USAGE ON *.* TO 'yzj'@'10.0.0.%' IDENTIFIED BY PASSWORD '*007D50CA06F69776D307B1BEC71CD73D0EA0999C' |
35 | GRANT CREATE ON `yinzhengjie`.`tb1` TO 'yzj'@'10.0.0.%'      #这里的授权是只能创建一个表                                             |
36 +-----------------------------------------------------------------------------------------------------------+
37 2 rows in set (0.00 sec)
38 
39 mysql> create table tb1 (Name varchar(30) not null)engine=innodb default charset=utf8;        #我们发现可以在yinzhengjie库中创建tb1的表名，
40 Query OK, 0 rows affected (0.06 sec)
41 
42 mysql> create table tb2 (Name varchar(30) not null)engine=innodb default charset=utf8;        #但是我们却没有权限创建其他名称的表名。
43 ERROR 1142 (42000): CREATE command denied to user 'yzj'@'www.yinzhengjie.com' for table 'tb2'
44 mysql>
45 
46 授权只能创建指定的表名

授权只能创建指定数据库的指定表名称

授权MySQL用户只能在某个库创建任意表名

 1 mysql> grant drop on yinzhengjie.* to 'yzj'@'10.0.0.%';
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 mysql> 
 5 
 6 
 7 
 8 
 9 
10 
11 
12 
13 
14 #进行以上操作后，我们新建一个终端进行以下验证：
15 [root@yinzhengjie ~]# mysql -uyzj -h 10.0.0.115 -p666
16 Welcome to the MySQL monitor.  Commands end with ; or \g.
17 Your MySQL connection id is 30
18 Server version: 5.1.73 Source distribution
19 
20 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
21 
22 Oracle is a registered trademark of Oracle Corporation and/or its
23 affiliates. Other names may be trademarks of their respective
24 owners.
25 
26 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
27 
28 mysql> use yinzhengjie
29 Reading table information for completion of table and column names
30 You can turn off this feature to get a quicker startup with -A
31 
32 Database changed
33 mysql> 
34 mysql> show grants for 'yzj'@'10.0.0.%';
35 +-----------------------------------------------------------------------------------------------------------+
36 | Grants for yzj@10.0.0.%                                                                                   |
37 +-----------------------------------------------------------------------------------------------------------+
38 | GRANT USAGE ON *.* TO 'yzj'@'10.0.0.%' IDENTIFIED BY PASSWORD '*007D50CA06F69776D307B1BEC71CD73D0EA0999C' |
39 | GRANT CREATE, DROP ON `yinzhengjie`.* TO 'yzj'@'10.0.0.%'                                                 |
40 | GRANT CREATE ON `yinzhengjie`.`tb1` TO 'yzj'@'10.0.0.%'                                                   |
41 +-----------------------------------------------------------------------------------------------------------+
42 3 rows in set (0.00 sec)
43 
44 mysql> show tables;
45 +-----------------------+
46 | Tables_in_yinzhengjie |
47 +-----------------------+
48 | Classes               |
49 | score                 |
50 | students              |
51 | tb1                   |
52 | tb2                   |
53 | tb3                   |
54 | tearchers             |
55 +-----------------------+
56 7 rows in set (0.00 sec)
57 
58 mysql> drop table tb2;                                                  #你会发现你是可以可劲的删除表~
59 Query OK, 0 rows affected (0.51 sec)
60 
61 mysql> drop table tb1;
62 Query OK, 0 rows affected (0.02 sec)
63 
64 mysql> drop table tb3;
65 Query OK, 0 rows affected (0.58 sec)
66 
67 mysql> drop table score;
68 Query OK, 0 rows affected (0.23 sec)
69 
70 mysql> 
71 mysql> desc students;                                                 #但是仍然是没有select权限哟！因此不能查看表结构，想要获得此权限授权即可！
72 ERROR 1142 (42000): SELECT command denied to user 'yzj'@'www.yinzhengjie.com' for table 'students'
73 mysql>

授权MySQL用户拥有某个库的删除权限

 1 mysql> grant select,insert,update,delete on yinzhengjie.* to 'yzj'@'10.0.0.%';
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 mysql> 
 5 
 6 
 7 
 8 
 9 
10 
11 #进行以上操作后，我们新建一个终端进行以下验证：
12 mysql> \q
13 Bye
14 [root@yinzhengjie ~]# mysql -uyzj -h 10.0.0.115 -p666
15 Welcome to the MySQL monitor.  Commands end with ; or \g.
16 Your MySQL connection id is 31
17 Server version: 5.1.73 Source distribution
18 
19 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
20 
21 Oracle is a registered trademark of Oracle Corporation and/or its
22 affiliates. Other names may be trademarks of their respective
23 owners.
24 
25 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
26 
27 mysql> use yinzhengjie
28 Reading table information for completion of table and column names
29 You can turn off this feature to get a quicker startup with -A
30 
31 Database changed
32 mysql> show tables;
33 +-----------------------+
34 | Tables_in_yinzhengjie |
35 +-----------------------+
36 | Classes               |
37 | students              |
38 | tearchers             |
39 +-----------------------+
40 3 rows in set (0.00 sec)
41 
42 mysql> desc students;
43 +-----------+--------------------+------+-----+---------+----------------+
44 | Field     | Type               | Null | Key | Default | Extra          |
45 +-----------+--------------------+------+-----+---------+----------------+
46 | StudentID | mediumint(9)       | NO   | PRI | NULL    | auto_increment |
47 | Name      | varchar(50)        | NO   | PRI | NULL    |                |
48 | Age       | tinyint(4)         | NO   | MUL | NULL    |                |
49 | Gender    | enum('boy','girl') | YES  |     | NULL    |                |
50 | ClassID   | tinyint(4)         | YES  |     | NULL    |                |
51 | TeacherID | tinyint(4)         | YES  |     | NULL    |                |
52 +-----------+--------------------+------+-----+---------+----------------+
53 6 rows in set (0.00 sec)
54 
55 mysql>

授权MySQL用户多个权限案例展示

2.查看回收权限用法帮助信息

 1 mysql> help revoke 
 2 Name: 'REVOKE'
 3 Description:
 4 Syntax:
 5 REVOKE
 6     priv_type [(column_list)]
 7       [, priv_type [(column_list)]] ...
 8     ON [object_type] priv_level
 9     FROM user [, user] ...                #我们可以指定从某个用户收回个别的权限
10 
11     REVOKE ALL PRIVILEGES, GRANT OPTION        #一也可以一次性收回所有的权限
12     FROM user [, user] ...
13 
14     REVOKE PROXY ON user
15     FROM user [, user] ...

　　以下收回权限的一个案例，仅供参考，通过以下案例，你也可以做其他操作：

 1 mysql> revoke create,select,insert on yinzhengjie.* from  'yzj'@'10.0.0.%';        #收回对MySQL用户（'yzj'@'10.0.0.%）的create,select,insert这三个权限.
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 mysql> FLUSH PRIVILEGES;                                #刷新授权信息，让在线的用户也能重读授权表，这样用户不需要退出当前客户端就能读取到最新的授权信息
 5 Query OK, 0 rows affected (0.00 sec)
 6 
 7 mysql> 
 8 
 9 
10 
11 
12 
13 
14 
15 
16 
17 #进行以上操作后，我们新建一个终端进行以下验证：
18 mysql> show grants for 'yzj'@'10.0.0.%';                        #我们在授权修改之前查看一下当前权限
19 +-----------------------------------------------------------------------------------------------------------+
20 | Grants for yzj@10.0.0.%                                                                                   |
21 +-----------------------------------------------------------------------------------------------------------+
22 | GRANT USAGE ON *.* TO 'yzj'@'10.0.0.%' IDENTIFIED BY PASSWORD '*007D50CA06F69776D307B1BEC71CD73D0EA0999C' |
23 | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `yinzhengjie`.* TO 'yzj'@'10.0.0.%'                 |
24 | GRANT CREATE ON `yinzhengjie`.`tb1` TO 'yzj'@'10.0.0.%'                                                   |
25 +-----------------------------------------------------------------------------------------------------------+
26 3 rows in set (0.00 sec)
27 
28 mysql> show grants for 'yzj'@'10.0.0.%';                          #等到用户授权完毕之后，我们在一次查看当前权限，发现权限的确少了三个！尽管我没有退出msyql客户端重新登录。
29 +-----------------------------------------------------------------------------------------------------------+
30 | Grants for yzj@10.0.0.%                                                                                   |
31 +-----------------------------------------------------------------------------------------------------------+
32 | GRANT USAGE ON *.* TO 'yzj'@'10.0.0.%' IDENTIFIED BY PASSWORD '*007D50CA06F69776D307B1BEC71CD73D0EA0999C' |
33 | GRANT UPDATE, DELETE, DROP ON `yinzhengjie`.* TO 'yzj'@'10.0.0.%'                                         |
34 | GRANT CREATE ON `yinzhengjie`.`tb1` TO 'yzj'@'10.0.0.%'                                                   |
35 +-----------------------------------------------------------------------------------------------------------+
36 3 rows in set (0.00 sec)
37 
38 mysql>

3.几个跟用户授权相关的表：

 1 mysql> show databases;
 2 +--------------------+
 3 | Database           |
 4 +--------------------+
 5 | information_schema |
 6 | mysql              |
 7 | test               |
 8 | yinzhengjie        |
 9 +--------------------+
10 4 rows in set (0.00 sec)
11 
12 mysql> use mysql
13 Reading table information for completion of table and column names
14 You can turn off this feature to get a quicker startup with -A
15 
16 Database changed
17 mysql> show tables;
18 +---------------------------+
19 | Tables_in_mysql           |
20 +---------------------------+
21 | columns_priv              |　　　　　　#列（字段）级别的权限
22 | db                        |　　　　　　#库级别的权限
23 | event                     |
24 | func                      |
25 | general_log               |
26 | help_category             |
27 | help_keyword              |
28 | help_relation             |
29 | help_topic                |
30 | host                      |　　　　　　#主机级别权限，已废弃
31 | ndb_binlog_index          |
32 | plugin                    |
33 | proc                      |
34 | procs_priv                |　　　　　　#存储过程和存储函数相关的权限
35 | servers                   |
36 | slow_log                  |
37 | tables_priv               |　　　　　　#表级别权限
38 | time_zone                 |
39 | time_zone_leap_second     |
40 | time_zone_name            |
41 | time_zone_transition      |
42 | time_zone_transition_type |
43 | user                      |
44 +---------------------------+
45 23 rows in set (0.00 sec)
46 
47 mysql>

　　补充： proxies_priv表是存储代理用户权限的。

4.练习

 1 1>.授权testuser能够通过172.16.0.0/16网络内的任意主机访问当前mysql服务器的权限
 2    grant alter on *.* to 'testuser'@'172.16.%.%' identified by 'password'
 3 2>.让此用户能够创建和删除testdb数据库，及库中的表
 4     grant create,drop on testdb.* to 'testuser'@'172.16.%.%';
 5 3>.让此用户能够在testdb库中的t1表中执行查询、删除、更新和插入操作
 6     grant select,update,insert,delect on testdb.t1 to 'testuser'@'172.16.%.%'
 7 4>.让此用户能够在testdb库上创建和删除索引
 8     grant index,drop index on testdb to 'testuser'@'172.16.%.%';
 9 5>.让此用户能够在testdb.t2表上查询id和name字段，并允许其将此权限传授予其他用户
10     grant select(id,name) on testdb.t2 to 'testuser'@'172.16.%.%'
11     grant grant option on testdb.t2 to 'testuser'@'172.16.%.%'

posted @ 2017-11-22 22:33 尹正杰阅读(420) 评论(0) 编辑收藏举报

刷新页面返回顶部

尹正杰

MySQL的用户管理

公告