2.1 自主存取控制实验

2.1.1 实验目的

掌握自主存取控制权限的定义和维护方法。

2.1.2 实验内容和要求

定义用户、角色，分配权限给用户、角色，回收权限，以相应的用户名登陆数据库验证权限分配是否正确。选择一个应用场景，使用自主存取控制机制设计权限分配。可以采用两种方案。
方案一：采用 SYSTEM 超级用户登录数据库（对于 MYSQL(Linux) 则是 root），完成所有权限分配工作，然后用相应用户名登陆数据库以验证权限分配正确性；
方案二：采用 SYSTEM 用户登陆数据库创建三个部门经理用户，并分配相应的权限，然后分别用三个经理用户名登陆数据库，创建相应部门的 USER、ROLE ，并分配相应权限。

2.1.3 实验重点和难点

实验重点：定义角色，分配权限和回收权限。
实验难点：实验方案二实现权限的再分配和回收。

2.1.4 实验内容记录

本次实验中，为财务部（finance）创建一个管理员，部门的管理员只有管理自己部门内部的员工的权限以及访问部门表（departments）、部门管理员表（dept_manager）的权限。
注：为了减少工作量，只为财务部门创建相关用户。其它部门的操作可以类比。

2.1.4.1 建立视图

数据库基本表没有区分不同的部门，因此通过视图来划分不同部门的员工。
先创建员工所在部门视图：

1
2
3
4
CREATE VIEW v_emp_dept(emp_no, dept_no, dept_name) AS
SELECT de.emp_no, d.dept_no, d.dept_name
FROM dept_emp de, departments d
WHERE de.dept_no=d.dept_no;

然后为各个表创建部门视图：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE VIEW v_finance_employees AS
SELECT e.*
FROM employees e, v_emp_dept ved
WHERE e.emp_no=ved.emp_no
AND ved.dept_name='Finance'
WITH CHECK OPTION;
CREATE VIEW v_finance_salaries AS
SELECT s.*
FROM salaries s, v_emp_dept ved
WHERE s.emp_no=ved.emp_no
AND ved.dept_name='Finance'
WITH CHECK OPTION;
CREATE VIEW v_finance_dept_emp AS
SELECT de.*
FROM dept_emp de, v_emp_dept ved
WHERE de.emp_no=ved.emp_no
AND ved.dept_name='Finance'
WITH CHECK OPTION;
CREATE VIEW v_finance_titles AS
SELECT t.*
FROM titles t, v_emp_dept ved
WHERE t.emp_no=ved.emp_no
AND ved.dept_name='Finance'
WITH CHECK OPTION;

image.png

2.1.4.2 建立角色

为财务部门建立一个经理角色（完全控制本部门数据，同时能查询其它部门的数据，能够为本部门职员分配权限），一个职员角色（可以插入和查询本部门数据）。

1
2
3
CREATE ROLE 
  role_finance_manager,
  role_finance_staff;

为角色分配权限。
image.png
注：MYSQL 只有 WITH GRANT OPTION，并且行为是非级联权限收回。
先分配部门经理角色对本部门表的权限。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
GRANT ALL 
ON emplorees.v_finance_emplorees
TO role_finance_manager
WITH GRANT OPTION;
GRANT ALL 
ON emplorees.v_finance_salaries
TO role_finance_manager
WITH GRANT OPTION;
GRANT ALL 
ON emplorees.v_finance_dept_emp
TO role_finance_manager
WITH GRANT OPTION;
GRANT ALL 
ON emplorees.v_finance_titles
TO role_finance_manager
WITH GRANT OPTION;

image.png
分配部门经理对其它部门表的查询权限。

1
2
3
GRANT SELECT
ON employees.*
TO role_finance_manager;

分配员工对本部门的权限。

1
2
3
4
5
6
7
8
9
10
11
12
GRANT SELECT, INSERT
ON employees.v_finance_employees
TO role_finance_staff;
GRANT SELECT, INSERT
ON employees.v_finance_salaries
TO role_finance_staff;
GRANT SELECT, INSERT
ON employees.v_finance_dept_emp
TO role_finance_staff;
GRANT SELECT, INSERT
ON employees.v_finance_titles
TO role_finance_staff;

image.png

2.1.4.3 建立用户

为财务部门建立一个经理用户和一个职员用户。

1
2
3
4
CREATE USER user_manager
IDENTIFIED BY '123456';
CREATE USER user_staff
IDENTIFIED BY '123456';

image.png
将角色赋予用户。

1
2
3
4
GRANT role_finance_manager
TO user_manager;
GRANT role_finance_staff
TO user_staff;

image.png

2.1.4.4 查询角色和用户的权限

查询权限使用 SHOW GRANTS 。
查询角色的权限。

