Most companies are biased toward protecting rather than exploiting information. However there are many very good and very bad reasons to restrict, or secure, the information in a data warehouse. In this article I will discuss some of the primary issues and decision points of a data warehouse with data security.

Purpose of Data Warehouse:

A solid understanding of the purpose and use of your data warehouse is a requirement before any security plan can be designed. Be honest. If the majority of your users are going to be viewing standard reports, even though the main “selling” point of the warehouse was data analysis, the security plan should be biased toward standardized reporting. The impact of security will last far longer than the initial warehouse proposal.

Types of Data Warehouses:

In this article I am going to address the three fundamental types of data warehouses.

Analytical:

• These are the data warehouses that you read about. They enjoy significant mind share during the planning and selling of a data warehouse.
• Security or anything that restricts access to information has no place in an analytical warehouse.
• Standardized reporting:
• These warehouses will have widespread usage throughout an organization and are the most sensitive to performance degradation.

Data Homogenization/Consolidation:

• These data warehouses combine multiple sources of information with a primary goal of integrating the information.
• The complexity of security increases as different sources of information are combined. Different areas of the organization may have their own security plan.

Application Level Security:

Security is integrated into the application and can be specific to the data accessed by the application as well as the functions of the application.

Database Level Security:

The place for security is in the data warehouse. Security is thereby provided consistently to all applications and has a single point of maintenance.

The Security Table:

• The security table contains each attribute which is to be secured along with a user identification that relates the person logged into the warehouse to the attributes.
• For each user, the table contains the values of each of the attributes that the user is permitted to access.
• If the warehouse is secured at the lowest levels of granularity, this table can become quite large. It can be the largest table in the data warehouse.
• This table or tables become the basis for all security views, roles or partitions that provide the physical implementation of the security plan.

Determining the level of Security:

By carefully matching the purpose of the warehouse with the possibilities of the information, the appropriate level of security can be determined.

Performance:

• A restrictive security plan will bring upon you the greatest penalty with the worst performance for the least restricted users.
• A loose security plan will provide the best performance with the most restricted users having the worst performance.

Usability:

• Securing the dimension in a data warehouse increases the usability.
• Your level of security only presents you with information that is permitted.
• List boxes and tree controls are manageable in size.
• Performance navigating the front-end application during query preparation may be improved over an unrestricted warehouse.

Types of Data Security:

There are actually three definable types of data warehouse security and a fourth type that is a conglomeration of all three.

Individual Security:

If your warehouse is accessible by individuals outside your company this is the most common type of security.

Group Security:

For an internal data warehouse this is the least restrictive security plan where information can be viewed within but not outside. It is also the rarest.

Hierarchical Security:

This is most often used in sales and marketing warehouses or in single subject area data marts where a person at any level can view information related to him or the information that is directly related to him. It provides an acceptable impact to performance and meshes with the corporate cultures of most companies.

Conglomeration Security:

For a secured enterprise data warehouse, this is the security plan where a person at any level can view details of their assignment, the assignments of their direct reports and summarized corporate level data.. These warehouses are the ones that require analysis of the feasibility and maintainability of the security plan.

Schema Implications:

• The design and specification of your security plan must be completed before you finalize the design of your data warehouse.
• Underlying database must be designed in concert with the database views, roles and partitions.

Maintenance:

Maintaining security in a database is difficult. Organizational changes can require rebuilding the entire security plan. Security views can degrade the performance beyond acceptable limits. Applications and interfaces may be required to administer the rights and levels of security.

Reporting:

• The report needs to include information about the missing components.
• Each report needs to be correct and appropriate when viewed in isolation.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Beware of Worse Oracle performance after Migration!!
   Efficient Recovery by Skipping Tablespaces!!
   Great Tips for improving join queries performance!!
   Amazing Tips to Fix Broken Oracle ASM Instance!!