本文详细介绍了MySQL中如何进行用户管理，包括查看、创建、修改和删除用户，以及密码的设置和破解。此外，重点讲解了权限管理，包括列权限、表权限、数据库权限和全局权限的授予和撤销，并解释了权限刷新机制和权限存储的原理。
目录
- DDL:数据库定义语言，建库，建表
- DML:数据库操纵语言，增删改
- DQL:数据库查询语言，select
- DCL:数据库控制语言，用户控制
用户管理
我们现在默认使用的都是root用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库
查看用户
- MySQL的所有用户都在MySQL数据库中的user表中存储：
select * from mysql.user;
创建用户
语法：
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';关键词说明:
'用户名'：将创建的用户名
‘主机名’：指定该用户在哪个主机上可以登录，如果是本地用户可用local host，如果想让该用户可以从任意远程主机登录，可以使用通配符%
‘密码’：该用户的登录密码，密码可以为空，如果为空则该用户可以不需要密码登录服务器
tips:用户名、主机名和密码都应该加上单引号
案例：创建zhangsan用户，只能在local host这个服务器登录MySQL服务器，密码为123456
create user 'zhangsan'@'localhost' identified by '123456';
flush privileges;
使用zhangsan账号登录MySQL服务器：
mysql -uzhangsan -p123456案例：创建lisi用户可以在任何电脑上登录MySQL服务器，密码为admin
create user 'lisi'@'%' identified by 'admin';
flush privileges;
修改密码
语法：
mysqladmin -uroot -p password 新密码tips：
- mysqladmin是MySQL服务器提供的cmd命令，和mysql命令一样需要在cmd窗口执行；
- -p后面有个空格，输入password，然后后面输入新密码
案例1：修改root的密码为123：
mysqladmin -uroot -p password 123
- 使用新密码登录mysql服务器：
mysql -uroot -p123
破解密码
在mysql/bin文件夹下可执行文件，不用登录，但要指定管理员的密码
mysqladmin -uroot -p password 新密码
tips：回车后要输出原密码，才能更改成功。如果原密码不正确，则修改失败
在控制台输入：
mysql --help往下滚动，查看MySQL的配置文件存放位置：
MySQL服务启动时，会加载如下几个位置的配置文件（权重从上到下）：
- 1）C:\WINDOWS\my.ini
- 2）C:\WINDOWS\my.cnf
- 3）C:\my.ini
- 4）C:\my.cnf
- 5）C:\Program Files\MySQL\MySQL Server 5.7\my.ini
- 6）C:\Program Files\MySQL\MySQL Server 5.7\my.cnf
默认情况下，MySQL的核心配置文件在C:\ProgramData\MySQL\MySQL Server 5.7\my.ini（我们也可以将其复制到上面说的那几个目录）
需要注意的是，默认情况下MySQL会在提供一个文件：C:\Program Files\MySQL\MySQL Server 5.7\my-default.ini，MySQL并不会加载这个文件，这个文件只是MySQL提供给我们复制用的配置文件；
Tips：MySQL只会加载我们前面说到的那几个目录中的指定名称的配置文件
打开C:\ProgramData\MySQL\MySQL Server 5.7\my.ini文件，该配置里面配置很多MySQL系统方面的配置
在[mysqld]组下面添加如下配置：
skip-grant-tables
以管理员身份运行cmd窗口：
重启MySQL服务：
net stop mysql57
net start mysql57
使用MySQL客户端登录MySQL（此时不需要输入密码）：
修改root用户密码：
-- 切换到mysql数据库
use mysql;
-- 修改root用户密码
update user set authentication_string=password('123456') where user='root';
-- 刷新权限
flush privileges;
exit;去掉mysql配置文件中的skip-grant-tables配置；
重启MySQL服务：
net stop mysql57
net start mysql57使用新密码登录：
drop user ''@'';
drop user ''@'';
select * from mysql.user; --查询用户发现已经没有了zhangsan、lisi用户删除用户
- 语法：
drop user '用户名'@'主机名';案例1：删除zhangsan、lisi用户
drop user 'zhangsan'@'localhost';
drop user 'lisi'@'%';
select * from mysql.user; -- 查询用户发现已经没有了zhangsan、lisi用户
- 再次使用zhangsan/lisi账号登录MySQL服务器，发现登录失败：
mysql -uzhangsan -p123456;
权限管理
- 创建两张测试表
drop database if exists test01;
use test01;
drop table if exists user;
create table user(
id int,
username varchar(30),
password int
);
insert into user values(1,'root','123');
insert into user values(2,'admin','456');
insert into user values(3,'guest','000');
drop table if exists student;
create table student(
id int,
name varchar(30),
age int
);
INSERT INTO student VALUES (1, 'zhangsan', 20);
INSERT INTO student VALUES (2, 'lisi', 18);
INSERT INTO student VALUES (3, 'wangwu', 23);- 创建4个测试用户：
create user 'zhangsan'@'localhost' identified by 'aaa';
create user 'lisi'@'localhost' identified by 'bbb';
create user 'wangwu'@'localhost' identified by 'ccc';
create user 'zhaoliu'@'localhost' identified by 'ddd';用户创建之后，没什么任何权限，需要给用户授权
mysql> use test01
ERROR 1044 (42000): Access denied for user 'zhangsan'@'localhost' to database 'test01'
mysql>
tips：连切换数据库的权限都没有
权限管理
授予权限
-
授予权限语法说明：
grant...on...to:授权关键字
权限：授予用户的权限，如create、alter、select、insert、update、delete等，如果要授予所有的权限则使用all
数据库名.表名：该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
'用户名'@'主机名'：给哪个用户授权，要加上单引号。与创建用户时的用户名和主机名要相同
在MySQL中，权限范围分为4种，分别是：列权限、表权限、数据库权限、全局权限，不同的权限作用范围不一样；
需要注意的是：如果一个用户连select权限都没有，那么对应的update和delete权限也将失效，但insert权限不受影响；
案例1-列权限
给zhangsan用户分配对test01数据库的student表权限：insert（id，name）、updata（name）、delete
tips：delete属于表权限，不能指定列
- 使用root账号给zhangsan账号分配权限：
--分配权限
grant insert(id,name),update(name),delete on test01.student to 'zhangsan'@'localhost';
--查看权限
show grants for 'zhangsan'@'localhost';
- 登录zhangsan用户:
# 登录zhangsan用户
mysql -uzhangsan -paaa测试权限：
mysql> use test01; # 切换到test01数据库
Database changed
mysql> select * from student; # 没有select权限
ERROR 1142 (42000): SELECT command denied to user 'zhangsan'@'localhost' for table 'student'
mysql> insert into student values(100,'t1',20); # 不能插入age列
ERROR 1142 (42000): INSERT command denied to user 'zhangsan'@'localhost' for table 'student'
mysql> insert into student(id,name) values(100,'t1'); # 可以插入id,name列
Query OK, 1 row affected (0.00 sec)
mysql> update student set age=100 where id=1; # 不可以修改age列
ERROR 1143 (42000): SELECT command denied to user 'zhangsan'@'localhost' for column 'id' in table 'student'
mysql> update student set name='zs' where id=1; # 连name列也不可以修改(因为zhangsan用户没有select权限)
ERROR 1143 (42000): SELECT command denied to user 'zhangsan'@'localhost' for column 'id' in table 'student'
mysql> delete from student where id=1;
ERROR 1143 (42000): SELECT command denied to user 'zhangsan'@'localhost' for column 'id' in table 'student'
mysql>- 使用root账号给zhangsan用户分配select权限：
-- 分配权限
grant select(id,name),update(age) on test01.student to 'zhangsan'@'localhost';
-- 查看权限
show grants for 'zhangsan'@'localhost';
- 不用重写登录，再次测试zhangsan用户的权限；
- 测试完毕删除用户
drop user 'zhangsan'@'localhost';案例2-表权限
- 使用root账号清空表，插入测试数据：
truncate student;
insert into student values(1,'zhangsan',20);
insert into student values(2,'lisi',18);
insert into student values(3,'wangwu',23);给lisi用户分配delete、insert、update权限，该权限针对与student表
注意：如果一个用户连select权限都没有，那么update、delete权限也将执行不了，但insert权限可以。
- 使用root账号给lisi账号分配权限：
-- 分配权限
grant delete,insert,update on test01.student to 'lisi'@'localhost';
-- 查看权限
show grants for 'zhangsan'@'localhost';
- 登录lisi用户：
mysql -ulisi -pbbb- 测试权限：
- 使用root账号给lisi用户分配select权限：
grant select on test01.student to 'lisi'@'localhost';
show grants for 'lisi'@'localhost';
- 不用重新登录，再次测试权限
- 测试完毕删除用户：
drop user 'lisi'@'localhost';案例3-数据库权限
- 使用root账号清空表，插入测试数据：
truncate student;
insert into student values(1,'zhangsan',20);
insert into student values(2,'lisi',18);
insert into student values(3,'wangwu',23);首先给wangwu用户分配test02数据库中的student所有的权限
- 使用root账号给wangwu账号分配权限：
-- 分配权限
grant all on test01.student to 'wangwu'@'localhost';
-- 查看权限
show grants for 'wangwu'@'localhost';
- 登录wangwu账号：
mysql -uwangwu -pccc测试wangwu账号的权限：
- 使用root账号扩大wangwu账号权限：
-- 分配权限
grant all on test01.* to 'wangwu'@'localhost';
-- 查看权限
show grants for 'wangwu'@'localhsot';
- 需要重新切换数据库，才能刷新数据库权限；
更新数据库权限时，需要重新切换数据库权限才能刷新；（当然重新登录也是可以刷新权限的）
如果会话在持有某个数据库的权限时进入了该数据库，那么会话在执行use dbName时拿到的权限就会保存在会话变量中；此后更改了用户的数据库权限（赋予新权限、回收权限等）将不会被刷新；但如果用户在use dbName之前就更改好了数据的权限，那么此时并不需要退出账号来刷新权限；
- 测试完毕删除用户：
drop user 'wangwu'@'localhost';案例4-全局权限
- 使用root账号清空表，插入测试数据：
truncate student;
insert into student values(1,'zhangsan',20);
insert into student values(2,'lisi',18);
insert into student values(3,'wangwu',23);首先给zhaoliu用户分配test02数据库中的所有表的所有的权限
- 使用root账号给zhaoliu账号分配权限：
-- 分配权限
grant all on test01.* to 'zhaoliu'@'localhost';
-- 查看权限
show grants for 'wangwu'@'localhost'
- 登录zhaoliu账号的权限
tips：此时zhaoliiu账号可以对test01数据库里面的任意表执行任意操作
- 使用root账号扩大zhaoliu账号权限：
-- 分配权限
grant all on *.* to 'zhaoliu'@'localhost';
-- 查看权限
show grants for 'wangwu'@'localhost'
- 需要重新登录，才能刷新全局权限
- 测试完毕删除用户：
drop user 'zhaoliu'@'localhost';小结
- 权限小结：
- 数据库权限范围分为列权限、表权限、数据库权限、全局权限
- 如果一个用户没有select权限，那么update和delete权限也会失效
- 关于权限刷新：
- 用户被分配列权限、表权限时不需要重新登录权限即可刷新
- 分配数据库权限时需要重新切换数据库才能刷新权限
- 分配全局权限时需要重新登录才能刷新权限
- 权限语法：
# 创建用户
mysql> create user 'test'@'localhost' identified by 'aaa';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'test'@'localhost';
+------------------------------------------+
| Grants for test@localhost |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
+------------------------------------------+
1 row in set (0.00 sec)
# 列权限
mysql> grant select(id,name) on test01.student to 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'test'@'localhost';
+---------------------------------------------------------------------+
| Grants for test@localhost |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
| GRANT SELECT (name, id) ON `test01`.`student` TO 'test'@'localhost' |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
# 表权限
mysql> grant select on test01.student to 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'test'@'localhost';
+-----------------------------------------------------------------------------+
| Grants for test@localhost |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
| GRANT SELECT, SELECT (name, id) ON `test01`.`student` TO 'test'@'localhost' |
+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
# 数据库权限
mysql> grant select on test01.* to 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'test'@'localhost';
+-----------------------------------------------------------------------------+
| Grants for test@localhost |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
| GRANT SELECT ON `test01`.* TO 'test'@'localhost' |
| GRANT SELECT, SELECT (name, id) ON `test01`.`student` TO 'test'@'localhost' |
+-----------------------------------------------------------------------------+
3 rows in set (0.00 sec)
# 全局权限
mysql> grant select on *.*to 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'test'@'localhost';
+-----------------------------------------------------------------------------+
| Grants for test@localhost |
+-----------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'test'@'localhost' |
| GRANT SELECT ON `test01`.* TO 'test'@'localhost' |
| GRANT SELECT, SELECT (name, id) ON `test01`.`student` TO 'test'@'localhost' |
+-----------------------------------------------------------------------------+
3 rows in set (0.00 sec)
# 删除用户
mysql> drop user 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql>撤销权限
- 撤销权限语法：
revoke 权限1，权限2.... on 数据库.表名 from '用户名'@'主机名';- 关键字说明：
revoke...on...from:撤销权限的关键字
权限：用户的权限，如CREATE、ALTER、SELECT、INSERT、UPDATE、SELECT等，所有的权限则使用ALL
数据库.表名:对哪些数据库的哪些表，如果要取消该用户对所有数据库和表的操作权限则可用*表示，如*.*
'用户名'@'主机名':给哪个用户撤销，要加上单引号。与创建用户时的用户名和主机名要相同
- 创建一个用户赋予如下权限
create user 'xiaohui'@'localhost' identified by 'admin';
-- 列权限
grant select(id,name) on test01.student to 'xiaohui'@'localhost';
-- 表权限
grant select on test01.student to 'xiaohui'@'localhost';
-- 数据库权限
grant select on test01.* to 'xiaohui'@'localhost';
-- 全局权限
grant select on *.* to 'xiaohui'@'localhost';- 查看用户的权限：
show grants for 'xiaohui'@'localhost';
- 回收权限：
mysql> revoke select(id,name) on test01.student from 'xiaohui'@'localhost'; # 回收列权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'xiaohui'@'localhost';
+-------------------------------------------------------------+
| Grants for xiaohui@localhost |
+-------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'xiaohui'@'localhost' |
| GRANT SELECT ON `test01`.* TO 'xiaohui'@'localhost' |
| GRANT SELECT ON `test01`.`student` TO 'xiaohui'@'localhost' |
+-------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> revoke select on test01.student from 'xiaohui'@'localhost'; # 回收表权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'xiaohui'@'localhost';
+-----------------------------------------------------+
| Grants for xiaohui@localhost |
+-----------------------------------------------------+
| GRANT SELECT ON *.* TO 'xiaohui'@'localhost' |
| GRANT SELECT ON `test01`.* TO 'xiaohui'@'localhost' |
+-----------------------------------------------------+
2 rows in set (0.00 sec)
mysql> revoke select on test01.* from 'xiaohui'@'localhost'; # 回收数据库权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'xiaohui'@'localhost';
+----------------------------------------------+
| Grants for xiaohui@localhost |
+----------------------------------------------+
| GRANT SELECT ON *.* TO 'xiaohui'@'localhost' |
+----------------------------------------------+
1 row in set (0.00 sec)
mysql> revoke select on *.* from 'xiaohui'@'localhost'; # 回收全局权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'xiaohui'@'localhost';
+---------------------------------------------+
| Grants for xiaohui@localhost |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'xiaohui'@'localhost' |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql>Tips：在回收数据库权限和全局权限时，被回收权限的账号需要退出重新登录才能刷新最新的权限；
- 测试完毕删除用户：
drop user 'xiaohui'@'localhost';权限原理
1）权限磁盘表
在MySQL中，每种权限的信息都会在磁盘和内存中存储，具体的存储位置为：
- 列权限：
-
- 磁盘：mysql.columns_priv表
- 内存：和表权限组成的hash结构column_priv_hash
- 表权限：
-
- 磁盘：mysql.tables_priv表
- 内存：和列权限组成的hash结构column_priv_hash
- 数据库权限：
-
- 磁盘：mysql.db表
- 内存：数组acl_dbs
- 全局权限：
-
- 磁盘：mysql.user表
- 内存：数组acl_user
Tips：只有列权限和表权限粗壮
重新创建一个用户，分别赋予列、表、数据库、全局权限：
create user 'xiaohui'@'localhost' identified by 'admin';
-- 列权限
grant select(id,name) on test01.student to 'xiaohui'@'localhost';
-- 表权限
grant select on test01.student to 'xiaohui'@'localhost';
-- 数据库权限
grant select on test01.* to 'xiaohui'@'localhost';
-- 全局权限
grant select on *.* to 'xiaohui'@'localhost';- 查看磁盘表：
select * from mysql.columns_priv;
select * from mysql.tables_priv;
select * from mysql.db;
select * from mysql.user;列权限：
mysql> select * from mysql.columns_priv;
+-----------+--------+---------+------------+-------------+---------------------+-------------+
| Host | Db | User | Table_name | Column_name | Timestamp | Column_priv |
+-----------+--------+---------+------------+-------------+---------------------+-------------+
| localhost | test01 | xiaohui | student | name | 0000-00-00 00:00:00 | Select |
| localhost | test01 | xiaohui | student | id | 0000-00-00 00:00:00 | Select |
+-----------+--------+---------+------------+-------------+---------------------+-------------+
2 rows in set (0.00 sec)
mysql>表权限:
mysql> select * from mysql.tables_priv;
+-----------+--------+-----------+------------+----------------+---------------------+------------+-------------+
| Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |
+-----------+--------+-----------+------------+----------------+---------------------+------------+-------------+
| localhost | sys | mysql.sys | sys_config | root@localhost | 2021-06-10 20:29:01 | Select | |
| localhost | test01 | xiaohui | student | root@localhost | 0000-00-00 00:00:00 | Select | Select |
+-----------+--------+-----------+------------+----------------+---------------------+------------+-------------+
2 rows in set (0.00 sec)
mysql>表权限:
mysql> select * from mysql.tables_priv;
+-----------+--------+-----------+------------+----------------+---------------------+------------+-------------+
| Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |
+-----------+--------+-----------+------------+----------------+---------------------+------------+-------------+
| localhost | sys | mysql.sys | sys_config | root@localhost | 2021-06-10 20:29:01 | Select | |
| localhost | test01 | xiaohui | student | root@localhost | 0000-00-00 00:00:00 | Select | Select |
+-----------+--------+-----------+------------+----------------+---------------------+------------+-------------+
2 rows in set (0.00 sec)
mysql>数据库权限：
mysql> select * from mysql.db\G;
*************************** 2. row ***************************
Host: localhost
Db: test01 # 数据库名
User: xiaohui # 用户名
Select_priv: Y # 查询
Insert_priv: N # 插入
Update_priv: N # 修改
Delete_priv: N # 删除
Create_priv: N # 创建表
Drop_priv: N # 删除表
Grant_priv: N # 赋予表的其他权限
References_priv: N # 创建外键
Index_priv: N # 创建索引
Alter_priv: N # 修改表结构
Create_tmp_table_priv: N # 创建临时表
Lock_tables_priv: N # 锁表
Create_view_priv: N # 创建视图
Show_view_priv: N # 查看视图
Create_routine_priv: N # 创建存储过程和存储函数
Alter_routine_priv: N # 修改存储过程和存储函数
Execute_priv: N # 执行存储过程和存储函数
Event_priv: N # 创建事件
Trigger_priv: N # 创建触发器
2 rows in set (0.00 sec)全局权限：
mysql> select * from mysql.user\G;
*************************** 3. row ***************************
Host: localhost
User: xiaohui
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *4ACFE3202A5FF5CF467898FC58AAB1D615029441
password_expired: N
password_last_changed: 2023-04-03 16:37:59
password_lifetime: NULL
account_locked: N
3 rows in set (0.00 sec)- 测试完毕删除用户：
drop user 'xiaohui'@'localhost';flush privileges
flush privileges命令用于刷新权限；
flush privileges操作会清空权限内存数组，然后从对应的权限磁盘表中读取数据重新构造一个内存数组，也就是以数据表中数据为准，将内存数组重新加载一遍；所以说如果内存中的权限数据和磁盘表中的数据一致的话，flush privileges其实是可以不用做的。
而对于正常的grant/revoke/create user等操作，内存和磁盘中的数据都是同步更新的，所以正常的grant/revoke操作后是不需要flush privileges的。
但是，更改权限、回收权限、创建用户等操作不仅可以使用grant/revoke/create user等命令来完成；同样可以通过修改磁盘权限表来完成；
【创建账号】
- 使用磁盘表方式创建一个测试账号：
INSERT INTO mysql.user(Host, User, authentication_string, ssl_cipher, x509_issuer, x509_subject)
VALUES ('localhost', 'xiaohong', PASSWORD('admin'), '', '', '');
flush privileges; -- 此时一定要刷新权限上述操作就一定要执行flush privileges来刷新权限，将磁盘权限表中的数据加载到MySQL服务器内
- 使用xiaohong账号登录：
mysql -uxiaohong -padmin赋权限
- 使用root账号直接通过修改mysql.user表来赋予权限
-- 注意: 此时的权限是全局权限(因为改的是mysql.user表)
update mysql.user set select_priv='Y' where user='xiaohong';
-- 一定要刷新权限,将磁盘权限表中的数据加载到MySQL服务器内存
flush privileges;- 重新使用xiaohong账号登录MySQL服务器（因为刚刚修改的是全局权限）
mysql -uxiaohong -padmin- 执行查询：
select * from student;发现权限正常;
扫一扫
1063
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
