MySQL的用户管理-白红宇

MySQL的用户管理

阅读量：7256 次

发布时间：2019-06-29

本文共 19183 字，大约阅读时间需要 63 分钟。

　　　　　　　　　　　　　　　　　　　　　　　　　　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 -p12316 Welcome to the MySQL monitor.  Commands end with ; or \g.17 Your MySQL connection id is 1918 Server version: 5.1.73 Source distribution19 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 its23 affiliates. Other names may be trademarks of their respective24 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 2124 Server version: 5.1.73 Source distribution25 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 its29 affiliates. Other names may be trademarks of their respective30 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 Bye20 [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 2325 Server version: 5.1.73 Source distribution26 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 its30 affiliates. Other names may be trademarks of their respective31 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_specification15     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 2714 Server version: 5.1.73 Source distribution15 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 its19 affiliates. Other names may be trademarks of their respective20 owners.21 22 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.23 24 mysql> 25 mysql> use yinzhengjie26 Database changed27 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 -p66616 Welcome to the MySQL monitor.  Commands end with ; or \g.17 Your MySQL connection id is 3018 Server version: 5.1.73 Source distribution19 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 its23 affiliates. Other names may be trademarks of their respective24 owners.25 26 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.27 28 mysql> use yinzhengjie29 Reading table information for completion of table and column names30 You can turn off this feature to get a quicker startup with -A31 32 Database changed33 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> \q13 Bye14 [root@yinzhengjie ~]# mysql -uyzj -h 10.0.0.115 -p66615 Welcome to the MySQL monitor.  Commands end with ; or \g.16 Your MySQL connection id is 3117 Server version: 5.1.73 Source distribution18 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 its22 affiliates. Other names may be trademarks of their respective23 owners.24 25 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.26 27 mysql> use yinzhengjie28 Reading table information for completion of table and column names29 You can turn off this feature to get a quicker startup with -A30 31 Database changed32 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 user15     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 mysql13 Reading table information for completion of table and column names14 You can turn off this feature to get a quicker startup with -A15 16 Database changed17 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.%.%'