通过数据集市整合实现全球化，第 1 利用行和列安全性提供特定于国家（行政区）和地区的数据视图以及正

通过数据集市整合实现全球化，第
1 部分
英文原文：http://ibmdatamag.com/2012/04/going-global-with-data-mart-consolidation-part-1/
利用行和列安全性提供特定于国家（行政区）和地区的数据视图以及正
确的货币聚合
作者：Dan Gibson |
发布日期：2012 年 4 月 30 日
假设您的企业决定将所有单独的数据库和数据集市整合为单独一个企业数据仓库。除了必要
的技术工作之外，整合数据集市可能带来诸多挑战。构建企业数据仓库往往意味着改动现有
策略、创建新策略、组织重组、审查和更改最佳实践，依此类推。
当然，整合同样也能带来许多优势。举例来说，通过消除数据孤岛，多个业务线将能够访问
相同的信息，允许所有人根据相同的信息制作报表，最终提高报表准确性。
将来自多个国家（行政区）的数据整合到同一组物理表中时，面临的挑战与任何系统或数据
集市整合项目并无二致，甚至还要更多一些。例如，遵守各国的安全性和法规要求，处理货
币和汇率，这些都是极为复杂的问题。
在未来的文章中，我们将进一步讨论有关货币和汇率的主题。首先，我们来查看一下安全性。
行级和列级安全性
尽管隐私性和数据安全性始终是一个关键问题，但将来自多个国家（行政区）的数据并入单
独一组表中时，隐私和安全性尤为敏感。职责分离和关注各国法规与安全合规性法律是不可
妥协的要求。
DB2 V10.1 引入了行和列访问控制特性，可帮助组织满足这些要求。行和列访问控制有时
也称为细粒度访问控制 (FGAC)，它们提供了诸多优势：

分离 DBA 和安全性/访问控制职责

无需查看即可实施安全性，简化应用程序开发

能够控制用户可查看哪些行（以及这些行中的哪些数据）

