openGauss每日一练第4天

今天学习openGauss创建角色、修改角色属性、更改角色权限和删除角色
1.创建角色
–列出所有数据库角色

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

复制

–创建角色manager1，密码test_123

CREATE ROLE manager1 IDENTIFIED BY ‘test_123’;

omm=# CREATE ROLE manager1 IDENTIFIED BY 'test_123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
复制

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

CREATE ROLE manager2 LOGIN SYSADMIN IDENTIFIED BY ‘test_456’;

omm=# CREATE ROLE manager2 LOGIN SYSADMIN IDENTIFIED BY 'test_456';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
复制

–创建角色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’;

omm=# CREATE ROLE manager3 WITH LOGIN PASSWORD 'test_789' VALID BEGIN '2021-12-10' VALIDUNTIL '2021-12-30';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
复制

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

\du+
 Role name |                                                    Attributes                       
                             | Member of | Description 
-----------+-------------------------------------------------------------------------------------
-----------------------------+-----------+-------------
 gaussdb   | Sysadmin                                                                            
                             | {}        | 
 manager1  | Cannot login                                                                        
                             | {}        | 
 manager2  | Sysadmin                                                                            
                             | {}        | 
 manager3  | Role valid begin 2021-12-10 00:00:00+08                                             
                            +| {}        | 
           | Role valid until 2021-12-30 00:00:00+08                                             
                             |           | 
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Opera
toradmin, Policyadmin, UseFT | {}        | 
复制

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

ALTER ROLE manager1 SYSADMIN LOGIN;

ALTER ROLE manager1 SYSADMIN LOGIN;
ALTER ROLE
复制

–查看manager1

\du+ manager1
                  List of roles
 Role name | Attributes | Member of | Description 
-----------+------------+-----------+-------------
 manager1  | Sysadmin   | {}        | 
复制

–修改角色manager2密码

ALTER ROLE manager2 IDENTIFIED BY ‘abcd@123’ ;

ALTER ROLE manager2 IDENTIFIED BY 'abcd@123' ;
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
复制

–重命名manager2

ALTER ROLE manager2 RENAME TO manager20;

ALTER ROLE manager2 RENAME TO manager20;
NOTICE:  MD5 password cleared because of role rename
ALTER ROLE
复制

3.授权
–将omm的权限授权给manager1

GRANT omm to manager1 with admin option;

grant omm to manager1 with admin option;
GRANT ROLE
复制

4.回收权限
revoke all privilege from manager1;

revoke all privilege from manager1;
ALTER ROLE
复制

5.删除角色
drop role manager1;
drop role manager20;
drop role manager3;

drop role manager1;
DROP ROLE
drop role manager2; 
ERROR:  role "manager2" does not exist
drop role manager20;
DROP ROLE
drop role manager3; 
DROP ROLE
复制

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

create role role1 sysadmin identified by 'abcd.1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
create role role2 identified by 'abcd.1234' VALID BEGIN '2021-12-11' VALID UNTIL '2021-12-30';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
create role role3 login identified by 'abcd.1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
复制

2.重命名role1

alter role role1 rename to role10;
NOTICE:  MD5 password cleared because of role rename
ALTER ROLE
复制

3.修改role2密码

alter role role2 identified by 'abcd.12345';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
复制

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

grant omm to role3 with admin option;
GRANT ROLE
revoke all privilege from role3;
ALTER ROLE
\du
                                                              List of roles
 Role name |                                                    Attributes                       
                             | Member of 
-----------+-------------------------------------------------------------------------------------
-----------------------------+-----------
 gaussdb   | Sysadmin                                                                            
                             | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Opera
toradmin, Policyadmin, UseFT | {}
 role10    | Cannot login, Sysadmin                                                              
                            +| {}
           | Role valid begin 2021-12-11 00:00:00+08                                             
                            +| 
           | Role valid until 2021-12-30 00:00:00+08                                             
                             | {omm}

                             | {}
 role2     | Cannot login                                                                        
                             | 
 role3     |            
复制

5.删除所有创建角色

drop role role2;
DROP ROLE
drop role role10;
DROP ROLE
drop role role3;
DROP ROLE
\q
复制