openGauss每日一练第 4天|多用户管理一个数据库

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

--创建user1并赋予sysadmin权限
create user user1 sysadmin  password 'GaussDB@123';
--创建user2、user3
create user user2 IDENTIFIED BY 'GaussDB@123';
create user user3 password 'GaussDB@123';
--赋予user2、user3数据库系统sysadmin权限
alter user user2 sysadmin ;
alter user user3 sysadmin ;
--查看用户信息
omm=# \du+
                                                                     List of roles
 Role name |                                                    Attributes                                                    | Member of | Description 
-----------+------------------------------------------------------------------------------------------------------------------+-
 gaussdb   | Sysadmin                                                                                                         | {}        | 
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}        | 
 user1     | Sysadmin                                                                                                         | {}        | 
 user2     | Sysadmin                                                                                                         | {}        | 
 user3     | Sysadmin                                                                                                         | {}        | 

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

--user1连接到musicdb2
\l+
\c musicdb2 user1
--创建表 products1
create table products1(
 product_id     INTEGER ,
 product_name   Char(20) ,
 category       Char(30) 
);
comment on column  products1.product_id is '产品编号';
comment on column  products1.product_name is '产品';
comment on column  products1.category is '种类';
--查看表结构
\d+ products1
--插入数据
insert into products1 values (1502,'olympus camera','electrncs'),
(1601,'lamaze','toys'),
(1700,'wait interface','Books'),
(1666,'harry potter','toys');
--查看数据
 select * from products1 ;
    
 --user2连接到musicdb2
\l+
\c musicdb2 user2
--创建表 products2
create table products2(
 product_id     INTEGER ,
 product_name   Char(20) ,
 category       Char(30) 
);
comment on column  products2.product_id is '产品编号';
comment on column  products2.product_name is '产品';
comment on column  products2.category is '种类';
--查看表结构
\d+ products2
--插入数据
insert into products2 (product_id,product_name,category)values
(1502,'olympus camera','electrncs'),
(1601,'lamaze','toys'),
(1700,'wait interface','Books'),
(1666,'harry potter','toys');
--查看数据
select * from products2 ; 
       
 --user3连接到musicdb2
\l+
\c musicdb2 user3
--创建表 products3
create table products3(
 product_id     INTEGER ,
 product_name   Char(20) ,
 category       Char(30) 
);
comment on column  products2.product_id is '产品编号';
comment on column  products2.product_name is '产品';
comment on column  products2.category is '种类';
--查看表结构
\d+ products3
--插入数据
insert into products3(product_id,product_name,category)values
(1502,'olympus camera','electrncs'),
(1601,'lamaze','toys'),
(1700,'wait interface','Books'),
(1666,'harry potter','toys');
--查看数据
select * from products3 ;

表名分别为： products1、 products2、 products3

向表中插入数据：

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

musicdb2=> \conninfo
You are connected to database "musicdb2" as user "user3" via socket in "/tmp" at port "5432".
musicdb2=> \d+
 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} |