openGauss每日一练第 19 天 |用户和角色管理

原创 newdata 2022-12-12

361

openGauss每日一练第19天

今日目标：openGauss的用户和角色管理。

使用create user创建的用户与使用create role创建的用户的区别在于，前者可以直接连接登录数据库，而使用create role创建的用户不能直接登录到数据库。必须添加LOGIN权限后，才能登录到数据库管理系统。

删除用户，首先需要将用户拥有的数据库对象转移或者删除。

查看用户

\du

删除用户

drop user <user_name>

删除用户，同时会删除同名的schema。

omm=# \dn
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
(10 rows)

omm=# create user test identified by 'gauss@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# \dn
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
 test            | test
(11 rows)

omm=# drop user test ;
DROP ROLE
omm=# \dn
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
(10 rows)

omm=# 
复制

REASSIGN OWNED

修改数据库对象的属主。REASSIGN OWNED要求系统将所有old_roles拥有的数据库对象的属主更改为new_role

REASSIGN OWNED BY old_role [, …] TO new_role;

REASSIGN OWNED常用于在删除角色之前的准备工作。
执行REASSIGN OWNED需要有原角色和目标角色上的权限。

REVOKE

REVOKE用于撤销一个或多个角色的权限。

2.课后作业

2.1 创建test10_tbs的表空间，在这个表空间中创建数据库testdb10

omm=# create tablespace test10_tbs relative location 'tablesapce/test10_tbs';
CREATE TABLESPACE
omm=# create database testdb10;
CREATE DATABASE
omm=# \l
                         List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+-------+----------+---------+-------+-------------------
 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
 testdb    | user1 | UTF8     | C       | C     | =Tc/user1        +
           |       |          |         |       | user1=CTc/user1  +
           |       |          |         |       | user1=APm/user1
 testdb10  | omm   | UTF8     | C       | C     | 
(6 rows)

omm=# \db
            List of tablespaces
    Name    | Owner |       Location        
omm=# ------------+-------+-----------------------
 pg_default | omm   | 
 pg_global  | omm   | 
 test10_tbs | omm   | tablesapce/test10_tbs
 test_tbs   | user1 | tablespace/test_tbs1
 test_tbs2  | user1 | tablespace/test_tbs2
(5 rows)
复制

2.2 使用create user创建用户user10，登录数据库testdb10，创建测试表t1和t2

omm=# create user user10 identified by 'gauss@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# \c testdb10 user10
Password for user user10: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb10" as user "user10".
testdb10=> create table t1(col int);
ERROR:  permission denied for schema public
DETAIL:  N/A
testdb10=> \q
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=# grant all on DATABASE testdb10 TO user10 ;
GRANT
omm=# \c testdb10 user10 
Password for user user10: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb10" as user "user10".
testdb10=> create table t1(col int);
ERROR:  permission denied for schema public
DETAIL:  N/A
testdb10=> \q
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=# grant all privileges to user10;
ALTER ROLE
omm=# \c testdb10 user10
Password for user user10: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb10" as user "user10".
testdb10=> create table t1(col int);
CREATE TABLE
testdb10=> create table t2(col int);
CREATE TABLE
复制

2.3 使用create role创建角色role10，登录数据库testdb10

使用create role创建的用户不能直接登录到数据库。必须添加LOGIN权限后，才能登录到数据库管理系统。

testdb10=> create role role10 identified by 'gauss@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
testdb10=> \du
 gaussdb   | Sysadmin                                                                                                         
| {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT 
| {}
 role10    | Cannot login                                                                                                     
| {}
 user10    | Sysadmin                                                                                                         
| {}

                                                              List of roles
 Role name |                                                    Attributes                                                    
| Member of 
-----------+------------------------------------------------------------------------------------------------------------------
+-----------
testdb10=> alter user role10 login ;
ALTER ROLE
testdb10=> \du
                                                              List of roles
 Role name |                                                    Attributes                                                    
| Member of 
-----------+------------------------------------------------------------------------------------------------------------------
+-----------
 gaussdb   | Sysadmin                                                                                                         
| {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT 
| {}
 role10    |                                                                                                                  
| {}
 user10    | Sysadmin                                                                                                         
| {}

testdb10=> \q
omm@modb:~$ gsql -d testdb10 -U role10 -W gauss@1234 -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.

testdb10=> 

复制

2.4.将表t1直接删除，将前面创建的表空间和数据库、表t2转给role10，删除用户user10

-- 删除用户，首先需要将用户拥有的数据库对象转移或者删除。
omm=# drop user  user10 ;
ERROR:  role "user10" cannot be dropped because some objects depend on it
DETAIL:  privileges for database testdb10
2 objects in database testdb10

omm=# \c testdb10 user10
Password for user user10: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb10" as user "user10".
testdb10=> drop table t1 ;
DROP TABLE
-- 将user10拥有的数据库对象转移
testdb10=> reassign owned by user10 to role10 ;
REASSIGN OWNED
testdb10=> drop user user10 ;
ERROR:  current user cannot be dropped
testdb10=> \testdb10 ommtestdb10=> 
Invalid command \testdb10. Try \? for help.

testdb10=> \q
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 user user10 ;
ERROR:  role "user10" cannot be dropped because some objects depend on it
DETAIL:  privileges for database testdb10
omm=# revoke all on DATABASE testdb10 from user10;
REVOKE
omm=# drop user user10 ;
DROP ROLE
omm=# 
复制

2.5.最后删除role10

omm=# \c testdb10 role10 
Password for user role10: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb10" as user "role10".
testdb10=> \dt
                         List of relations
 Schema | Name | Type  | Owner  |             Storage              
--------+------+-------+--------+----------------------------------
 public | t2   | table | role10 | {orientation=row,compression=no}
(1 row)

testdb10=> \db
 pg_global  |       | 
 test10_tbs |       | 
(3 rows)

testdb10=>       List of tablespaces
    Name    | Owner | Location 
------------+-------+----------
 pg_default |       | 

testdb10=> drop table t2 ;
DROP TABLE
testdb10=> revoke all on DATABASE testdb10 from role10;
WARNING:  no privileges could be revoked for "testdb10"
REVOKE
testdb10=> drop user role10;
ERROR:  Permission denied to drop role.
testdb10=> \q
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 user  role10 ;
DROP 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 
| {}

omm=# 
复制