1.登录数据库
root@modb:~#
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, Policyadmin, UseFT | {}
role1 | Sysadmin
| {}
创建角色manager1，manager2，manager3
omm=# create role manager1 identified by 'test_123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create role manager2 login sysadmin identified by 'test_456';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create role manager3 with login password 'test_789' valid begin '2021-12-10' valid until '2021-12-30';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
查看角色
omm=# \du+
List of r
oles
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, Operatoradmin, Policyadmin, UseFT | {} |
role1 | Sysadmin
| {} |
修改角色属性、密码、及角色名
omm=# alter role manager1 sysadmin login;
ALTER ROLE
omm=# \du+ manager1
manager1 | Sysadmin | {} |
omm=# alter role manager2 identified by 'abcd@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
omm=# alter role manager2 rename to manager20;
NOTICE: MD5 password cleared because of role rename
omm=# ALTER ROLE
授权角色及收回
omm=# grant omm to manager1 with admin option;
omm=# GRANT ROLE
omm=# revoke all privilege from manager1;
ALTER ROLE
删除角色
omm=# drop role manager1;
DROP ROLE
omm=# drop role manager20;
DROP ROLE
omm=# drop role manager3;
DROP ROLE
3.课后作业
1.创建角色role1为系统管理员, role2指定生效日期, role3具有LOGIN属性
omm=# create role role1 sysadmin identified by 'abcd@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create role role2 identified by 'abcd@123' valid begin '2021-12-15' valid until '2021-12-31';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create role role3 login identified by 'abcd@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
omm=# CREATE ROLE
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 'abcd@1234';
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;
omm=# GRANT ROLE
omm=# revoke all privileges from role3;
ALTER ROLE
5.删除所有创建角色
omm=# drop role role10;
omm=# DROP ROLE
omm=# drop role role2;
DROP ROLE
omm=# drop role role3;
DROP ROLE
