openGauss
每日一练第4天|创建角色、修改角色属性、更改角色权限和删除角色
学习内容
1、创建角色
列出所有数据库角色
\du /* 列出所有数据库角色 */
\du+ /* 多出栏目Description */
\du+ 角色名 /* 详细查看某一个角色 */
创建角色
create role snooze identified by 'mypassword' ; /* 创建角色snooze，密码mypassword */
omm=# create role role1 sysadmin identified by 'password';
ERROR: Password must contain at least three kinds of characters.
创建角色密码需要有三种字符！
login----------具有login属性
sysadmin-----系统管理员 并非创建后就不可修改
create role Snooze login sysadmin indentified by 'loginpassword'; /* 创建系统管理员，且具有login属性 */
创建有时限的角色
create role snoozes with login password 'tempsw' valid begin '2021-1-10' valid until '2021-12-31';
2、修改角色属性
角色重命名
alter role 原始名字 rename to 新名字；
修改密码
alter role 角色名 identified by 'newPasswo234rdyeah123';
修改角色manage1具有LOGIN属性且为系统管理员
alter Role 角色名 login sysadmin;
3、权限
授权
将omm的权限授权给snooze
grant omm to snooze with admin option;
回收权限
revoke all privilege from snooze;
privilege不是复数！
4、删除角色
drop role snooze;
drop role 角色名1,角色名2; /* 同时删掉多个角色 */
作业内容
1.创建角色role1为系统管理员, role2指定生效日期, role3具有LOGIN属性
create role role1 sysadmin identified by 'password123!!';
create role role2 with login password 'passwordHanSome123!!' valid begin '2021-12-10' valid until '2022-12-10';
create role role3 login identified by 'CQMYGjtsgg23648!!';
效果
create role role1 sysadmin identified by 'password123!!';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create role role2 with login password 'passwordHanSome123!!' valid begin '2021-12-10' valid until '2022-12-10';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create role role3 login identified by 'CQMYGjtsgg23648!!';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE
2.重命名role1
alter role role1 rename to newrole1;
效果
omm=# alter role role1 rename to newrole1;
NOTICE: MD5 password cleared because of role rename
ALTER ROLE
omm=# \du
List of roles
Role name | Attributes | Member of
| {}
omm=# -----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
newrole1 | Cannot login, Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
role2 | Role valid begin 2021-12-10 00:00:00+08 +| {}
| Role valid until 2022-12-10 00:00:00+08 |
role3 | | {}
tamprole | Cannot login
3.修改role2密码
alter role role2 identified by 'aNHewsomePassw234ord$';
效果
omm=# alter role role2 identified by 'aNHewsomePassw234ord$';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
4.将omm权限授权给role3,再回收role3的权限
\du+ role3;
grant omm to role3;
\du+ role3;
revoke all privilege from role3;
\du+ role3;
效果
omm=# \du+ role3;
List of roles
Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
role3 | | {} |
/*role3的“Member of”列为空*/
omm=# grant omm to role3;
GRANT ROLE
omm=# \du+ role3;
List of roles
Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
role3 | | {omm} |
/*role3的“Member of”列为omm*/
omm=# revoke all privileges from role3;
ALTER ROLE
omm=# \du+ role3;
List of roles
Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
role3 | | {omm} |
omm=# revoke all privilege from role3;
ALTER ROLE
omm=# \du+ role3;
List of roles
Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
role3 | | {omm} |
omm=# \durevoke all privilege from omm;
ERROR: Permission denied to change privilege of the initial account.
疑问： 为什么role3的member of列信息没有变化？
5.删除所有创建角色
过程中使用\du或\du+查看角色信息
drop role newrole1;
drop role role2,role3;
效果
omm=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------------------------------------------------------------+-----------+-------------
gaussdb | Sysadmin | {} |
newrole1 | Cannot login, Sysadmin | {} |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {} |
role2 | Role valid begin 2021-12-10 00:00:00+08 +| {} |
| Role valid until 2022-12-10 00:00:00+08 | |
role3 | | {omm} |
omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
newrole1 | Cannot login, Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
role2 | Role valid begin 2021-12-10 00:00:00+08 +| {}
| Role valid until 2022-12-10 00:00:00+08 |
role3 | | {omm}
omm=# drop role newrole1;
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 | {}
role2 | Role valid begin 2021-12-10 00:00:00+08 +| {}
| Role valid until 2022-12-10 00:00:00+08 |
role3 | | {omm}
omm=# drop role 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 | {}
omm=#
最后修改时间：2021-12-23 22:48:14
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
