数据库环境
openGauss:2.0.0 - 数据库实训平台
学习目标
学习openGauss创建角色、修改角色属性、更改角色权限和删除角色
学习笔记
- 回收sysadmin权限
omm=# revoke all privilege from manager1;
ALTER ROLE
- 具有生效日期和失效日期的角色
CREATE ROLE manager3 WITH LOGIN PASSWORD 'test_789' VALID BEGIN '2021-12-10' VALID UNTIL '2021-12-30';
课后作业
1.创建角色role1为系统管理员, role2指定生效日期, role3具有LOGIN属性
omm=# create role role1 sysadmin identified by 'role1-pwd-1';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create role role2 with login password 'role2-pwd-2' valid begin '2021-12-13';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create role role3 login identified by 'role3-pwd-3';
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, Policyadmin, UseFT | {}
role1 | Cannot login, Sysadmin | {}
role2 | Role valid begin 2021-12-13 00:00:00+08 | {}
role3 | | {}
2.重命名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 | Description
-----------+------------------------------------------------------------------------------------------------------------------+-----------+-------------
gaussdb | Sysadmin | {} |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {} |
role11 | Cannot login, Sysadmin | {} |
role2 | Role valid begin 2021-12-13 00:00:00+08 | {} |
role3 | | {} |
omm=#
3.修改role2密码
omm=# alter role role2 identified by 'role2-new-pwd';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
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, Policyadmin, UseFT | {}
role11 | Cannot login, Sysadmin | {}
role2 | Role valid begin 2021-12-13 00:00:00+08 | {}
role3 | | {}
omm=#
4.将omm权限授权给role3,再回收role3的权限
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 | {}
role11 | Cannot login, Sysadmin | {}
role2 | Role valid begin 2021-12-13 00:00:00+08 | {}
role3 | | {}
omm=# grant omm to role3 with admin option;
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, Policyadmin, UseFT | {}
role11 | Cannot login, Sysadmin | {}
role2 | Role valid begin 2021-12-13 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 | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
role11 | Cannot login, Sysadmin | {}
role2 | Role valid begin 2021-12-13 00:00:00+08 | {}
role3 | | {omm}
omm=# revoke omm from role3;
REVOKE ROLE
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 | {}
role3 | | {}
role11 | Cannot login, Sysadmin | {}
role2 | Role valid begin 2021-12-13 00:00:00+08 | {}
omm=#
5.删除所有创建角色
omm=# drop role role11,role2,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, Policyadmin, UseFT | {}
学习体会
公司中有组织体系架构图，图包含了角色、位置、关系和每个位置有其使用资源的权限范围。
开通权限要遵循最小权限原则，做好
- 用户分组
- 密码弱口令检测
- 离职员工的账号停用
- 日志检查
- 密码错误次数上限
- 关键账号密码定期更改
- 密码台账定期核实
- 备份，备份，备份！
等重要的日常工作。
要遵守开通和停用流程，关键步骤发邮件确认。有的时候流程也是待优化的，遇到特殊情况，多想一两步，没坏处。做好周报月报的分析汇总，尽量及时地发现可疑行为。
学习资源
- openGauss SQL学习参考资料
- 每日一练：openGauss数据库在线实训课程
- openGauss每日一练 | 21期养成好习惯，提升技术能力！
- 墨天轮Markdown编辑器使用介绍
- 墨天轮数据库在线实训平台V1.0操作手册
- 墨天轮数据社区
欢迎各位同学一起来交流学习心得！
最后修改时间：2021-12-16 18:37:15
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
