openGauss每日一练第 4 天

原创陶笑 2022-11-28

118

学习目标

学习openGauss体系结构，使用多个用户访问同一个数据库

课后作业

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

--创建3个用户
openGauss=#  CREATE USER user1 IDENTIFIED BY 'kunpeng@1234';
CREATE ROLE
openGauss=#  CREATE USER user2 IDENTIFIED BY 'kunpeng@1234';
CREATE ROLE
openGauss=#  CREATE USER user3 IDENTIFIED BY 'kunpeng@1234';
CREATE ROLE
--对3个用户分别授权sysadmin
openGauss=# ALTER USER user1 SYSADMIN;
ALTER ROLE
openGauss=# ALTER USER user2 SYSADMIN;
ALTER ROLE
openGauss=# ALTER USER user3 SYSADMIN;
ALTER ROLE
--查看3个用户，可以看到用户和对应的权限
 Role name |                                                    Attributes                                                    | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
 user1     | Sysadmin                                                                                                         | {}
 user2     | Sysadmin                                                                                                         | {}
 user3     | Sysadmin                                                                                                         | {}
复制

2.分别使用user1、user2、user3访问数据库musicdb，创建各自的表，并插入数据。表名和数据如下：

表名分别为： products1、 products2、 products3

字段名数据类型含义
product_id INTEGER 产品编号
product_name Char(20) 产品名
category Char(30) 种类
向表中插入数据：
product_id product_name category
1502 olympus camera electrncs
1601 lamaze toys
1700 wait interface Books
1666 harry potter toys

--user1
openGauss=#  \c musicdb user1
Password for user user1:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "user1".
musicdb=> create table t1(col1 char(20));
CREATE TABLE
musicdb=>   insert into t1 values('Hello kunpeng 1');
INSERT 0 1
musicdb=>   select * from t1;
         col1
----------------------
 Hello kunpeng 1
(1 row)
musicdb=>  create table products1(product_id integer ,product_name char(20),category char(30));
CREATE TABLE
musicdb=>  COMMENT ON COLUMN products1.product_id IS '产品编号';
COMMENT
musicdb=>   COMMENT ON COLUMN products1.product_name IS '产品名';
COMMENT
musicdb=>   COMMENT ON COLUMN products1.category IS '种类';
COMMENT
musicdb=> insert into products1 values(1502,'olympus camera','electrncs');
INSERT 0 1
musicdb=> insert into products1 values(1601,'lamaze','toys');
INSERT 0 1
musicdb=> insert into products1 values(1700,'wait interface','Books');
INSERT 0 1
musicdb=> insert into products1 values(1666,'harry potter','toys');
INSERT 0 1
musicdb=> select * from products1;
 product_id |     product_name     |            category
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs
       1601 | lamaze               | toys
       1700 | wait interface       | Books
       1666 | harry potter         | toys
(4 rows)

--user2
musicdb=> \c musicdb user2
Password for user user2:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "user2".
musicdb=> create table products2(product_id integer ,product_name char(20),category char(30));
CREATE TABLE
musicdb=> COMMENT ON COLUMN products1.product_id IS '产品编号';
COMMENT
musicdb=> COMMENT ON COLUMN products1.product_name IS '产品名';
COMMENT
musicdb=> COMMENT ON COLUMN products1.category IS '种类';
COMMENT
musicdb=> insert into products2 values(1502,'olympus camera','electrncs');
INSERT 0 1
musicdb=> insert into products2 values(1601,'lamaze','toys');
INSERT 0 1
musicdb=> insert into products2 values(1700,'wait interface','Books');
INSERT 0 1
musicdb=> insert into products2 values(1666,'harry potter','toys');
INSERT 0 1
musicdb=> select * from products2;
 product_id |     product_name     |            category
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs
       1601 | lamaze               | toys
       1700 | wait interface       | Books
       1666 | harry potter         | toys
(4 rows)



--user3	
musicdb=> \c musicdb user3
Password for user user3:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "user3".
musicdb=> create table products3(product_id integer ,product_name char(20),category char(30));
CREATE TABLE
musicdb=> COMMENT ON COLUMN products3.product_id IS '产品编号';
COMMENT
musicdb=> COMMENT ON COLUMN products3.product_name IS '产品名';
COMMENT
musicdb=> COMMENT ON COLUMN products3.category IS '种类';
COMMENT
musicdb=>   insert into products3 values(1502,'olympus camera','electrncs');
INSERT 0 1
musicdb=> insert into products3 values(1601,'lamaze','toys');
INSERT 0 1
musicdb=> insert into products3 values(1700,'wait interface','Books');
INSERT 0 1
musicdb=> insert into products3 values(1666,'harry potter','toys');
INSERT 0 1
musicdb=> select * from products3
musicdb-> ;
 product_id |     product_name     |            category
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs
       1601 | lamaze               | toys
       1700 | wait interface       | Books
       1666 | harry potter         | toys
(4 rows)









复制

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

用user3查看所有表

musicdb=> \dt+
                                        List of relations
 Schema |   Name    | Type  | Owner |    Size    |             Storage              | Description
--------+-----------+-------+-------+------------+----------------------------------+-------------
 public | products1 | table | user1 | 8192 bytes | {orientation=row,compression=no} |
 public | products2 | table | user2 | 8192 bytes | {orientation=row,compression=no} |
 public | products3 | table | user3 | 8192 bytes | {orientation=row,compression=no} |
 public | t1        | table | user1 | 8192 bytes | {orientation=row,compression=no} |
(4 rows)
\connect    \conninfo   \copy       \copyright
musicdb=> \conninfo
You are connected to database "musicdb" as user "user3" via socket in "/datafs/tmp" at port "26000".

复制