- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

GaussDB(DWS)实践系列-业务表拆分显示策略分享

举报

四叶草发表于 2020/10/22 22:22:11 2020/10/22

【摘要】 XXX局点的数据库为全省集中部署，涉及到20000+业务数据表，且业务数据表全部放在一个schema下。为应对审计部门的核查，每季度需要将全省集中的生产数据库拷贝到汇总库中，再按照省市县约100+区划进行分离，实现A区划用户只有权限查看其本区划内的数据，其余区划信息均不可见，并且能够实现A区划数据的分离提取。

GaussDB(DWS)-业务表拆分显示策略分享

一、摘要

XXX局点的数据库为全省集中部署，涉及到20000+业务数据表，且业务数据表全部放在一个schema下。为应对审计部门的核查，每季度需要将全省集中的生产数据库拷贝到汇总库中，再按照省市县约100+区划进行分离，实现A区划用户只有权限查看其本区划内的数据，其余区划信息均不可见，并且能够实现A区划数据的分离提取。

二、解决方案

(一) 需求分析

客户需求大致如下图所示，业务表table1保存全省数据，现有区划A用户和区划B两个用户，为实现按区划显示和拆分，需要做到区划A用户仅能看到A区划的数据，例row1、row3、row5,而B区划数据对他不可见，并且能够实现A区划数据的分离提取。

(二) 解决方案

通过对客户业务表进行分析总结大致分为两类，一类具有明确的区划属性（含有province字段），一类不含区划属性，属于全省公共业务表。具有区划属性的业务表需要实现按区划显示和拆分，没有区划属性的业务表保存全省公共数据，对于每个区划均可见。

步骤一：业务表分类

业务数据表全部放在同一个schema下，例如‘schema_hxxt’,首先需要能够区分出哪些业务表具有区划属性，哪些业务表没有区划属性，可通过如下方式进行筛选。

（1）具有区划属性的业务表筛选方式。

SELECT

n.nspname AS schemaname,

c.relname AS tablename,

pg_catalog.pg_get_userbyid(c.relowner) as "Owner"

FROM pgxc_class x

LEFT join pg_class c on c.oid = x.pcrelid

LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

WHERE n.nspname ='schema_hxxt'

AND c.oid IN (SELECT attrelid FROM pg_attribute WHERE attname = 'province');

（2）没有区划属性的业务表筛选方式。

SELECT

n.nspname AS schemaname,

c.relname AS tablename,

pg_catalog.pg_get_userbyid(c.relowner) as "Owner"

FROM pgxc_class x

LEFT join pg_class c on c.oid = x.pcrelid

LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

WHERE n.nspname ='schema_hxxt'

AND c.oid NOT IN (SELECT attrelid FROM pg_attribute WHERE attname = 'province');

步骤二：用户访问控制

客户的需求核心是能够在不修改业务表结构的情况下，实现用户访问控制，考虑使用视图view来实现。各区划用户以“us+区划编码”命名,例如A区划的编码province为610000，A区划用户名设置为 “us610000”。视图view以“v_tablename”命名，例如业务表名为table1,对应视图命名为“v_table1”。如下表所示。

区划名	区划编码（province字段值）	区划用户名	业务表名	视图命名
A区划	610000	us610000	table1	v_table1
			table2	v_table2
			table3	v_table3
B区划	710001	us710001	table4	v_table4
			table5	v_table5
			table6	v_table6

（1）针对具有区划属性的业务表

由于该类业务表中具有区划字段province，并且每个区划有全局唯一的区划编码，例如A区划的区划编码为610000，B区划的区划编码为710001。视图定义中通过将区划数据和登录的区划用户关联的方式，进行权限控制。

视图定义设计如下:

create view v_table1 as

select * from table1 where province=substr(current_user,3);

（2）针对没有区划属性的业务表

由于该类业务表中没有区划字段province，各个区划用户均可访问全量的业务表数据，不需要按照区划进行访问控制。

视图定义设计如下:

create view v_table2 as select * from table2;

构造最小用例测试验证：

--创建测试的业务用户 province_test

