学习目标
学习openGauss创建角色、修改角色属性、更改角色权限和删除角色
课程学习
角色是用来管理权限的，从数据库安全的角度考虑，可以把所有的管理和操作权限划分到不同的角色上
1.创建角色role1为系统管理员, role2指定生效日期, role3具有LOGIN属性
2.重命名role1
3.修改role2密码
4.将omm权限授权给role3,再回收role3的权限
5.删除所有创建角色
过程中使用\du或\du+查看角色信息
create role role1 sysadmin identified by 'Oracle123';
omm=# create role role2 with login password 'Oracle123' valid begin '2022-01-01' valid until '2022-12-12';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create role role3 login sysadmin identified by 'Oracle123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=#
omm=# alter role role1 rename to role1_1;
NOTICE: MD5 password cleared because of role rename
ALTER ROLE
omm=# alter role role2 identified by 'Oracleasdf123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
omm=#
omm=# grant omm to role3 with admin option;
GRANT ROLE
omm=# revoke all privileges from role3;
ALTER ROLE
omm=# drop role role1;
ERROR: role "role1" does not exist
omm=# drop role role1_1;
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, Operatora
dmin, Policyadmin, UseFT | {}
omm=# \du+
List of roles
Role name | Attributes
| Member of | Description
-----------+-----------------------------------------------------------------------------------------
-------------------------+-----------+-------------
gaussdb | Sysadmin
| {} |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatora
dmin, Policyadmin, UseFT | {} |
omm=#
