数据库环境
openGauss:2.0.0 - 数据库实训平台
学习目标
学习openGauss创建用户、修改用户属性、更改用户权限和删除用户
学习笔记
- 为用户jim增加CREATEROLE权限
omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
dim | Create DB | {}
gaussdb | Sysadmin | {}
jim | | {}
kim | | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
omm=# ALTER USER jim CREATEROLE;
ALTER ROLE
omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
dim | Create DB | {}
gaussdb | Sysadmin | {}
jim | Create role | {}
kim | | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
- 将sysadmin权限授权给用户dim
omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
dim | Create DB | {}
gaussdb | Sysadmin | {}
jim | Create role | {}
kim | | {jim}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
omm=# GRANT ALL PRIVILEGES TO dim;
ALTER ROLE
omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
dim | Create DB, Sysadmin | {}
gaussdb | Sysadmin | {}
jim | Create role | {}
kim | | {jim}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
课后作业
- 1.创建用户user1、user2和user3，user1具有CREATEROLE权限，user2具有CREATEDB权限，要求使用两种不同的方法设置密码
omm=# create user user1 password 'user-111-pwd';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create user user2 identified by 'user-222-pwd';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create user user3 password 'user-333-pwd';
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 | {}
user1 | | {}
user2 | | {}
user3 | | {}
omm=# alter user user1 createrole;
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 | {}
user1 | Create role | {}
user2 | | {}
user3 | | {}
omm=# alter user user2 createdb;
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 | {}
user3 | | {}
user1 | Create role | {}
user2 | Create DB | {}
omm=#
2.修改用户user1的密码
omm=# alter user user1 identified by 'user1-pwd-new';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
3.重命名用户user2
omm=# alter user user2 rename to user2new;
NOTICE: MD5 password cleared because of role rename
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 | {}
user1 | Create role | {}
user2new | Create DB | {}
user3 |
omm=#
4.将用户user1的权限授权给用户user3，再回收用户user3的权限
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 | {}
user1 | Create role | {}
user2new | Create DB | {}
user3 | | {}
omm=# grant user1 to user3;
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 | {}
user1 | Create role | {}
user2new | Create DB | {}
user3 | | {user1}
omm=# revoke user1 from user3;
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 | {}
user1 | Create role | {}
user2new | Create DB | {}
user3 | | {}
omm=#
5.删除所有创建用户
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 | {}
user1 | Create role | {}
user2new | Create DB | {}
user3 | | {}
omm=# drop user user1,user2new,user3;
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 | {}
学习体会
课程中要求"过程中使用\du或\du+查看用户信息"，这是很好的自学习惯，推荐！
变更前进行快照操作，得到info_start -> 运行变更指令 -> 变更完成后进行快照操作，得到info_end。通过对比info_start和info_end，来尝试理解变更指令的执行效果。
《大学》中言：“物有本末，事有终始，知所先后，则近道矣。”
学习资源
- openGauss SQL学习参考资料
- 每日一练：openGauss数据库在线实训课程
- openGauss每日一练 | 21期养成好习惯，提升技术能力！
- 墨天轮Markdown编辑器使用介绍
- 墨天轮数据库在线实训平台V1.0操作手册
- 墨天轮数据社区
欢迎各位同学一起来交流学习心得！
最后修改时间：2021-12-16 18:37:19
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
