暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

openGauss每日一练第 4 天 | 创建角色、修改角色属性、更改角色权限和删除角色

原创 手机用户2761 2021-12-07
477

0. 连接openGauss

su - omm
gsql -r

1. 创建角色role1为系统管理员, role2指定生效日期, role3具有LOGIN属性

CREATE ROLE role1 LOGIN SYSADMIN IDENTIFIED BY 'test_123';
CREATE ROLE role2  IDENTIFIED BY 'test_456' VALID BEGIN '2021-12-01' VALID UNTIL '2021-12-30';
CREATE ROLE role3 LOGIN IDENTIFIED BY 'test_789';

– 回显

 gaussdb   | Sysadmin| {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT| {}
 role1     | Sysadmin| {}
 role2     | Cannot login                                                                                             
           | Role valid begin 2021-12-01 00:00:00+08                                                                         
|
           | Role valid until 2021-12-30 00:00:00+08
|
 role3     | {} | 

2. 重命名role1

ALTER ROLE role1 RENAME TO role10;
\du

– 回显

ALTER ROLE
role10     | Sysadmin| {}

3. 修改role2密码

ALTER ROLE role2 IDENTIFIED BY 'abcd@123' replace 'test_456';

– 回显

NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE

4. 将omm权限授权给role3,再回收role3的权限

GRANT omm to role3;
\du

– 回显

GRANT ROLE
| role3 | {omm} |
REVOKE omm from role3;
\du

– 回显

REVOKE ROLE
| role3 | {}   |

5. 删除所有创建角色

过程中使用\du或\du+查看角色信息

\du+

– 回显

 Role name |                                                    Attributes
| Member of | Description
-----------+------------------------------------------------------------------------------------------------------------------
+-----------+-------------
 gaussdb   | Sysadmin
| {}        |
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT
| {}        |
 role10    | Sysadmin
| {}        |
 role2     | Cannot login                                                                                                    +
| {}        |
           | Role valid begin 2021-12-01 00:00:00+08                                                                         +
|           |
           | Role valid until 2021-12-30 00:00:00+08
|           |
 role3     |
| {}        |
drop role role10;
drop role role2;
drop role role3;
\du

– 回显

                                                              List of roles
 Role name |                                                    Attributes
| Member of
-----------+------------------------------------------------------------------------------------------------------------------
+-----------
 gaussdb   | Sysadmin
| {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT
| {}

6. 总结

\du
\du+
create role <role_name> <options> identified by <password>
GRANT <database> to <user> with <role> option;
revoke <role>/all privilege from <user>;
drop role

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论