DB2 分地域限制用户查询数据子集的技术

核心提示：问题提出本文作者在实施某省级电信运营商的综合业务支撑系统时，遇到这样的问题：该系统是数据集中、应用集中的大型业务系统，DB2 分地域限制用户查询数据子集的技术，该省（区）共有 12 个地市，每个地市都设有各自的支撑中心负责维护本地网范围内的数据，不过应当把 DB2 实用命令替换为相应数据库的命令，系统表也应当作相应替换

问题提出

本文作者在实施某省级电信运营商的综合业务支撑系统时，遇到这样的问题：该系统是数据集中、应用集中的大型业务系统，该省（区）共有 12 个地市，每个地市都设有各自的支撑中心负责维护本地网范围内的数据，支撑中心技术人员都有运用 SQL 直接操纵数据库的能力。省公司希望能以简便的方式达到如下目标：各地市支撑中心人员能查询到系统内绝大多数表的数据；能查询所属本地网范围内的数据，不能看到其他本地网的数据；不能给技术人员带来额外的学习和管理负担。

在业务级别，应用系统已经限制了特定用户只能访问特定数据子集，本文只考虑限制技术人员运用 SQL 直接操作数据库的情况。视图是数据库管理系统提供的限制访问特定行集和列集的基本技术，在这里，对表的列操作不敏感，只考虑操作行集的限制。数据库可以在视图上对特定用户授予访问权限。

图 1. 限制特定用户查询特定数据子集的需求模型

查看原图（大图）

该系统数据库以 DB2 v9 和 IBM AIX 5L 为基础架构软件，下面就在现有系统环境的基础上提出针对这一问题的解决方案。本文第二节介绍达到预定目标的基本技术原理，第三节结合使用 Shell 脚本和 DB2 实用命令，半自动地实现限制特定用户查询特定数据子集，第四节验证方案运行结果，第五节展望更广泛的应用领域。

技术原理

视图、模式和名称空间

在 DB2 数据库中，模式（Schema）能够划分不同的数据库对象，例如：表、视图和存储过程等。数据库用户登录后，操作同名模式下的数据库对象可以省略模式名前缀。以用户 NMZF474 为例，查询视图 NMZF474.T_SERVICE_INSTANCE 可以省略模式名前缀，而查询其他模式下的视图或表 IBSS.T_SERVICE_INSTANCE 则必须加上模式名前缀：

　SELECT　*　FROM　T_SERVICE_INSTANCE;　--　访问本模式的视图　
　SELECT　*　FROM　IBSS.T_SERVICE_INSTANCE;　--　访问其他模式的视图或表　

这样，不同的模式可以拥有同名的视图，如果给各个支撑中心分别创建数据库用户，再在相应用户模式下创建与源表同名的视图，就不会发生命名冲突。定义视图时添加 WHERE 选择条件，仅仅检索出所属本地网的数据记录。

基本原理

下面以本地网 474 为例说明该技术的基本原理：

创建数据库用户 nmzf474，对 IBM AIX 来说可以创建操作系统用户 nmzf474；

对于所有需要限制查询特定数据子集的表，在模式 nmzf474 下创建同名视图；

在新建的视图上，给用户 nmzf474 授予 SELECT 权限，必要时可以对部分视图授予 UPDATE、INSERT 或 DELETE 等权限。

DROP　VIEW　NMZF474.T_SERVICE_INSTANCE;　
CREATE　VIEW　NMZF474.T_SERVICE_INSTANCE　AS　
SELECT　*　FROM　ATHENA.T_SERVICE_INSTANCE　WHERE　LATN_ID　=　474;　
　
GRANT　SELECT　ON　NMZF474.T_SERVICE_INSTANCE　TO　USER　NMZF474;

本地网 474 支撑中心用户以 nmzf474 身份登录数据库，用 SQL DML 语句直接操纵视图数据。因为视图名和表名相同，用户的体验就像在操纵同名的数据表，不会感觉到有任何差别，实际上用户只是操纵同名表的特定数据子集。

