第5日学习记录，在GAUSS数据库中，用户为全局共享对象；
各数据库为逻辑隔离对象；
各数据库内的相同用户创建的表，具有sysadmin权限的用户去查询，只能看到当前数据库内创建的表对象；跨库查询并不能查询到表信息；
库对象为全局可见，sysadmin权限用户查询\du 所有库信息可见；
查看库信息：\l
查看用户信息：\du
查看表空间：\db
查看对象：\dd
查看schema关系：\dt
查看当前用户登录信息：\c
删除表空间：
drop tablespace IF EXISTS music_tbs;
创建表空间：
CREATE TABLESPACE music_tbs RELATIVE LOCATION ‘tablespace/test_ts1’;
创建数据库：
CREATE DATABASE musicdb WITH TABLESPACE = music_tbs;
创建用户：
CREATE USER user1 IDENTIFIED BY ‘kunpeng@1234’;
赋权:
ALTER USER user2 SYSADMIN;
切换登录数据：
\c musicdb2 user3
创建表：
create table test3(product_id INTEGER,product_name Char(20),category Char(30));
插入数据：
insert into test3 values(1502,‘olympus camera’,‘electrncs’),(1601,‘lamaze’,‘toys’),(1700,‘wait interface’,‘Books’),(1666,‘harry potter’,‘toys’);
操作前环境检查：
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=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
omm | omm | UTF8 | C | C |
postgres | omm | UTF8 | C | C |
omm=# template0 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
(4 rows)
ldt
omm-# \dt
No relations found.
omm-# \db
List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | omm |
pg_global | omm |
(2 rows)
omm-# \du
List of roles
Role name | Attributes
| Member of
-----------+-------------------------------------------------------------------------
-----------------------------------------+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitor
admin, Operatoradmin, Policyadmin, UseFT | {}
创建表空间数据库用户：
omm=# CREATE TABLESPACE music_tbs RELATIVE LOCATION 'tablespace/test_ts1';
CREATE TABLESPACE
omm=# CREATE DATABASE musicdb1 WITH TABLESPACE = music_tbs;
CREATE DATABASE
omm=# CREATE DATABASE musicdb2 WITH TABLESPACE = music_tbs;
CREATE DATABASE
omm=# CREATE DATABASE musicdb3 WITH TABLESPACE = music_tbs;
CREATE DATABASE
omm=# CREATE USER user1 IDENTIFIED BY 'kunpeng@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# ALTER USER user1 SYSADMIN;
ALTER ROLE
验证：
omm=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
musicdb1 | omm | UTF8 | C | C |
musicdb2 | omm | UTF8 | C | C |
musicdb3 | omm | UTF8 | C | C |
omm | omm | UTF8 | C | C |
postgres | omm | UTF8 | C | C |
template0 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
omm=# template1 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
(7 rows)
omm=# \db
List of tablespaces
Name | Owner | Location
------------+-------+---------------------
music_tbs | omm | tablespace/test_ts1
pg_default | omm |
pg_global | omm |
(3 rows)
omm=# \du
List of roles
Role name | Attributes
| Member of
-----------+-------------------------------------------------------------------------
-----------------------------------------+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitor
admin, Operatoradmin, Policyadmin, UseFT | {}
user1 | Sysadmin
| {}
插入表数据：
omm=# \c musicdb1 user1
Password for user user1:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb1" as user "user1".
musicdb1=> create table t11(col1 char(20));
CREATE TABLE
musicdb1=> insert into t11 values('Hello openGauss! 11');
INSERT 0 1
musicdb1=> select * from t11;
col1
----------------------
Hello openGauss! 11
(1 row)
musicdb1=> \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 t21(col1 char(20));
CREATE TABLE
musicdb2=> insert into t21 values('Hello openGauss! 22');
INSERT 0 1
musicdb2=> select * from t21;
col1
----------------------
Hello openGauss! 22
(1 row)
musicdb2=>
musicdb2=> \c musicdb3 user1
Password for user user1:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb3" as user "user1".
musicdb3=> create table t31(col1 char(20));
CREATE TABLE
musicdb3=> insert into t31 values('Hello openGauss! 33');
INSERT 0 1
musicdb3=> select * from t31;
col1
----------------------
Hello openGauss! 33
(1 row)
课程作业：(创建用户密码策略最低为8位字符)
omm=# CREATE DATABASE musicdb10 WITH TABLESPACE = music_tbs;
CREATE DATABASE
omm=# CREATE USER user10 IDENTIFIED BY 'user10';
ERROR: Password must contain at least 8 characters.
omm=# ALTER USER user10 SYSADMIN;
ERROR: role "user10" does not exist
omm=# CREATE USER user10 IDENTIFIED BY 'user10@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# ALTER USER user10 SYSADMIN;
ALTER ROLE
omm=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
musicdb1 | omm | UTF8 | C | C |
musicdb10 | omm | UTF8 | C | C |
musicdb2 | omm | UTF8 | C | C | omm=#
musicdb3 | omm | UTF8 | C | C |
omm | omm | UTF8 | C | C |
postgres | omm | UTF8 | C | C |
template0 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
(8 rows)
omm=# \du
List of roles
Role name | Attributes
| Member of
-----------+-------------------------------------------------------------------------
-----------------------------------------+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitor
admin, Operatoradmin, Policyadmin, UseFT | {}
user1 | Sysadmin
| {}
user10 | Sysadmin
| {}
omm=# \c postgres user10
Password for user user10:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "user10".
openGauss=>
openGauss=> create table p1(col1 char(20));
CREATE TABLE
openGauss=> insert into p1 values('HelloPostgres 1');
INSERT 0 1
openGauss=> select * from p1;
col1
----------------------
HelloPostgres 1
(1 row)
openGauss=> \c omm user10
Password for user user10:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "user10".
omm=> create table o1(col1 char(20));
CREATE TABLE
omm=> insert into o1 values('HelloOmm 1');
INSERT 0 1
omm=> select * from o1;
col1
----------------------
HelloOmm 1
(1 row)
omm=> \c musicdb10 user10
Password for user user10:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb10" as user "user10".
musicdb10=> create table m1(col1 char(20));
CREATE TABLE
musicdb10=> insert into m1 values('Hellomusicdb10 10');
INSERT 0 1
musicdb10=> select * from m1;
col1
----------------------
Hellomusicdb10 10
(1 row)
omm=# \dt
No relations found.
omm=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
musicdb1 | omm | UTF8 | C | C |
musicdb10 | omm | UTF8 | C | C |
musicdb2 | omm | UTF8 | C | C |
(8 rows)
musicdb3 | omm | UTF8 | C | C |
omm | omm | UTF8 | C | C |
postgres | omm | UTF8 | C | C |
template0 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
omm=# \c
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "omm".
omm=#
omm=# \c omm user10
Password for user user10:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "user10".
omm=> \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+------+-------+--------+----------------------------------
user10 | o1 | table | user10 | {orientation=row,compression=no}
(1 row)
omm-> \d
List of relations
Schema | Name | Type | Owner | Storage
--------+------+-------+--------+----------------------------------
user10 | o1 | table | user10 | {orientation=row,compression=no}
(1 row)
omm-> \c musicdb10 user10
Password for user user10:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb10" as user "user10".
musicdb10-> \d
musicdb10-> List of relations
Schema | Name | Type | Owner | Storage
--------+------+-------+--------+----------------------------------
public | m1 | table | user10 | {orientation=row,compression=no}
(1 row)
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
