Blogs

Going Global with Data Mart Consolidation: Part 1

Using row and column security to provide country- and region-specific views of data and correct currency aggregation

Suppose your company has decided to collapse its separate databases and data marts into a single enterprise data warehouse. Beyond the technical effort required, consolidating data marts can create multiple challenges. Building an enterprise data warehouse often means changing existing policies, creating of new policies, organizational restructuring, reviewing, and changing best practices—the list goes on.

Of course, there are many benefits associated with consolidation as well. For example, by removing data silos, multiple lines of businesses are able to access the same information, which allows everyone to report on the same information and leads to improved reporting accuracy.

Consolidating data from many countries into the same set of physical tables brings all of the challenges as any system or data mart consolidation project—plus a few more. For example, meeting each county’s security and legal requirements and handling currency and exchange rates can be tricky.

More on the topic of currency and exchange rates later. But first, let’s take a look at security.

Row-level and column-level security

While privacy and data security is always a concern, combining data from different countries into a single set of tables is especially sensitive. Separation of duties and observance of each country’s legal and security compliance laws are non-negotiable.

DB2 V10.1 introduces row and column access control features to help organizations meet those requirements. Sometimes referred to as Fine-Grained Access Control (FGAC), row and column access control provides several benefits:

  • Separation of DBA and security/access control responsibilities
  • No need for views to implement security, which simplifies application development
  • Ability to control which rows (and which data in those rows) a user can view
  • Business queries do not have to be changed to implement row and column security

To demonstrate row and column security, let’s use the example of consolidating different countries sales data into the same table.

 

The REAL_ESTATE_SALES table stores data about each country’s 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
)

It is a requirement that end users only be able to access data from their own country. The business requirements for row access data security are as follows:

  1. Database roles will be used to determine which rows a user can query, where the name of the role includes the country’s name that the role is defined for (e.g., the role SINGAPORE_ROLE is for the country Singapore)
  2. Rows for each country are identified by their country code (for example, 65 is the country code for Singapore)
  3. Each user will only be able to access data from their own country by being added to that country’s database role

To address these requirements, use of the CREATE ROLE and CREATE PERMISSION statement is required. So, the following code creates a role for each country:

CREATE ROLE ARGENTINA_ROLE
CREATE ROLE AUSTRALIA_ROLE
CREATE ROLE BRAZIL_ROLE
CREATE ROLE CHINA_ROLE
CREATE ROLE EGYPT_ROLE
CREATE ROLE HONG_KONG_ROLE
CREATE ROLE INDIA_ROLE
CREATE ROLE KENYA_ROLE
CREATE ROLE MEXICO_ROLE
CREATE ROLE NEW_ZEALAND_ROLE
CREATE ROLE SINGAPORE_ROLE
CREATE ROLE SOUTH_AFRICA_ROLE
CREATE ROLE UNITED_ARAB_EMIRATES_ROLE

Next, define a security policy that allows each role to only view rows from its respective county based on database role and country code:

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)
OR
ENABLE

Next, activate row access control. Note that row permissions will not be evaluated and enforced until row access control is activated.

ALTER TABLE REAL_ESTATE_SALES ACTIVATE ROW ACCESS CONTROL

Then grant the SELECT privilege to each role:

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

Now end-users will only be able to view rows that their respective roles allow. For example, if Bob is granted the role CHINA_ROLE:

GRANT ROLE CHINA_ROLE TO USER BOB

Any query that Bob issues against the REAL_ESTATE_SALES table will only be able to access rows that satisfy this condition:

(COUNTRY_CODE = 81 AND VERIFY_ROLE_FOR_USER (SESSION_USER,'CHINA_ROLE') = 1)

For example, the query:

SELECT SUM (TRANSACTION_AMOUNT)
FROM REAL_ESTATE_SALES
WHERE PROPERTY_TYPE = ‘RESIDENTIAL’

Is effectively rewritten to:

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

Implementing row security is quick, easy, and transparent. No changes to business queries are required because the optimizer transparently rewrites and optimizes all queries to ensure that only the rows that a user is authorized to access are returned.

Allowing regional access

So far in our example, users can only query a single country. However regional reporting is often required—so users need the ability to query more than one country at a time. To satisfy this requirement, database roles are created where those roles are then granted one or more of the country roles already defined. For example, three regional roles can be created:

CREATE ROLE ASIA_PACIFIC_ROLE
CREATE ROLE LATIN_AMERICA_ROLE
CREATE ROLE MEA_ROLE

Next, each role is granted the corresponding roles for that region:

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

At this point, any end user granted one of these roles will be able to view all rows for that region. For example, if Sally is granted the role LATIN_AMERICA_ROLE:

GRANT ROLE LATIN_AMERICA_ROLE TO USER SALLY

When Sally queries the REAL_ESTATE_SALES table, she will see data for the countries Argentina, Brazil, and Mexico, but nothing for other countries.

Aggregating currencies across countries

End users granted the roles LATIN_AMERICA_ROLE, MEA_ROLE or ASIA_PACIFIC_ROLE can access data for more than one country. However, any aggregation against a local currency will be incorrect. For example, one cannot sum up Hong Kong and Singapore local currency values because the sum would not result in a meaningful value.

If aggregation of local currency values across countries is to be of any value, it would make sense to first convert each country’s local currency into a common currency. To address this requirement, the following is required:

  1. A currency lookup table that stores the exchange rate for each currency
  2. A (secure) user defined function (UDF) that performs the conversion
  3. A column mask defined on the column TRANSATION_AMOUNT that determines what currency representation is returned (local currency or common currency)

First, create a currency lookup table named EXCHANGE_RATES:

CREATE TABLE EXCHANGE_RATES
(
COUNTRY_CODE SMALLINT NOT NULL,
EXCHANGE_RATE DECIMAL (6, 6) NOT NULL
)

Then define a user-defined function named CURRENCY_CONVERSION that returns a common currency equivalent based on each country’s exchange rate:

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

Secured functions

All functions must be deemed secure when referenced as part of a row permission or a column mask. This is accomplished using the ALTER FUNCTION statement:
ALTER FUNCTION CURRENCY_CONVERSION SECURED

Defining a column mask

The column mask named CURRENCY_CONVERTER_MASK determines whether the column TRANSACTION_AMOUNT is returned either in a country’s local currency or the common currency equivalent based on the following:

  1. If the user is a member of ASIA_PACIFIC_ROLE, MEA_ROLE, or LATIN_AMERICA_ROLE, return the common currency equivalent
  2. If the user is not a member of ASIA_PACIFIC_ROLE, MEA_ROLE, or LATIN_AMERICA_ROLE, return the local currency (i.e. the value stored in TRANSACTION_AMOUNT)

To create a column mask:

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

Column masks are not active until column access control is activated:

ALTER TABLE REAL_ESTATE_SALES ACTIVATE
COLUMN ACCESS CONTROL

All queries will transparently either return an end user’s local currency or a common currency based on that end user’s role.

Data masking

Masking of column data is also possible. Let’s suppose that users with regional access should not be able to view account numbers. The following CREATE MASK statement accomplishes just that by returning the value -999999999 instead of the real account number:

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

Creating a single database—with a single set of tables that store a single copy of your company’s data—is possible. As shown in this article, DB2 10 allows you to implement row and column security policies that give users access only to data that they are authorized to access. In addition, you can handle multiple countries in a single set of tables and also address the aggregation and conversion of different currencies.

For more on global data mart consolidation, see Part 2 of this series.

[followbutton username='IBMdatamag' count='false' lang='en' theme='light']