openGauss 每日一练第6天

准备

• 切换用户，连接数据库

  su - omm
  gsql -r
  

• 创建表空间和测试数据库

  CREATE TABLESPACE music_tbs RELATIVE LOCATION 'tablespace/test_ts1';
  CREATE DATABASE musicdb  WITH TABLESPACE = music_tbs;
  CREATE DATABASE musicdb1  WITH TABLESPACE = music_tbs;
  CREATE DATABASE musicdb2  WITH TABLESPACE = music_tbs;
  

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

• 创建用户user1 ，给与sysadmin权限

  #创建用户
  omm=# CREATE USER user1 IDENTIFIED BY 'abcd@1234';
  
  NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
  CREATE ROLE
  # 给用户user1 赋予 sysadmin权限
  omm=# alter user user1 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                                                                                                         | {}
  
  

• 在数据库 musicdb1、musicdb2中分别创建表t11,t12

  # 使用user1 用户连接到  musicdb1 
  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=> \conninfo
  You are connected to database "musicdb1" as user "user1" via socket in "/tmp" at port "5432".
  
  #创建表t11，并插入一条数据
  musicdb1=>  create table t11(col1 char(20));
  musicdb1=> CREATE TABLE
  
  musicdb1=>  insert into t11 values('Hello openGauss! 11');
  musicdb1=> INSERT 0 1
  
  -------------------------------------
  #切换到 musicdb2 数据库
  musicdb1=>  \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".
  # 查看当前连接
  musicdb2=> \conninfo
  You are connected to database "musicdb2" as user "user1" via socket in "/tmp" at port "5432".
  #创建表t21，并插入一条数据
  musicdb2=>  create table t21(col1 char(20));
  CREATE TABLE 
  musicdb2=>  insert into t21 values('Hello openGauss! 22');
  INSERT 0 1
  

访问测试，对比从musicdb1库中访问 muiscdb2 中的t21 表和从musicdb2库中访问 muiscdb2 中的t21 表 的执行结果

# 使用user1 连接 musicdb1库
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".

#访问 musicdb2 库中的表，可以看到不允许跨库访问
musicdb1=>  select * from musicdb2.public.t21;
ERROR:  cross-database references are not implemented: "musicdb2.public.t21"
LINE 1: select * from musicdb2.public.t21;

#切换到 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".
#查询t21库中的数据
musicdb2=>  select * from musicdb2.public.t21;
         col1         
----------------------
 Hello openGauss! 22 
(1 row)