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

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.

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-10’ VALID
UNTIL ‘2021-12-30’;
CREATE ROLE role3 LOGIN IDENTIFIED BY ‘test_789’;

omm=# CREATE ROLE role1 SYSADMIN IDENTIFIED BY 'test_123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# CREATE ROLE role2  WITH LOGIN PASSWORD 'test_456' VALID BEGIN '2021-12-10' VALID
omm-# UNTIL '2021-12-30';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# CREATE ROLE role3 LOGIN IDENTIFIED BY 'test_789';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# \du+
                                                                     List of roles
 Role name |                                                    Attributes                                       
             | Member of | Description 
-----------+-----------------------------------------------------------------------------------------------------
-------------+-----------+-------------
 gaussdb   | Sysadmin                                                                                            
             | {}        | 
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policy
admin, UseFT | {}        | 
 role1     | Cannot login, Sysadmin                                                                              
             | {}        | 
 role3     |                                                                                                     
             | {}        | 

 role2     | Role valid begin 2021-12-10 00:00:00+08                                                             
            +| {}        | 
           | Role valid until 2021-12-30 00:00:00+08                                                             
             |           | 
omm=# 

2.重命名role1

\du+
alter role role1 rename to role10;

omm=# alter role role1 rename to role10;
NOTICE:  MD5 password cleared because of role rename
ALTER ROLE
omm=# \du
                                                              List of roles
 Role name |                                                    Attributes                                       
             | Member of 
-----------+-----------------------------------------------------------------------------------------------------
-------------+-----------
 gaussdb   | Sysadmin                                                                                            
             | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policy
admin, UseFT | {}
 role10    | Cannot login, Sysadmin                                                                              
             | {}
 role2     | Role valid begin 2021-12-10 00:00:00+08                                                             
            +| {}
           | Role valid until 2021-12-30 00:00:00+08                                                             
             | 
 role3     |                                                                                                     
             | {}

3.修改role2密码

alter role role2 identified by ‘Abcd_123’ replace ‘test_456’;

omm=# alter role role2 identified by 'Abc_123' replace 'test_456';
ERROR:  Password must contain at least 8 characters.
#密码必须要满足8位字符
omm=# 
omm=# alter role role2 identified by 'Abcd_123' replace 'test_456';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
omm=# 

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

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

omm=# grant omm to role3;
GRANT ROLE
omm=# du+ role3
omm-# \du+role3
                                                                     List of roles
 Role name |                                                    Attributes                                       
             | Member of | Description 
-----------+-----------------------------------------------------------------------------------------------------
-------------+-----------+-------------
 gaussdb   | Sysadmin                                                                                            
             | {}        | 
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policy
admin, UseFT | {}        | 
 role2     | Role valid begin 2021-12-10 00:00:00+08                                                             
            +| {}        | 
 role10    | Cannot login, Sysadmin                                                                              
             | {}        | 
 role3     |                                                                                                     
             | {omm}     | 

           | Role valid until 2021-12-30 00:00:00+08                                                             
             |           | 
omm-# \du+ role3
                  List of roles
 Role name | Attributes | Member of | Description 
-----------+------------+-----------+-------------
 role3     |            | {omm}     | 

omm-# 

5.删除所有创建角色

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

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, Policy
admin, UseFT | {}