实验05-用户权限控制

# 实验5、用户权限控制
## 5.1 实验目的
熟悉SQL的用户权限控制功能，能够使用SQL语句实现用户、角色和Schema的创建、修改与删除，用户权限的授予和回收，安全策略设置等。
## 5.2 实验内容
1. 为方便管理，采用系统管理员用户nbuuser登录数据库，完成初始的权限分配工作，然后用相应用户名登录数据库以验证权限分配正确性。
2. 使用GRANT语句来对用户授权，对单个用户或多个用户授权，或使用保留字ALL、PUBLIC对所有用户授权。对不同的操作对象包括数据库、视图、基本表等进行不同权限的授权。
3. 使用WITH GRANT OPTION和WITH ADMIN OPTION子句授予用户传播该权限的权利。
4. 当在授权时发生循环授权，考察DBS能否发现这个错误。如果不能，结合取消权限操作，查看DBS对循环授权的控制。
5. 使用REVOKE子句收回授权，取消授权的级联反应。
## 5.3 实验步骤
为简化数据库db_uni的管理，老师的信息和学生的信息由校级教务教学部门来设置，课程信息和选课信息由院级教务教学部门来设置，教师可以设置学生成绩，学生查看课程成绩。以系统管理员nbuuser的身份登陆数据库db_uni，分别建立如下用户和角色。用户包括校级教务教学主管UeduSL，院级教务教学部门主管CeduSJ、CeduSA，教师TeaY、TeaW和学生StuG、StuL。角色包括UeduManagerRole、CeduManagerRole和TeacherRole、StudentRole。验证权限分配之前，请备份好数据库，针对不同用户所具有的权限，分别以系统管理员nbuuser身份或以上用户身份登陆到数据库db_uni中（用gsql客户端工具登陆更方便切换用户，具体命令可参考《02 openGauss数据库开发调试工具指导手册.docx》），设计相应的SQL 语句，进行操作加以验证，并记录操作结果。 
1. 创建用户
> 注意: 
CREATE USER 语句不是SQL标准，因此不同的RDBMS 语法和内容相差甚远,这里用的是openGauss创建用户语句,同时查阅资料如何将系统权限授权给角色或用户，参考GRANT语法解释。
> 通过CREATE USER创建的用户，默认具有LOGIN权限；
> 通过CREATE USER创建用户的同时系统会在执行该命令的数据库中，为该用户创建一个同名的SCHEMA；其他数据库中，则不自动创建同名的SCHEMA；用户可使用CREATE SCHEMA命令，分别在其他数据库中，为该用户创建同名SCHEMA。
> 系统管理员在普通用户同名schema下创建的对象，所有者为schema的同名用户（非系统管理员）。

