Databases

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 users will 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, the use of the CREATE ROLE and CREATE PERMISSION statement is required. So, to create 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 their 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)

ENFORCED FOR ALL ACCESS

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 users will only be able to view rows that their respective roles allow. For example, if the user 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 user granted one of these roles will be able to view all rows for that region. For example, if the user 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

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, we will require the following:

  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 a user’s local currency or a common currency based on that 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

 

Conclusion

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.

Previous post

Going Global with Data Mart Consolidation: Part 2

Next post

Pushing IBM Informix Innovator-C to its Limits

Dan Gibson

From his position as the DB2 Lab Advocate at the Atlanta, Nagano, and Sydney Olympic Games to his Master of Science Degree in Computer Science, Dan knows technology—specifically DB2—inside and out. Since joining IBM over a decade ago, Dan has dedicated his career to diving in with DB2.