openGauss每日一练第4天|role

学习openGauss创建角色、修改角色属性、更改角色权限和删除角色

1.连接数据库
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.


2.列出所有数据库角色

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

3.创建角色role1为系统管理员, role2指定生效日期, role3具有LOGIN属性.
注意：创建角色是必须指定密码，而且密码的长度至少8位字符。

omm=# create role role1 with sysadmin identified by 'test_role1';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create role role2 VALID BEGIN '2021-12-16' identified by 'role2_test';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create role role3 WITH LOGIN identified by 'role3_test';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# \du
List of roles
Role name | Attributes
| Member of


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

4.重命名role1
omm=# alter role role1 rename to role11;
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, Poli
cyadmin, UseFT | {}
role11 | Cannot login, Sysadmin
| {}
role3 |
| {}

role2 | Cannot login
+| {}
| Role valid begin 2021-12-16 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, Poli
cyadmin, UseFT | {} |
role11 | Cannot login, Sysadmin
| {} |
role2 | Cannot login
+| {} |
| Role valid begin 2021-12-16 00:00:00+08
| |
role3 |
| {} |



5.修改role2密码
omm=# alter role role2 identified by 'test_role2';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE

6.将omm权限授权给role3,再回收role3的权限
omm=# grant omm to role3;
GRANT ROLE
omm=# \du
List of roles
Role name | Attributes
| Member of
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Poli
cyadmin, UseFT | {}
role11 | Cannot login, Sysadmin
| {}
role2 | Cannot login
+| {}
-----------+---------------------------------------------------------------------------------------------------
---------------+-----------
| Role valid begin 2021-12-16 00:00:00+08
|


role3 |
| {omm}omm=#
omm=# \du+
List of roles
Role name | Attributes
| Member of | Description
role11 | Cannot login, Sysadmin
| {} |
role2 | Cannot login
-----------+---------------------------------------------------------------------------------------------------
---------------+-----------+-------------
gaussdb | Sysadmin
| {} |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Poli
cyadmin, UseFT | {} |
+| {} |
| Role valid begin 2021-12-16 00:00:00+08
| |
role3 |
| {omm} |

omm=# revoke omm from role3;
REVOKE ROLE
omm=# \du
List of roles
Role name | Attributes | Role valid begin 2021-12-16 00:00:00+08
|
role3 |
| {}
| Member of
-----------+---------------------------------------------------------------------------------------------------
---------------+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Poli
cyadmin, UseFT | {}
role11 | Cannot login, Sysadmin
| {}
role2 | Cannot login
+| {}


omm=# \du+
List of roles
Role name | Attributes
| Member of | Description
-----------+---------------------------------------------------------------------------------------------------
---------------+-----------+-------------
gaussdb | Sysadmin
| {} |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Poli
cyadmin, UseFT | {} |
role11 | Cannot login, Sysadmin
| {} |
role2 | Cannot login
+| {} |
| Role valid begin 2021-12-16 00:00:00+08
| |
role3 |
| {} |

7.删除所有创建角色
omm=# drop role role11;
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, Poli
cyadmin, UseFT | {}

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