openGauss每日一练第 4天

#opengauss学习的第四天

创建角色role1为系统管理员, role2指定生效日期, role3具有LOGIN属性
omm=# create role role1 sysadmin encrypted password ‘r^123.comCOM’;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

omm=# create role role2 valid begin ‘2022-01-01 00:00:00’ password ‘r^123.comCOM’;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create role role3 login password ‘r^123.comCOM’;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.

重命名role1
omm=# alter role role1 rename to reole1111;
NOTICE: MD5 password cleared because of role rename
ALTER ROLE

修改role2密码
alter role role2 ENCRYPTED password ‘Abc123.com’;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE

将omm权限授权给role3,再回收role3的权限
omm=# \du
List of roles
Role name | Attributes
| Member of
-----------±------------------------------------------------------------------------------------------------------
-----------±----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyad
min, UseFT | {}
reole1111 | Cannot login, Sysadmin
| {}
role3 |
| {}

role2 | Cannot login
+| {}
| Role valid begin 2022-01-01 00:00:00+08
|
omm=# grant all privileges to role3;

omm=# \du
List of roles
Role name | Attributes
| Member of
-----------±------------------------------------------------------------------------------------------------------
-----------±----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyad
min, UseFT | {}
reole1111 | Cannot login, Sysadmin
| {}
| {}

role2 | Cannot login
+| {}
| Role valid begin 2022-01-01 00:00:00+08
|
role3 | Sysadmin

omm=# revoke omm from role3;
WARNING: role “role3” is not a member of role “omm”
REVOKE ROLE

删除所有创建角色

omm=# \du
List of roles
Role name | Attributes
| Member of
-----------±------------------------------------------------------------------------------------------------------
-----------±----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyad
min, UseFT | {}
reole1111 | Cannot login, Sysadmin
| {}
role2 | Cannot login
+| {}
| Role valid begin 2022-01-01 00:00:00+08
|
role3 | Sysadmin
| {}

omm=# drop role role1111;
ERROR: role “role1111” does not exist
omm=# drop role reole1111;
DROP ROLE
omm=# drop role role2;
DROP ROLE
omm=# drop role role;3;
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, Policyad
min, UseFT | {}