openGauss每日一练第4天

今日目标：openGauss体系结构——使用多个用户访问同一个数据库

1.创建用户

CREATE USER user_name [ [ WITH ] option [ … ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { ‘password’ [EXPIRED] | DISABLE };

option子句用于设置权限及属性等信息。

{SYSADMIN | NOSYSADMIN}
    | {MONADMIN | NOMONADMIN}
    | {OPRADMIN | NOOPRADMIN}
    | {POLADMIN | NOPOLADMIN}
    | {AUDITADMIN | NOAUDITADMIN}
    | {CREATEDB | NOCREATEDB}
    | {USEFT | NOUSEFT}
    | {CREATEROLE | NOCREATEROLE}
    | {INHERIT | NOINHERIT}
    | {LOGIN | NOLOGIN}
    | {REPLICATION | NOREPLICATION}
    | {INDEPENDENT | NOINDEPENDENT}
    | {VCADMIN | NOVCADMIN}
    | {PERSISTENCE | PERSISTENCE}
    | CONNECTION LIMIT connlimit
    | VALID BEGIN 'timestamp'
    | VALID UNTIL 'timestamp'
    | RESOURCE POOL 'respool'
    | USER GROUP 'groupuser'
    | PERM SPACE 'spacelimit'
    | TEMP SPACE 'tmpspacelimit'
    | SPILL SPACE 'spillspacelimit'
    | NODE GROUP logic_cluster_name
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid
    | DEFAULT TABLESPACE tablespace_name
    | PROFILE DEFAULT
    | PROFILE profile_name
    | PGUSER

2.课后作业

2.1 创建用户user1、user2、user3，授予user1、user2、user3数据库系统的SYSADMIN权限

两种方式
方式1 先创建用户，在执行授权语句
create user identified by ‘‘；
alter user SYSADDMIN；
方式2 创建用户的同时，授予SYSADMIN权限
create user SYSADMIN identified by ‘’;

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=# create user user2 SYSADMIN identified by 'kunpeng@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create user user3 SYSADMIN identified by 'kunpeng@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# 

查看有哪些用户

\du //查看用户信息

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.2分别使用user1、user2、user3访问数据库musicdb2，创建各自的表，并插入数据

omm=# \c musicdb user1 
Password for user user1: 
musicdb=> Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "user1".
musicdb=> 
musicdb=> create table products1(
musicdb(> product_id integer,
musicdb(> product_name char(50),
musicdb(> category char(30)
musicdb(> );
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=> \dt
                           List of relations
 Schema |   Name    | Type  | Owner |             Storage              
--------+-----------+-------+-------+----------------------------------
 public | products1 | table | user1 | {orientation=row,compression=no}
(1 row)

musicdb=> create table products2
musicdb-> (product_id integer,
musicdb(> product_name char(20),
musicdb(> category char(30)
musicdb(> );
CREATE TABLE
musicdb=> 
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 products3(
musicdb(> product_id integer,
musicdb(> product_name char(30),
musicdb(> category char(30)
musicdb(> );
CREATE TABLE
musicdb=> \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)

musicdb=>

查看库中有哪些表

\dt

musicdb=> \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)

musicdb=> 

使用user3，往表中插入数据

musicdb=> insert into products3 values(1502,'olympus camera','electrncs');
INSERT 0 1
musicdb=> insert into products2^Clues(1502,'olympus camera','electrncs');2 values(1502,'olympus camera','electrncs');
musicdb=> insert into products3 values(150,'olympus camera','electrncs'),'olympus camera','electrncs');^C
musicdb=> insert into products2 values(1601,'lamaze','toys');
INSERT 0 1
musicdb=> \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)

musicdb=> select * from products3;
       1502 | olympus camera                 | electrncs                     
(1 row)

musicdb=>  product_id |          product_name          |            category            
------------+--------------------------------+--------------------------------
^C
musicdb=> select * from products2;
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1601 | lamaze               | toys                          
(1 row)

musicdb=>