openGauss每日一练第4天 | 学习心得体会

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

openGauss=# CREATE USER user1 IDENTIFIED BY 'kunpeng@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
openGauss=# CREATE USER user2 IDENTIFIED BY 'kunpeng@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
openGauss=# CREATE USER user3 IDENTIFIED BY 'kunpeng@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
openGauss=# ALTER USER user1 SYSADMIN;
ALTER ROLE
openGauss=# ALTER USER user2 SYSADMIN;
ALTER ROLE
openGauss=# ALTER USER user3 SYSADMIN;
ALTER ROLE

openGauss=# \du user*
           List of roles
 Role name | Attributes | Member of
-----------+------------+-----------
 user1     | Sysadmin   | {}
 user2     | Sysadmin   | {}
 user3     | Sysadmin   | {}

2.分别使用user1、user2、user3访问数据库musicdb2，创建各自的表，并插入数据

openGauss=# \c musicdb2 user1
Password for user user1:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb2" as user "user1".

musicdb2=> create table products1(product_id INTEGER,product_name char(20),category char(30));
CREATE TABLE
musicdb2=> insert into products1 values(1502,'olympus camera','electrncs');
INSERT 0 1
musicdb2=> insert into products1 values(1601,'lamaze','toys');
INSERT 0 1
musicdb2=> insert into products1 values(1700,'wait interface','Books');
INSERT 0 1
musicdb2=> insert into products1 values(1666,'harry potter','toys');
musicdb2=> \c musicdb2 user2
Password for user user2:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb2" as user "user2".
musicdb2=>
musicdb2=> create table products2(product_id INTEGER,product_name char(20),category char(30));
CREATE TABLE
musicdb2=> insert into products2 values(1502,'olympus camera','electrncs');
INSERT 0 1
musicdb2=> insert into products2 values(1601,'lamaze','toys');
INSERT 0 1
musicdb2=> insert into products2 values(1700,'wait interface','Books');
INSERT 0 1
musicdb2=> insert into products2 values(1666,'harry potter','toys');
INSERT 0 1
musicdb2=> \c musicdb2 user3
Password for user user3:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb2" as user "user3".
musicdb2=>
musicdb2=> create table products3(product_id INTEGER,product_name char(20),category char(30));
CREATE TABLE
musicdb2=> insert into products3 values(1502,'olympus camera','electrncs');
INSERT 0 1
musicdb2=> insert into products3 values(1601,'lamaze','toys');
INSERT 0 1
musicdb2=> insert into products3 values(1700,'wait interface','Books');
INSERT 0 1
musicdb2=> insert into products3 values(1666,'harry potter','toys');
INSERT 0 1
musicdb2=>
--当前数据库musicdb2有哪些表
musicdb2=> \dt
                           List of relations
 Schema |   Name    | Type  | Owner |             Storage
--------+-----------+-------+-------+----------------------------------
 public | products1 | table | user1 | {orientation=row,compression=no}
 public | products2 | table | user2 | {orientation=row,compression=no}
 public | products3 | table | user3 | {orientation=row,compression=no}
(3 rows)

musicdb2=> 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)

musicdb2=> 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)

musicdb2=> select * from products3;
 product_id |     product_name     |            category
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs
       1601 | lamaze               | toys
       1700 | wait interface       | Books
       1666 | harry potter         | toys
(4 rows)

结论：以上实验表明，数据库musicdb2可以被用户user1、user2、user3访问（分别在数据库中创建了一张表、插入一行数据、进行查询）。也就是说一个数据库可以被多个用户来访问。
3.因为用户user1、user2、user3具有数据库系统的SYSADMIN权限，因此这几个用户可以随便查看相互之间创建的表的数据。也就是说使用用户user1、user2、user3中的任何一个，连接到数据库musicdb2，可以查看这三个用户创建的所有的表的内容！下面的测试验证了这一点。
重新打开一个Linux终端窗口，执行如下命令，使用用户user3连接到testdb数据库：

$ gsql -d musicdb2 -h 192.168.50.21 -U user3 -p 15400 -W kunpeng@1234 -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:19 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

musicdb2=>
musicdb2=> 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)

musicdb2=> 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)

musicdb2=> select * from products3;
 product_id |     product_name     |            category
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs
       1601 | lamaze               | toys
       1700 | wait interface       | Books
       1666 | harry potter         | toys
(4 rows)