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

课后作业

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

create role role1 sysadmin identified by 'role1_123';
create role role2 with login password 'role2_456' valid begin '2021-12-05';
create role role3 login identified by 'role3_789';

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 | {}        | 
 role1     | Cannot login, Sysadmin                                                                                           | {}        | 
 role2     | Role valid begin 2021-12-05 00:00:00+08                                                                          | {}        | 
 role3     |                                                                                                                  | {}        | 

2.重命名role1

alter role role1 rename to role10;

3.修改role2密码

alter role role2 identified by 'role2_456789';

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

grant omm to role3 with admin option;
revoke all privilege from role3;

5.删除所有创建角色

drop role role10;
drop role role2;
drop role role3;

openGauss SQL学习参考资料