数据库的安全管理

157 阅读7分钟

实验目的

  1. 掌握数据库用户账号的建立与删除方法;

  2. 掌握数据库用户权限的授予方法。

实验表数据参考MySQL的存储过程和函数 - 掘金 (juejin.cn)

知识点

1.特定的用户特定的权限:db 表

db 表也是 MySQL 数据库中非常重要的权限表,db 表存储了用户对某个数据库的操作权限,决定用户能在哪个主机操作哪个数据库 查看用户对某数据库是否具有增、删、改、查等操作权限,其 SQL 语句如下:

USE mysql;
SELECT user, db, select_priv, insert_priv, update_priv, delete_priv FROM db;

image.png

2.单表权限设置:tables_priv 表

tables_priv 表用来对单个表进行权限设置。tables_priv 表共有 8 个字段:hostdbusertable_namegrantortimestamptable_priv 以及 column_priv。查看该表信息结果如下:

USE mysql;
SELECT * FROM tables_priv;

image.png 各字段说明如下:

  • 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 个字段:hostdbusertable_namecolumn_nametimestamp 以及 column_priv。其中,column_name 用来指定对哪一列进行权限设置,其它字段和 tables_priv 表字段含义相同。该表信息如下:

USE mysql;
DESC columns_priv;

image.png 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;

image.png 各字段说明如下:

  • 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_nameuser_name 是账号名称,host_name 为主机名称,即指定账户的同时又指定发起连接的客户端主机名字。也可以不指定主机名,默认为 %,表示匹配任意客户端主机。

IDENTIFIED BY 子句用于指定账户的密码,该子句若省略,表示创建的用户没有密码

查看用户

进入 mysql 数据库,在 USER 表中查看新插入的用户记录。其 SQL 语句如下:

SELECT user, host FROM user;

修改用户密码

修改用户密码使用 ALTER 子句,其语法如下:

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

其中,其中 FLUSH PRIVILEGES 表示刷新用户权限。

删除用户

  1. 使用 DROP 子句删除用户,其语法如下:

    DROP USER 用户名@主机名;
    
  2. 使用 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的密码修改为123456ALTER 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;