openGauss每日一练第5天 | 数据库用户的相关操作

今天学习openGauss创建用户、修改用户属性、更改用户权限和删除用户的相关操作；

附上官方的相关说明：https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/%E7%94%A8%E6%88%B7.html

创建用户的相关语法：

语法格式

CREATE USER user_name [ [ WITH ] option [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] 
{ PASSWORD | IDENTIFIED BY } { 'password' [EXPIRED] | DISABLE };

https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/CREATE-USER.html

注意事项

• 通过CREATE USER创建的用户，默认具有LOGIN权限。
• 通过CREATE USER创建用户的同时，系统会在执行该命令的数据库中，为该用户创建一个同名的SCHEMA。
• 系统管理员在普通用户同名schema下创建的对象，所有者为schema的同名用户（非系统管理员）。

从创建用户来看，也会默认创建和用户同名的角色，这一点跟oracle还是不一样的。

课程作业

1.创建用户user1、user2和user3，user1具有CREATEROLE权限，user2具有CREATEDB权限，要求使用两种不同的方法设置密码

openGauss=# create user user1 createrole password '1234@abc';
CREATE ROLE
openGauss=# create user user2 createdb identified by '1234@abc';
CREATE ROLE
openGauss=# create user user3 password 'abcd@123';
CREATE ROLE
openGauss=# \du
                                                              List of roles
 Role name |                                                    Attributes                                                    | Member of 
-----------+------------------------------------------------------------------------------------------------------------------+-----------
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
 user1     | Create role                                                                                                      | {}
 user2     | Create DB                                                                                                        | {}
 user3     |                                                                                                                  | {}

openGauss=# 
openGauss=# select rolname from pg_roles;
        rolname         
------------------------
 gs_role_copy_files
 gs_role_signal_backend
 gs_role_tablespace
 gs_role_replication
 gs_role_account_lock
 gs_role_pldebugger
 omm
 user2
 user3
 user1
(11 rows)
openGauss=# 

2.修改用户user1的密码

openGauss=# alter user user1 identified by '987@abcd' replace '1234@abc';
ALTER ROLE

3.重命名用户user2

openGauss=# alter user user2 rename to user20;
ALTER ROLE
openGauss=# \du
                                                              List of roles
 Role name |                                                    Attributes                                                    | Member of 
-----------+------------------------------------------------------------------------------------------------------------------+-----------
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
 user1     | Create role                                                                                                      | {}
 user20    | Create DB                                                                                                        | {}
 user3     |                                                                                                                  | {}

4.将用户user1的权限授权给用户user3，再回收用户user3的权限

openGauss=# grant user1 to user3;
GRANT ROLE
openGauss=# \du
                                                              List of roles
 Role name |                                                    Attributes                                                    | Member of 
-----------+------------------------------------------------------------------------------------------------------------------+-----------
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
 user1     | Create role                                                                                                      | {}
 user20    | Create DB                                                                                                        | {}
 user3     |                                                                                                                  | {user1}

openGauss=# revoke user1 from user3;
REVOKE ROLE
openGauss=# \du
                                                              List of roles
 Role name |                                                    Attributes                                                    | Member of 
-----------+------------------------------------------------------------------------------------------------------------------+-----------
 ea_ipmp2  | Sysadmin                                                                                                         | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
 user1     | Create role                                                                                                      | {}
 user20    | Create DB                                                                                                        | {}
 user3     |                                                                                                                  | {}

openGauss=# 

5.删除所有创建用户

openGauss=# drop user user1 cascade;
DROP ROLE
openGauss=# drop user user20 cascade;
DROP ROLE
openGauss=# drop user user3 cascade;
DROP ROLE
openGauss=# \du
                                                              List of roles
 Role name |                                                    Attributes                                                    | Member of 
-----------+------------------------------------------------------------------------------------------------------------------+-----------
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}

openGauss=#