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区划us610000B区划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个月内不可修改。