概述

本文档记录openGauss 3.0.0数据库每日一练第4天课程作业，学习openGauss体系结构，使用多个用户访问同一个数据库。

课程练习

创建用户user1、user2、user3，授予user1、user2、user3数据库系统的SYSADMIN权限

[omm@ogauss1 ~]$ gsql -d zsdba -p 15400 -r -E
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 [zsdba]=#
omm@local:/opt/huawei/tmp [zsdba]=#create user user1 with sysadmin identified by "zs@123456";
CREATE ROLE
omm@local:/opt/huawei/tmp [zsdba]=#create user user2 with sysadmin identified by "zs@123456";
CREATE ROLE
omm@local:/opt/huawei/tmp [zsdba]=#create user user3 with sysadmin identified by "zs@123456";
CREATE ROLE

omm@local:/opt/huawei/tmp [zsdba]=#\du
                                                              List of roles
 Role name |                                                    Attributes                                                    | Member of 
-----------+------------------------------------------------------------------------------------------------------------------+-----------
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
 test1     | Sysadmin                                                                                                         | {}
 user1     | Sysadmin                                                                                                         | {}
 user2     | Sysadmin                                                                                                         | {}
 user3     | Sysadmin                                                                                                         | {}

omm@local:/opt/huawei/tmp [zsdba]=#\dn
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
 user1           | user1
 user2           | user2
 user3           | user3
(13 rows)

分别使用user1、user2、user3访问数据库musicdb2

omm@local:/opt/huawei/tmp [zsdba]=#\c - user1
Password for user user1: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "zsdba" as user "user1".
user1@local:/opt/huawei/tmp [zsdba]=>create table products1(product_id INTEGER,product_name Char(20),category Char(30));
CREATE TABLE
user1@local:/opt/huawei/tmp [zsdba]=>COMMENT ON COLUMN user1.products1.product_id IS '产品编号.';
COMMENT
user1@local:/opt/huawei/tmp [zsdba]=>COMMENT ON COLUMN user1.products1.product_name IS '产品名.';
COMMENT
user1@local:/opt/huawei/tmp [zsdba]=>COMMENT ON COLUMN user1.products1.category IS '种类.';
COMMENT
user1@local:/opt/huawei/tmp [zsdba]=>\d
                           List of relations
 Schema |   Name    | Type  | Owner |             Storage              
--------+-----------+-------+-------+----------------------------------
 public | t         | table | omm   | {orientation=row,compression=no}
 user1  | products1 | table | user1 | {orientation=row,compression=no}
(2 rows)

user1@local:/opt/huawei/tmp [zsdba]=>\d+
                                        List of relations
 Schema |   Name    | Type  | Owner |    Size    |             Storage              | Description 
--------+-----------+-------+-------+------------+----------------------------------+-------------
 public | t         | table | omm   | 8192 bytes | {orientation=row,compression=no} | 
 user1  | products1 | table | user1 | 0 bytes    | {orientation=row,compression=no} | 
(2 rows)

user1@local:/opt/huawei/tmp [zsdba]=>\d+ products1
                             Table "user1.products1"
    Column    |     Type      | Modifiers | Storage  | Stats target | Description 
--------------+---------------+-----------+----------+--------------+-------------
 product_id   | integer       |           | plain    |              | 产品编号.
 product_name | character(20) |           | extended |              | 产品名.
 category     | character(30) |           | extended |              | 种类.
Has OIDs: no
Options: orientation=row, compression=no

user1@local:/opt/huawei/tmp [zsdba]=>\c - user2
Password for user user2: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "zsdba" as user "user2".
user2@local:/opt/huawei/tmp [zsdba]=>create table products2(product_id INTEGER,product_name Char(20),category Char(30));
CREATE TABLE
user2@local:/opt/huawei/tmp [zsdba]=>COMMENT ON COLUMN user2.products2.product_id IS '产品编号.';
COMMENT
user2@local:/opt/huawei/tmp [zsdba]=>COMMENT ON COLUMN user2.products2.product_name IS '产品名.';
COMMENT
user2@local:/opt/huawei/tmp [zsdba]=>COMMENT ON COLUMN user2.products2.category IS '种类.';
COMMENT
user2@local:/opt/huawei/tmp [zsdba]=>
user2@local:/opt/huawei/tmp [zsdba]=>\d
                           List of relations
 Schema |   Name    | Type  | Owner |             Storage              
--------+-----------+-------+-------+----------------------------------
 public | t         | table | omm   | {orientation=row,compression=no}
 user2  | products2 | table | user2 | {orientation=row,compression=no}
(2 rows)