编写定义视图的 SQL 语句

由于该系统异常复杂，相应地，数据表数量也极为庞大，如何快捷地编写定义视图的 SQL 语句显得尤为重要。经过研究发现，DB2 数据库中表的定义都在系统表 SYSIBM.SYSTABLES 中，列的定义都在表 SYSIBM.SYSCOLUMNS 中。我们希望通过关联两个系统表，查询出包含本地网号列名的模式名称和表名。本地网号在各个表中的列名并不完全一致，但也有一定规律，所有不同的列名称不超过 10 个。

图 2. 包含表信息的系统表 SYSIBM.SYSTABLES

查看原图（大图）

图 3. 包含列信息的系统表 SYSIBM. SYSCOLUMNS

查看原图（大图）

分析上一小节建视图语句，除了新模式名（NMZF474）、新视图名、源模式名（ATHENA）、源表名、本地网号列名以及本地网号不同之外，其余部分完全相同，因此可以共享相同的 SQL 语句模板。规律被揭示之后，可以用 Shell 脚本自动地创建定义视图和授权 SQL 语句。

技术实现

对上一节介绍的技术原理进行分析、归纳，得到如下图所示的实现步骤：

图 4 技术实现流程

查看原图（大图）

下面详细说明各步骤的具体实现：

1、创建包含表名和本地网号列名的中间文件

对于各个表来说，创建新视图时其差异仅仅是模式名、表名和本地网号列名，因此，我们要生成文本文件，每行包含模式名、表名和本地网号列名，中间以空格或 TAB 分隔，以换行符 \n 分隔新行，每个数据表对应这个文件的一行。其中，对于有些表，本地网号列名是可以省略的，例如大部分静态参数表都不区分本地网。中间文件格式举例如下：

　--　模式名　表名　本地网号列名　
　ATHENA　T_SERVICE_INSTANCE　LATN_ID　--　分本地网　
　IBSS　ORG_DEPT　--　不区分本地网　

经过分析本地网号的列名特征，可以抽取列本地网号在不同表中的列名。经过连接两个系统表 SYSIBM.SYSTABLES 和 SYSIBM.SYSCOLUMNS，检索出模式名、表名和列名。检索的 SQL 语句如下：

带本地网的表：

　SELECT　DISTINCT　A.CREATOR　AS　SCHEMA,　A.NAME　AS　TABNAME,　B.NAME　AS　LATN_COLUMN　
　FROM　SYSIBM.SYSTABLES　A,SYSIBM.SYSCOLUMNS　B　
　WHERE　A.CREATOR　IN('IBSS','ATHENA')　
　AND　A.TYPE='T'　
　AND　A.NAME　=B.TBNAME　
　AND　(B.NAME　='LATN_ID'　OR　B.NAME='LOCAL_NET_ID'　OR　B.NAME　='LANID'　
OR　B.NAME='LOACL_NET'　OR　B.NAME='LATNID'　OR　B.NAME　='LOCAL_NET')　
　
　AND　TBCREATOR　IN('IBSS','ATHENA')　
　AND　A.NAME　NOT　LIKE　'%CATTSOFT'　
　AND　A.NAME　NOT　LIKE　'%HISTORY'　
　AND　A.NAME　NOT　LIKE　'%BAK%'　
　AND　A.NAME　NOT　LIKE　'%MID_%'　
　AND　A.NAME　NOT　LIKE　'%_HIS'　
　AND　A.NAME　NOT　LIKE　'%LIWEI%'　
　AND　A.NAME　NOT　LIKE　'CTO%'　
　AND　A.NAME　NOT　LIKE　'CONV%'　
　AND　A.NAME　NOT　LIKE　'%07%'　
　AND　A.NAME　NOT　LIKE　'TEMP%'　
　AND　A.NAME　NOT　LIKE　'%TMP%'　
　ORDER　BY　A.CREATOR,A.NAME;　

不带本地网的表：

