数据库作业4.1

例4.1

1.新创建一个用户：user1

先用 omm 用户登录数据库

gsql -d postgres -p 26000 -r
SELECT * FROM pg_user;	//查看数据库用户列表。
CREATE USER user1 IDENTIFIED BY 'user1USER!';//创建用户user1，密码是user1USER！
SELECT * FROM pg_user;	//查看数据库用户列表。

2.退出omm用户，用user1登录数据库

用user1用户查询pg_user表，发现没有权限。

gsql -d postgres -p 26000 -r -U user1 -W user1USER!

SELECT * FROM pg_user;

3.登录用 omm 用户给 user1 授权

gsql -d postgres -p 26000 -r

GRANT SELECT ON TABLE pg_user TO user1;

4.再次登录 user1 用户，查询 pg_user 表

gsql -d postgres -p 26000 -r -U user1 -W user1USER!

SELECT * FROM pg_user;

授权成功，查询成功。

例4.2

1.切换到数据库 teach

\c teach              //切换数据库
\dt                   //show tables
select * from course;

可见 user1 没有查询权限

2.登录teach数据库的所有者（dboper1206）给 user1 授权

gsql -d teach -p 26000 -r -U dboper1206 -W Cfq_123123

GRANT ALL PRIVILEGES ON TABLE course,student TO user1;

3.重新用user1登录teach数据库

gsql -d teach -p 26000 -r -U user1 -W user1USER!
\dt
SELECT * FROM course;

INSERT INTO course VALUES(0211,'cname_test',NULL,2);
SELECT * FROM course;

UPDATE course SET cname='cname_test2' WHERE cno=0211;
SELECT * FROM course;

DELETE FROM course WHERE cno=0211;
SELECT * FROM course;

这回有了查询、插入、更新、删除的权限。

例4.3

1.确认哪些用户没有对 teach 数据库中 sc 表的查询权限

omm 和 dboper1206 肯定有权限

gsql -d teach -p 26000 -r -U user1 -W user1USER!
SELECT * FROM sc；

gsql -d teach -p 26000 -r -U cfq1206 -W Cfq_123123
SELECT * FROM sc；

user1 和 cfq1206 这两个用户没有查询权限

2.登录 dboper1206 授权（允许所有用户查询 SC 表）

gsql -d teach -p 26000 -r -U dboper1206 -W Cfq_123123

GRANT SELECT ON TABLE sc TO PUBLIC;

3.登录user1 和 cfq1206 进行验证

gsql -d teach -p 26000 -r -U user1 -W user1USER!
SELECT * FROM sc limit 2;

gsql -d teach -p 26000 -r -U cfq1206 -W Cfq_123123
SELECT * FROM sc limit 2;

例4.4

1.先看看cfq1206的teach数据库中student表的权限

gsql -d teach -p 26000 -r -U cfq1206 -W Cfq_123123

SELECT * FROM student limit 2;
DELETE FROM student;

可见没有查询和删除权限。不知道表的属性也没法 INSERT 和 UPDATE。

2.登录 dboper1206 进行授权

gsql -d teach -p 26000 -r -U dboper1206 -W Cfq_123123

GRANT UPDATE(Sno),SELECT ON TABLE student TO cfq1206;

3.重新登录 cfq1206 进行验证

gsql -d teach -p 26000 -r -U cfq1206 -W Cfq_123123

SELECT * FROM student limit 2;

UPDATE student SET Sno=300215128 WHERE Sno=200215128;
SELECT * FROM student where sname='孙铭旋';

UPDATE student SET Sname='test' WHERE Sno=300215128;
DELETE FROM student;
INSERT INTO student VALUES(400215129,'test',null,null);

发现没有修改student表sname属性的权限，也没有 insert 和 delete 权限。

例4.5

1.登录 cfq1206 尝试给 user1 授权

gsql -d teach -p 26000 -r -U cfq1206 -W Cfq_123123

GRANT INSERT ON TABLE sc TO user1;

不允许

2.登录 dboper 进行授权

gsql -d teach -p 26000 -r -U dboper1206 -W Cfq_123123

GRANT INSERT ON TABLE sc TO cfq1206 WITH GRANT OPTION;

3.再次登录 cfq1206 尝试给 user1 授权

gsql -d teach -p 26000 -r -U cfq1206 -W Cfq_123123

GRANT INSERT ON TABLE sc TO user1;

她说授权成功，那再登录 user1 验证一下。

例4.6

1.创建 user2

gsql -d postgres -p 26000 -r
CREATE USER user2 IDENTIFIED BY 'user2USER@';
SELECT * FROM pg_user;

2.用 cfq1206 给 user1 授权

gsql -d teach -p 26000 -r -U cfq1206 -W Cfq_123123

