Provide country-and region-specific data views with row and column security

Suppose your business decides to consolidate all individual databases and data marts into a single enterprise http://www.aliyun.com/zixun/aggregation/8302.html > Data Warehouse. In addition to the necessary technical work, consolidating data marts can pose many challenges. Building an enterprise data warehouse often means changing existing policies, creating new policies, reorganizing organizations, reviewing and changing best practices, and so forth.

Of course, consolidation can also bring many advantages. For example, by eliminating islands of data, multiple lines of business will be able to access the same information, allowing everyone to make reports based on the same information, and ultimately improve the accuracy of the report.

When consolidating data from multiple countries (administrative regions) into the same set of physical tables, the challenge is no different from any system or data mart consolidation project, or even more. For example, complying with the security and regulatory requirements of countries, dealing with currencies and exchange rates, are extremely complex issues.

In future articles, we will discuss the subject of currency and exchange rates further. First, let's look at security.

Row-level and column-level security

While privacy and data security are always a key issue, privacy and security are particularly sensitive when incorporating data from multiple countries (cantons) into a separate set of tables. Separation of duties and attention to national laws and regulations and security compliance law is an uncompromising requirement.

DB2 V10.1 introduces row and column access control features to help organizations meet these requirements. Row and column access control, sometimes referred to as fine-grained access control (FGAC), offers many advantages:

 Separate DBA and security/access control responsibilities
 To implement security without looking at it, simplifying application development
 Can control which rows a user can view (and which data in those rows)
 Without changing the business query when enforcing row and column security

To demonstrate row and column security, here's an example of how to consolidate data from different countries (administrative regions) into the same table.

Table Real_estate_sales stores data on real estate sales in various countries:

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 (2) Not NULL,
Transaction_date DATE not NULL,
Effective_date DATE not NULL,
Expiry_date DATE not NULL
)

Users should have access only to data from their own country (administrative region), as required. The business requirements for row access to data security are as follows:

1. The database role will be used to determine which rows the user can query, and the name of the role contains the country (administrative) name corresponding to the role definition (for example, the singapore_role role corresponds to Singapore)

2. The lines of each country (administrative region) will be identified by their national codes (for example, 65 is the National Code of Singapore)

3. Each user is added to the database role in his or her country (administrative region) and thus has access only to data from their country (region) in order to meet this requirement, you need to use the Create role and create PERMISSION statements. Therefore, you need to create roles for each country (administrative Region):

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