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

实操作业

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

CREATE TABLESPACE test10_tbs LOCATION '/opt/huawei/tbs';
create database testdb10  tablespace test10_tbs;

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

drop user if exists user10;
create user user10 sysadmin password 'test@123';
\c testdb10 user10 
create  table t1(id int);
create  table t2(id int);

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

 create role role10 identified by 'test@123';
 testdb10=> \du
                                                               List of roles
 Role name |                                                    Attributes                                                    | Member of  
-----------+------------------------------------------------------------------------------------------------------------------+----------
 gnzyy     | Create role, Create DB, Sysadmin                                                                                 | {omm}
 gnzyydba  | Create role, Create DB, Replication, Administer audit, Sysadmin                                                 +| {}
           | Role valid begin 2022-11-25 22:26:09+08                                                                         +| 
           | Role valid until 2169-11-25 22:26:09+08                                                                          | 
 joe       | Cannot login                                                                                                     | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {gnzyydba}
 role10    | Cannot login                                                                                                     | {}
 user10    | Sysadmin                                                                                                         | {}
--默认 create role 创建的用户没有登录权限，需要手动赋予登录权限

 testdb10=> \c testdb10 role10
Password for user role10: 
FATAL:  role "role10" is not permitted to login
Previous connection kept
--赋予role10登录权限
testdb10=> alter  role role10 login ;
ALTER ROLE
--登录成功
testdb10=> \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".

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

gnzyyhis=> drop table if exists t1;
DROP TABLE
--查看表空间、数据库、t2表所有者
testdb10=> \db test10_tbs 
          List of tablespaces
    Name    | Owner  |    Location     
------------+--------+-----------------
 test10_tbs | user10 | /opt/huawei/tbs
(1 row)
testdb10=> \l
                          List of databases
   Name    | Owner  | Encoding  | Collate | Ctype | Access privileges 
-----------+--------+-----------+---------+-------+-------------------
 gnzyyhis  | gnzyy  | SQL_ASCII | C       | C     | 
 postgres  | omm    | SQL_ASCII | C       | C     | 
 template0 | omm    | SQL_ASCII | C       | C     | =c/omm           +
           |        |           |         |       | omm=CTc/omm
 template1 | omm    | SQL_ASCII | C       | C     | =c/omm           +
           |        |           |         |       | omm=CTc/omm
 testdb10  | user10 | SQL_ASCII | C       | C     | 
(5 rowstestdb10=> \dt public.t2
                         List of relations
 Schema | Name | Type  | Owner  |             Storage              
--------+------+-------+--------+----------------------------------
 public | t2   | table | user10 | {orientation=row,compression=no}
(1 row)
--删除user10
 testdb10=> drop user  if exists user10;
ERROR:  role "user10" cannot be dropped because some objects depend on it
DETAIL:  owner of tablespace test10_tbs
owner of table public.t2
owner of table public.t1
owner of table public.t
owner of database testdb10
2 objects in database gnzyyhis
--不能删除user10 原因是user10拥有提示的5个对象
--将提示的5个对象所有者转给role10
 alter table public.t2 owner to role10;
 alter table public.t1 owner to role10;
 alter table public.t owner to role10;
 alter database testdb10 owner to role10;
 alter tablespace test10_tbs owner to role10;
 --再次删除，删除成功
 gnzyyhis=> drop user user10;
DROP ROLE

5、最后删除role10

gnzyyhis=> drop role  if exists role10;
ERROR:  role "role10" cannot be dropped because some objects depend on it
DETAIL:  owner of tablespace test10_tbs
owner of database testdb10
3 objects in database testdb10
--删除角色拥有的对象
gnzyyhis=> drop if exists  database testdb10 ;
DROP DATABASE
gnzyyhis=> drop tablespace if exists test10_tbs;
DROP TABLESPACE
--再次删除 删除成功
gnzyyhis=> drop role  role10 ;
DROP ROLE