openGauss 每日一练第 5 天学习打卡，巩固 openGauss 数据库用户管理基本操作！
学习目标
学习openGauss创建用户、修改用户属性、更改用户权限和删除用户.
前面每日一练链接：
openGauss每日一练第1天 | 数据库和表的基本操作（一）
openGauss每日一练第2天 | 数据库和表的基本操作（二）
openGauss每日一练第3天 | 前三课作业实操练习
openGauss每日一练第4天 | openGauss 角色管理及课后作业
课程学习
注意：在启动 gsql 的命令行中加上“-E”参数，就可以把 gsql 中各种以“\”开头的命令执行的实际 SQL 语句打印出来.如果在已运行的 gsql 中显示了某个命令实际执行的 SQL 语句后又想关闭此功能，该怎么办？这时可以使用“\set ECHO_HIDDEN on|off”命令。
用户是用来登录数据库的，通过对用户赋予不同的权限，可以方便地管理用户对数据
库的访问及操作
连接 openGauss
su - omm
gsql -r
1.创建用户
–以下两种设置密码方法等效
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;
CREATE USER jim PASSWORD 'abcd@123';
CREATE USER kim IDENTIFIED BY 'abcd@456';
注意：查看角色、用户都是用的 pg_catalog.pg_roles 表。
–用户 dim 具有创建数据库权限
CREATE USER dim CREATEDB PASSWORD 'abcd@789';
–查看用户
omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
dim | Create DB | {}
gaussdb | Sysadmin | {}
jim | Create role | {}
joe | Cannot login | {}
kim | | {}
manager1 | | {omm}
manager20 | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
2.修改用户属性
–修改密码
ALTER USER jim IDENTIFIED BY 'Abcd@123'
or
ALTER USER jim IDENTIFIED BY 'Abcd@123' REPLACE 'abcd@123';
普通用户修改自己的密码必须要求输入正确的旧密码才可以。而初始用户、系统管理员或者拥有创建用户权限的用户才可以不需要旧密码直接重置普通用户密码。
看到这个才明白了，我是管理员账号 omm 登录,重置普通用户密码不需要加 REPLACE 关键字也可以。
–为用户 jim 增加 CREATEROLE 权限
ALTER USER jim CREATEROLE;
–查看用户
omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
dim | Create DB | {}
gaussdb | Sysadmin | {}
jim | Create role | {}
joe | Cannot login | {}
kim | | {}
manager1 | | {omm}
manager20 | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
3. GRANT 授权
对角色和用户进行授权操作。
使用GRANT命令进行用户授权包括以下三种场景：
将系统权限授权给角色或用户
系统权限又称为用户属性，包括SYSADMIN、CREATEDB、CREATEROLE、AUDITADMIN、MONADMIN、OPRADMIN、POLADMIN和LOGIN。
系统权限一般通过CREATE/ALTER ROLE语法来指定。其中，SYSADMIN权限可以通过GRANT/REVOKE ALL PRIVILEGE授予或撤销。但系统权限无法通过ROLE和USER的权限被继承，也无法授予PUBLIC。
将数据库对象授权给角色或用户
将数据库对象（表和视图、指定字段、数据库、函数、模式、表空间等）的相关权限授予特定角色或用户；
GRANT命令将数据库对象的特定权限授予一个或多个角色。这些权限会追加到已有的权限上。
关键字PUBLIC表示该权限要赋予所有角色，包括以后创建的用户。PUBLIC可以看做是一个隐含定义好的组，它总是包括所有角色。任何角色或用户都将拥有通过GRANT直接赋予的权限和所属的权限，再加上PUBLIC的权限。
如果声明了WITH GRANT OPTION，则被授权的用户也可以将此权限赋予他人，否则就不能授权给他人。这个选项不能赋予PUBLIC，这是openGauss特有的属性。
openGauss会将某些类型的对象上的权限授予PUBLIC。默认情况下，对表、表字段、序列、外部数据源、外部服务器、模式或表空间对象的权限不会授予PUBLIC，而以下这些对象的权限会授予PUBLIC：数据库的CONNECT权限和CREATE TEMP TABLE权限、函数的EXECUTE特权、语言和数据类型（包括域）的USAGE特权。当然，对象拥有者可以撤销默认授予PUBLIC的权限并专门授予权限给其他用户。为了更安全，建议在同一个事务中创建对象并设置权限，这样其他用户就没有时间窗口使用该对象。另外可参考安全加固指南的权限控制章节，对PUBLIC用户组的权限进行限制。这些初始的默认权限可以使用ALTER DEFAULT PRIVILEGES命令修改。
对象的所有者缺省具有该对象上的所有权限，出于安全考虑所有者可以舍弃部分权限，但ALTER、DROP、COMMENT、INDEX、VACUUM以及对象的可再授予权限属于所有者固有的权限，隐式拥有。
将角色或用户的权限授权给其他角色或用户
将一个角色或用户的权限授予一个或多个其他角色或用户。在这种情况下，每个角色或用户都可视为拥有一个或多个数据库权限的集合。
当声明了WITH ADMIN OPTION，被授权的用户可以将该权限再次授予其他角色或用户，以及撤销所有由该角色或用户继承到的权限。当授权的角色或用户发生变更或被撤销时，所有继承该角色或用户权限的用户拥有的权限都会随之发生变更。
数据库系统管理员可以给任何角色或用户授予/撤销任何权限。拥有CREATEROLE权限的角色可以赋予或者撤销任何非系统管理员角色的权限。
–将用户jim的权限授权给用户kim
GRANT jim to kim;
GRANT 是针对数据库对象授权，将表，数据库的相关权限授予角色或用户，像 create DB, create role 这样的系统权限一般通过 create或 alter role 授予。
–将 sysadmin 权限授权给用户 dim
GRANT ALL PRIVILEGES TO dim;
GRANT 无法直接将 Sysadmin 授予用户 dim,ALL PRIVILEGES 则是 sysadmin 权限。
–重命名用户dim
alter user dim rename to tim;
–查看用户
\du
4. REVOKE 回收权限
–撤消 kim 的权限
REVOKE jim FROM kim;
–撤消用户dim的sysadmin权限
revoke all privilege from tim;
–查看用户
omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
jim | Create role | {}
joe | Cannot login | {}
kim | | {}
manager1 | | {omm}
manager20 | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
tim | Create DB, Sysadmin | {}
omm=# omm=#
omm=#
omm=# revoke all privilege from tim;
ALTER ROLE
omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
jim | Create role | {}
joe | Cannot login | {}
kim | | {}
manager1 | | {omm}
manager20 | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
tim | Create DB | {}
5.删除用户
drop user tim;
drop user jim;
drop user kim;
omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
joe | Cannot login | {}
manager1 | | {omm}
manager20 | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
课程作业
1.创建用户user1、user2和user3，user1具有CREATEROLE权限，user2具有CREATEDB权限，要求使用两种不同的方法设置密码
创建用户语法格式：
CREATE USER user_name [ [ WITH ] option [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { 'password' [EXPIRED] | DISABLE };
其中 option 子句用于设置权限及属性等信息。
{SYSADMIN | NOSYSADMIN}
| {MONADMIN | NOMONADMIN}
| {OPRADMIN | NOOPRADMIN}
| {POLADMIN | NOPOLADMIN}
| {AUDITADMIN | NOAUDITADMIN}
| {CREATEDB | NOCREATEDB}
| {USEFT | NOUSEFT}
| {CREATEROLE | NOCREATEROLE}
| {INHERIT | NOINHERIT}
| {LOGIN | NOLOGIN}
| {REPLICATION | NOREPLICATION}
| {INDEPENDENT | NOINDEPENDENT}
| {VCADMIN | NOVCADMIN}
| CONNECTION LIMIT connlimit
| VALID BEGIN 'timestamp'
| VALID UNTIL 'timestamp'
| RESOURCE POOL 'respool'
| PERM SPACE 'spacelimit'
| TEMP SPACE 'tmpspacelimit'
| SPILL SPACE 'spillspacelimit'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
| DEFAULT TABLESPACE tablespace_name
| PROFILE DEFAULT
| PROFILE profile_name
| PGUSER
create user user1 CREATEROLE PASSWORD 'JiekeXu1';
create user user2 CREATEDB identified by "JiekeXu2";
create user user3 identified by "JiekeXu3";
用户名称为字符串，要符合标识符的命名规范，且最大长度不超过 63 个字符。密码不能少于 8 位。
密码规则如下：
密码默认不少于8个字符。
不能与用户名及用户名倒序相同。
至少包含大写字母（A-Z），小写字母（a-z），数字（0-9），非字母数字字符（限定为~!@#$%^&*()-_=+\|[{}];:,<.>/?）四类字符中的三类字符。
密码也可以是符合格式要求的密文字符串，这种情况主要用于用户数据导入场景，不推荐用户直接使用。如果直接使用密文密码，用户需要知道密文密码对应的明文，并且保证明文密码复杂度，数据库不会校验密文密码复杂度，直接使用密文密码的安全性由用户保证。
创建用户时，应当使用双引号或单引号将用户密码括起来。
2.修改用户user1的密码
alter user user1 identified by 'JiekeXu10';
alter user user1 identified by 'JiekeXu11' replace 'JiekeXu10';
alter user user1 PASSWORD 'JiekeXu12' REPLACE 'JiekeXu11';
omm=# alter user user1 identified by 'JiekeXu10';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
omm=# alter user user1 identified by 'JiekeXu11' replace 'JiekeXu10';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
omm=# alter user user1 PASSWORD 'JiekeXu12';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
joe | Cannot login | {}
manager1 | | {omm}
manager20 | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
user1 | Create role | {}
user2 | Create DB | {}
user3 | | {}
密码连接测试
omm@modb:~$ gsql -r -h localhost -U user1 -W JiekeXu1
gsql: FATAL: Invalid username/password,login denied.
omm@modb:~$ gsql -r -h localhost -U user1 -W JiekeXu12
gsql: FATAL: database "user1" does not exist
omm@modb:~$ gsql -r -h localhost -U user1 -W JiekeXu12 -d postgres
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.
postgres=> \q
gsql -r -h localhost -U user3 -W JiekeXu3 -d postgres
3.重命名用户 user2
alter user user2 rename to JiekeXu_user2;
\du
\q
omm@modb:~$ gsql -r -h localhost -U JiekeXu_user2 -W JiekeXu2 -d postgres
gsql: FATAL: Invalid username/password,login denied.
omm@modb:~$ gsql -r
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=# alter user JiekeXu_user2 identified by "JiekeXu_user2";
omm=# ERROR: Password should not equal to the rolname.
--用户名不能和密码一样。
omm=# alter user JiekeXu_user2 identified by "abcd@123";
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
omm=# \du
List of roles
Role name | Attributes | Member of
---------------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
jiekexu_user2 | Create DB | {}
omm=# joe | Cannot login | {}
manager1 | | {omm}
manager20 | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
user1 | Create role | {}
user3 |
--用户名不分大小写
omm@modb:~$ gsql -r -h localhost -U jiekexu_user2 -W abcd@123 -d postgres
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.
postgres=> \du
List of roles
Role name | Attributes | Member of
---------------+------------+-----------
jiekexu_user2 | Create DB | {}
4.将用户 user1 的权限授权给用户 user3，再回收用户 user3 的权限
grant user1 to user3;
revoke user1 from user3;
5.删除所有创建用户
过程中使用 \du或 \du+ 查看用户信息
\du
drop user user1;
\du+
drop user jiekexu_user2;
\du+
drop user user3;
\du
欧耶，第五课用户管理实操及课后作业练习完成啦！
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
