openGauss每日一练第4天｜openGauss创建角色、修改角色属性、更改角色权限和删除角色

坚持学习openGauss数据库，坚持每天打卡。第四天学习openGauss创建角色、修改角色属性、更改角色权限和删除角色。

连接openGauss

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=#

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

omm=# create role role1 sysadmin identified by 'test@role1';
CREATE ROLE
omm=# create role role2 with login password 'test@role2' valid begin '2021-12-04';
CREATE ROLE
omm=# create role role3 sysadmin identified by 'test@role3';
CREATE ROLE
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.

2.重命名role1

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 'role@123' replace 'testrole';
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

5.删除所有创建角色,过程中使用\du或\du+查看角色信息

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

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

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    | Cannot login, Sysadmin                                                                                           
| {}        | 

 role2     | Role valid begin 2021-12-04 00:00:00+08                                                                          
| {}        | 
 role3     | Cannot login                                                                                                     
| {omm}     |
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 
| {}        | 
 role2     | Role valid begin 2021-12-04 00:00:00+08                                                                          
| {}        | 
 role3     | Cannot login                                                                                                     
| {omm}     | 

 role10    | Cannot login, Sysadmin                                                                                           
| {}        |

omm=# drop role role10;
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 
| {}

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 | Description

通过学习和实操openGauss创建角色、修改角色属性、更改角色权限和删除角色。