openGauss 每日一练第 19 天 |openGauss 用户和角色管理

学习目标

掌握 openGauss 的用户和角色管理。

前面每日一练链接

openGauss 每日一练第 1 天 | openGauss 数据库状态查看
openGauss 每日一练第 2 天 | 学习 gsql 命令行的使用
openGauss 每日一练第 3 天 | openGauss 数据库状态查看
openGauss 每日一练第 4 天 | openGauss 中一个数据库可以被多个用户访问
openGauss 每日一练第 5 天 | openGauss 中一个用户可以访问多个数据库
openGauss 每日一练第 6 天 | openGauss 中用户一次只能连接到一个数据库
openGauss 每日一练第 7 天 | openGauss 中一个数据库中可以创建多个模式
openGauss 每日一练第 8 天 | openGauss 中一个数据库可以存储在多个表空间中
openGauss 每日一练第 9 天 | openGauss 中一个表空间可以存储多个数据库
openGauss 每日一练第 10 天 | openGauss 逻辑结构：表空间管理
openGauss 每日一练第 11 天 | openGauss 逻辑结构：数据库管理
openGauss 每日一练第 12 天 | openGauss 逻辑结构：模式管理
openGauss 每日一练第 13 天 |openGauss 逻辑结构：表管理 1
openGauss 每日一练第 14 天 |openGauss 逻辑结构：表管理 2
openGauss 每日一练第 15 天 |openGauss 逻辑结构：表管理 3
openGauss 每日一练第 16 天 |openGauss 逻辑结构：表管理 4
openGauss 每日一练第 17 天 |openGauss 逻辑结构：索引管理
openGauss 每日一练第 18 天 |openGauss 逻辑结构：视图管理

课程学习

使用 create user 创建的用户与使用 create role 创建的用户的区别在于，前者可以直接连接登录数据库，而使用 create role 创建的用户不能直接登录到数据库。必须添加 LOGIN 权限后，才能登录到数据库管理系统。
删除用户，首先需要将用户拥有的数据库对象转移或者删除。

1.环境准备

创建一个名叫 test_tbs 的表空间和一个名叫 testdb 的数据库：

gsql -d omm  -p 5432 -r 
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

CREATE TABLESPACE test_tbs RELATIVE LOCATION 'tablespace/test_tbs1';
CREATE DATABASE testdb WITH TABLESPACE = test_tbs;

2.使用 create user 创建用户

使用 create user 创建的用户，具有登录权限。

--创建一个名叫user1的数据库用户，其密码为kunpeng@1234，并将数据库testdb所有的权限都授予用户user1：

 CREATE USER user1 IDENTIFIED BY 'JiekeXu_1234';
 GRANT ALL ON DATABASE testdb TO user1;
--执行下面的 gsql 元命令，查看系统目前有哪些数据库：
\l
    
--执行下面的 gsql 元命令，查看系统目前有哪些用户：
\du
\q
--用刚刚创建的数据库用户 user1 登录到数据库testdb
gsql -d testdb   -U user1   -W JiekeXu_1234 -r
\q

3.使用 create role 创建用户

使用 create role 命令创建的用户 user2，没有登录权限。

--创建一个新的名叫 user2 的角色，并将数据库 testdb 所有的权限都授予用户 user2：
用户 user2 对数据库 testdb 具有的权限和用户 user1 一模一样。

gsql -d omm  -p 5432 -r 

CREATE ROLE user2 IDENTIFIED BY 'JiekeXu_1234';
GRANT ALL ON DATABASE testdb TO user2;
                         
--查看当前 openGauss 数据库集群有哪些用户：
  \du
  \q
                                    
--使用用户user2尝试登录到数据库testdb：
gsql -d testdb  -U user2  -W JiekeXu_1234 -r

gsql: FATAL:  role "user2" is not permitted to login

原因：
由于数据库用户 user2 是使用 create role 语句创建的，目前还不被允许登录到openGauss 数据库管理系统。