GRANT INSERT ON TABLE sc TO user1 WITH GRANT OPTION;

3.用 user1 给 user2 授权

gsql -d teach -p 26000 -r -U user1 -W user1USER!

GRANT INSERT ON sc TO user2 WITH GRANT OPTION;

授权成功

例4.7

1.创建user3，user4

gsql -d postgres -p 26000 -r
CREATE USER user3 IDENTIFIED BY 'user3USER#';
CREATE USER user4 IDENTIFIED BY 'user4USER$';
SELECT * FROM pg_user;

2.用 user2 给 user3 授权

gsql -d teach -p 26000 -r -U user2 -W user2USER@

GRANT INSERT ON sc TO user3;

3.尝试用 user3 给 user 4授权

gsql -d teach -p 26000 -r -U user3 -W user3USER#

GRANT INSERT ON sc TO user4;

验证了 user3 确实不能传播权限。

遇到的问题

1.外键完整性约束

当我想修改 student 表的 sno 字段的时候，他不让我改。

刚开始我想把 no action 改成 CASCADE ，查了半天的 openGauss 语法，也没搞出来。

只能是先把这个约束条件删了，再重新添加一个 CASCADE 的约束条件。

alter table sc drop CONSTRAINT sc_sno_fkey;

alter table sc add constraint sc_sno_fkey foreign key(sno) references student(sno) on delete cascade on update cascade;

例4.8

1.登录 dboper1206 收回 cfq1206 的权限

gsql -d teach -p 26000 -r -U dboper1206 -W Cfq_123123

REVOKE UPDATE(Sno) ON TABLE Student FROM cfq1206;

2.验证

例 4.4 已经验证了 cfq1206 是有 update 权限的。

所以只要验证现在没有即可。

gsql -d teach -p 26000 -r -U cfq1206 -W Cfq_123123

UPDATE student SET Sno=200215128 WHERE Sno=300215128;

例4.9

1.收回所有用户对SC的查询权限

gsql -d teach -p 26000 -r -U dboper1206 -W Cfq_123123

REVOKE SELECT ON TABLE SC FROM PUBLIC;

2.验证

cfq1206的权限被收回

gsql -d teach -p 26000 -r -U cfq1206 -W Cfq_123123

select * from sc limit 2;

然而 dboper 和 omm 的权限还存在

gsql -d teach -p 26000 -r -U dboper1206 -W Cfq_123123

select * from sc limit 2;

例4.10

1.登录 dboper1206 级联收回例4.5 授予 user1 的 insert 权限

gsql -d teach -p 26000 -r -U dboper1206 -W Cfq_123123

REVOKE INSERT ON TABLE sc FROM user1 CASCADE;

2.验证

权限没有回收成功

user1 还能 insert

gsql -d teach -p 26000 -r -U user1 -W user1USER!

INSERT INTO sc VALUES(200215133,'0201',2);

user2 还能 insert

gsql -d teach -p 26000 -r -U user1 -W user1USER!

INSERT INTO sc VALUES(200215133,'0202',2);

遇到的问题

问题1

REVOKE SELECT ON TABLE SC FROM PUBLIC 是否如教材所说，是收回所有用户的权限？

经过例4.9的实践，我们知道，教材上的这条指令只能收回 public 的权限，而不是所有用户的权限。

其中 dboper 和 omm 用户的权限，并不属于public。

换言之，管理员用户的权限和数据库所有者的权限，是不能被数据库所有者自己所收回的。

这里的 public 应该是指除了管理员和当前数据库所有者之外的用户。

问题2

例 4.10 权限级联回收失败。

首先验证是否是 dboper1206 权限不够大的原因

用 omm 用户（管理员用户）重新尝试

gsql -d teach -p 26000 -r
REVOKE INSERT ON TABLE sc FROM user1 CASCADE;
\q

gsql -d teach -p 26000 -r -U user1 -W user1USER!
INSERT INTO sc VALUES(200215133,'0201',2);

确实还有 insert 权限。（如果没有权限的话，应该直接报permission denied）

不用级联操作看看是否有效

gsql -d teach -p 26000 -r
REVOKE INSERT ON TABLE sc FROM user1 CASCADE;
\q

gsql -d teach -p 26000 -r -U user1 -W user1USER!
INSERT INTO sc VALUES(200215133,'0201',2);

也没能收回 insert 权限。

经过不断尝试，最后用 Cfq1206 这个用户收回了权限

gsql -d teach -p 26000 -r -U cfq1206 -W Cfq_123123
REVOKE INSERT ON TABLE sc FROM user1 CASCADE;
\q

gsql -d teach -p 26000 -r -U user1 -W user1USER!
INSERT INTO sc VALUES(200215133,'0201',2);
\q

