1.创建角色role1为系统管理员, role2指定生效日期, role3具有LOGIN属性
omm=# CREATE ROLE role1 LOGIN SYSADMIN IDENTIFIED BY 'test_456';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=#
omm=#
omm=# CREATE ROLE role2 WITH LOGIN PASSWORD 'test_789' VALID BEGIN '2021-12-10' VALID UNTIL '2021-12-30';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=#
omm=#
omm=# CREATE ROLE role3 WITH LOGIN PASSWORD 'test_123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=#
omm=#
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 | {}
role1 | Sysadmin | {}
role2 | Role valid begin 2021-12-10 00:00:00+08 +| {}
| Role valid until 2021-12-30 00:00:00+08 |
role3 | | {}
omm=# \du+
List of roles
Role name | Attributes | Member of | Description
role2 | Role valid begin 2021-12-10 00:00:00+08 +| {} |
| Role valid until 2021-12-30 00:00:00+08 | |
role3 | | {} |
omm=# -----------+------------------------------------------------------------------------------------------------------------------+-----------+-------------
gaussdb | Sysadmin | {} |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {} |
role1 | Sysadmin | {} |
2.重命名role1
omm=#
omm=# ALTER ROLE role1 RENAME TO role10;
NOTICE: MD5 password cleared because of role rename
ALTER ROLE
3.修改role2密码
omm=# ALTER ROLE role2 IDENTIFIED BY 'abcd@123' replace 'test_789';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
omm=#
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-10 00:00:00+08 +| {}
| Role valid until 2021-12-30 00:00:00+08 |
role3 | | {}
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 | {} |
role10 | Sysadmin | {} |
role2 | Role valid begin 2021-12-10 00:00:00+08 +| {} |
| Role valid until 2021-12-30 00:00:00+08 | |
role3 | | {} |
4.将omm权限授权给role3,再回收role3的权限
omm=# GRANT omm to role3 with admin option;
GRANT ROLE
omm=#
omm=# revoke all privilege from role3;
ALTER ROLE
omm=#
5.删除所有创建角色
omm=# drop role role10;
DROP ROLE
omm=# drop role role2;
DROP ROLE
omm=# drop role role3;
DROP ROLE
omm=#
omm=#
omm=# \du
gaussdb | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
omm=# List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
omm=#