CREATE USER province_test PASSWORD 'Bigdata123@';

--创建测试的数据库 db_test，属主为 province_test

CREATE DATABASE db_test OWNER province_test ENCODING 'GBK' template = template0;

--创建区划用户，A区划us610000，B区划us710001

create user us610000 identified by 'Bigdata123@';

create user us710001 identified by 'Bigdata123@';

--创建含有province字段的业务表

create table table1(province varchar(30),id int);

create table table2(province varchar(30),id int);

create table table3(province varchar(30),id int);

--创建不含province字段的业务表

create table table4(codename varchar(30),id int);

create table table5(codename varchar(30),id int);

create table table6(codename varchar(30),id int);

--创建含有province字段业务表对应的用户访问控制视图

create view v_table1 as select * from table1 where province=substr(current_user,3);

create view v_table2 as select * from table2 where province=substr(current_user,3);

create view v_table3 as select * from table3 where province=substr(current_user,3);

--创建不含province字段业务表对应的查询视图

create view v_table4 as select * from table4;

create view v_table5 as select * from table5;

create view v_table6 as select * from table6;

--插入业务数据

insert into table1 values('610000',110);

insert into table1 values('610000',111);

insert into table1 values('610000',112);

insert into table1 values('710001',113);

insert into table1 values('710001',114);

insert into table1 values('710001',115);

insert into table1 values('710001',116);

insert into table2 values('610000',210);

insert into table2 values('610000',211);

insert into table2 values('610000',212);

insert into table2 values('710001',213);

insert into table2 values('710001',214);

insert into table2 values('710001',215);

insert into table2 values('710001',216);

insert into table3 values('610000',310);

insert into table3 values('610000',311);

insert into table3 values('610000',312);

insert into table3 values('710001',313);

insert into table3 values('710001',314);

insert into table3 values('710001',315);

insert into table3 values('710001',316);

insert into table4 values('n610000',1110);

insert into table4 values('n610000',1111);

insert into table4 values('n610000',1112);

insert into table4 values('n710001',1113);

insert into table4 values('n710001',1114);

insert into table4 values('n710001',1115);

insert into table4 values('n710001',1116);

insert into table4 values('n810002',1117);

insert into table4 values('n810002',1118);

insert into table4 values('n810002',1119);

insert into table5 values('n610000',2210);

insert into table5 values('n610000',2211);

insert into table5 values('n610000',2212);

insert into table5 values('n710001',2213);

insert into table5 values('n710001',2214);

insert into table5 values('n710001',2215);

insert into table5 values('n710001',2216);

insert into table5 values('n810002',2217);

insert into table5 values('n810002',2218);

insert into table5 values('n810002',2219);

insert into table6 values('n610000',3310);

insert into table6 values('n610000',3311);

insert into table6 values('n610000',3312);

insert into table6 values('n710001',3313);

insert into table6 values('n710001',3314);

insert into table6 values('n710001',3315);

insert into table6 values('n710001',3316);

insert into table6 values('n810002',3317);

insert into table6 values('n810002',3318);

insert into table6 values('n810002',3319);

--使用业务用户 province_test为区划用户赋权

grant select on v_table1 to us610000;

grant select on v_table2 to us610000;

grant select on v_table3 to us610000;

grant select on v_table4 to us610000;

grant select on v_table5 to us610000;

grant select on v_table6 to us610000;

grant select on v_table1 to us710001;

grant select on v_table2 to us710001;

grant select on v_table3 to us710001;

grant select on v_table4 to us710001;

grant select on v_table5 to us710001;

grant select on v_table6 to us710001;

初始化后的测试业务表和视图如下所示：

通过视图可以实现用户访问控制，针对有区划属性的业务表，A区划用户仅能看到A区划对应的业务数据，其余区划的业务数据均不可见；针对没有区划属性的业务表，A区划用户可以全部业务数据，该方案能够满足客户的整体需求。

步骤三：指定区划数据提取

使用copy命令导出时需要赋予用户超户权限（must be system admin to COPY to or from a file）。

--使用copy命令操作前，需要赋予区划用户例如us610000超户权限

