实验目的
-
掌握数据库用户账号的建立与删除方法;
-
掌握数据库用户权限的授予方法。
实验表数据参考MySQL的存储过程和函数 - 掘金 (juejin.cn)
知识点
1.特定的用户特定的权限:db 表
db 表也是 MySQL 数据库中非常重要的权限表,db 表存储了用户对某个数据库的操作权限,决定用户能在哪个主机操作哪个数据库 查看用户对某数据库是否具有增、删、改、查等操作权限,其 SQL 语句如下:
USE mysql;
SELECT user, db, select_priv, insert_priv, update_priv, delete_priv FROM db;
2.单表权限设置:tables_priv 表
tables_priv
表用来对单个表进行权限设置。tables_priv
表共有 8 个字段:host
、db
、user
、table_name
、grantor
、timestamp
、table_priv
以及 column_priv
。查看该表信息结果如下:
USE mysql;
SELECT * FROM tables_priv;
各字段说明如下:
- Host:主机名;
- Db:数据库名;
- User:用户名;
- Table_name:表名;
- Grantor:修改该记录的用户;
- Timestamp:修改该记录的时间;
- Table_priv:对表操作的权限,包括 SELECT、INSERT、UPDATE、DELETE、CREAT、DROP 等;
- Column_priv:对表中字段的操作权限,包括 SELECT、INSERT、UPDATE、REFERENCE 等。
3.单列权限设置:column_priv 表
columns_priv
表用来对单个数据列进行权限设置。该表共有 7 个字段:host
、db
、user
、table_name
、column_name
、timestamp
以及 column_priv
。其中,column_name
用来指定对哪一列进行权限设置,其它字段和 tables_priv
表字段含义相同。该表信息如下:
USE mysql;
DESC columns_priv;
MySQL 权限判定优先级顺序是 user 表 → db 表 → tables_priv 表 → column_priv 表,先检查全局权限表 user,如果 user 中对应的权限为 Y,则此用户对所有数据库的权限都为 Y,将不再检查 db, tables_priv, columns_priv;如果为 N,则到 db 表中检查此用户对应的具体数据库,并得到 db 中为 Y 的权限;如果 db 中为 N,则检查 tables_priv 中此数据库对应的具体表,取得表中的权限 Y,以此类推。因此为了做到“最小权限”原则,建议从列、表级配置开始,尽量不给全库或全局的权限。
4.储存过程和储存函数权限:procs_priv 表
procs_priv 表可以对存储过程和存储函数进行权限设置。该表共有 8 个字段:host、db、user、routine_name、routine_type、grantor、proc_priv 以及 timestamp。输入以下命令查询表信息:
USE mysql;
DESC procs_priv;
各字段说明如下:
- Host、Db 和 User:分别表示主机名、数据库名和用户名;
- Routine_name:存储过程或函数名;
- Routine_type:存储过程或函数的类型。该字段有两个值,FUNCTION 和 PROCEDURE。FUNCTION 示自定义函数,PROCEDURE 表示存储过程;
- Grantor:插入或修改该记录的用户;
- Proc_priv:拥有的权限,包括 Execute、Alter Routine 和 Grant 三种;
- Timestamp:记录更新时间。
如果要修改权限,可以通过 UPDATE 语句更新这些权限表,也可以使用 GRANT 语句为用户赋权,用 REVOKE 语句取消授权
5.添加、查看和删除用户
MySQL 是一个多用户数据库管理系统,分为两种:root 用户和普通用户。root 用户又叫超级管理员用户,拥有对数据库操作的所有权限;普通用户的权限由 root 用户或者拥有分配权的用户进行分配。
添加用户
刚安装好 MySQL 系统时,只存在 root 用户,该用户由 MySQL 服务器自动创建,并且被赋予了操作 MySQL 的所有权限。
在对 MySQL 的日常管理和实际操作中,为了避免恶意用户滥用 root 账户操控数据库,尽可能不用或少用 root 账户登录数据库系统,以确保数据库的安全,因此需要创建一系列普通用户。
使用 CREATE USER
语句创建新用户,其语法如下:
CREATE USER '账号' [IDENTIFIED BY '密码'];
其中,“账号”的格式为 user_name@host_name
。user_name
是账号名称,host_name
为主机名称,即指定账户的同时又指定发起连接的客户端主机名字。也可以不指定主机名,默认为 %
,表示匹配任意客户端主机。
IDENTIFIED BY
子句用于指定账户的密码,该子句若省略,表示创建的用户没有密码
查看用户
进入 mysql
数据库,在 USER 表中查看新插入的用户记录。其 SQL 语句如下:
SELECT user, host FROM user;
修改用户密码
修改用户密码使用 ALTER
子句,其语法如下:
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
其中,其中 FLUSH PRIVILEGES
表示刷新用户权限。
删除用户
-
使用
DROP
子句删除用户,其语法如下:DROP USER 用户名@主机名;
-
使用
DELETE FROM
子句对USER
表的记录进行删除,其 SQL 语句如下:
DELETE FROM USER WHERE USER = '用户名' AND host = '主机名';
6.用户权限管理之查看用户权限
查看用户权限
语法为:
SHOW GRANTS FOR 用户名@主机名;
使用 “zhangsan” 用户(密码123456)连接 MySQL,然后尝试切换到 MySQL 数据库中:
EXIT
mysql -uzhangsan -p123456
USE mysql;
出现错误提示,因为当前 “zhangsan” 用户不具备 mysql 这个数据库的访问权限。
7.用户权限管理之权限授予
使用关键字 GRANT,其语法格式如下:
GRANT 权限名称[(列名)][, 权限名称(列名)] ON 权限级别 TO 用户 [with option]
参数说明如下:
权限名称:如 SELECT、UPDATE、INSERT、DELETE 等数据库操作。
列名:可以给某一列或多列指定权限,省略则表示所有列。
权限级别:有以下几种情况。
- 列权限:表中的一个具体列相关,影响权限表 columns_priv;
- 表权限:和一个表中的所有数据相关,影响权限表 tables_priv;
- 数据库权限:和一个数据库中的所有表相关,影响权限表 db;
- 用户权限:和 MySQL 中所有数据库相关,影响权限表 user。
指定权限级别可以有以下几种格式:
*
:表示当前数据库中的所有表;_._
:表示所有数据库中的所有表;db_name.*
:表示指定数据库的所有表,db_name
为数据库名;db_name.tb_name
:表示指定数据库中的指定表或视图,db_name
为数据库名,tb_name
为表名或视图名;tb_name
:表示指定表或视图;db_name.routine_name
:表示指定数据库中指定存储过程或函数,db_name
为数据库名,routine_name
为存储过程或函数名。
如果想给某个用户所有权限,将 权限名称[(列名)][, 权限名称(列名)] ON 权限级别
替换为 ALL PRIVILEGES
。
用户:该参数格式为 user_name@host_name
with option:可选项,进行权限限制。
8.用户权限管理之权限限制与撤销
权限限制
在 GRANT 语法中,可使用 WITH 对权限进行限制,主要包括:
- GRANT OPTION:表示将自己拥有的权限转移给其它用户;
- max_queries_per_hour count:表示限制每小时可以查询数据库次数,count 表示具体数字,0 表示不限制。
- max_update_per_hour count:表示限制每小时可以修改数据库次数,count 表示具体数字,0 表示不限制。
- max_connections_per_hour count:表示限制每小时可以连接数据库次数,count 表示具体数字,0 表示不限制。
- max_user_connections count:表示限制同时连接 MySQL 数据库的最大用户数。
权限撤销
当某个用户不再适合拥有某个权限的时候,我们需要撤销权限。撤销权限使用关键字 REVOKE,其语法有两种格式。
格式一:
REVOKE 权限名称[(列名)] ON 权限级别 FROM 用户;
该语法用于回收某个用户的特定权限。
格式二:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户;
该语法用于回收某个用户的所有权限。
实验
-- 1. 创建数据库用户user_1和user_2,密码都为1234(假设服务器名为localhost)。
USE MYSQL;
CREATE USER user_1@LOCALHOST IDENTIFIED BY '1234';
CREATE USER user_2@LOCALHOST IDENTIFIED BY '1234';
-- 2. 将用户user_2的名称修改为user_3。
rename user user_2@localhost to user_3@localhost;
-- 3. 将用户user_3的密码修改为123456。
ALTER USER 'user_3'@'localhost' IDENTIFIED BY '123456';
-- 4. 删除用户user_3。
drop user user_3@localhost;
-- 5. 以user_1用户身份登录MySQL。
-- 这个语句在window或者linux里面使用,不是mysql里面
mysql -uuser_1 -p1234
-- 6. 授予用户user_1对学生作业管理系统数据库中student表的所有操作权限及查询操作权限。
GRANT all on table scdb.student to user_1@localhost;
-- 7. 授予用户user_1对student表进行插入、修改、删除操作权限。
grant insert,update,DELETE on table scdb.student to user_1@localhost;
-- 8. 授予用户user_1对数据库学生作业管理系统的所有权限。
grant all on table scdb.* to user_1@localhost;
-- 9. 授予user_1在course表上的select权限,并允许其将该权限授予其他用户。
grant select on table scdb.course to user_1@localhost;
-- 10. 回收user_1的student表上的select权限。
REVOKE select on scdb.student from user_1@localhost;