第四课 学习openGauss创建角色、修改角色属性、更改角色权限和删除角色
学习目标
学习openGauss创建角色、修改角色属性、更改角色权限和删除角色
课程学习
角色是用来管理权限的，从数据库安全的角度考虑，可以把所有的管理和操作权限划分到不同的角色上
连接openGauss
#第一次进入等待15秒
#数据库启动中…
su - omm
gsql -r
学习内容如下
1.创建角色
–列出所有数据库角色
\du
–创建角色manager1，密码test_123
CREATE ROLE manager1 IDENTIFIED BY ‘test_123’;
–创建角色manager2，密码test_456，具有LOGIN属性且为系统管理员
CREATE ROLE manager2 LOGIN SYSADMIN IDENTIFIED BY ‘test_456’;
–创建角色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’;
–再次查看所有数据库角色
\du+
2.修改角色属性
–修改角色manage1具有LOGIN属性且为系统管理员
ALTER ROLE manager1 SYSADMIN LOGIN;
–查看manager1
\du+ manager1
–修改角色manager2密码
ALTER ROLE manager2 IDENTIFIED BY ‘abcd@123’ ;
–重命名manager2
ALTER ROLE manager2 RENAME TO manager20;
3.授权
–将omm的权限授权给manager1
GRANT omm to manager1 with admin option;
4.回收权限
revoke all privilege from manager1;
5.删除角色
drop role manager1;
drop role manager20;
drop role manager3;
课后作业
1.创建角色role1为系统管理员, role2指定生效日期, role3具有LOGIN属性
2.重命名role1
3.修改role2密码
4.将omm权限授权给role3,再回收role3的权限
5.删除所有创建角色
过程中使用\du或\du+查看角色信息
课后作业脚本
CREATE ROLE role1 SYSADMIN IDENTIFIED BY 'test_123';
CREATE ROLE role2 WITH LOGIN PASSWORD 'test_456' VALID BEGIN '2021-12-10' VALID UNTIL '2021-12-30';
CREATE ROLE role3 LOGIN IDENTIFIED BY 'test_789';
\du+
ALTER ROLE role1 RENAME TO role11;
ALTER ROLE role2 IDENTIFIED BY 'test@456';
\du+
GRANT omm to role3 with admin option;
\du+
revoke all privilege from role3;
\du+
drop role role11;
drop role role2;
drop role role3;
课后作业记录
omm@modb:~$
omm@modb:~$
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=# \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=#
omm=# CREATE ROLE manager1 IDENTIFIED BY 'test_123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=#
omm=# CREATE ROLE manager2 LOGIN SYSADMIN IDENTIFIED BY 'test_456';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=#
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=#
omm=#
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
dmin, Policyadmin, UseFT | {} |
| |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatora
omm=#
omm=# omm=#
ALTER ROLE manager1 SYSADMIN LOGIN;
ALTER ROLE
omm=#
omm=# \du+ manager1
List of roles
Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
manager1 | Sysadmin | {} |
omm=#
omm=# ALTER ROLE manager2 IDENTIFIED BY 'abcd@123' ;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
omm=#
omm=# ALTER ROLE manager2 RENAME TO manager20;
NOTICE: MD5 password cleared because of role rename
ALTER ROLE
omm=#
omm=# \du+
List of roles
Role name | Attributes
| Member of | Description
-----------+-----------------------------------------------------------------------------------------
-------------------------+-----------+-------------
gaussdb | Sysadmin
| {} |
manager1 | Sysadmin
| {} |
manager20 | 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, Operatora
dmin, Policyadmin, UseFT | {} |
omm=#
omm=#
omm=#
omm=# GRANT omm to manager1 with admin option;
GRANT ROLE
omm=#
omm=# \du+
List of roles
Role name | Attributes
| Member of | Description
-----------+-----------------------------------------------------------------------------------------
-------------------------+-----------+-------------
gaussdb | Sysadmin
| {} |
manager1 | Sysadmin
| {omm} |
manager20 | Sysadmin
| {} |
| Role valid until 2021-12-30 00:00:00+08
| |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatora
dmin, Policyadmin, UseFT | {} |
manager3 | Role valid begin 2021-12-10 00:00:00+08
+| {} |
omm=#
omm=# revoke all privilege from manager1;
ALTER ROLE
omm=#
omm=# \du+
List of roles
Role name | Attributes
| Member of | Description
-----------+-----------------------------------------------------------------------------------------
-------------------------+-----------+-------------
gaussdb | Sysadmin
| {} |
manager1 |
| {omm} |
manager20 | 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, Operatora
dmin, Policyadmin, UseFT | {} |
omm=#
omm=# drop role manager1;
DROP ROLE
omm=# drop role manager20;
DROP ROLE
omm=# drop role manager3;
DROP ROLE
omm=#
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=#
omm=#
omm=#
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=#
omm=#
omm=# CREATE ROLE role1 SYSADMIN IDENTIFIED BY '123';
ERROR: Password must contain at least 8 characters.
omm=#
omm=# CREATE ROLE role1 SYSADMIN IDENTIFIED BY '123456789';
ERROR: Password must contain at least three kinds of characters.
omm=#
omm=#
omm=# CREATE ROLE role1 SYSADMIN IDENTIFIED BY 'test_123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=#
omm=# CREATE ROLE role2 WITH LOGIN PASSWORD 'test_456' VALID BEGIN '2021-12-10' VALID UNTIL '2021-12-30';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=#
omm=# CREATE ROLE role3 LOGIN IDENTIFIED BY 'test_789';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=#
omm=# du+
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 | {} |
role1 | Cannot login, Sysadmin
| {} |
role2 | Role valid begin 2021-12-10 00:00:00+08
+| {} |
| Role valid until 2021-12-30 00:00:00+08
| |
role3 |
| {} |
omm-#
omm-# ALTER ROLE role1 RENAME TO role11;
ERROR: syntax error at or near "du"
LINE 1: du+
^
omm=#
omm=# ALTER ROLE role1 RENAME TO role11;
NOTICE: MD5 password cleared because of role rename
ALTER ROLE
omm=#
omm=#
omm=# ALTER ROLE role2 IDENTIFIED BY 'test@456';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
omm=#
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 | {} |
role11 | Cannot login, Sysadmin
| {} |
role2 | Role valid begin 2021-12-10 00:00:00+08
+| {} |
| Role valid until 2021-12-30 00:00:00+08
| |
role3 |
| {} |
omm=#
omm=# GRANT omm to role3 with admin option;
GRANT ROLE
omm=#
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 | {} |
role11 | Cannot login, Sysadmin
| {} |
role2 | Role valid begin 2021-12-10 00:00:00+08
+| {} |
| Role valid until 2021-12-30 00:00:00+08
| |
role3 |
| {omm} |
omm=#
omm=# revoke all privilege from role3;
ALTER ROLE
omm=#
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 | {} |
role11 | Cannot login, Sysadmin
| {} |
role2 | Role valid begin 2021-12-10 00:00:00+08
+| {} |
| Role valid until 2021-12-30 00:00:00+08
| |
role3 |
| {omm} |
omm=#
omm=# drop role role11;
DROP ROLE
omm=# drop role role2;
DROP ROLE
omm=# drop role role3;
DROP ROLE
omm=#
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=#
最后修改时间：2021-12-30 21:44:52
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