GRANT ALL PRIVILEGES TO us610000;

--使用区划用户导出对应数据文件

copy (select * from public.v_table1) to '/srv/BigData/mppdb/data1/copy_table1_us610000.csv'

with(format 'csv',header 'on',encoding 'gbk');

--使用区划用户导入对应数据文件

copy public.table1 from '/srv/BigData/mppdb/data1/copy_table1_us610000.csv'

with(format 'csv',header 'on',encoding 'gbk');

--完成copy命令导出导入后，回收超户权限

REVOKE ALL PRIVILEGES FROM us610000;

步骤四：批量化脚本处理

--有区划属性的视图批量创建

\o /srv/BigData/mppdb/data1/create_view_command_province.sql

select 'create view '||n.nspname||'.v_'||c.relname||' as select * from '||

c.relname||' where province=substr(current_user,3);' as create_view_command

from pgxc_class x

left join pg_class c on c.oid = x.pcrelid

left join pg_namespace n on n.oid = c.relnamespace

where c.oid in (select attrelid from pg_attribute where attname = 'province');

\o

--没有区划属性的视图批量创建

\o /srv/BigData/mppdb/data1/create_view_command_noprovince.sql

select 'create view '||n.nspname||'.v_'||c.relname||' as select * from '||

c.relname||';' as create_view_command

from pgxc_class x

left join pg_class c on c.oid = x.pcrelid

left join pg_namespace n on n.oid = c.relnamespace

where c.oid not in (select attrelid from pg_attribute where attname = 'province');

\o

--区划用户使用copy命令批量导出

\o /srv/BigData/mppdb/data1/copy_out_command.sql

select 'copy (select * from '||vw.schemaname||'.'||vw.viewname||

' where province = '''||substr(current_user,3)||

''') to '||'''/srv/BigData/mppdb/data1/'||substr(current_user,3)||'/copy_'||

vw.viewname||'.csv'''||' with(format '||'''csv'''||',header '||'''on'''||',encoding '||

'''gbk'''||');' as as copy_out_command

from pg_views vw where vw.schemaname='public' and vw.viewname ~ '^v_.';

\o

--区划用户使用copy命令批量导入

\o /srv/BigData/mppdb/data1/copy_in_command.sql

select 'copy '||tb.schemaname||'.'||tb.tablename||

' from '||'''/srv/BigData/mppdb/data1/'||substr(current_user,3)||'/copy_v_'||

tb.tablename||'.csv'''||' with(format '||'''csv'''||',header '||'''on'''||',encoding '||'''gbk'''||

');' as copy_in_command

from pg_tables tb where tb.schemaname='public';

\o

--赋予区划用户的批量权限

\o /srv/BigData/mppdb/data1/grant_privileges.sql

select 'grant select on v_'||tb.tablename||' to '||us.usename||';' as grant_privileges

from pg_tables tb,(select usename from pg_user where usename ~ '^us.') us

where tb.schemaname='public' order by us.usename,tb.tablename;

\o

--回收区划用户的批量赋权

\o /srv/BigData/mppdb/data1/revoke_privileges.sql

select 'revoke all privileges from '||us.usename||';' as grant_privileges from pg_user us where usename ~ '^us.';

\o

【相关链接】

GaussDB(DWS)-SQL语句上线验收指导：

https://bbs.huaweicloud.com/blogs/188162

数据仓库自动化清理功能实现：

https://bbs.huaweicloud.com/blogs/175440

数据仓库日常巡检策略总结：

https://bbs.huaweicloud.com/blogs/175009

GaussDB(DWS)-资源管控方案技术实战分享：

https://bbs.huaweicloud.com/blogs/174637

GaussDB(DWS)-性能优化最佳实践

https://bbs.huaweicloud.com/blogs/195263

华为云社区论坛链接：

https://bbs.huaweicloud.com/forum/forum-598-1.html

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息，否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com

点赞
收藏
关注作者

评论（0）

0/1000

抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

加入云驻计划，成为创作者

华为云周边好礼
免费体验产品
特殊身份标识
线下官方门票
内部专家零距离
与10000+优质创作者共同成长