openGauss每日一练第5天 | openGauss创建用户、修改用户属性、更改用户权限和删除用户

首先连接openGauss 数据库，连接命令如下：

root@modb:~# su - omm 

omm@modb:~$ gsql -r

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

首先查看用户，命令如下：

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 | {}

创建用户user1，具有CREATEROLE权限，语句如下：

创建用户user2， 具有CREATEDB权限，语句如下：

omm=# CREATE USER user2 CREATEDB IDENTIFIED BY 'lxs_data@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
使用\du 命令查看创建的user1 用户和是否具有CREATEDB 权限：

omm=# \du user2;
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
user2 | Create DB | {}

创建用户user3，使用不同的方法设置密码，语句如下：

omm=# CREATE USER user3 PASSWORD 'lxs_data@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

使用\du 命令查看创建的user3 用户：

omm=# \du user3;
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
user3 | | {}

操作截图：

2.修改用户user1的密码，语句如下：

将user1 密码修改为：lxs_data@456  ，语句如下：

omm=# ALTER USER user1 IDENTIFIED BY 'lxs_data@456' REPLACE 'lxs_data@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE

将user1 密码修改为：lxs_data@789  ，语句如下：

omm=# ALTER USER user1 IDENTIFIED BY 'lxs_data@789';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE

两种修改方法。

操作截图如下：

3.重命名用户user2

将用户名user2 重命名为userlxs_data,语句如下:

omm=# \du user2;
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
user2 | Create DB | {}

omm=# alter user user2 rename to userlxs_data;
NOTICE: MD5 password cleared because of role rename
ALTER ROLE
omm=# \du userlxs_data;
List of roles
Role name | Attributes | Member of
--------------+------------+-----------
userlxs_data | Create DB | {}

操作截图：

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

首选查看用户user1 权限,语句如下：

omm=# \du user1;
List of roles
Role name | Attributes | Member of
-----------+-------------+-----------
user1 | Create role | {}

查看用户user3 权限,语句如下：

omm=# \du user3;
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
user3 | | {}

将用户user1的权限授权给用户user3，语句如下：

omm=# grant user1 to user3;
GRANT ROLE

再查看user3 权限，语句如下：

omm=# \du user3;
omm=# List of roles
Role name | Attributes | Member of
-----------+------------+-----------
user3 | | {user1}

回收用户user3的权限，语句如下：

omm=# revoke user1 from user3;

REVOKE ROLE

omm=# \du user3;
omm=# List of roles
Role name | Attributes | Member of
-----------+------------+-----------
user3 | | {}

操作截图：

5.删除所有创建用户

查看所有用户：

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 | {}
user1 | Create role | {}
user3 | | {}
userlxs_data | Create DB | {}

omm=# \du+ user1;        --------查看指定某个用户
omm=# List of roles
Role name | Attributes | Member of | Description
-----------+-------------+-----------+-------------
user1 | Create role | {} | 

删除user1、user3、userlxs_data  这三个用户，语句如下:

omm=# drop user user1;
omm=# DROP ROLE

omm=# drop user user3;
DROP ROLE
omm=#
omm=# drop user userlxs_data;
DROP ROLE
omm=# 

再查看所有用户，语句如下：

omm=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+----------------------------------------------
gaussdb | Sysadmin | {} |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin,Operatoradmin, Policyadmin, UseFT | {} |

操作截图：

持续打卡第五天，继续！！！！！