学习目标
学习openGauss体系结构，使用多个用户访问同一个数据库
课程学习
创建user1、user2、user3用户，验证数据库musicdb可以被用户user1、user2、user3访问（分别在数据库中创建了一张表、插入数据、进行查询）。即一个数据库可以被多个用户访问。
课后作业
1.创建用户user1、user2、user3，授予user1、user2、user3数据库系统的SYSADMIN权限
--创建用户user1、user2、user3：
omm=# CREATE USER user1 IDENTIFIED BY 'meiriyilian@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# CREATE USER user2 IDENTIFIED BY 'meiriyilian@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# CREATE USER user3 IDENTIFIED BY 'meiriyilian@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
--授予user1、user2、user3数据库系统的SYSADMIN权限：
omm=# ALTER USER user1 SYSADMIN;
ALTER ROLE
omm=# ALTER USER user2 SYSADMIN;
ALTER ROLE
omm=# ALTER USER user3 SYSADMIN;
ALTER ROLE
--查看用户是否正确创建：
omm=# \du
List of roles
Role name | Attributes
| Member of
-----------+------------------------------------------------------------------------------------------------------------
------+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin,
UseFT | {}
user1 | Sysadmin
| {}
user2 | Sysadmin
| {}
user3 | Sysadmin
| {}
omm=#
2.分别使用user1、user2、user3访问数据库musicdb2，创建各自的表，并插入数据。表名和数据如下：
表名分别为： 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
--\l 查看是否已经存在musicdb2
omm-# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+-------+----------+---------+-------+-------------------+-------+------------+------------------------------
--------------
music_db | omm | UTF8 | C | C | | 12 MB | music_tbs1 |
music_db1 | omm | UTF8 | C | C | | 12 MB | music_tbs1 |
music_db2 | omm | UTF8 | C | C | | 12 MB | music_tbs1 |
musicdb | omm | UTF8 | C | C | | 12 MB | music_tbs |
musicdb1 | omm | UTF8 | C | C | | 12 MB | music_tbs |
musicdb2 | omm | UTF8 | C | C | | 12 MB | music_tbs |
musicdb3 | omm | UTF8 | C | C | | 12 MB | music_tbs |
omm | omm | UTF8 | C | C | | 12 MB | pg_default |
postgres | omm | UTF8 | C | C | | 12 MB | pg_default | default administrative connec
tion database
template0 | omm | UTF8 | C | C | =c/omm +| 12 MB | pg_default | default template for new data
bases
| | | | | omm=CTc/omm | | |
template1 | omm | UTF8 | C | C | =c/omm +| 12 MB | pg_default | unmodifiable empty database
| | | | | omm=CTc/omm | | |
(11 rows)
\db
List of tablespaces
Name | Owner | Location
------------+-------+----------------------
music_tbs | omm | tablespace/test_ts1
music_tbs1 | omm | tablespace/test_ts12
pg_default | omm |
pg_global | omm |
(4 rows)
--也可重新创建
drop DATABASE IF EXISTS musicdb2;
drop tablespace IF EXISTS music_tbs;
CREATE TABLESPACE music_tbs RELATIVE LOCATION 'tablespace/test_ts1';
CREATE DATABASE musicdb2 WITH TABLESPACE = music_tbs;
--以用户user1的身份在数据库musicdb2中创建表products1，并插入一条数据：
omm-# \c musicdb2 user1
Password for user user1:
musicdb2-> Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb2" as user "user1".
/*
CREATE TABLE products1(
product_id integer,
product_name char(20),
category char(20)
) ;
*/
musicdb2=> CREATE TABLE products1(
musicdb2(> product_id integer,
musicdb2(> product_name char(20),
musicdb2(> category char(20)
musicdb2(> ) ;
CREATE TABLE
/*
COMMENT ON COLUMN products1.product_id IS '产品编号';
COMMENT ON COLUMN products1.product_name IS '产品名';
COMMENT ON COLUMN products1.category IS '种类';
*/
musicdb2=> COMMENT ON COLUMN products1.product_id IS '产品编号';
COMMENT
musicdb2=> COMMENT ON COLUMN products1.product_name IS '产品名';
COMMENT
musicdb2=> COMMENT ON COLUMN products1.category IS '种类';
COMMENT
musicdb2-> \d+ products1
Table "public.products1"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+---------------+-----------+----------+--------------+--------------
product_id | integer | | plain | | 产品编号
product_name | character(20) | | extended | | 产品名
category | character(20) | | extended | | 种类
Has OIDs: no
Options: orientation=row, compression=no
musicdb2->
/*
insert into products1 values
(1502,'olympus camera','electrncs'),
(1601,'lamaze','toys'),
(1700,'wait interface','Books'),
(1666,'harry potter','toys');
*/
musicdb2=> insert into products1 values
musicdb2-> (1502,'olympus camera','electrncs'),
musicdb2-> (1601,'lamaze','toys'),
musicdb2-> (1700,'wait interface','Books'),
musicdb2-> (1666,'harry potter','toys');
INSERT 0 4
--查看添加数据后表情况 select * from products1;
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)
--以用户user2的身份在数据库musicdb2中创建表products2，并插入一条数据：
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(20)
) ;
CREATE TABLE
musicdb2=> COMMENT ON COLUMN products2.product_id IS '产品编号';
COMMENT
musicdb2=> COMMENT ON COLUMN products2.product_name IS '产品名';
COMMENT
musicdb2=> COMMENT ON COLUMN products2.category IS '种类';
COMMENT
musicdb2=> \d+ products2
Table "public.products2"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+---------------+-----------+----------+--------------+--------------
product_id | integer | | plain | | 产品编号
product_name | character(20) | | extended | | 产品名
category | character(20) | | extended | | 种类
Has OIDs: no
Options: orientation=row, compression=no
musicdb2=>
musicdb2=> insert into products2 values
musicdb2-> (1502,'olympus camera','electrncs'),
musicdb2-> (1601,'lamaze','toys'),
musicdb2-> (1700,'wait interface','Books'),
musicdb2-> (1666,'harry potter','toys');
INSERT 0 4
--查看添加数据后表情况 select * from products2;
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)
--以用户user3的身份在数据库musicdb2中创建表products3，并插入一条数据：
\c musicdb2 user3
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=> CREATE TABLE products3(
musicdb2(> product_id integer,
musicdb2(> product_name char(20),
musicdb2(> category char(20)
musicdb2(> ) ;
CREATE TABLE
musicdb2=> COMMENT ON COLUMN products3.product_id IS '产品编号';
COMMENT
musicdb2=> COMMENT ON COLUMN products3.product_name IS '产品名';
COMMENT
musicdb2=> COMMENT ON COLUMN products3.category IS '种类';
COMMENT
musicdb2=> \d+ products3
Table "public.products3"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+---------------+-----------+----------+--------------+--------------
product_id | integer | | plain | | 产品编号
product_name | character(20) | | extended | | 产品名
category | character(20) | | extended | | 种类
Has OIDs: no
Options: orientation=row, compression=no
musicdb2=> insert into products3 values
musicdb2-> (1502,'olympus camera','electrncs'),
musicdb2-> (1601,'lamaze','toys'),
musicdb2-> (1700,'wait interface','Books'),
musicdb2-> (1666,'harry potter','toys');
INSERT 0 4
--查看添加数据后表情况select * from products3
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)
3.使用user1、user2、user3用户中的任何一个，查看当前数据库musicdb2有哪些表
--\dt 进行查看：
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)
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
