openGauss每日一练第 6 天| 学习心得体会

学习心得

本节课我选择先做题，然后再看学习内容，所以作业中有一些报错，为了做个记录，索性也把报错没有删掉，直接放在了代码块中，以后翻起来，也许也能提醒自己，本节课主要内容是让用户了解，一次只能连一个数据库，并且通常没有办法访问其他数据库的对象，使用\dt也只能看到自己的表。另外，通过错误也可以看到，用户不赋权，什么也做不了，且要切到omm用户才能赋权。

课后作业

1.创建用户user1，分别在数据库musicdb1、数据库musicdb2创建表t11,t21

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 user user1 identified by user1;
ERROR:  Password must be quoted
omm=# create user user1 identified by 'user1';
omm=# ERROR:  Password must contain at least 8 characters.

omm=# create user user1 identified by 'test@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
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(i int);
ERROR:  permission denied for schema public
DETAIL:  N/A

musicdb1=> create table t11(col1 char(20));
musicdb1=> ERROR:  permission denied for schema public
DETAIL:  N/A

musicdb1=> 
musicdb1=> alter user user1 sysadmin;
ERROR:  Permission denied.
musicdb1=> \c omm
Password for user user1: 
FATAL:  Invalid username/password,login denied.
Previous connection kept
musicdb1=> \c omm
Password for user user1: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "user1".
omm=> alter user user1 sysadmin;
ERROR:  Permission denied.
omm=> \c omm omm
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "omm".
omm=# alter user user1 sysadmin;
ALTER ROLE
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(i int);
CREATE TABLE
musicdb1=> insert into t11 values (1);
INSERT 0 1
musicdb1=> select * from t11;
 i 
---
 1
(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 (i int);
CREATE TABLE
musicdb2=> insert into t21 values(2);
musicdb2=> INSERT 0 1

musicdb2=> select * from t21;
 i 
---
 2
(1 row)

musicdb2=> \dt
musicdb2=>                         List of relations
 Schema | Name | Type  | Owner |             Storage              
--------+------+-------+-------+----------------------------------
 public | t21  | table | user1 | {orientation=row,compression=no}
(1 row)

2.user1用户连接到数据库musicdb1，并访问数据库musicdb1下的表t11

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=> select * from t11;
 i 
---
 1
(1 row)

3.在这个连接中（使用数据库用户user1连接到数据库musicdb1）访问数据库musicdb2下的t21表(会报错)

musicdb2=> \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=> select * from t21;
ERROR:  relation "t21" does not exist on gaussdb
LINE 1: select * from t21;
                      ^
musicdb1=> \dt
musicdb1=>                         List of relations
 Schema | Name | Type  | Owner |             Storage              
--------+------+-------+-------+----------------------------------
 public | t11  | table | user1 | {orientation=row,compression=no}
(1 row)

musicdb1=> select * from musicdb2.public.t21;
ERROR:  cross-database references are not implemented: "musicdb2.public.t21"
LINE 1: select * from musicdb2.public.t21;
                      ^
musicdb1=> 

4.使用数据库用户user1连接到数据库musicdb2，访问数据库musicdb2下的t21表。

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=> select * from t21;
 i 
---
 2
(1 row)