openGauss 每日一练第 4 天学习打卡，巩固 openGauss 数据库角色管理基本操作基础知识！
学习目标
学习 openGauss 创建角色、修改角色属性、更改角色权限和删除角色.
前面每日一练链接：
openGauss每日一练第1天 | 数据库和表的基本操作（一）
openGauss每日一练第2天 | 数据库和表的基本操作（二）
openGauss每日一练第3天 | 前三课作业实操练习
课程学习
注意：在启动 gsql 的命令行中加上“-E”参数，就可以把 gsql 中各种以“\”开头的命令执行的实际 SQL 语句打印出来.如果在已运行的 gsql 中显示了某个命令实际执行的 SQL 语句后又想关闭此功能，该怎么办？这时可以使用“\set ECHO_HIDDEN on|off”命令。
omm@modb:~$ gsql -E
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=# \l
********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
omm | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
(4 rows)
omm=# SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_striomm-# ng(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
omm-# omm-# omm-# omm-# omm-# omm-#
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
omm | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
(4 rows)
--取消
omm=# \set ECHO_HIDDEN OFF
omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
jack | | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
omm=# \set ECHO_HIDDEN ON
omm=# \du
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, r.rolvalidbegin, r.rolvaliduntil,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolauditadmin
, r.rolsystemadmin
, r.rolmonitoradmin
, r.roloperatoradmin
, r.rolpolicyadmin
, r.roluseft
, r.rolkind
FROM pg_catalog.pg_roles r
ORDER BY 1;
**************************
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
jack | | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
omm=#
创建角色
–列出所有数据库角色
\du
–创建角色 manager1，密码test_123
omm=# create role jiekexu;
ERROR: The password could not be NULL.
--不允许密码为空，故创建角色时需要密码，Oracle 里便可以直接创建角色而不需要任何密码。
--创建语法如下：
CREATE ROLE role_name [ [ WITH ] option [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { 'password' [EXPIRED] | DISABLE };
CREATE ROLE manager1 IDENTIFIED BY 'test_123';
–创建角色 manager2，密码 test_456，具有 LOGIN 属性且为系统管理员
CREATE ROLE manager2 LOGIN SYSADMIN IDENTIFIED BY 'test_456';
omm=# \du
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, r.rolvalidbegin, r.rolvaliduntil,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolauditadmin
, r.rolsystemadmin
, r.rolmonitoradmin
, r.roloperatoradmin
, r.rolpolicyadmin
, r.roluseft
, r.rolkind
FROM pg_catalog.pg_roles r
ORDER BY 1;
**************************
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
manager1 | Cannot login | {}
manager2 | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
–创建角色manager3，密码test_789，从2021年12月10日生效，2021年12月30日失效
CREATE ROLE manager3 WITH LOGIN PASSWORD 'test_789' VALID BEGIN '2021-12-10' VALID
UNTIL '2021-12-30';
–再次查看所有数据库角色
\du+
2.修改角色属性
–修改角色manage1具有LOGIN属性且为系统管理员
ALTER ROLE manager1 SYSADMIN LOGIN;
–查看manager1
\du+ manager1
omm=# \du+
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, r.rolvalidbegin, r.rolvaliduntil,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description
, r.rolreplication
, r.rolauditadmin
, r.rolsystemadmin
, r.rolmonitoradmin
, r.roloperatoradmin
, r.rolpolicyadmin
, r.roluseft
, r.rolkind
FROM pg_catalog.pg_roles r
ORDER BY 1;
**************************
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------------------------------------------------------------+-----------+-------------
gaussdb | Sysadmin | {} |
manager1 | Sysadmin | {} |
manager2 | Sysadmin | {} |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {} |
omm=# \du+ manager1
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, r.rolvalidbegin, r.rolvaliduntil,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description
, r.rolreplication
, r.rolauditadmin
, r.rolsystemadmin
, r.rolmonitoradmin
, r.roloperatoradmin
, r.rolpolicyadmin
, r.roluseft
, r.rolkind
FROM pg_catalog.pg_roles r
WHERE r.rolname ~ '^(manager1)$'
ORDER BY 1;
**************************
List of roles
Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
manager1 | Sysadmin | {} |
–修改角色manager2密码
ALTER ROLE manager2 IDENTIFIED BY 'abcd@123' replace 'test_456';
–重命名角色 manager2
ALTER ROLE manager2 RENAME TO manager20;
**************************
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
manager1 | Sysadmin | {}
manager20 | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
3.授权
–将omm的权限授权给manager1
GRANT omm to manager1 with admin option;
4.回收权限
revoke all privilege from manager1;
5.删除角色
drop role manager1;
drop role manager20;
drop role manager3;
=====================================
课后作业
1.创建角色 role1 为系统管理员, role2 指定生效日期, role3具有 LOGIN 属性
\du
create role role1 sysadmin identified by 'JiekeXu1';
**************************
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
omm=# joe | Cannot login | {}
manager1 | | {omm}
manager20 | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
role1 | Cannot login, Sysadmin | {}
create role role2 identified by "JiekeXu2" VALID BEGIN '2021-12-07' VALID
UNTIL '2021-12-31';
CREATE ROLE role3 LOGIN identified by 'JiekeXu3';
ERROR: Password must be quoted 注意：密码必须要用引号引起来。
注意：密码规则如下：
密码默认不少于8个字符。
不能与用户名及用户名倒序相同。
至少包含大写字母（A-Z），小写字母（a-z），数字（0-9），非字母数字字符（限定为~!@#$%^&*()-_=+\|[{}];:,<.>/?）四类字符中的三类字符。
密码也可以是符合格式要求的密文字符串，这种情况主要用于用户数据导入场景，不推荐用户直接使用。如果直接使用密文密码，用户需要知道密文密码对应的明文，并且保证明文密码复杂度，数据库不会校验密文密码复杂度，直接使用密文密码的安全性由用户保证。
创建角色时，应当使用双引号或单引号将用户密码括起来。
2.重命名role1
alter role role1 rename to JiekeXu_role1;
\du
**************************
List of roles
Role name | Attributes | Member of
---------------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
jiekexu_role1 | Cannot login, Sysadmin | {}
joe | Cannot login | {}
manager1 | | {omm}
manager20 | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
role2 | Cannot login +| {}
| Role valid begin 2021-12-07 00:00:00+08 +|
| Role valid until 2021-12-31 00:00:00+08 |
role3 |
3.修改role2密码
alter role role2 identified by 'JiekeXu_role2';
alter role role2 identified by 'JiekeXu_Role@2' replace 'JiekeXu_role2';
4.将 omm 权限授权给 role3,再回收 role3 的权限
grant omm to role3;
grant omm to role3 with admin option;
\du
revoke omm from role3;
\du
5.删除所有创建角色
过程中使用 \du 或 \du+ 查看角色信息
\du
drop role role3;
drop role role2;
\du+
drop role JiekeXu_role1;
欧耶，第四课实操及课后作业练习完成啦！
最后修改时间：2021-12-07 10:55:52
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
