学习目标：学习openGauss创建角色、修改角色属性、更改角色权限和删除角色
学习内容部分如下：
课后作业：
1.创建角色role1为系统管理员, role2指定生效日期, role3具有LOGIN属性
omm=# create role role1 sysadmin identified by 'password_1';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create role role2 identified by 'password_2' valid begin '2021-12-18' valid until '2021-12-19';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create role role3 with login identified by 'password_3';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
omm=# CREATE ROLE
2.重命名role1
omm=# alter role role1 rename to role4;
NOTICE: MD5 password cleared because of role rename
ALTER ROLE
3.修改role2密码
omm=# alter role role2 identified by 'password_4' replace 'password_2';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER 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
| {} |
role2 | Cannot login +
| {} |
| Role valid begin 2021-12-18 00:00:00+08 +
| | | Role valid until 2021-12-19 00:00:00+08
| |
role3 |
| {} |
role4 | Cannot login, Sysadmin
| {} |
4.将omm权限授权给role3,再回收role3的权限
omm=# grant omm to role3 with admin option;
GRANT ROLE
omm=# revoke all privilege from role3;
ALTER 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
| {} |
role2 | Cannot login +
| {} |
| Role valid until 2021-12-19 00:00:00+08
| |
role3 |
| {omm} |
role4 | Cannot login, Sysadmin
| {} | | Role valid begin 2021-12-18 00:00:00+08 +
| |
5.删除所有创建角色
omm=# drop role role2;
DROP ROLE
omm=# drop role role3;
DROP ROLE
omm=# drop role role4;
omm=# DROP 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
| {}
过程中使用\du或\du+查看角色信息
