[openGauss 学废系列]- openGauss体系结构-多个用户访问同一个数据库
一、学习目的本节课是本次实训的第四次训练课，本节课的学习学习目的仍然是熟悉openGauss体系结构，能利用多个用户访问同一个数据库。
重点是：
1、怎样删除\创建数据库
2、怎样删除\创建表空间
3、怎样创建用户
4、怎样为用户授权
5、怎样多个用户访问同一个数据库
二、测试环境准备
2.1 删除已有数据库及表空间
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=# drop DATABASEIF EXISTSmusicdb;
NOTICE:database "musicdb" does not exist, skipping
DROP DATABASE
omm=# drop DATABASEIF EXISTSmusicdb1;
NOTICE:database "musicdb1" does not exist, skipping
DROP DATABASE
omm=# drop DATABASEIF EXISTSmusicdb2;
NOTICE:database "musicdb2" does not exist, skipping
DROP DATABASE
omm=# drop DATABASEIF EXISTSmusicdb3;
NOTICE:database "musicdb3" does not exist, skipping
DROP DATABASE
omm=# drop tablespace IF EXISTS music_tbs;
DROP TABLESPACE
https://i-blog.csdnimg.cn/img_convert/136cad8707611d33fa3a0b0d39bbdc2e.png
2.2 创建数据库及表空间
omm=# CREATE TABLESPACE music_tbs RELATIVE LOCATION 'tablespace/test_ts1';
CREATE TABLESPACE
omm=# CREATE DATABASE musicdbWITH TABLESPACE = music_tbs;
CREATE DATABASE
https://i-blog.csdnimg.cn/img_convert/aff013028418da55ef222cab50938252.png
2.3 创建用户user1、user2、user3
omm=# CREATE USER user1 IDENTIFIED BY 'kunpeng@1234';
NOTICE:The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# CREATE USER user2 IDENTIFIED BY 'kunpeng@1234';
NOTICE:The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# CREATE USER user3 IDENTIFIED BY 'kunpeng@1234';
NOTICE:The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
https://i-blog.csdnimg.cn/img_convert/7e047d3e424f855ca614d59dfe6bff22.png
2.4 授予user1、user2、user3数据库系统SYSADMIN权限
omm=# ALTER USER user1 SYSADMIN;
omm=# ALTER ROLE
omm=# ALTER USER user2 SYSADMIN;
ALTER ROLE
omm=# ALTER USER user3 SYSADMIN;
omm=# 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
| {}
user2 | Sysadmin
| {}
user3 | Sysadmin
| {}
user1 | Sysadmin
| {}
https://i-blog.csdnimg.cn/img_convert/01d2bf26e0861b1d830840e8ce378df8.png
https://i-blog.csdnimg.cn/img_convert/18150abd66727f8f85fcc6d317132a93.png
2.5 利用user1、user2、user3访问musicdb
--以用户user1的身份在数据库musicdb中创建表t1，并插入一条数据：
omm=# \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)
--以用户user2的身份在数据库musicdb中创建表t2，并插入一条数据：
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 t2(col1 char(20));
CREATE TABLE
musicdb=> insert into t2 values('Hello kunpeng 2');
musicdb=> INSERT 0 1
musicdb=> select * from t2;
col1
----------------------
Hello kunpeng 2
(1 row)
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 t3(col1 char(20));
CREATE TABLE
musicdb=> insert into t3 values('Hello kunpeng 3');
INSERT 0 1
musicdb=> select * from t2;
col1
----------------------
Hello kunpeng 2
(1 row)
https://i-blog.csdnimg.cn/img_convert/84b02709c35f0cf813638871702ae349.png
https://i-blog.csdnimg.cn/img_convert/3447e6b5875b4927a8def2cac713a185.png
2.6 利用user1用户查看当前数据库musicdb有哪些表
musicdb=> \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=> \dt
List of relations
Schema | Name | Type| Owner | Storage
--------+------+-------+-------+----------------------------------
public | t1 | table | user1 | {orientation=row,compression=no}
public | t2 | table | user2 | {orientation=row,compression=no}
public | t3 | table | user3 | {orientation=row,compression=no}
(3 rows)
https://i-blog.csdnimg.cn/img_convert/c82ba6689a15be0da34ea9385a7e54bc.png
三、课后作业
3.1 创建数据库musicdb2
omm=# CREATE DATABASE musicdb2WITH TABLESPACE = music_tbs;
CREATE DATABASE
https://i-blog.csdnimg.cn/img_convert/219f0cb43b04a41fac22a202d9953733.png
3.2 创建用户user11、user22、user33
omm=# CREATE USER user11 IDENTIFIED BY 'kunpeng@1234';
NOTICE:The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# CREATE USER user22 IDENTIFIED BY 'kunpeng@1234';
NOTICE:The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# CREATE USER user33 IDENTIFIED BY 'kunpeng@1234';
NOTICE:The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
3.3 授予user11、user22、user33用户SYSADMIN权限
omm=# ALTER USER user11 SYSADMIN;
omm=# ALTER ROLE
omm=# ALTER USER user22 SYSADMIN;
ALTER ROLE
omm=#
omm=# ALTER USER user33 SYSADMIN;
ALTER ROLE
https://i-blog.csdnimg.cn/img_convert/fea6391deccc414e5eaea485585b9d98.png
3.4 分别在user11、user22、user33用户下创建对应products表并插入数据
omm=# \c musicdb user11
Password for user user11:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "user11".
musicdb=> create table products1(product_id INT,product_name char(20),category char(30));
musicdb=> CREATE TABLE
musicdb=> insert into products1 values (1502,'olympus camera','category');
INSERT 0 1
musicdb=> insert into products1 values (1601,'lamaze','toys');
INSERT 0 1
musicdb=> insert into products1 values (1700,'wait interface','Books');
musicdb=> 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 | category
1601 | lamaze | toys
1700 | wait interface | Books
1666 | harry potter | toys
(4 rows)
https://i-blog.csdnimg.cn/img_convert/faabd0959e3eff1373db15e85ba9ced9.png
musicdb=> \c musicdb user22
Password for user user22:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "user22".
musicdb=> create table products1(product_id INT,product_name char(20),category char(30));
ERROR:relation "products1" already exists in schema "public"
DETAIL:creating new table with existing name in the same schema
musicdb=> create table products2(product_id INT,product_name char(20),category char(30));
CREATE TABLE
musicdb=> insert into products2 values (1502,'olympus camera','category');
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 | category
1601 | lamaze | toys
1700 | wait interface | Books
1666 | harry potter | toys
(4 rows)
https://i-blog.csdnimg.cn/img_convert/36ff832df1c66092483b17f6d4d861c7.png
musicdb=> \c musicdb user33
Password for user user33:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "user33".
musicdb=> create table products3(product_id INT,product_name char(20),category char(30));
CREATE TABLE
musicdb=> insert into products3 values (1502,'olympus camera','category');
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');
musicdb=> INSERT 0 1
musicdb=> select * from products3;
product_id | product_name | category
------------+----------------------+--------------------------------
1502 | olympus camera | category
1601 | lamaze | toys
1700 | wait interface | Books
1666 | harry potter | toys
(4 rows)
https://i-blog.csdnimg.cn/img_convert/3034a9a9d182ef8aff321737f93e0c41.png
3.4 利用user11查看musicdb有哪些表
musicdb=> \c musicdb user11
Password for user user11:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "user11".
musicdb=> \dt
List of relations
Schema | Name | Type| Owner| Storage
--------+-----------+-------+--------+----------------------------------
public | products1 | table | user11 | {orientation=row,compression=no}
public | products2 | table | user22 | {orientation=row,compression=no}
public | products3 | table | user33 | {orientation=row,compression=no}
public | t1 | table | user1| {orientation=row,compression=no}
public | t2 | table | user2| {orientation=row,compression=no}
public | t3 | table | user3| {orientation=row,compression=no}
(6 rows)
https://i-blog.csdnimg.cn/img_convert/5f7a7345964a6e2cec17b7423d995dc3.png
四、心得体会
通过本节课的学习，加深了对openGauss体系架构的明白，在学习过程中要反复举行测试，通过大量实行来总结学到的知识。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]