1
2
3
4
5
6
7
8
9
10
11
12
SHOW GRANTS FOR role_finance_manager;
MariaDB [employees]> SHOW GRANTS FOR role_finance_manager;
+-------------------------------------------------------------------------------------------------------+
| Grants for role_finance_manager                                                                       |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'role_finance_manager'                                                          |
| GRANT SELECT ON `employees`.* TO 'role_finance_manager'                                               |
| GRANT ALL PRIVILEGES ON `emplorees`.`v_finance_titles` TO 'role_finance_manager' WITH GRANT OPTION    |
| GRANT ALL PRIVILEGES ON `emplorees`.`v_finance_emplorees` TO 'role_finance_manager' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `emplorees`.`v_finance_dept_emp` TO 'role_finance_manager' WITH GRANT OPTION  |
| GRANT ALL PRIVILEGES ON `emplorees`.`v_finance_salaries` TO 'role_finance_manager' WITH GRANT OPTION  |
+-------------------------------------------------------------------------------------------------------+

image.png

1
2
3
4
5
6
7
8
9
10
11
SHOW GRANTS FOR role_finance_staff;
MariaDB [employees]> SHOW GRANTS FOR role_finance_staff;
+-----------------------------------------------------------------------------------+
| Grants for role_finance_staff                                                     |
+-----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'role_finance_staff'                                        |
| GRANT SELECT, INSERT ON `employees`.`v_finance_salaries` TO 'role_finance_staff'  |
| GRANT SELECT, INSERT ON `employees`.`v_finance_titles` TO 'role_finance_staff'    |
| GRANT SELECT, INSERT ON `employees`.`v_finance_employees` TO 'role_finance_staff' |
| GRANT SELECT, INSERT ON `employees`.`v_finance_dept_emp` TO 'role_finance_staff'  |
+-----------------------------------------------------------------------------------+

image.png
查询用户的权限。

1
SHOW GRANTS FOR user_manager;

image.png

1
SHOW GRANTS FOR user_staff;

image.png

2.1.4.5 验证权限分配正确性

仅仅对职员用户进行尝试。
使用职员用户登录。
image.png
访问数据库时，会出现报错：

1
ERROR 1044 (42000): Access denied for user 'user_staff'@'%' to database 'employees'

image.png
原因是角色未启用。 [1]
登录后，启用角色
此时查询表，为
image.png

思考

  • 请分析 WITH CHECK OPTION、WITH GRANT OPTION、WITH ADMIN OPTION 有何区别和联系。

WITH CHECK OPTION 是视图选项，用于达到对视图进行更新时检查视图的条件的要求。

  1. 对于update,有with check option，要保证update后，数据要被视图查询出来 - 数据要满足创建视图时所设定的where子句；
  2. 对于delete,有无with check option都一样；
  3. 对于insert,有with check option，要保证insert后，数据要被视图查询出来；
  4. 对于没有where 子句的视图，使用with check option是多余的。

WITH GRANT OPTION 和 WITH ADMIN OPTION 在授权时使用，前者（WITH GRANT OPTION）在权限被撤销时，会级联撤销，后者不会级联撤销。

不同点：

  • with admin option 只能在赋予 system privilege 的时使用
  • with grant option 只能在赋予 object privilege 的时使用
  • 撤消带有admin option 的system privileges 时，连带的权限将保留
    例如：
  1. DBA 给了CREATE TABLE 系统权限给JEFF WITH ADMIN OPTION
  2. JEFF CREATES TABLE
  3. JEFF grants the CREATE TABLE 系统权限给EMI
  4. EMI CREATES A table
  5. DBA 撤消CREATE TABLE 系统权限从JEFF
    结果：
    JEFF‘S TABLE 依然存在，但不能创建新的TABLE 了
    EMI’S TABLE 依然存在，他还保留着CREATE TABLE 系统权限。
  • 撤消带有grant option 的object privileges 时，连带的权限也将撤消
    例如：
  1. JEFF 给了SELECT object privileges 在EMP 上 WITH ADMIN OPTION
  2. JEFF 给了SELECT 权限在EMP 上 TO EMI
  3. 后来，撤消JEFF的SELECT 权限

结果：
EMI 的权限也被撤消了


注：MYSQL 中只有 WITH GRANT OPTION ，且其行为类似 WITH ADMIN OPTION 。

  • 请结合上述实验示例分析使用角色进行权限分配有何优缺点。

优点：
使用角色进行权限分配便于管理权限（一个角色对应一种权限，而不是一个用户对应一种权限）。
对于通常的系统，比如：存在多个用户拥有相同的权限，在分配的时候就要分别为这几个用户指定相同的权限，修改时也要为这几个用户的权限进行一一修改。有了角色后，我们只需要为该角色制定好权限后，将相同权限的用户都指定为同一个角色即可，便于权限管理。
对于批量的用户权限调整，只需调整用户关联的角色权限，无需对每一个用户都进行权限调整，既大幅提升权限调整的效率，又降低了漏调权限的概率。
缺点：

  1. 角色维护：使用角色进行权限分配需要对角色进行维护，包括添加、修改和删除角色，这可能会增加管理复杂性。
  2. 复杂性：如果角色与权限的关系非常复杂，那么使用角色进行权限分配可能会变得非常复杂。例如，不同用户之间的权限都互不相同，且用户数量较少，使用角色进行分配，就会复杂而且冗余
  3. 风险：如果角色的权限分配不当，可能会导致系统安全风险，因此需要谨慎使用。