数据库作业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 表的任何权限。
登录 user3 ,尝试查询和插入
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;
确实可以查询和插入
登录 user4 ,尝试查询、更新和删除
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.验证
登录 user4 ,
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.验证
登录 user3 ,尝试删除数据。
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.验证
登录 user3 ,尝试查询、插入、更新、删除。
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;
可见并不是。