第四天学习
openGauss创建角色、修改角色属性、更改角色权限和删除角色
课堂练习
Welcome to 墨天轮.
This is Web Terminal of modb.pro; Good Good Study, Day Day Up.
root@modb:~#
root@modb:~# #第一次进入等待15秒
root@modb:~# #数据库启动中...
root@modb:~# su - omm
omm@modb:~$ gsql -r
failed to connect Unknown:5432.
omm@modb:~$ #第一次进入等待15秒
omm@modb:~$ #数据库启动中...
omm@modb:~$ su - omm
Password:
^C
omm@modb:~$ logout
root@modb:~# #第一次进入等待15秒
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.
omm=#
omm=#
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
| {}
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
omm-# UNTIL '2021-12-30';
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
| {} |
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
| {} | omm=#
omm=#
omm=#
omm=# ALTER ROLE manager1 SYSADMIN LOGIN;
ALTER ROLE
omm=# \du+ manager1
List of roles
Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
manager1 | Sysadmin | {} |
omm=# ALTER ROLE manager2 IDENTIFIED BY 'abcd@123' replace 'test_456';
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
ALTER ROLE
omm=# ALTER ROLE manager2 IDENTIFIED BY 'abcd@123' replace 'test_456';
ERROR: role "manager2" does not exist
omm=# ALTER ROLE manager2 RENAME TO manager20;
omm=# ERROR: role "manager2" does not exist
omm=# ALTER ROLE manager20 RENAME TO manager2;
ALTER ROLE
omm=# ALTER ROLE manager2 RENAME TO manager20;
ALTER ROLE
omm=# GRANT omm to manager1 with admin option;
GRANT ROLE
omm=# revoke all privilege from manager1;
ALTER ROLE
omm=# drop role manager1;
drop role manager2;
DROP ROLE
omm=# ERROR: role "manager2" does not exist
omm=# drop role manager3;
DROP ROLE
omm=#
omm=#
课后作业
1.创建角色role1为系统管理员, role2指定生效日期, role3具有LOGIN属性
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 IDENTIFIED BY 'test_123' VALID BEGIN '2021-12-10' VALID 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_123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
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@123' replace 'test_123';
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;
GRANT ROLE
omm=# \du
List of roles
Role name | Attributes
| Member of
-----------+-------------------------------------------------------------------------------------------------------------
-----+-----------
gaussdb | Sysadmin
| {}
manager20 | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, U
seFT | {}
role10 | Cannot login, Sysadmin
| {}
role2 | Cannot login
+| {}
| Role valid begin 2021-12-10 00:00:00+08
+|
| Role valid until 2021-12-30 00:00:00+08
|
role3 |
| {omm}
omm=# revoke all privilege from role3;
ALTER ROLE
omm=# \du
List of roles
Role name | Attributes
| Member of
-----------+-------------------------------------------------------------------------------------------------------------
-----+-----------
gaussdb | Sysadmin
| {}
manager20 | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, U
seFT | {}
role10 | Cannot login, Sysadmin
| {}
role2 | Cannot login
+| {}
| Role valid begin 2021-12-10 00:00:00+08
+|
| Role valid until 2021-12-30 00:00:00+08
|
role3 |
| {omm}复制
5.删除所有创建角色
omm=# drop role role1;
ERROR: role "role1" does not exist
omm=# drop role role2;
DROP ROLE
omm=# drop role role3;
DROP ROLE
omm=# drop role role10;
DROP ROLE
omm=# \du
List of roles
Role name | Attributes
| Member of
-----------+-------------------------------------------------------------------------------------------------------------
-----+-----------
gaussdb | Sysadmin
| {}
manager20 | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, U
seFT | {}复制
过程中使用\du或\du+查看角色信息
omm=# \du
List of roles
Role name | Attributes
| Member of
-----------+-------------------------------------------------------------------------------------------------------------
-----+-----------
gaussdb | Sysadmin
| {}
manager20 | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, U
seFT | {}
role3 |
| {}
role10 | Cannot login, Sysadmin
| {}
role2 | Cannot login
+| {}
| Role valid begin 2021-12-10 00:00:00+08
+|
| Role valid until 2021-12-30 00:00:00+08
omm=# \du+
-----------+-------------------------------------------------------------------------------------------------------------
-----+-----------+-------------
gaussdb | Sysadmin
| {} |
manager20 | Sysadmin
| {} |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, U
seFT | {} |
role10 | Cannot login, Sysadmin
| {} | List of roles
Role name | Attributes
| Member of | Description
role3 |
| {} |
role2 | Cannot login
+| {} |
| Role valid begin 2021-12-10 00:00:00+08
+| |
| Role valid until 2021-12-30 00:00:00+08
| |复制
