虚拟机环境还原

omm=# \c postgres omm
musicdb2=# drop table products1;
DROP TABLE
musicdb2=# drop table products2;
DROP TABLE
musicdb2=# drop table products3;
DROP TABLE
omm=# drop database musicdb2;
DROP DATABASE
omm=# drop tablespace music_tbs;
DROP TABLESPACE

Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".
openGauss=# revoke all on database omm from user1;
REVOKE
openGauss=# drop user user1;
DROP ROLE
openGauss=# revoke all on database omm from user2;
REVOKE
openGauss=# revoke all on database omm from user3;
REVOKE
openGauss=# drop user user2;
DROP ROLE
openGauss=# drop user user3;
DROP ROLE
openGauss=#

课程作业
1.创建数据库musicdb10，创建用户user10，赋予sysadmin权限
2.用户user10访问数据库postgres，创建一个表并插入数据
3.用户user10访问数据库omm，创建一个表并插入数据
4.用户user10访问数据库musicdb10，创建一个表并插入数据

创建用户及授权

--进入数据库omm，创建表空间、测试数据库
drop DATABASE  IF EXISTS  musicdb;
drop DATABASE  IF EXISTS  musicdb1;
drop DATABASE  IF EXISTS  musicdb2;
drop DATABASE  IF EXISTS  musicdb3;
drop tablespace IF EXISTS music_tbs;

CREATE TABLESPACE music_tbs RELATIVE LOCATION 'tablespace/test_ts1';
CREATE DATABASE musicdb10 WITH TABLESPACE = music_tbs;

--执行下面的SQL语句，创建用户user10：
 CREATE USER user10 IDENTIFIED BY 'kunpeng@1234';
 
--授予user1数据库系统的SYSADMIN权限：
ALTER USER user10 SYSADMIN;

openGauss=# \l
                          List of databases
   Name    | Owner | Encoding  | Collate | Ctype | Access privileges
-----------+-------+-----------+---------+-------+-------------------
 musicdb10 | omm   | SQL_ASCII | C       | C     |
 omm       | omm   | SQL_ASCII | C       | C     | =Tc/omm          +
           |       |           |         |       | omm=CTc/omm
 postgres  | omm   | SQL_ASCII | C       | C     | =Tc/omm          +
           |       |           |         |       | omm=CTc/omm
 template0 | omm   | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
 template1 | omm   | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
(5 rows)

openGauss=#  CREATE USER user10 IDENTIFIED BY 'kunpeng@1234';
CREATE ROLE
openGauss=# ALTER USER user10 SYSADMIN;
ALTER ROLE

在不通数据库中建表并插入数据

\c postgres user10

CREATE TABLE products1
(
    product_id  integer,
    product_name char(20),
    category char(30)
);
comment on TABLE products1 is 'user1测试表';
comment on COLUMN products1.product_id IS '产品编号';
comment on COLUMN products1.product_name IS '产品名';
comment on COLUMN products1.category IS '种类';

insert into products1 values(1502,'olympus camera','electrncs');
insert into products1 values(1601,'lamaze','toys');
insert into products1 values(1700,'wait interface','Books');
insert into products1 values(1666,'harry potter','toys');

\c omm user10

CREATE TABLE products1
(
    product_id  integer,
    product_name char(20),
    category char(30)
);
comment on TABLE products1 is 'user1测试表';
comment on COLUMN products1.product_id IS '产品编号';
comment on COLUMN products1.product_name IS '产品名';
comment on COLUMN products1.category IS '种类';

insert into products1 values(2502,'olympus camera','electrncs');
insert into products1 values(2601,'lamaze','toys');
insert into products1 values(2700,'wait interface','Books');
insert into products1 values(2666,'harry potter','toys');


\c musicdb10 user10

CREATE TABLE products1
(
    product_id  integer,
    product_name char(20),
    category char(30)
);
comment on TABLE products1 is 'user1测试表';
comment on COLUMN products1.product_id IS '产品编号';
comment on COLUMN products1.product_name IS '产品名';
comment on COLUMN products1.category IS '种类';

insert into products1 values(3502,'olympus camera','electrncs');
insert into products1 values(3601,'lamaze','toys');
insert into products1 values(3700,'wait interface','Books');
insert into products1 values(3666,'harry potter','toys');