**创建校院两级教务教学主管用户，要求具有创建用户或角色的权利。**
```sql
CREATE USER "UeduSL" WITH CREATEROLE PASSWORD 'Bigdata@123';
CREATE USER "CeduSJ" WITH CREATEROLE PASSWORD 'Bigdata@123';
CREATE USER "CeduSA" WITH CREATEROLE PASSWORD 'Bigdata@123';
```
** 创建教师和学生用户。
```sql
CREATE USER "TeaY"  IDENTIFIED BY  'Bigdata@123';
CREATE USER "TeaW"  IDENTIFIED BY  'Bigdata@123';
CREATE USER "StuG"  IDENTIFIED BY  'Bigdata@123';
CREATE USER "StuL"  IDENTIFIED BY  'Bigdata@123';
```
2.	创建角色并分配权限
** 分别创建校院两级教务教学管理角色UeduManagerRole和CeduManagerRole、教师角色TeacherRole和学生角色StudentRole。
```sql
CREATE ROLE "UeduManagerRole" WITH CREATEROLE PASSWORD 'Bigdata@123';
CREATE ROLE "CeduManagerRole" WITH CREATEROLE PASSWORD 'Bigdata@123';
CREATE ROLE "TeacherRole" WITH  PASSWORD 'Bigdata@123';
CREATE ROLE "StudentRole" WITH  PASSWORD 'Bigdata@123'; 
```
** 把数据库db_uni的模式nbuuser的使用权限授予所有角色与部分用户。
```sql
GRANT USAGE ON SCHEMA nbuuser to "UeduManagerRole" WITH GRANT OPTION;
GRANT USAGE ON SCHEMA nbuuser to "CeduManagerRole" WITH GRANT OPTION;
GRANT USAGE ON SCHEMA nbuuser to "TeacherRole" WITH GRANT OPTION;
GRANT USAGE ON SCHEMA nbuuser to "StudentRole" WITH GRANT OPTION;
GRANT USAGE ON SCHEMA nbuuser to "UeduSL","CeduSJ","CeduSA","TeaY","TeaW","StuG","StuL" WITH GRANT OPTION;
```
** 把Teachers、Students、Courses和Reports的相应权限分别授权给不同的角色
```sql
GRANT ALL ON TABLE Teachers,Students to "UeduManagerRole" WITH GRANT OPTION;
GRANT ALL ON TABLE Courses,Reports to "CeduManagerRole" WITH GRANT OPTION;
GRANT SELECT,UPDATE ON TABLE Reports to "TeacherRole" WITH GRANT OPTION;
GRANT SELECT ON TABLE Reports to "StudentRole" WITH GRANT OPTION;
```
3. 为用户分配角色及权限
** 为用户按角色分配并授予权限
```sql
GRANT "UeduManagerRole" TO "UeduSL" WITH ADMIN OPTION;
GRANT "CeduManagerRole" TO "CeduSJ" WITH ADMIN OPTION;
GRANT "TeacherRole" TO "TeaY" WITH ADMIN OPTION;
GRANT "StudentRole" TO "StuG" WITH ADMIN OPTION;
```
** 按用户单独赋予权限
GRANT SELECT ON TABLE Teachers,Students, Courses,Reports to "CeduSA","TeaW", "StuL" WITH GRANT OPTION;
4.	分别以UeduSL、CeduSJ、TeaY和StuG的身份登陆数据库db_uni，验证按角色授予的权限，用SQL语言查询nbuuser.Teachers、nbuuser.Students、nbuuser.Courses和nbuuser.Reports表，查询结果如何？并分析原因。
5.	用系统管理员nbuuser授予用户"CeduSA"对表nbuuser.Students插入和更新的权限，但不授予删除权限，并且授予用户"CeduSA"传播这两个权限的权利。以"CeduSA"的身份登陆，用SQL语言插入和更新Students表，结果如何？（注意更新操作的授权）
GRANT Insert ON TABLE Teachers,Students, Courses,Reports to "CeduSA","TeaW", "StuL" WITH GRANT OPTION;
6.	用系统管理员nbuuser授予允许用户"TeaW"在表Reports中插入元组，更新Score列，可以查询除了Sno以外的所有列。以"TeaW"的身份登陆，用SQL语言插入更新并查询reports表，结果如何？（注意更新操作的授权）
操作如下SQL语句，结果如何？为什么？
```sql
SELECT * FROM nbuuser.Reports;
UPDATE nbuuser.Reports SET Score= Score+0.5 WHERE Sno=’S01’;
```
7.	用户"CeduSA"授予用户"TeaW"对表Students插入和更新的权限，并且授予用户"TeaW"传播插入操作的权利。分别以"CeduSA"和"TeaW"的身份登陆，用SQL语言验证以上授权操作，结果如何？

8.	收回用户"CeduSA"对表Courses查询权限的授权。分别以"CeduSA"和"TeaW"的身份登陆，用SQL语言查询Courses表，查询结果如何？

9.	由上面（6）和（7）的授权，再由用户"TeaW"对用户"StuL"授予表Students插入和更新的权限，并且授予用户"StuL"传播插入操作的权力。这时候，如果由"StuL"对"CeduSA"授予表Students的插入和更新权限是否能得到成功？如果能够成功，那么如果由用户"TeaW"取消"StuL"的权限，对"CeduSA"会有什么影响？如果再由nbuuser取消"CeduSA"的权限，对"TeaW"有什么影响？
## 5.4实验要求
1. 实验之前请细细阅读实验总体要求与说明指导书
2.  在OpenGauss环境中，完成以上实验(1)-(9)步所有SQL用户权限控制操作，并完成实验报告。

大数据学习

导航

最近发表

友情链接