转自: https://segmentfault.com/a/1190000014856560
MySQL笔记 - 用户管理
tags: 数据库 MySQL 用户管理
学习目标
MySQL是一个多用户数据库,具有功能强大的访问控制系统,可以为不同用户指定允许的权限。
MySQL用户可以分为普通用户和root用户。root用户是超级管理员,拥有所有权限,包括创建用户、删除用户和修改用户的密码等管理权限;普通用户只有被授予的各种权限。
用户管理包括管理用户账户、权限等。
1. 权限表
MySQL服务器通过权限表来控制用户对数据库的访问,权限表存放在MySQL数据库中,由MySQL_install_db
脚本初始化。
存储账户权限信息表主要有:user
、host
、db
、tables_priv
、columns_priv
、procs_priv
。本节主要介绍这些表的内容和作用。
1.1 user表
user
表是MySQL中最重要的一个权限表,记录允许连接到服务器的账号信息,这里的权限是全局的。
执行mysql> describe mysql.user;
命令得到如下显示表信息:
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
Host | char(60) | NO | PRI | ||
User | char(32) | 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 | ||
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) | NO | mysql_native_password | ||
authentication_string | text | YES | NULL | ||
password_expired | enum('N','Y') | NO | N | ||
password_last_changed | timestamp | YES | NULL | ||
password_lifetime | smallint(5) unsigned | YES | NULL | ||
account_locked | enum('N','Y') | NO | N |
45 rows in set (0.00 sec)
字段说明:
- 用户列
user
表的用户信息列包括Host
、User
、authentication_string
,其中Host
和User
为表的联合主键。authentication_sting
为用户密码的哈希值。当一个用户连接时,只有这3个值完全匹配才被允许。 - 权限列
后面带_pri
的都是用户权限字段,包括了增、删、改、查等普通权限,还包括了关闭服务器、加载用户等高级权限。普通权限用于对数据库实施操作行为的限制;高级权限用于数据库管理行为。user
表中对应的权限是针对所有用户数据数据库的。这些字段值的类型为ENUM
,可以取值只能为Y和N。修改权限使用grant
语句和update
语句。 - 安全列
安全列只有6个字段,其中两个是ssl相关,两个是x509相关,另外两个是授权插件相关。 资源控制列
max_questions
- 用户每小时允许执行的查询操作次数。max_updates
- 用户每小时允许执行的更新操作次数。max_connections
- 用户每小时允许执行的连接操作次数。max_user_connections
- 用户允许同时建立的连接次数。
1.2 db表和host表
db
表和host
表是MySQL数据中非常重要的权限表。db
表中存储了用户对某个数据库的操作权限,决定用户能从哪个主机存取那个数据库。
执行mysql> describe mysql.db;
后显示结果如下:
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
Host | char(60) | NO | PRI | ||
Db | char(64) | NO | PRI | ||
User | char(32) | 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.04 sec)
host
表中存储了某个主机对数据库的操作权限,配合db
权限表对给定主机上的数据库级操作权限做更细致的控制,这个表不受grant
和revoke
语句的影响。此表在“Server version: 5.7.20 MySQL Community Server (GPL)”中并未发现。
1.3 tables_priv表和columns_priv表
这2张表分别对具体的表和表中的字段来设置权限。
tables_priv
表信息:
;mysql> describe mysql.tables_priv;
Field | Type | Null | Key | Default | Extra | |
---|---|---|---|---|---|---|
Host | char(60) | NO | PRI | |||
Db | char(64) | NO | PRI | |||
User | char(32) | NO | PRI | |||
Table_name | char(64) | NO | PRI | |||
Grantor | char(93) | 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.04 sec)
columns_priv
表信息:
mysql> describe mysql.columns_priv;
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
Host | char(60) | NO | PRI | ||
Db | char(64) | NO | PRI | ||
User | char(32) | 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.02 sec)
1.4 procs_priv表
procs_priv
表可以对存储过程和存储函数设置操作权限。
mysql> describe mysql.procs_priv;
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
Host | char(60) | NO | PRI | ||
Db | char(64) | NO | PRI | ||
User | char(32) | NO | PRI | ||
Routine_name | char(64) | NO | PRI | ||
Routine_type | enum( 'FUNCTION', 'PROCEDURE') | NO | PRI | NULL | |
Grantor | char(93) | NO | MUL | ||
Proc_priv | set('Execute', 'Alter Routine', 'Grant') | NO | |||
Timestamp | timestamp | NO | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
rows in set (0.02 sec)
2. 账户管理
2.1 登录和退出MySQL服务器
通过MySQL -help
命令可以查看MySQL命令的帮助信息。MySQL命令的常用参数如下:
-h 主机名
:可以指定主机名称和IP
,如果不指定,默认是localhost。
-P 端口号
:指定服务器的端口号,默认为3306。-u 用户名
:指定用户名。-p密码
:可以用使用该参数指定登录密码,注意: 密码与p
之间不能有空格。-e "SQL语句"
:如果指定了该语句,会在登录后执行SQL语句。- '
数据库名称
:可以在命令的最后指定数据库名称。
[例1] 用户root
,密码为foo
,从主机localhost
登录到MySQL服务器,设置test_db
数据库为登录后的默认当前数据库。
C:\mysql5.7.2\bin>mysql -h localhost -u root -pfoo test_db
Enter password: *********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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 database();
+------------+
| database() |
+------------+
| test_db |
+------------+
1 row in set (0.00 sec)
[例2] 使用root
用户,密码为foo
,从主机localhost
登录到MySQL服务器,设置缺省数据库为test_db
,并执行语句describe test1; select * from test1;
。
C:\mysql5.7.2\bin>mysql -h localhost -u root -pfoo test_db -e "describe test1; select * from test1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a1 | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
+------+
| a1 |
+------+
| 1 |
| 3 |
| 4 |
+------+
要退出MySQL服务器登录,请使用quit
。
mysql> quit
Bye
C:\mysql5.7.2>bin>
2.2 新建普通用户
要创建新用户,必须具有相应的权限来执行创建操作。在MySQL数据库中,有两种方式创建新用户,一种是使用create user
或grant
语句;另一种是直接操作MySQL授权表。
(1). 使用create user
语句创建新用户
create user
语句基本语法格式如下:
CREATE USER user_specification[, user_specification]...
user_specification:
user@host [IDENTIFIED BY [PASSWORD] 'password' | IDENTIFIED WITH auth_plugin [AS 'auth_sting']]
值得注意的是:如果使用PASSWORD
关键字,后面的密码要使用哈希值字符窜,否则'password'
使用明文密码。
[例3] 使用create user
创建一个用户,用户名是jack
,密码是pw
,主机名为localhost
,语句如下:
mysql> create user `jack`@`localhost` identified by 'pw';
Query OK, 0 rows affected (0.24 sec)
-- 如果使用password关键字则要如下操作:
mysql> select password('pw');
+-------------------------------------------+
| password('pw') |
+-------------------------------------------+
| *D821809F681A40A6E379B50D0463EFAE20BDD122 |
+-------------------------------------------+
1 row in set, 1 warning (0.03 sec)
mysql> create user jack1@localhost identified by password '*D821809F681A40A6E379B50D0463EFAE20BDD122';
Query OK, 0 rows affected, 1 warning (0.00 sec)
(2). 使用grant
语句创建新用户
基本语法如下:
GRANT privileges ON [object_type] priv_level TO user@host [IDENTIFIED BY 'password'] [, user [IDENTIFIED BY 'password']] [WITH GRANT OPTION]
object_type: {
TABLE
| FUNCTION
| PROCEDURE
}
priv_level: {
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
}
说明:WITH GRANT OPTION
子句表示对新建立的用户赋予GRANT
权限,即该用户可以对其他用户赋予权限。
[例4] 使用grant
语句创建两个新用户jack2
和jack3
,密码分别为pw1
和pw2
,并授于他们对所有数据表的select和update权限。
mysql> grant select,update on table *.* to jack2@localhost identified by 'pw1', jack3@localhost identified by 'pw2';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> show warnings;
Level | Code | Message |
---|---|---|
Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
2 rows in set (0.00 sec)
-- 查看警告可知,最好不要用grant语句来添加新用户,而要用create user语句添加新用户后再授权。如下所示,则不会出现警告:
mysql> create user jack4@localhost identified by 'pw3';
Query OK, 0 rows affected (0.00 sec)
mysql> grant select, update on table *.* to jack4@localhost;
Query OK, 0 rows affected (0.00 sec)
对于用户名到底是使用user@host
,还是user
呢?请点此进一步查看,简单来说指定user就是要同时指定用户和主机,这两个是一体的。
(3). 直接对user表进行操作
在MySQL中create user
和grant
语句都是实际上都是对user
表进行操作,但一般不建议这样做,除非特殊情况或者极熟悉MySQL
的user
表中的各项设置才可以。
[例5] 使用insert
创建一个新账户
mysql> insert into mysql.user (host,user,authentication_string)
-> values('localhost', 'jack5', password('pw'));
ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value
mysql> show warnings;
Level | Code | Message |
---|---|---|
Warning | 1681 | 'PASSWORD' is deprecated and will be removed in a future release. |
Error | 1364 | Field 'ssl_cipher' doesn't have a default value |
Error | 1364 | Field 'x509_issuer' doesn't have a default value |
Error | 1364 | Field 'x509_subject' doesn't have a default value |
4 rows in set (0.01 sec)
由提示信息可以看出并未添加成功,需要指定多个相关字段才能添加成功,所以请使用MySQL提供的标准的语句。
2.3 删除普通用户
(1). 使用drop user
语句删除用户
基本语法:
drop user user [, user];
[例6] 删除用户jack4@localhost。
mysql> drop user jack4@localhost;
Query OK, 0 rows affected (0.02 sec)
(2). 直接对user表进行操作
[例7] 删除用户jack3@localhost。
mysql> delete from mysql.user where user='jack3' and host='localhost';
Query OK, 1 row affected (0.16 sec)
2.4 root用户修改自己的密码
(1). 使用mysqladmin
命令在命令行中修改密码
[例8] 将root用户的密码修改为"xfoox"
C:\>mysqladmin -u root -h 192.168.1.33 -pfoo password "xfoox"
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
说明: -p
的密码foo
是紧跟着的,不能有空格;password
后面的密码要使用双引号;如果省略掉-p
后面的密码会要求你输入原密码。当然你可以使用SSL连接服务器,以增加安全性,MySQL开启SSL安全连接的文章请点此访问
(2). 使用set password
语句修改密码
正常登陆服务器后,使用如下语句修改:
mysql> set password=password("foo");
Query OK, 0 rows affected (0.14 sec)
注意:在最新版本的MySQL8.0中并不支持password函数,只需要直接写密码即可。
(3). 直接修改user表
mysql> update mysql.user set password=password("foo") where user = "root" and host = "localhost";
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> update mysql.user set authentication_string=password("foo") where user = "root" and host = "localhost";
Query OK, 0 rows affected, 1 warning (0.15 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.17 sec)
注意:一般不要使用这种方法,因为每个版本的密码存贮字段名可能不同,最后要记得使用flush privileges
语句来冲洗权限表,然后才可以使用新密码登陆。
2.5 root用户修改普通用户的密码
(1). 使用set password
语句修改
mysql> set password for jack@localhost = password('foo');
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> show warnings;
| Warning | 1287 | 'SET PASSWORD FOR <user> = PASSWORD('<plaintext_password>')' is deprecated and will be removed in a future release. Please use SET PASSWORD FOR <user> = '<plaintext_password>' instead |
1 row in set (0.00 sec)
(2). 使用grant usage
语句来修改
mysql> grant usage on *.* to jack@localhost identified by 'foo';
Query OK, 0 rows affected, 1 warning (0.12 sec)
说明:警告提示仍然为未来版本可能不支持该语句来修改用户的密码。
(3). 直接修改user表
同root用户。
2.6 普通用户修改自的密码
普通用户正常登陆后,使用如下语句修改:
mysql> set password = 'foo';
Query OK, 0 rows affected (0.07 sec)
2.7 root用户密码丢失的解决办法
基本步骤
- 越过权限表来启动服务。(MySQL服务名字可以自定义的,请按自己的情况处理,一般为
mysql
) - 修改密码。
- 刷新权限表。
详细步骤演示
打开管理员命令窗口,停止本机的MySQL服务,再用参数
--skip-grant-tables
来越过权限表的检查来启动服务。注意,此时光标会不断闪烁,不要关闭此窗口。
如果在MySQL8.0版本中,启动服务的命令为:
E:\mysql8\bin>mysqld --skip-grant-tables --shared-memory --console
,附加--console
是为了将信息输出到控制台,不然信息会不显示。E:\mysql8\bin>mysqld --skip-grant-tables --shared-memory --console 2018-05-14T11:53:03.811523Z 0 [System] [MY-010116] [Server] E:\mysql8\bin\mysqld.exe (mysqld 8.0.11) starting as process 4472 2018-05-14T11:53:06.244140Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2018-05-14T11:53:06.320312Z 0 [System] [MY-010931] [Server] E:\mysql8\bin\mysqld.exe: ready for connections. Version: '8.0.11' socket: '' port: 0 MySQL Community Server - GPL. 2018-05-14T11:53:06.455078Z 0 [Warning] [MY-011311] [Server] Plugin mysqlx reported: 'All I/O interfaces are disabled, X Protocol won't be accessible' (注:光标会在这里闪烁) (注:在进行完第3步后,在这里我按了Ctrl+c终止,下面是按键完毕后的输出:) 2018-05-14T12:02:51.10644^5CZ 0 [System] E:\mysql8\bin> [MY-013105] [Server] E:\mysql8\bin\mysqld.exe: Normal shutdown. 2018-05-14T12:02:53.120117Z 0 [Warning] [MY-010909] [Server] E:\mysql8\bin\mysqld.exe: Forcing close of thread 9 user: 'root'. 2018-05-14T12:02:54.515625Z 0 [System] [MY-010910] [Server] E:\mysql8\bin\mysqld.exe: Shutdown complete (mysqld 8.0.11) MySQL Community Server - GPL.
打开新的命令窗口,使用root用户登陆,由于越过了权限表,此时虽然要求输入密码,但只需要回车即可,登录后修改密码。
C:\mysql -u root -p enter password: (此处直接回车即可) mysql> update mysql.user set authentication_string = password('foo') where user='root' and host='localhost'; Query OK, 0 rows affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 1
注意:在5.7版本以前的密码是存储在
password
字段里的,以后的版本存储在authentication_string
字段里,并且在MySQL8.0以后,password()
函数也被取消了,采用了caching_sha2_password
的加密验证方式,所以,只能把密码字段先用空白字窜替换,即:authentication_string=''
,然后重新登陆,因为密码为空白,所以只需要回车即可,然后使用alter user root@localhost identified by 'foo';
语句来更改密码。刷新权限
mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
最后关闭管理员窗口,退出登录,即可重新使用新密码连接服务器。
注意:有时关闭了管理员命令窗口,一般MySQL服务仍然会在后台运行,但有时却不行。如果不能正常登陆,请查看进程,杀掉后,再使用
net start mysql
启动MySQL服务即可.(MySQL服务名字可以自定义的,请按自己的情况处理)