在实施行和列安全性时，无需更改业务查询
为了演示行和列安全性，下面我们举例说明如何将不同国家（行政区）的数据整合到同一个
表中。
表 REAL_ESTATE_SALES 存储着有关各国房地产销售的数据：
CREATE TABLE REAL_ESTATE_SALES
(
COUNTRY_CODE SMALLINT NOT NULL,
PROPERTY_TYPE SMALLINT NOT NULL,
ACCOUNT_NUMBER INTEGER NOT NULL,
TRANSACTION_TYPE VARCHAR (10),
TRANSACTION_AMOUNT
DECIMAL (12, 2) NOT NULL,
TRANSACTION_DATE DATE NOT NULL,
EFFECTIVE_DATE DATE NOT NULL,
EXPIRY_DATE DATE NOT NULL
)
根据要求，用户应只能访问来自其自己国家（行政区）的数据。行访问数据安全性的业务需
求如下：
1. 数据库角色将用于确定用户可以查询哪些行，而角色的名称包含该角色定义所对应的国
家（行政区）名称（例如，SINGAPORE_ROLE 角色对应于新加坡）
2. 各国家（行政区）的行将按其国家编码识别（例如，65 是新加坡的国家编码）
3. 每名用户都会被添加到其所在国家（行政区）的数据库角色之中，因而都仅能访问来自
其所在国家（行政区）的数据
为了满足这项要求，需要使用 CREATE ROLE 和 CREATE PERMISSION 语句。因此，需
要针对各国家（行政区）创建角色：
CREATE ROLE ARGENTINA_ROLE
CREATE ROLE AUSTRALIA_ROLE
CREATE ROLE BRAZIL_ROLE
CREATE ROLE CHINA_ROLE
CREATE ROLE EGYPT_ROLE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
ROLE
ROLE
ROLE
ROLE
ROLE
ROLE
ROLE
ROLE
HONG_KONG_ROLE
INDIA_ROLE
KENYA_ROLE
MEXICO_ROLE
NEW_ZEALAND_ROLE
SINGAPORE_ROLE
SOUTH_AFRICA_ROLE
UNITED_ARAB_EMIRATES_ROLE
接下来，定义一项安全性策略，根据数据库角色和国家编码，仅允许各角色查看来自其相应
国家（行政区）的行：
CREATE PERMISSION ROW_ACCESS_PERMISSION
ON REAL_ESTATE_SALES
FOR ROWS WHERE
(COUNTRY_CODE = 971 AND VERIFY_ROLE_FOR_USER
(SESSION_USER,’UNITED_EMIRATES_ROLE’) = 1)
OR
(COUNTRY_CODE = 20 AND VERIFY_ROLE_FOR_USER
(SESSION_USER,’EGYPT_ROLE’) = 1)
OR
(COUNTRY_CODE = 27 AND VERIFY_ROLE_FOR_USER
(SESSION_USER,’SOUTH_AFRICA_ROLE’) = 1)
OR
(COUNTRY_CODE = 254 AND VERIFY_ROLE_FOR_USER
(SESSION_USER,’KENYA_ROLE’) = 1)
OR
(COUNTRY_CODE = 55 AND VERIFY_ROLE_FOR_USER
(SESSION_USER,’BRAZIL_ROLE’) = 1)
OR
(COUNTRY_CODE = 54 AND VERIFY_ROLE_FOR_USER
(SESSION_USER,’ARGENTINA_ROLE’) = 1)
OR
(COUNTRY_CODE = 52 AND VERIFY_ROLE_FOR_USER
(SESSION_USER,’MEXICO_ROLE’) = 1)
OR
(COUNTRY_CODE = 852 AND VERIFY_ROLE_FOR_USER
(SESSION_USER,’HONG_KONG_ROLE’) = 1)
OR
(COUNTRY_CODE = 91 AND VERIFY_ROLE_FOR_USER
(SESSION_USER,’INDIA_ROLE’) = 1)
OR
(COUNTRY_CODE = 61 AND VERIFY_ROLE_FOR_USER
(SESSION_USER,’AUSTRALIA_ROLE’) = 1)
OR
(COUNTRY_CODE = 81 AND VERIFY_ROLE_FOR_USER
(SESSION_USER,’CHINA_ROLE’) = 1)
OR
(COUNTRY_CODE = 65 AND VERIFY_ROLE_FOR_USER
(SESSION_USER,’SINGAPORE_ROLE’) = 1)
ENFORCED FOR ALL ACCESS
ENABLE
接下来，激活行访问控制。请注意，若未激活行访问控制，则不能评估和实施行权限。
ALTER TABLE REAL_ESTATE_SALES ACTIVATE ROW ACCESS CONTROL
随后为各角色授予 SELECT 权限：
GRANT SELECT ON REAL_ESTATE_SALES
TO ROLE MEXICO_ROLE, ROLE BRAZIL_ROLE,
ROLE ARGENTINA_ROLE, ROLE HONG_KONG_ROLE,
ROLE INDIA_ROLE, ROLE AUSTRALIA_ROLE, ROLE CHINA_ROLE,
ROLE SINGAPORE_ROLE, ROLE NEW_ZEALAND_ROLE,
ROLE UNITED_ARAB_EMIRATES_ROLE, ROLE EGYPT_ROLE,
ROLE SOUTH_AFRICA_ROLE, ROLE KENYA_ROLE
现在，用户仅能查看其相应角色允许其查看的行。例如，如果为用户 Bob 授予角色
CHINA_ROLE：
GRANT ROLE CHINA_ROLE TO USER BOB
Bob 对 REAL_ESTATE_SALES 表发出的任何查询都将仅能访问满足以下条件的行：
(COUNTRY_CODE = 81 AND VERIFY_ROLE_FOR_USER
(SESSION_USER,’CHINA_ROLE’) = 1)
例如，查询：
SELECT SUM (TRANSACTION_AMOUNT)
FROM REAL_ESTATE_SALES
WHERE PROPERTY_TYPE = ‘RESIDENTIAL’
实际上可将它改写为：
SELECT SUM (TRANSACTION_AMOUNT)
FROM REAL_ESTATE_SALES
WHERE PROPERTY_TYPE = ‘RESIDENTIAL’
AND COUNTRY_CODE = 81
AND VERIFY_ROLE_FOR_USER (SESSION_USER,’CHINA_ROLE’) = 1
行安全性的实现快捷、简单而又透明。优化器能够透明地改写和优化所有查询，确保仅返回
用户获得访问授权的行，因此无需更改业务查询。
允许进行地区访问
至此，在我们的示例中，用户仅查询单独一个国家（行政区）。然而，地区报告往往必不可
少，因此用户必须有能力一次查询多个国家（行政区）。为了满足这样的要求，需要创建数
据库角色，然后为这些角色授予已定义的一个或多个国家（行政区）角色。例如，可以创建
三个地区角色：
CREATE ROLE ASIA_PACIFIC_ROLE
CREATE ROLE LATIN_AMERICA_ROLE
CREATE ROLE MEA_ROLE
接下来，需要为每个角色授予该地区的对应角色：
GRANT ROLE EGYPT_ROLE, ROLE SOUTH_AFRICA_ROLE,
ROLE KENYA_ROLE, ROLE UAE_ROLE
TO MEA_ROLE
GRANT ROLE ARGENTINA_ROLE, ROLE BRAZIL_ROLE,
ROLE MEXICO_ROLE
TO LATIN_AMERICA_ROLE
GRANT ROLE HONG_KONG_ROLE, ROLE INDIA_ROLE,
ROLE AUSTRALIA_ROLE, ROLE CHINA_ROLE,
ROLE SINGAPORE_ROLE, ROLE NEW_ZEALAND_ROLE
TO ASIA_PACIFIC_ROLE
至此，被授予这些角色之一的任何用户均可查看对应于该地区的所有行。例如，假设为用户
Sally 授予 LATIN_AMERICA_ROLE 角色：
GRANT ROLE LATIN_AMERICA_ROLE TO USER SALLY
在 Sally 查询 REAL_ESTATE_SALES 表时，她就会看到针对阿根廷、巴西和墨西哥的数
据，但无法查看其他国家（行政区）的任何内容。
聚合多个国家（行政区）的币种
被授予 LATIN_AMERICA_ROLE、MEA_ROLE 或 ASIA_PACIFIC_ROLE 角色的用户可访
问多个国家（行政区）的数据。然而，根据一个国家（行政区）的币种做出的任何加总都是
不正确的。例如，中国香港和新加坡所用的货币的值无法汇总，因为两者之和不属于任何有
意义的值。
如果需要保证在汇总多个国家（行政区）的币种时得到有价值的结果，则必须先将各国的币
种转为一种通用币种。为满足这项需求，我们需要提供以下工具：
1. 一个币种查找表，存储各币种的汇率
2. 一个（安全的）用户定义的函数（UDF），用于执行转换
3. 在 TRANSATION_AMOUNT 列中定义的一个列屏蔽，确定返回哪种币种（本国币种或通
用币种）
首先，创建一个名为 EXCHANGE_RATES 的币种查找表：
CREATE TABLE EXCHANGE_RATES
(
COUNTRY_CODE SMALLINT NOT NULL,
EXCHANGE_RATE DECIMAL (6, 6) NOT NULL
)
随后定义一个名为 CURRENCY_CONVERSION 的用户定义的函数，根据各国汇率返回使用
通用币种的同等值：
CREATE FUNCTION CURRENCY_CONVERSION
(TRANSACTION_AMOUNT DECIMAL (10, 2), CCODE SMALLINT)
RETURNS DECIMAL (12, 2)
LANGUAGE SQL
READS SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
RETURN
SELECT TRANSACTION_AMOUNT * EXCHANGE_RATE
FROM EXCHANGE_RATES WHERE CCODE = COUNTRY_CODE
安全的函数
将函数作为行权限或列屏蔽的一部分引用时，所有函数都必须被视为安全的。这是利用
ALTER FUNCTION 语句完成的：
ALTER FUNCTION CURRENCY_CONVERSION SECURED
定义列屏蔽
名为 CURRENCY_CONVERTER_MASK 的列屏蔽决定了是使用本国币种返回
TRANSACTION_AMOUNT，还是使用同等的通用币种返回 TRANSACTION_AMOUNT，依据
如下：
1. 如果用户是 ASIA_PACIFIC_ROLE、MEA_ROLE 或 LATIN_AMERICA_ROLE 的成员，
则返回通用币种的同等值
2. 如果用户不是 ASIA_PACIFIC_ROLE、MEA_ROLE 或 LATIN_AMERICA_ROLE 的成
员，则返回本国币种（即 TRANSACTION_AMOUNT 中存储的值）
要创建列屏蔽，请执行以下命令：
CREATE MASK CURRENCY_CONVERTER_MASK ON REAL_ESTATE_SALES FOR
COLUMN TRANSACTION_AMOUNT RETURN
CASE
WHEN VERIFY_ROLE_FOR_USER
(SESSION_USER,’ASIA_PACIFIC_ROLE’,'LATIN_AMERICA_ROLE’,
‘MEA_ROLE’) = 1
THEN
CURRENCY_CONVERSION (TRANSACTION_AMOUNT, COUNTRY_CODE)
ELSE
TRANSACTION_AMOUNT
END
ENABLE
必须先激活列访问控制，然后才能让列屏蔽处于活动状态：
ALTER TABLE REAL_ESTATE_SALES ACTIVATE
COLUMN ACCESS CONTROL
所有查询都将根据用户角色，透明地返回用户的本国币种或通用币种。
数据屏蔽
用户也可以屏蔽列数据。假设拥有地区访问权限的用户不应能够查看账号。下面的 CREATE
MASK 语句可实现此目的，在这种情况下返回 -999999999 值，而非真实账号：
CREATE MASK ACCOUNT_NUMBER_MASK ON REAL_ESTATE_SALES
FOR COLUMN ACCOUNT_NUMBER RETURN
CASE
WHEN VERIFY_ROLE_FOR_USER
(SESSION_USER,’ASIA_PACIFIC_ROLE’,'LATIN_AMERICA_ROLE’,’MEA_ROLE’)
= 1
THEN
-999999999
ELSE
ACCOUNT_NUMBER
END
ENABLE
结束语
您完全可以创建单独一个数据库，使用其中单独的一组表来存储公司数据的一个副本。如本
文所述，DB2 10 允许您实现行和列安全性策略，保证用户仅能访问他们有权访问的数据。
除此之外，您还可以在单独一组表内处理多个国家（行政区），还能解决不同币种的聚合和
转换问题。
有关全球化数据集市整合的更多内容，请参阅本系列的第 2 部分。
无相关文章。