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>
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>
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的权限类型
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>
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>
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.%.%'