openGauss每日一练第4天 | 学习openGauss创建角色、修改角色属性、更改角色权限和删除角色

本次课程的学习目标是学习openGauss创建角色、修改角色属性、更改角色权限和删除角色。

角色是用来管理权限的，从数据库安全的角度考虑，可以把所有的管理和操作权限划分到不同的角色上

连接openGauss

#第一次进入等待15秒
#数据库启动中...
su - omm
gsql -r

1.创建角色

–列出所有数据库角色

\du

–创建角色manager1，密码test_123

CREATE ROLE manager1 IDENTIFIED BY 'test_123';

–创建角色manager2，密码test_456，具有LOGIN属性且为系统管理员

CREATE ROLE manager2 LOGIN SYSADMIN IDENTIFIED BY 'test_456';

–创建角色manager3，密码test_789，从2021年12月10日生效，2021年12月30日失效

CREATE ROLE manager3 WITH LOGIN PASSWORD 'test_789' VALID BEGIN '2021-12-10' VALID
UNTIL '2021-12-30';

–再次查看所有数据库角色

\du+

2.修改角色属性

–修改角色manage1具有LOGIN属性且为系统管理员

ALTER ROLE manager1 SYSADMIN LOGIN;

–查看manager1

\du+ manager1

–修改角色manager2密码

ALTER ROLE manager2 IDENTIFIED BY 'abcd@123' ;

–重命名manager2

ALTER ROLE manager2 RENAME TO manager20;

3.授权

–将omm的权限授权给manager1

GRANT omm to manager1 with admin option;

4.回收权限

revoke all privilege from manager1;

5.删除角色

drop role manager1;
drop role manager20;
drop role manager3;

课后作业

1.创建角色role1为系统管理员, role2指定生效日期, role3具有LOGIN属性

omm=# create role role1 sysadmin login identified by 'abcd@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

omm=# create role role2 with login password 'abcd@123' valid begin '2021-12-04' valid until '2021-12-31';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

omm=# create role role3 login identified by 'abcd@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

2.重命名role1

omm=# create role role3 login identified by 'abcd@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

然后查看一下是否成功

omm=# \du
                                                              List of roles
 Role name |                                                    Attributes                                                    | Member of 
-----------+------------------------------------------------------------------------------------------------------------------+-----------
 gaussdb   | Sysadmin                                                                                                         | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
 role10    | Sysadmin                                                                                                         | {}
 role2     | Role valid begin 2021-12-04 00:00:00+08                                                                         +| {}
           | Role valid until 2021-12-31 00:00:00+08                                                                          | 
 role3     |                                                                                                                  | {}

3.修改role2密码

omm=# alter role role2 identified by 'abcd@123' replace 'abcd_123';
ERROR:  New password should not equal to the old ones.
omm=# alter role role2 identified by 'abcd_123' replace 'abcd@123';    
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE

可以看到我第一次吧密码写反了，identified后面的是更新后的密码，后面的才是原密码。

4.将omm权限授权给role3,再回收role3的权限

omm=# grant omm to role3 with admin option;
GRANT ROLE

查看修改结果

omm=# \du
                                                              List of roles
 Role name |                                                    Attributes                                                    | Member of 
-----------+------------------------------------------------------------------------------------------------------------------+-----------
 gaussdb   | Sysadmin                                                                                                         | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
 role10    | Sysadmin                                                                                                         | {}
 role2     | Role valid begin 2021-12-04 00:00:00+08                                                                         +| {}
           | Role valid until 2021-12-31 00:00:00+08                                                                          | 
 role3     |                                                                                                                  | {omm}

5.删除所有创建角色

omm=# drop role role10;
DROP ROLE
omm=# drop role role2;
DROP ROLE
omm=# drop role role3;
DROP ROLE
omm=# \du+
                                                                     List of roles
 Role name |                                                    Attributes                                                    | Member of | Description 
-----------+------------------------------------------------------------------------------------------------------------------+-----------+-------------
 gaussdb   | Sysadmin                                                                                                         | {}        | 
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}        | 

过程中使用\du或\du+查看角色信息

啊啊啊开始补作业啦