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=# 
复制