MySQL学习——管理用户权限
摘要：本文主要学习了使用DCL语句管理用户权限的方法。
了解用户权限
什么是用户
用户，指的就是操作和使用MySQL数据库的人。使用MySQL数据库需要用户先通过用户名和密码进行连接，然后才能进行操作，不同的用户可以设置不同的权限，让某些用户只能操作权限范围内的数据，也可以让某些用户只能查询不能修改。
在MySQL数据库中，所有的用户信息都是保存在mysql数据库的user表中的。
查看user表
使用 desc user; 命令查看user表的结构：
1 mysql> desc user; 2 +------------------------+-----------------------------------+------+-----+-----------------------+-------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +------------------------+-----------------------------------+------+-----+-----------------------+-------+ 5 | Host | char(60) | NO | PRI | | | 6 | User | char(16) | NO | PRI | | | 7 | Password | char(41) | NO | | | | 8 | Select_priv | enum('N','Y') | NO | | N | | 9 | Insert_priv | enum('N','Y') | NO | | N | | 10 | Update_priv | enum('N','Y') | NO | | N | | 11 | Delete_priv | enum('N','Y') | NO | | N | | 12 | Create_priv | enum('N','Y') | NO | | N | | 13 | Drop_priv | enum('N','Y') | NO | | N | | 14 | Reload_priv | enum('N','Y') | NO | | N | | 15 | Shutdown_priv | enum('N','Y') | NO | | N | | 16 | Process_priv | enum('N','Y') | NO | | N | | 17 | File_priv | enum('N','Y') | NO | | N | | 18 | Grant_priv | enum('N','Y') | NO | | N | | 19 | References_priv | enum('N','Y') | NO | | N | | 20 | Index_priv | enum('N','Y') | NO | | N | | 21 | Alter_priv | enum('N','Y') | NO | | N | | 22 | Show_db_priv | enum('N','Y') | NO | | N | | 23 | Super_priv | enum('N','Y') | NO | | N | | 24 | Create_tmp_table_priv | enum('N','Y') | NO | | N | | 25 | Lock_tables_priv | enum('N','Y') | NO | | N | | 26 | Execute_priv | enum('N','Y') | NO | | N | | 27 | Repl_slave_priv | enum('N','Y') | NO | | N | | 28 | Repl_client_priv | enum('N','Y') | NO | | N | | 29 | Create_view_priv | enum('N','Y') | NO | | N | | 30 | Show_view_priv | enum('N','Y') | NO | | N | | 31 | Create_routine_priv | enum('N','Y') | NO | | N | | 32 | Alter_routine_priv | enum('N','Y') | NO | | N | | 33 | Create_user_priv | enum('N','Y') | NO | | N | | 34 | Event_priv | enum('N','Y') | NO | | N | | 35 | Trigger_priv | enum('N','Y') | NO | | N | | 36 | Create_tablespace_priv | enum('N','Y') | NO | | N | | 37 | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | 38 | ssl_cipher | blob | NO | | NULL | | 39 | x509_issuer | blob | NO | | NULL | | 40 | x509_subject | blob | NO | | NULL | | 41 | max_questions | int(11) unsigned | NO | | 0 | | 42 | max_updates | int(11) unsigned | NO | | 0 | | 43 | max_connections | int(11) unsigned | NO | | 0 | | 44 | max_user_connections | int(11) | NO | | 0 | | 45 | plugin | char(64) | YES | | mysql_native_password | | 46 | authentication_string | text | YES | | NULL | | 47 | password_expired | enum('N','Y') | NO | | N | | 48 +------------------------+-----------------------------------+------+-----+-----------------------+-------+ 49 43 rows in set (0.00 sec) 50 51 mysql>
在MySQL数据库中，对用户的管理是通过对应的Host和User共同组成的主键来区分的。
其中，User代表用户的用户名，Host代表允许访问的客户端（IP地址或者是主机地址），Host使用*表示所有的客户端都可以访问。
查询user表数据：
1 mysql> select host, user, password from user; 2 +-----------+------+----------------------+ 3 | host | user | password | 4 +-----------+------+----------------------+ 5 | localhost | root | ******************** | 6 | 127.0.0.1 | root | ******************** | 7 | ::1 | root | ******************** | 8 +-----------+------+----------------------+ 9 3 rows in set (0.00 sec) 10 11 mysql>
在安装MySQL数据库的时候，如果没有创建匿名用户，那么在user表里只有一个root用户。
刷新设置
MySQL新设置用户或更改密码后需用 flush privileges 刷新MySQL的系统权限相关表，否则会出现拒绝访问，还有一种方法，就是重新启动服务器，来使新设置生效。
创建用户
两种方式
创建用户的方式有两种，一种是使用DML语句直接向user表里插入数据，另一种是使用DDL语句创建用户。
如果使用DML语句直接向user表里插入数据，那么在插入Password字段时，需要先进行加密，保存加密后的密文。如果使用DDL语句创建用户，则不需要考虑加密的问题，语句会自动将密码进行加密。
语法
1 create user 用户名@主机地址 identified by 明文密码;
其中， @主机地址 可以省略，则表示任何客户端都可以使用这个用户访问数据库。 identified by 铭文密码 也可以省略，表示不需要密码就可以登录。
实例
1 mysql> create user 'abc' identified by '123456'; 2 Query OK, 0 rows affected (0.00 sec) 3 4 mysql>
删除用户
两种方式
和创建用户相似，删除用户也有两种方式。
语法
使用DML语句删除：
1 delete from user where user=用户名 and host=主机地址;
使用DDL语句删除：
1 drop user 用户名@主机地址;
实例
使用DML语句删除：
1 mysql> delete from user where user = 'abc' ; 2 Query OK, 1 row affected (0.00 sec) 3 4 mysql>
使用DDL语句删除：
1 mysql> drop user 'abc'; 2 Query OK, 0 rows affected (0.00 sec) 3 4 mysql>
修改用户密码
两种方式
修改密码也有两种方式：
一种是使用 password() 方法加密后更新，5.6版本以及以前的版本需要使用这种方式。
一种是使用DCL语句进行更新，5.7版本以及之后的版本需要使用这种方式。
语法
使用DML语句修改，并用 password() 方法对密码加密：
1 update user set password=password(明文密码) where user=用户名 and host=主机地址;
使用DCL语句修改：
1 alter user 用户名@主机地址 identified by 明文密码;
实例
使用DML语句修改，并用 password() 方法对密码加密：
1 mysql> update user set password=password('654321') where user='abc' ; 2 Query OK, 1 row affected (0.00 sec) 3 Rows matched: 1 Changed: 1 Warnings: 0 4 5 mysql>
使用DCL语句修改：
1 mysql> alter user 'abc' identified by '123123'; 2 Query OK, 0 rows affected (0.00 sec) 3 4 mysql>
用户授权
语法
1 grant 权限列表 on 库名.表名 to 用户名@主机地址 indentified by 明文密码 [with grant option][with admin option]
其中，撤消带有 admin option 的用户权限时，连带的权限将保留，撤销带有 grant option 的用户权限时，连带的权限也会被撤销。
实例
1 mysql> grant all on *.* to root identified by '123456' with grant option; 2 Query OK, 0 rows affected (0.00 sec) 3 4 mysql>
取消授权
语法
1 revoke 权限列表 on 库名.表名 from 用户名@主机地址
实例
1 mysql> revoke all on *.* from 'root'@'%'; 2 Query OK, 0 rows affected (0.00 sec) 3 4 mysql>