gsql -d teach -p 26000 -r -U user2 -W user2USER@
INSERT INTO sc VALUES(200215133,'0201',2);

结论

OpenGuass 的权限回收，遵循 “谁授予，谁回收” 的原则，哪怕是管理员，也不能强行回收别人授予的权限。

例4.11

1.使用 dboper1206 创建角色 R1，并赋予权限

gsql -d postgres -p 26000 -r -U dboper1206 -W Cfq_123123

SELECT * FROM PG_ROLES;

当前有 7 个角色，也就是说用户默认有一个角色

CREATE ROLE R1;                //提示我要有密码
CREATE ROLE R1 IDENTIFIED BY 'user5USER%'; 
SELECT * FROM PG_ROLES;

创建了新的角色 R1

授权

gsql -d teach -p 26000 -r -U dboper1206 -W Cfq_123123

GRANT SELECT,UPDATE,INSERT ON TABLE Student TO R1;

2.使用角色 R1 给 user3，user4 授权

gsql -d teach -p 26000 -r -U dboper1206 -W Cfq_123123

GRANT R1 TO user3,user4;

3.验证

之前 user3 和 user4 没有关于 student 表的任何权限。

gsql -d teach -p 26000 -r -U user3 -W user3USER#

SELECT * FROM Student;

INSERT INTO student VALUES(1,'TEST',NULL,NULL,NULL);
SELECT * FROM STUDENT WHERE SNO=1;

确实可以查询和插入

gsql -d teach -p 26000 -r -U user4 -W user4USER$

SELECT * FROM Student limit 3;

UPDATE student SET Sage=19 WHERE Sno=1;
SELECT * FROM STUDENT WHERE SNO=1;

DELETE FROM Student;

确实有查询和更新的权限，没有删除的权限。

4.收回 user4 的 R1 角色权限

gsql -d teach -p 26000 -r -U dboper1206 -W Cfq_123123

REVOKE R1 FROM user4;

5.验证

gsql -d teach -p 26000 -r -U user4 -W user4USER$

SELECT * FROM Student limit 3;
UPDATE student SET Sage=19 WHERE Sno=1;
INSERT INTO student VALUES(1,'TEST',NULL,NULL,NULL);

可见，查询、插入和更新的权限都没有了

例4.12

1.登录 dboper1206 修改角色 R1 的权限

gsql -d teach -p 26000 -r -U dboper1206 -W Cfq_123123

GRANT DELETE ON TABLE Student TO R1;

2.验证

gsql -d teach -p 26000 -r -U user3 -W user3USER#

DELETE FROM student WHERE Sno=1;

可见 user3 确实拥有了删除的权限。

例4.13

1.收回角色 R1 的 select 权限

gsql -d teach -p 26000 -r -U dboper1206 -W Cfq_123123

REVOKE SELECT ON TABLE Student FROM R1;

2.验证

gsql -d teach -p 26000 -r -U user3 -W user3USER#

SELECT *  FROM Student;

INSERT INTO student VALUES(1,'TEST',NULL,NULL,NULL);
UPDATE student SET Sage=19 WHERE Sno=1;
DELETE FROM student WHERE Sno=1;

可见，查询权限没了，还可以insert，但是 update 和 delete 也不行了

问题

问题1

openGauss中的角色是否可以用作用户登录数据库？

gsql -d teach -p 26000 -r -U R1 -W user5USER%

不能登录

试一下 with login

gsql -d postgres -p 26000 -r

DROP ROLE R2;
CREATE ROLE R2 WITH LOGIN IDENTIFIED BY 'user6USER^';

gsql -d postgres -p 26000 -r -U R2 -W user6USER^

还是不能登录

问题2

使用角色给用户授予权限的时候，是直接赋予权限，还是将用户标识为某个角色，间接赋予权限？

换言之，使用角色授权后，数据库是如何判断用户的权限的？

是直接看到用户有 xxx 权限？还是先看到用户是某个角色，再去查询这个角色有哪些权限？

如果是直接去修改用户的权限的话，每当角色权限变化的时候，都要找到相关的用户，执行修改权限的操作。

如果角色自己维护一个权限的表的话，用户执行操作之前，去查询这张表，看看有没有权限就好了。

问题3

select ，insert，update，delete 四个权限是否是相互独立的？

通过例4.13的实践发现，select 和 insert 权限应该是相互独立的，而 update 和 delete 权限相当于 select 的 '子权限'。

也就是说，一旦角色的 select 权限被收回，那么 update 和 delete 权限也会自动一并一起收回。

为了更加详细的验证，下面进行补充验证。

第一：select 是否是 insert 的 '子权限'？

