openGauss的探索：私有用户

原创黄超 2022-07-12

1800

一、前言

在openGauss中，有一个功能：私有用户。
我们看看官方介绍：

对于有多个业务部门，各部门间使用不同的数据库用户进行业务操作，同时有一个同级的数据库维护部门使用数据库管理员进行维护操作的场景下，业务部门可能希望在未经授权的情况下，管理员用户只能对各部门的数据进行控制操作（DROP、ALTER、TRUNCATE），但是不能进行访问操作（INSERT、DELETE、UPDATE、SELECT、COPY）。即针对管理员用户，表对象的控制权和访问权要能够分离，提高普通用户数据安全性。

三权分立情况下，管理员对其他用户放在属于各自模式下的表无权限。但是，这种无权限包含了无控制权限，因此不能满足上面的诉求。为此，openGauss提供了私有用户方案。即在非三权分立模式下，创建具有INDEPENDENT属性的私有用户。具备CREATEROLE权限或者是系统管理员权限的用户可以创建私有用户或者修改普通用户的属性为私有用户，普通用户也可以修改自己的属性为私有用户。

曾经遇到过这样一个场景：一个公司，他的数据库管理员因为维护的需要拥有所有数据库的超级管理员帐户权限，其中包括了公司的所有客户资料表，所有员工的kpi表，所有员工的工资表等，意味着数据库管理员可以浏览到这些关键数据，甚至有可能增删改这些数据，而且数据库管理员有离职跳槽的可能性，公司领导层对这情况很焦虑，希望可以做到针对管理员用户，表对象的控制权和访问权要能够分离。

现在我们来测试一下这个openGauss中的私有用户的功能，看看是否满足它的数据不能被管理员查看和操作，做到表对象的控制权和访问权分离。

二、准备工作

1. 创建私有用户

postgres=# CREATE USER hc  WITH INDEPENDENT IDENTIFIED BY "1234@abc";
WARNING:  Please carefully use independent user as it need more self-management.
HINT:  Self-management include logical backup, password manage and so on.
CREATE ROLE

2.查看用户列表

postgres=# \du
                                                              List of roles
 Role name |                                                    Attributes                                                    | Member of 
-----------+------------------------------------------------------------------------------------------------------------------+-----------
 hc        | Independent                                                                                                      | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}

3.创建数据库

postgres=# create database testdb;
CREATE DATABASE

postgres=# \l
                          List of databases
   Name    | Owner | Encoding  | Collate | Ctype | Access privileges 
-----------+-------+-----------+---------+-------+-------------------
 postgres  | omm   | SQL_ASCII | C       | C     | 
 template0 | omm   | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
 template1 | omm   | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
 testdb    | omm   | SQL_ASCII | C       | C     | 
(4 rows)

4.修改数据库所有者为私有用户

postgres=# alter database testdb owner to hc;
ALTER DATABASE

postgres=# \l
                          List of databases
   Name    | Owner | Encoding  | Collate | Ctype | Access privileges 
-----------+-------+-----------+---------+-------+-------------------
 postgres  | omm   | SQL_ASCII | C       | C     | 
 template0 | omm   | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
 template1 | omm   | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
 testdb    | hc    | SQL_ASCII | C       | C     | 
(4 rows)

三、私有用户管理数据

1.私有用户登录