user2@local:/opt/huawei/tmp [zsdba]=>\d+ products2
                             Table "user2.products2"
    Column    |     Type      | Modifiers | Storage  | Stats target | Description 
--------------+---------------+-----------+----------+--------------+-------------
 product_id   | integer       |           | plain    |              | 产品编号.
 product_name | character(20) |           | extended |              | 产品名.
 category     | character(30) |           | extended |              | 种类.
Has OIDs: no
Options: orientation=row, compression=no

user2@local:/opt/huawei/tmp [zsdba]=>\c - user3
Password for user user3: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "zsdba" as user "user3".
user3@local:/opt/huawei/tmp [zsdba]=>create table products3(product_id INTEGER,product_name Char(20),category Char(30));
CREATE TABLE
user3@local:/opt/huawei/tmp [zsdba]=>COMMENT ON COLUMN user3.products3.product_id IS '产品编号.';
COMMENT
user3@local:/opt/huawei/tmp [zsdba]=>COMMENT ON COLUMN user3.products3.product_name IS '产品名.';
COMMENT
user3@local:/opt/huawei/tmp [zsdba]=>COMMENT ON COLUMN user3.products3.category IS '种类.';
COMMENT
user3@local:/opt/huawei/tmp [zsdba]=>\d
                           List of relations
 Schema |   Name    | Type  | Owner |             Storage              
--------+-----------+-------+-------+----------------------------------
 public | t         | table | omm   | {orientation=row,compression=no}
 user3  | products3 | table | user3 | {orientation=row,compression=no}
(2 rows)

user3@local:/opt/huawei/tmp [zsdba]=>\d+ products3
                             Table "user3.products3"
    Column    |     Type      | Modifiers | Storage  | Stats target | Description 
--------------+---------------+-----------+----------+--------------+-------------
 product_id   | integer       |           | plain    |              | 产品编号.
 product_name | character(20) |           | extended |              | 产品名.
 category     | character(30) |           | extended |              | 种类.
Has OIDs: no
Options: orientation=row, compression=no


user3@local:/opt/huawei/tmp [zsdba]=>insert into user1.products1 values(1502,'olympus camera','electrncs'),(1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');
INSERT 0 4
user3@local:/opt/huawei/tmp [zsdba]=>insert into user2.products2 values(1502,'olympus camera','electrncs'),(1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');
INSERT 0 4
user3@local:/opt/huawei/tmp [zsdba]=>insert into user3.products3 values(1502,'olympus camera','electrncs'),(1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');
INSERT 0 4

使用user1、user2、user3用户中的任何一个，查看当前数据库musicdb2有哪些表

omm@local:/opt/huawei/tmp [zsdba]=#show search_path ;
  search_path   
----------------
 "$user",public
(1 row)

omm@local:/opt/huawei/tmp [zsdba]=#set search_path='user1';
SET
omm@local:/opt/huawei/tmp [zsdba]=#show search_path ;
 search_path 
-------------
 user1
(1 row)

omm@local:/opt/huawei/tmp [zsdba]=#\d
                           List of relations
 Schema |   Name    | Type  | Owner |             Storage              
--------+-----------+-------+-------+----------------------------------
 user1  | products1 | table | user1 | {orientation=row,compression=no}
(1 row)

omm@local:/opt/huawei/tmp [zsdba]=#set search_path='user2';
SET
omm@local:/opt/huawei/tmp [zsdba]=#\d
                           List of relations
 Schema |   Name    | Type  | Owner |             Storage              
--------+-----------+-------+-------+----------------------------------
 user2  | products2 | table | user2 | {orientation=row,compression=no}
(1 row)

omm@local:/opt/huawei/tmp [zsdba]=#set search_path='user3';
SET
omm@local:/opt/huawei/tmp [zsdba]=#\d
                           List of relations
 Schema |   Name    | Type  | Owner |             Storage              
--------+-----------+-------+-------+----------------------------------
 user3  | products3 | table | user3 | {orientation=row,compression=no}
(1 row)


[omm@ogauss1 ~]$ gsql -d zsdba -p 15400 -r -U user2 -W zs@123456
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.

user2@local:/opt/huawei/tmp [zsdba]=>\d
                           List of relations
 Schema |   Name    | Type  | Owner |             Storage              
--------+-----------+-------+-------+----------------------------------
 public | t         | table | omm   | {orientation=row,compression=no}
 user2  | products2 | table | user2 | {orientation=row,compression=no}
(2 rows)

根据search_path默认搜索路径，《"$user",public》会查看当前schema下的表和pulbic的表，设置对应schema之后，即可查看对应schema下建的表。