openGauss创建角色、修改角色属性、更改角色权限和删除角色练习

1.创建角色

–列出所有数据库角色

omm=# \du
-----------+------------------------------------------------------------------------------------------------------------------
+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT
| {}

List of roles
Role name | Attributes
| Member of

–创建角色manager1，密码test_123 

omm=# CREATE ROLE manager1 IDENTIFIED BY 'test_123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

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

omm=# CREATE ROLE manager2 LOGIN SYSADMIN IDENTIFIED BY 'test_456';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

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

omm=# CREATE ROLE manager3 WITH LOGIN PASSWORD 'test_789' VALID BEGIN '2021-12-10' VALID
omm-# UNTIL '2021-12-30';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

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

omm=# \du+
List of roles
Role name | Attributes
| Member of | Description
-----------+------------------------------------------------------------------------------------------------------------------
+-----------+-------------
gaussdb | Sysadmin
| {} |
manager1 | Cannot login
| {} |
manager2 | Sysadmin
| {} |
manager3 | Role valid begin 2021-12-10 00:00:00+08 +
| {} |
| Role valid until 2021-12-30 00:00:00+08
| |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT
| {} | 

2.修改角色属性

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

omm=# ALTER ROLE manager1 SYSADMIN LOGIN;
ALTER ROLE
omm=# 

–查看manager1

omm=# \du+ manager1
List of roles
Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
manager1 | Sysadmin | {} |

–修改角色manager2密码

omm=# ALTER ROLE manager2 IDENTIFIED BY 'abcd@123' ;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE

–重命名manager2

omm=# ALTER ROLE manager2 RENAME TO manager20;
NOTICE: MD5 password cleared because of role rename
ALTER ROLE

3.授权

–将omm的权限授权给manager1

omm=# GRANT omm to manager1 with admin option;
GRANT ROLE

4.回收权限

omm=# revoke all privilege from manager1;
ALTER ROLE

5.删除角色

omm=# drop role manager1;
DROP ROLE
omm=# drop role manager20;
DROP ROLE
omm=# drop role manager3;
DROP ROLE

操作截图：

课后作业

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

omm=# CREATE ROLE role1 LOGIN SYSADMIN IDENTIFIED BY 'role_123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=#
omm=# CREATE ROLE role2 WITH LOGIN PASSWORD 'role_123' VALID BEGIN '2021-12-21' VALID UNTIL '2021-12-30';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=#
omm=# CREATE ROLE role3 WITH LOGIN PASSWORD 'role_123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
omm=# CREATE ROLE

2.重命名role1

omm=# ALTER ROLE role1 RENAME TO role1new;
NOTICE: MD5 password cleared because of role rename
ALTER ROLE

3.修改role2密码

omm=# ALTER ROLE role2 IDENTIFIED BY 'role@123' ;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE

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

omm=# GRANT omm to role3 with admin option;
GRANT ROLE
omm=# revoke all privilege from role3;
ALTER ROLE
omm=# 

5.删除所有创建角色

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

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
| {}
role1new | Sysadmin
| {}
role2 | Role valid begin 2021-12-21 00:00:00+08 +
| {}

| Role valid until 2021-12-30 00:00:00+08
|
role3 |
| {omm}

omm=# \du+ role1new;
List of roles
Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
role1new | Sysadmin | {} |

omm=# \du+ role2;
List of roles
Role name | Attributes | Member of | Description
-----------+-----------------------------------------+-----------+-------------
role2 | Role valid begin 2021-12-21 00:00:00+08+| {} |
| Role valid until 2021-12-30 00:00:00+08 | |

omm=# \du+ role3;
List of roles
Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
role3 | | {omm} | 

omm=# drop role role1new;
DROP ROLE
omm=# drop role role2;
DROP ROLE
omm=# drop role role3;
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
| {}

操作截图：