　SELECT　DISTINCT　A.CREATOR　AS　SCHEMA,　A.NAME　AS　TABNAME　
　FROM　SYSIBM.SYSTABLES　A　
　WHERE　A.CREATOR　IN('IBSS','ATHENA')　
　AND　A.TYPE='T'　
　AND　A.NAME　NOT　IN(SELECT　TBNAME　FROM　SYSIBM.SYSCOLUMNS　WHERE　(NAME　='LATN_ID'　
OR　NAME='LOCAL_NET_ID'　OR　NAME　='LANID'　OR　NAME='LOACL_NET'　OR　NAME='LATNID'　
OR　NAME　='LOCAL_NET')　AND　TBCREATOR　IN('IBSS','ATHENA'))　
　
　AND　A.NAME　NOT　LIKE　'%CATTSOFT'　
　AND　A.NAME　NOT　LIKE　'%HISTORY'　
　AND　A.NAME　NOT　LIKE　'%BAK%'　
　AND　A.NAME　NOT　LIKE　'%MID_%'　
　AND　A.NAME　NOT　LIKE　'%_HIS'　
　AND　A.NAME　NOT　LIKE　'%LIWEI%'　
　AND　A.NAME　NOT　LIKE　'CTO%'　
　AND　A.NAME　NOT　LIKE　'CONV%'　
　AND　A.NAME　NOT　LIKE　'%07%'　
　AND　A.NAME　NOT　LIKE　'TEMP%'　
　AND　A.NAME　NOT　LIKE　'%TMP%'　
　ORDER　BY　A.CREATOR,A.NAME;　

对这两个 SQL 语句选择条件的说明：

A.CREATOR IN ('IBSS','ATHENA') 仅选择模式 IBSS 和 ATHENA 下的表；

B.NAME ='LATN_ID' OR B.NAME='LOCAL_NET_ID' … 过滤出符合本地网号列命名特征的行；

A.TYPE = 'T' 选择类型为'T'是表，忽略视图；

其余条件是过滤掉没有意义的表。

将上述两个 SQL 语句分别存为文件 table_localnet.sql 和 table_no_localnet.sql，调用 db2 实用命令，SQL 命令输出结果保存在输出文件中：

　$　db2　connect　to　db-alias　user　db2inst1　using　<passwd>　
　$　db2　-tvf　table_localnet.sql　>　../data/table_list_localnet.txt　
　$　db2　-tvf　table_no_localnet.sql　>　../data/table_list_no_localnet.txt　

预期的输出文件内容如下所示：

　ATHENA　ACCOUNTINFO　LANID　
　ATHENA　ACCOUNTITEM　LANID　
　ATHENA　ADSLSERVICE　LANID　
　ATHENA　APP_CHM_BUSINESSMANAGER　LANID　
　ATHENA　APP_COM_CONTACT　LANID　
　ATHENA　APP_LNEAI_DETAILNUMBERSEGMENT　LATNID　

可以启用文本编辑器打开输出文件，删除文件头部和尾部的无用行。这个中间文件也是下一步骤的输入文件。

2、创建数据库用户

对于 DB2 数据库来说，数据库身份认证和操作系统认证紧密集成，创建操作系统用户就能创建同名的 DB2 用户。

Telnet 远程登录到 DB2 数据库所在的服务器，输入 root 根用户名和密码，或者以普通用户身份登录，再通过 su 切换到 root 用户：

　telnet　hostname　

创建新 OS 用户，修改用户密码。再以新用户远程登录主机，修改密码（如果没有经过首次登录，以后连接时会报告错误）：

　#　useradd　-m　nmzf474　
　#　passwd　nmzf474　
　#　telnet　localhost　

禁用新用户的远程登录权限。禁止新用户通过 telnet、rsh 和 rlogin 等方式远程登录到数据库主机，但是保留连接数据库的权限：

　#　smitty　user　