4.授予用户 user2 登录权限后：

授予用户 user2 登录权限后，可以登录数据库

gsql -d omm  -p 5432 -r 
alter user user2 LOGIN;
\du

 \q
--现在我们已经授予了 user2 用户登录数据库的权限，可以正常登录数据库 testdb：
 gsql -d testdb   -U user2   -W JiekeXu_1234 -r
\q

5.删除用户和角色

删除用户，首先需要将用户拥有的数据库对象转移或者删除，回收权限。

-- 授予用户 user2 数据库超级用户的权限：
gsql -d omm  -p 5432 -r 
ALTER USER user2 SYSADMIN;

--使用用户 user2 登录到数据库 testdb，创建表空间 test_tbs1、数据库testdb、表 test1 和表 test2：
 \c testdb user2

 CREATE TABLESPACE test_tbs1 RELATIVE LOCATION 'tablespace/test_tbs11';
CREATE DATABASE testdb WITH TABLESPACE = test_tbs1;
 CREATE TABLE test1(col int);
 CREATE TABLE test2(col int);
\q

--执行如下的命令，删除用户user2：
gsql -d omm  -p 5432 -r
drop user user2;

ERROR:  role "user2" cannot be dropped because some objects depend on it
DETAIL:  owner of database testdb
owner of tablespace test_tbs1
privileges for database testdb
2 objects in database testdb

###可以看出不能删除用户 user2 的原因是：
1）用户 user2 拥有数据库 testdb。
2）用户 user2 拥有表空间对象 test_tbs1。
3）用户 user2 对数据库 testdb 具有权限。
4）用户 user2 在数据库 testdb 中有两个对象。

--要删除用户 user2，必须先表空间对象和数据库对象的属主修改为其他的用户（如user1 用户），或者干脆将其删除：
alter database testdb owner to user1;
alter tablespace test_tbs1 owner to user1;

--回收用户 user2 对 testdb 数据库的权限：
 REVOKE ALL ON DATABASE testdb FROM user2;

--在 testdb 中属于用户 user2 的数据库对象的处理方法可以是：假如该对象还有用，可以将该对象转移给其他用户；假如该对象没有用，可以直接删除掉该对象
                      
--假设表 test1 已经没有用了，我们可以删除表 test1：
\c testdb user2
 drop table test1;

--假设表 test2 还有用将表 test2 转移给用户 user1：

 REASSIGN OWNED BY user2 to user1;
\dt
\q

--执行删除用户的操作,可以删除user2用户
gsql -d omm  -p 5432 -r
drop user user2;
 \du

课程作业

1、创建 test10_tbs 的表空间，在这个表空间中创建数据库 testdb10

gsql -d omm  -p 5432 -r 
CREATE TABLESPACE test10_tbs RELATIVE LOCATION 'tablespace/test10_tbs';
CREATE DATABASE testdb10 WITH TABLESPACE = test10_tbs;

2、使用 create user 创建用户 user10，登录数据库 testdb10，创建测试表 t1 和 t2

CREATE USER user10 IDENTIFIED BY 'JiekeXu_1234';
GRANT ALL ON DATABASE testdb10 TO user10;
alter user user10 sysadmin;

gsql -d testdb10   -U user10   -W JiekeXu_1234 -r
CREATE TABLE t1(col int);
CREATE TABLE t2(col int);

--建表会提示权限不足，这里临时给看 sysadmin 权限
testdb10=>  CREATE TABLE t1(col int);
ERROR:  permission denied for schema public
DETAIL:  N/A

3、使用 create role 创建角色 role10，登录数据库 testdb10

gsql -d omm  -p 5432 -r 

CREATE ROLE role10 IDENTIFIED BY 'JiekeXu_1234';
GRANT ALL ON DATABASE testdb10 TO role10;
alter user role10 LOGIN;

4、将表 t1 直接删除，将前面创建的表空间和数据库、表 t2 转给 role10，删除用户 user10

