openGauss每日一练第4天|角色的增删改操作

第4天openGauss每日一练的内容是学习创建角色、修改角色属性、更改角色权限和删除角色等操作。

课堂内容

1.创建角色

• 列出所有数据库角色

\du

• 创建角色manager1，密码test_123

CREATE ROLE manager1 IDENTIFIED BY 'test_123';

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

CREATE ROLE manager2 LOGIN SYSADMIN IDENTIFIED BY 'test_456';

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

CREATE ROLE manager3 WITH LOGIN PASSWORD 'test_789' VALID BEGIN '2021-12-10' VALID
UNTIL '2021-12-30';

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

\du+

2.修改角色属性

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

ALTER ROLE manager1 SYSADMIN LOGIN;

• 查看manager1

\du+ manager1

• 修改角色manager2密码

ALTER ROLE manager2 IDENTIFIED BY 'abcd@123' replace 'test_456';

• 重命名manager2

ALTER ROLE manager2 RENAME TO manager20;

3.授权

–将omm的权限授权给manager1

GRANT omm to manager1 with admin option;

4.回收权限

revoke all privilege from manager1;

5.删除角色

drop role manager1;
drop role manager20;
drop role manager3;

课后作业

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

create role role1 sysadmin identified by 'test@123';
create role role2 with login password 'test@456' valid begin '2021-12-04' valid until '2021-12-31';
create role role3 login identified by 'test@789';

omm=# \du
                                                              List of roles
 Role name |                                                    Attributes                                          
          | Member of 
-----------+--------------------------------------------------------------------------------------------------------
----------+-----------
 gaussdb   | Sysadmin                                                                                               
          | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadm
in, UseFT | {}
 role1     | Cannot login, Sysadmin                                                                                 
          | {}
           | Role valid until 2021-12-31 00:00:00+08                                                                
          | 
 role2     | Role valid begin 2021-12-04 00:00:00+08                                                                
         +| {}
 role3     |                                                                                                        
          | {}

2.重命名role1

alter role role1 rename to role10;

不过直接改role的名字后原密码会被清空，这时候我们要手动重新设一下。

alter role role10 identified by 'test@123123';

omm=# \du
                                                              List of roles
 Role name |                                                    Attributes                                          
          | Member of 
-----------+--------------------------------------------------------------------------------------------------------
----------+-----------
 gaussdb   | Sysadmin                                                                                               
          | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadm
in, UseFT | {}
 role10    | Cannot login, Sysadmin                                                                                 
          | {}
           | Role valid until 2021-12-31 00:00:00+08                                                                
          | 
 role3     |                                                                                                        
          | {}

 role2     | Role valid begin 2021-12-04 00:00:00+08                                                                
         +| {}

3.修改role2密码

alter role role2 identified by 'test@456456' replace 'test@456'; 

omm=# \du
                                                              List of roles
 Role name |                                                    Attributes                                          
          | Member of 
-----------+--------------------------------------------------------------------------------------------------------
----------+-----------
 gaussdb   | Sysadmin                                                                                               
          | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadm
in, UseFT | {}
 role10    | Cannot login, Sysadmin                                                                                 
          | {}
 role2     | Role valid begin 2021-12-04 00:00:00+08                                                                
         +| {}
           | Role valid until 2021-12-31 00:00:00+08                                                                
          | 
 role3     |                                                                                                        
          | {}

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

grant omm to role3 with admin option;

omm=# \du
                                                              List of roles
 Role name |                                                    Attributes                                          
          | Member of 
-----------+--------------------------------------------------------------------------------------------------------
----------+-----------
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadm
in, UseFT | {}
 role10    | Cannot login, Sysadmin                                                                                 
          | {}
 role2     | Role valid begin 2021-12-04 00:00:00+08                                                                
         +| {}
           | Role valid until 2021-12-31 00:00:00+08                                                                
          | 
 gaussdb   | Sysadmin                                                                                               
          | {}

 role3     |                                                                                                        
          | {omm}

revoke all privilege from role3;

omm=# \du
                                                              List of roles
 Role name |                                                    Attributes                                          
          | Member of 
-----------+--------------------------------------------------------------------------------------------------------
----------+-----------
 gaussdb   | Sysadmin                                                                                               
          | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadm
in, UseFT | {}
 role10    | Cannot login, Sysadmin                                                                                 
          | {}
           | Role valid until 2021-12-31 00:00:00+08                                                                
          | 
 role3     |                                                                                                        
          | {omm}

 role2     | Role valid begin 2021-12-04 00:00:00+08                                                                
         +| {}

在\du显示的时候，就算revoke掉了privilege，还是有{omm}的存在。

5.删除所有创建角色

drop role if exists role10;
drop role if exists role2;
drop role if exists role3;

omm=# \du
                                                              List of roles
 Role name |                                                    Attributes                                          
          | Member of 
-----------+--------------------------------------------------------------------------------------------------------
----------+-----------
 gaussdb   | Sysadmin                                                                                               
          | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadm
in, UseFT | {}