概述

本文档记录openGauss 3.0.0数据库每日一练第19天课程作业，掌握openGauss的用户和角色管理。

课程练习

创建day19的表空间，在这个表空间中创建数据库musicdb19

omm@local:/opt/huawei/tmp [postgres]=#CREATE TABLESPACE day19 RELATIVE LOCATION 'tablespace/day19';
CREATE TABLESPACE
omm@local:/opt/huawei/tmp [postgres]=#CREATE DATABASE musicdb19 WITH TABLESPACE = day19;
CREATE DATABASE

使用create user创建用户user19，登录数据库musicdb19，创建测试表t1和t2

omm@local:/opt/huawei/tmp [postgres]=#CREATE USER user19 IDENTIFIED BY 'zs@123456';
CREATE ROLE
omm@local:/opt/huawei/tmp [postgres]=#GRANT ALL ON DATABASE musicdb19 TO user19;
GRANT

omm@local:/opt/huawei/tmp [postgres]=#ALTER USER user19  SYSADMIN;
ALTER ROLE

[omm@ogauss1 ~]$ gsql -d musicdb19 -Uuser19 -Wzs@123456 -p 15400 -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:34 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

user19@local:/opt/huawei/tmp [musicdb19]=>create table t1(id int, name varchar(20));
CREATE TABLE
user19@local:/opt/huawei/tmp [musicdb19]=>create table t2(id int, name varchar(20));
CREATE TABLE

使用create role创建角色role19，登录数据库musicdb19

使用create user创建的用户与使用create role创建的用户的区别在于，前者可以直接连接登录数据库，而使用create role创建的用户不能直接登录到数据库。必须添加LOGIN权限后，才能登录到数据库管理系统。

[omm@ogauss1 ~]$ gsql -d postgres -p 15400 -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:34 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm@local:/opt/huawei/tmp [postgres]=#CREATE ROLE role19 IDENTIFIED BY 'zs@123456';
CREATE ROLE
omm@local:/opt/huawei/tmp [postgres]=#GRANT ALL ON DATABASE musicdb19 TO role19;
GRANT
omm@local:/opt/huawei/tmp [postgres]=#\q
[omm@ogauss1 ~]$ gsql -d musicdb19 -Urole19 -Wzs@123456 -p 15400 -r
gsql: FATAL:  role "role19" is not permitted to login
[omm@ogauss1 ~]$ gsql -d postgres -p 15400 -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:34 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
--授权后可以登录
omm@local:/opt/huawei/tmp [postgres]=#alter user role19 LOGIN;
ALTER ROLE
omm@local:/opt/huawei/tmp [postgres]=#\q
[omm@ogauss1 ~]$ gsql -d musicdb19 -Urole19 -Wzs@123456 -p 15400 -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:34 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

role19@local:/opt/huawei/tmp [musicdb19]=>

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

[omm@ogauss1 ~]$ gsql -d postgres -p 15400 -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:34 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
--删除提示有数据库权限，不能删除
omm@local:/opt/huawei/tmp [postgres]=#drop user user19;
ERROR:  role "user19" cannot be dropped because some objects depend on it
DETAIL:  privileges for database musicdb19
2 objects in database musicdb19

omm@local:/opt/huawei/tmp [postgres]=#\c musicdb19 user19
Password for user user19: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb19" as user "user19".
--删除无用表
user19@local:/opt/huawei/tmp [musicdb19]=>drop table t1;
DROP TABLE
user19@local:/opt/huawei/tmp [musicdb19]=>\d
                         List of relations
 Schema | Name | Type  | Owner  |             Storage              
--------+------+-------+--------+----------------------------------
 public | t2   | table | user19 | {orientation=row,compression=no}
(1 row)
--转移有用表
user19@local:/opt/huawei/tmp [musicdb19]=>REASSIGN OWNED BY user19 to role19;
REASSIGN OWNED
user19@local:/opt/huawei/tmp [musicdb19]=>\d
                         List of relations
 Schema | Name | Type  | Owner  |             Storage              
--------+------+-------+--------+----------------------------------
 public | t2   | table | role19 | {orientation=row,compression=no}
(1 row)

--回收数据库权限
user19@local:/opt/huawei/tmp [musicdb19]=>REVOKE ALL ON DATABASE musicdb19 FROM user19;
REVOKE

user19@local:/opt/huawei/tmp [musicdb19]=>\c postgres omm
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".
--成功删除用户user19
omm@local:/opt/huawei/tmp [postgres]=#drop user user19;
DROP ROLE

最后删除role19

--删除提示用对象或者权限
omm@local:/opt/huawei/tmp [postgres]=#drop user role19;
ERROR:  role "role19" cannot be dropped because some objects depend on it
DETAIL:  privileges for database musicdb19
1 object in database musicdb19
omm@local:/opt/huawei/tmp [postgres]=#\c musicdb19 role19
Password for user role19: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb19" as user "role19".
--删除对象
role19@local:/opt/huawei/tmp [musicdb19]=>drop table t2;
DROP TABLE
--回收权限提示没有
role19@local:/opt/huawei/tmp [musicdb19]=>REVOKE ALL ON DATABASE musicdb19 FROM role19;
WARNING:  no privileges could be revoked for "musicdb19"
REVOKE

--回收第二次 提示成功
omm@local:/opt/huawei/tmp [musicdb19]=#REVOKE ALL ON DATABASE musicdb19 FROM role19;
REVOKE
omm@local:/opt/huawei/tmp [musicdb19]=#\c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".
--成功删除
omm@local:/opt/huawei/tmp [postgres]=#drop user role19;
DROP ROLE