[omm@jiekexu1 ~]$  gsql -d testdb10   -U user10   -W JiekeXu_1234 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

testdb10=> \dt
                         List of relations
 Schema | Name | Type  | Owner  |             Storage              
--------+------+-------+--------+----------------------------------
 public | t1   | table | user10 | {orientation=row,compression=no}
 public | t2   | table | user10 | {orientation=row,compression=no}
(2 rows)

testdb10=> drop table t1;
DROP TABLE

testdb10=> alter database testdb10 owner to role10;
ALTER DATABASE
testdb10=> alter tablespace test10_tbs owner to role10;
ALTER TABLESPACE
testdb10=> REVOKE ALL ON DATABASE testdb10 FROM user10;
REVOKE
testdb10=> \dt
                         List of relations
 Schema | Name | Type  | Owner  |             Storage              
--------+------+-------+--------+----------------------------------
 public | t2   | table | user10 | {orientation=row,compression=no}
(1 row)

testdb10=> REASSIGN OWNED BY user10 to role10;
REASSIGN OWNED
testdb10=> \dt
                         List of relations
 Schema | Name | Type  | Owner  |             Storage              
--------+------+-------+--------+----------------------------------
 public | t2   | table | role10 | {orientation=row,compression=no}
(1 row)


$ gsql -d omm  -p 5432 -r 
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# \du
                                                              List of roles
 Role name |                                                    Attributes                                                    | M
ember of 
-----------+------------------------------------------------------------------------------------------------------------------+--
---------
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {
}
 role10    |                                                                                                                  | {
}
 user1     |                                                                                                                  | {
}
 user10    | Sysadmin                                                                                                         | {
}

omm=#  drop user user10;
DROP ROLE

5、最后删除 role10

omm=# drop user role10;
ERROR:  role "role10" cannot be dropped because some objects depend on it
DETAIL:  owner of tablespace test10_tbs
owner of database testdb10
1 object in database testdb10

omm=# \c testdb10  role10
Password for user role10: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb10" as user "role10".
testdb10=> \dt
                         List of relations
 Schema | Name | Type  | Owner  |             Storage              
--------+------+-------+--------+----------------------------------
 public | t2   | table | role10 | {orientation=row,compression=no}
(1 row)

testdb10=> drop table t2;
DROP TABLE

[omm@jiekexu1 ~]$ gsql -d omm  -p 5432 -r 
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# drop user role10;
ERROR:  role "role10" cannot be dropped because some objects depend on it
DETAIL:  owner of tablespace test10_tbs
owner of database testdb10
omm=# 
omm=# \db
             List of tablespaces
    Name    | Owner  |       Location        
------------+--------+-----------------------
 jieke_tbs  | omm    | tablespace/jieke_tbs
 myindex_ts | omm    | tablespace/myindex_ts
 pg_default | omm    | 
 pg_global  | omm    | 
 test10_tbs | role10 | tablespace/test10_tbs
 test_tbs   | omm    | tablespace/test_tbs1
 test_tbs1  | user1  | tablespace/test_tbs11
(7 rows)

omm=# alter tablespace test10_tbs owner to user1;
ALTER TABLESPACE
omm=# \db
            List of tablespaces
    Name    | Owner |       Location        
------------+-------+-----------------------
 jieke_tbs  | omm   | tablespace/jieke_tbs
 myindex_ts | omm   | tablespace/myindex_ts
 pg_default | omm   | 
 pg_global  | omm   | 
 test10_tbs | user1 | tablespace/test10_tbs
 test_tbs   | omm   | tablespace/test_tbs1
 test_tbs1  | user1 | tablespace/test_tbs11
(7 rows)

omm=# drop user role10;
ERROR:  role "role10" cannot be dropped because some objects depend on it
DETAIL:  owner of database testdb10
omm=# alter database testdb10 owner to user1;
ALTER DATABASE
omm=# drop user role10;
DROP ROLE