选择菜单 Change / Show Characteristics of a User 进入后，输入用户名 nmzf474，修改选项 User can LOGIN REMOTELY(rsh,tn,rlogin)?，选择 false，禁用远程登录。禁用远程登录权限后，新用户不能远程登录操作系统，能提高操作系统的安全性。

新用户创建完成后，可以用下列命令连接 DB2 数据库：

　$　db2　connect　to　db-alias　user　nmzf474　using　<passwd>　

如果连接正常，说明新用户创建成功。

3、创建定义视图的 SQL 文件

在技术原理一节提到，创建视图的语句有共同的 SQL 模板，反复向模板填入模式名、表名和列名等等，就能得到所有建视图语句。在 Unix 系统上，实用程序 awk 有强大的文本处理能力，能很好地完成这个任务。脚本 gen_create_view.awk 能够从输入文件读取模式名、表名和本地网列名等信息项，套用 SQL 模板后输出规范的创建视图 SQL 语句：

　#!/usr/bin/awk　
　
　BEGIN　{　
　#　定义常量　　
　SCHEMA_VIEW　=　"NMZF474";　
　LATN_ID　=　"474";　
　　
　#　定义变量　
　COUNT　=　0;　
　　
　#　输出　DDL　头　
　print　"--------------------------------------------------------------------";　
　print　"--　为用户　"　SCHEMA_VIEW　"　定义查询所属本地网数据子集的用户视图";　
　print　"--------------------------------------------------------------------";　
　print　"";　
　}　
　
　#　循环体　
　{　
#　定义　WHERE　子句　
　WHERE_CLAUSE　=　"";　
　if　($3　!=　""){　
　WHERE_CLAUSE　=　"　WHERE　"　$3　"　=　"　LATN_ID;　
　}　
　　
#　输出　DDL　语句　
　print　"--　VIEW　"　SCHEMA_VIEW"."$2;　
　print　"--　DROP　VIEW　"　SCHEMA_VIEW"."$2";";　
　print　"CREATE　VIEW　"　SCHEMA_VIEW"."$2　"　AS　SELECT　*　FROM　"　$1"."$2　WHERE_CLAUSE";";　
　print　"GRANT　SELECT　ON　"　SCHEMA_VIEW"."$2　"　TO　USER　"　SCHEMA_VIEW";";　
　print　"";　
　　
　COUNT++;　
　}　
　
　END　{　
　#　输出汇总信息　
　print　"--　一共为用户　"　SCHEMA_VIEW　"　定义了　"　COUNT　"　个用户视图。";　
　}　

BEGIN 段首先被调用，运行且仅运行一次。该段定义常量，初始化变量，输出文件的头部说明。

中间的段是循环体，对于输入文件的每一行运行一次，直到输入文件结束。该段输出 DROP VIEW、CREATE VIEW、GRANT 等 SQL 语句。

END 段在结束前调用一次。该段输出汇总信息。

在 AIX 命令提示符下运行 awk 脚本，生成创建视图的 SQL 语句：

　$awk　-f　gen_create_view.awk　../data/table_list_localnet.txt　\　
　>../sql/CREATE_VIEW_WITHLOCALNET_NMZF474.SQL　

输出文件是 CREATE_VIEW_WITHLOCALNET_NMZF474.SQL，预期的输出内容如下所示：

　--------------------------------------------------------------------　
　--　为用户　NMZF474　定义查询所属本地网数据子集的用户视图　
　--------------------------------------------------------------------　
　
　--　VIEW　NMZF474.NM_ACCOUNTINFO　
　DROP　VIEW　NMZF474.NM_ACCOUNTINFO;　
　CREATE　VIEW　NMZF474.NM_ACCOUNTINFO　AS　SELECT　*　FROM　ATHENA.NM_ACCOUNTINFO　
WHERE　LATN_ID　=　474;　
　GRANT　SELECT　ON　NMZF474.NM_ACCOUNTINFO　TO　USER　NMZF474;　
　
　--　VIEW　NMZF474.NM_ADDRESSINFO　
　DROP　VIEW　NMZF474.NM_ADDRESSINFO;　
　CREATE　VIEW　NMZF474.NM_ADDRESSINFO　AS　SELECT　*　FROM　ATHENA.NM_ADDRESSINFO　
WHERE　LATN_ID　=　474;　
　GRANT　SELECT　ON　NMZF474.NM_ADDRESSINFO　TO　USER　NMZF474;　
…　
--　一共为用户　NMZF474　定义了　605　个用户视图。　