[omm@prod bin]$ gsql -d testdb -p 26000 -U hc -W"1234@abc"  -r
gsql ((openGauss 2.0.1 build d97c0e8a) compiled at 2021-06-02 19:37:17 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

testdb=> \l
                          List of databases
   Name    | Owner | Encoding  | Collate | Ctype | Access privileges 
-----------+-------+-----------+---------+-------+-------------------
 postgres  |       | SQL_ASCII | C       | C     | 
 template0 |       | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
 template1 |       | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
 testdb    | hc    | SQL_ASCII | C       | C     | 
(4 rows)

2.创建模式

testdb=> create schema testcn;
CREATE SCHEMA

3.创建表对象

testdb=> CREATE TABLE testcn.customer_t
testdb-> (  c_customer_sk             integer,   
testdb(>   c_customer_id             char(5),    
testdb(>   c_first_name              char(6),    
testdb(>   c_last_name               char(8) 
testdb(> ) ;
CREATE TABLE

4.添加测试数据

testdb=> INSERT INTO testcn.customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES    
testdb-> (6885, 1, 'Joes', 'Hunter'),    
testdb-> (4321, 2, 'Lily','Carter'),    
testdb-> (9527, 3, 'James', 'Cook'),
testdb-> (9500, 4, 'Lucy', 'Baker');
INSERT 0 4

5.查询数据

testdb=> select * from testcn.customer_t;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          6885 | 1             | Joes         | Hunter  
          4321 | 2             | Lily         | Carter  
          9527 | 3             | James        | Cook    
          9500 | 4             | Lucy         | Baker   
(4 rows)

6.更新数据

testdb=> update testcn.customer_t set c_last_name='Henry' where c_customer_sk=9527;
UPDATE 1
testdb=> select * from testcn.customer_t;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          6885 | 1             | Joes         | Hunter  
          4321 | 2             | Lily         | Carter  
          9500 | 4             | Lucy         | Baker   
          9527 | 3             | James        | Henry   
(4 rows)

四、管理员管理私有用户数据

1.管理员登录数据库

[omm@prod bin]$ gsql -d postgres -p 26000 -r
gsql ((openGauss 2.0.1 build d97c0e8a) compiled at 2021-06-02 19:37:17 commit 0 last mr  )
NOTICE : The password has been expired, please change the password. 
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=# \c testdb;
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb" as user "omm".

2.查看用户信息

testdb=# \du
                                                              List of roles
 Role name |                                                    Attributes                                                    | Member of 
-----------+------------------------------------------------------------------------------------------------------------------+-----------
 hc        | Independent                                                                                                      | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}

3.查看当前所有模式

testdb=# \dn
   List of schemas
    Name     | Owner 
-------------+-------
 cstore      | omm
 dbe_perf    | omm
 pkg_service | omm
 public      | omm
 snapshot    | omm
 testcn      | hc
(6 rows)

4.查看私有用户在模式下的表对象

testdb=# SELECT distinct(tablename),schemaname from pg_tables where schemaname = 'testcn';
 tablename  | schemaname 
------------+------------
 customer_t | testcn
(1 row)

5.查看私有用户的表对象详细信息

testdb=# \d+ testcn.customer_t;
                            Table "testcn.customer_t"
    Column     |     Type     | Modifiers | Storage  | Stats target | Description 
---------------+--------------+-----------+----------+--------------+-------------
 c_customer_sk | integer      |           | plain    |              | 
 c_customer_id | character(5) |           | extended |              | 
 c_first_name  | character(6) |           | extended |              | 
 c_last_name   | character(8) |           | extended |              | 
Has OIDs: no
Options: orientation=row, compression=no

6.查看私有用户表数据

testdb=# select * from testcn.customer_t;
ERROR:  permission denied for relation customer_t

7.更新私有用户表数据

testdb=# update testcn.customer_t set c_first_name='all';
ERROR:  permission denied for relation customer_t

8. 删除私有用户表数据

testdb=# delete from testcn.customer_t;
ERROR:  permission denied for relation customer_t

9.插入私有用户表新数据

testdb=# insert into testcn.customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, 'Grace','White');
ERROR:  permission denied for relation customer_t

10.copy私有用户表新数据

testdb=# copy testcn.customer_t to '/home/omm/customer_t.dat';
ERROR:  permission denied for relation customer_t

11.清空私有用户的表数据

testdb=# truncate table testcn.customer_t;
TRUNCATE TABLE

12.修改私有用户的表的模式

testdb=# alter table testcn.customer_t  set schema public;
ALTER TABLE

13.删除私有用户的表

testdb=# drop table customer_t;
DROP TABLE

五、管理员被授权后

1.私有用户登录

[omm@prod bin]$ gsql -d testdb -p 26000 -U hc -W"1234@abc"  -r
gsql ((openGauss 2.0.1 build d97c0e8a) compiled at 2021-06-02 19:37:17 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

2.创建表对象

testdb=> CREATE TABLE testcn.customer_t
testdb-> (  c_customer_sk             integer,   
testdb(>   c_customer_id             char(5),    
testdb(>   c_first_name              char(6),    
testdb(>   c_last_name               char(8) 
testdb(> ) ;
CREATE TABLE

3.添加测试数据

testdb=> INSERT INTO testcn.customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES    
testdb-> (6885, 1, 'Joes', 'Hunter'),    
testdb-> (4321, 2, 'Lily','Carter'),    
testdb-> (9527, 3, 'James', 'Cook'),
testdb-> (9500, 4, 'Lucy', 'Baker');
INSERT 0 4

4.查询数据

testdb=> select * from testcn.customer_t;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          6885 | 1             | Joes         | Hunter  
          4321 | 2             | Lily         | Carter  
          9527 | 3             | James        | Cook    
          9500 | 4             | Lucy         | Baker   
(4 rows)

5.给管理员omm授权

testdb=> GRANT ALL PRIVILEGES ON testcn.customer_t TO omm;
GRANT

6.管理员omm登录

[omm@prod ~]$ gsql -d testdb -p 26000 -r
gsql ((openGauss 2.0.1 build d97c0e8a) compiled at 2021-06-02 19:37:17 commit 0 last mr  )
NOTICE : The password has been expired, please change the password. 
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

7.查询私有用户表数据

testdb=# select * from testcn.customer_t;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          6885 | 1             | Joes         | Hunter  
          4321 | 2             | Lily         | Carter  
          9527 | 3             | James        | Cook    
          9500 | 4             | Lucy         | Baker   
(4 rows)

8.更新私有用户表数据

testdb=# update testcn.customer_t set c_last_name='Mary' where c_customer_sk=9500;
UPDATE 1

testdb=# select * from testcn.customer_t;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          6885 | 1             | Joes         | Hunter  
          4321 | 2             | Lily         | Carter  
          9527 | 3             | James        | Cook    
          9500 | 4             | Lucy         | Mary    
(4 rows)

六、总结

1.私有用户对自己的表对象有所有权操作。
2.针对私有用户的表对象，系统管理员在未经其授权前，只能进行控制操作（DROP、ALTER、TRUNCATE），无权进行INSERT、DELETE、SELECT、UPDATE、COPY、GRANT、REVOKE、ALTER OWNER操作，做到表对象的控制权和访问权分离。
3.管理员在得到私有用户授权后，根据所授权限可以对表对象进行对应访问操作。

这功能很实用！

openGauss 加油！国产数据库雄起！

墨力计划国产数据库 opengauss

最后修改时间：2022-07-27 10:41:36

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

openGauss专栏（共25篇）

openGauss专栏

openGauss的探索：私有用户

一、 前言

二、准备工作

1. 创建私有用户

2.查看用户列表

3.创建数据库

4.修改数据库所有者为私有用户

三、私有用户管理数据

1.私有用户登录

2.创建模式

3.创建表对象

4.添加测试数据

5.查询数据

6.更新数据

四、管理员管理私有用户数据

1.管理员登录数据库

2.查看用户信息

3.查看当前所有模式

4.查看私有用户在模式下的表对象

5.查看私有用户的表对象详细信息

6.查看私有用户表数据

7.更新私有用户表数据

8. 删除私有用户表数据

9.插入私有用户表新数据

10.copy私有用户表新数据

11.清空私有用户的表数据

12.修改私有用户的表的模式

13.删除私有用户的表

五、管理员被授权后

1.私有用户登录

2.创建表对象

3.添加测试数据

4.查询数据

5.给管理员omm授权

6.管理员omm登录

7.查询私有用户表数据

8.更新私有用户表数据

六、总结

文章被以下合辑收录

评论

一、前言