gsql -d teach -p 26000 -r -U dboper1206 -W Cfq_123123

GRANT SELECT ON TABLE Student TO R1;
REVOKE INSERT ON TABLE Student FROM R1;

gsql -d teach -p 26000 -r -U user3 -W user3USER#

SELECT * FROM student;
INSERT INTO student VALUES(1,'TEST',NULL,NULL,NULL);
UPDATE student SET Sage=19 WHERE Sno=1;
DELETE FROM student WHERE Sno=1;

可见收回 insert 权限，不影响 select 、update 、delete 的权限。

第二：update 和 deleter 是否相互独立？

gsql -d teach -p 26000 -r -U dboper1206 -W Cfq_123123

GRANT INSERT ON TABLE Student TO R1;
REVOKE UPDATE ON TABLE Student FROM R1;

\q
gsql -d teach -p 26000 -r -U user3 -W user3USER#

INSERT INTO student VALUES(1,'TEST',NULL,NULL,NULL);
UPDATE student SET Sage=19 WHERE Sno=1;
DELETE FROM student WHERE Sno=1;

可见收回 update权限，不影响delete权限

gsql -d teach -p 26000 -r -U dboper1206 -W Cfq_123123

GRANT UPDATE ON TABLE Student TO R1;
REVOKE DELETE ON TABLE Student FROM R1;

\q
gsql -d teach -p 26000 -r -U user3 -W user3USER#

INSERT INTO student VALUES(1,'TEST',NULL,NULL,NULL);
UPDATE student SET Sage=19 WHERE Sno=1;
DELETE FROM student WHERE Sno=1;

可见收回deleter权限，也不影响update权限

综上所述，update 和 delete 互不影响，相互独立，同时为 select 权限的 '子权限'。

第三：update 和 deleter 是否是 insert 的'子权限'？

gsql -d teach -p 26000 -r -U dboper1206 -W Cfq_123123

GRANT SELECT ON TABLE Student TO R1;
REVOKE INSERT ON TABLE Student FROM R1;

\q
gsql -d teach -p 26000 -r -U user3 -W user3USER#

SELECT * FROM student LIMIT 3;
INSERT INTO student VALUES(1,'TEST',NULL,NULL,NULL);
UPDATE student SET Sage=19 WHERE Sno=1;
DELETE FROM student WHERE Sno=1;

可见并不是。

结论

posted @ 2022-05-16 15:47 191206 阅读(88) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

191206

数据库作业4.1

例4.1

1.新创建一个用户：user1

2.退出omm用户，用user1登录数据库

3.登录用 omm 用户给 user1 授权

4.再次登录 user1 用户，查询 pg_user 表

例4.2

1.切换到数据库 teach

2.登录teach数据库的所有者（dboper1206）给 user1 授权

3.重新用user1登录teach数据库

例4.3

1.确认哪些用户没有对 teach 数据库中 sc 表的查询权限

2.登录 dboper1206 授权（允许所有用户查询 SC 表）

3.登录user1 和 cfq1206 进行验证

例4.4

1.先看看cfq1206的teach数据库中student表的权限

2.登录 dboper1206 进行授权

3.重新登录 cfq1206 进行验证

例4.5

1.登录 cfq1206 尝试给 user1 授权

2.登录 dboper 进行授权

3.再次登录 cfq1206 尝试给 user1 授权

例4.6

1.创建 user2

2.用 cfq1206 给 user1 授权

3.用 user1 给 user2 授权

例4.7

1.创建user3，user4

2.用 user2 给 user3 授权

3.尝试用 user3 给 user 4授权

遇到的问题

1.外键完整性约束

例4.8

1.登录 dboper1206 收回 cfq1206 的权限

2.验证

例4.9

1.收回所有用户对SC的查询权限

2.验证

例4.10

1.登录 dboper1206 级联收回 例4.5 授予 user1 的 insert 权限

2.验证

遇到的问题

问题1

问题2

首先验证是否是 dboper1206 权限不够大的原因

不用级联操作看看是否有效

经过不断尝试，最后用 Cfq1206 这个用户收回了权限

结论

例4.11

1.使用 dboper1206 创建角色 R1，并赋予权限

2.使用角色 R1 给 user3，user4 授权

3.验证

4.收回 user4 的 R1 角色权限

5.验证

例4.12

1.登录 dboper1206 修改角色 R1 的权限

2.验证

例4.13

1.收回角色 R1 的 select 权限

2.验证

问题

问题1

问题2

问题3

第一：select 是否是 insert 的 '子权限'？

第二：update 和 deleter 是否相互独立？

第三：update 和 deleter 是否是 insert 的'子权限'？

结论

公告

1.登录 dboper1206 级联收回例4.5 授予 user1 的 insert 权限