对输入文件 table_list_no_localnet.txt 再执行一次上述 awk 脚本，得到另一个 SQL 文件。

4、运行 SQL 文件创建视图

运行 SQL 文件创建视图是相当容易的事情了，调用 db2 实用命令执行 SQL 语句，不出意外的话将会创建一系列的视图：

　$　db2　connect　to　db-alias　user　db2inst1　using　<passwd>　
　$　db2　-tvf　../sql/CREATE_VIEW_WITHLOCALNET_NMZF474.SQL　
　$　db2　-tvf　../sql/CREATE_VIEW_NOLOCALNET_NMZF474.SQL　

重复第 2 步至第 5 步，直到对所有本地网都走了一遍流程，且没有待处理的本地网为止。

在第 3 步，如果已经生成了创建视图 SQL 文件，可以不修改 awk 脚本，直接启用文本编辑器修改 SQL 文件，用新本地网号替换原本地网号，得到新本地网的 SQL 文件。

第 5 步技术验证放在下一节叙述。

效果验证

效果验证主要验证两点：未授权的访问应当被禁止；新用户通过视图只能访问源表的特定数据子集。

以新用户 NMZF474 身份登录数据库，查询本模式下视图的记录条数，再查询源表的记录条数，观察输出结果：

　$　db2　connect　to　db-aliasuser　nmzf474using　'password'　
　$　db2　"SELECT　COUNT(*)　FROM　T_SERVICE_INSTANCE"　

返回 635104 条记录。这是所属本地网数据子集的记录条数。

　$　db2　"SELECT　COUNT(*)　FROM　ATHENA.T_SERVICE_INSTANCE"　

提示没有访问权限：

　SQL0551N　"NMZF474"　does　not　have　the　privilege　to　perform　operation　"SELECT"　
on　object　"ATHENA.T_SERVICE_INSTANCE".　SQLSTATE=42501　

以 db2inst1 用户身份登录数据库，再查询记录条数：

　$　db2　connect　to　db-aliasuser　db2inst1using　'password'　
　$　db2　"SELECT　COUNT(*)　FROM　ATHENA.T_SERVICE_INSTANCE"　

返回 2180862 条记录。这是数据全集的记录条数。

经过效果验证， 本解决方案达到预期目标。

结束语

在 DB2 数据库创建视图后，会在两个系统表增加大量的表和列，因为系统表已经建立恰当的索引，经过实验证实对数据库性能没有明显影响。

本文以某省级电信项目为例来说明技术原理，其实它可以推广到更广泛的领域，例如：对银行的大集中系统，省分行和市级支行的关系也有数据全集和子集的约束；对于连锁企业的总部和分支机构之间的关系等等。

本文介绍的技术原理对其他关系型数据库和 Unix 操作系统也有指导价值，不过应当把 DB2 实用命令替换为相应数据库的命令，系统表也应当作相应替换。有兴趣的读者可以在其他关系型数据库上作进一步的工作。

下表列出各主流数据库的关键信息：

数据库	Column 系统表 / 视图	Table 系统表 / 视图	创建视图语句	授权语句
DB2	SYSIBM.SYSCOLUMNS	SYSIBM.SYSTABLES	CREATE VIEW	GRANT
Oracle	SYS.ALL_TAB_COLUMNS	SYS.ALL_TABLES	CREATE VIEW	GRANT
Sybase	DBO.SYSCOLUMNS	DBO.SYSOBJECTS	CREATE VIEW	GRANT
SQL Server	DBO.SYSCOLUMNS	DBO.SYSOBJECTS	CREATE VIEW	GRANT