Many aspects of data warehousing, including source system analysis, backup architecture and configuration of front-end tools to support business intelligence initiatives, require careful planning. One area, however, that often gets little attention during the planning stages of data warehousing is security. In operational systems that support business functions such as finance, manufacturing and order management, the users accessing the system all work within a particular part of the business, resulting in a simple security requirement. All the rules change in data warehousing, however, since it involves creating a single source repository that contains data from numerous areas within an organization. This is essential since most high-level decisions within an organization require analysis of data from all across the enterprise. This article presents a proven methodology for implementing security within such data warehouses, supporting functional access as well as high-level reporting that spans multiple functional areas.

Functional Area Breakdown

The first step of the methodology involves determining the high-level functional areas that comprise the scope of the data warehouse. A given data warehouse, for instance, may contain data associated with the business functions of human resources, finance and order management. Typically, users within the order management function would not be concerned about human resource data and vice versa; therefore, the first level of security access would be by functional area. Thus, an order management user would be granted access to order management information but not to financial information. On the surface this may seem sufficient, but more careful consideration reveals that within a given functional area, the security architect must consider user roles within a given functional area.

Role Determination

The second step of the methodology involves breaking the functional areas into detailed job roles. For example, the human resources manager may be able to access salary information for reporting and analysis; however, the human resources clerk would not be allowed to see confidential information such as salary and annual review data. Defining the functional areas and detailed job roles is typically a joint effort between the security architect and the business analysts signing off on the initiative. Within the financial arena, for example, this may result in four roles including controller, financial manager, financial analyst and accounts receivable clerk.

User Specification

The third and obvious step involves defining the users who fit into each job role. A security architecture outline should be constructed to support the results of steps 1 through 3 of the security methodology. The document should include three key components: the functional areas within the warehouse, the job roles within each area and the users who make up each role. Figure 1 provides an example of a security architecture document for the fictitious XYZ company.

HR Manager
Jim Brown
Teresa Livingston
HR Analyst
Peter Nance
Libby Wilson
Tyronne Peters
Mitchell James
HR Clerk
Janice Smith
Pedro Sanchez
John Wong
Nancy Brown
Bill Robbins
Clark Williams
Finance Manager
Paul Madison
Jody Hart
Mark Wood
Finance Analyst
Julie Ford
Jill Ross
James Simpson
A/R Clerk
Bonnie Woo
Bob Jacobs
Order Entry Analyst
Marty Williams
Maxine Parker
Order Entry Clerk
Calvin Baker
Jacob Smythe

Figure 1: Security Architecture Outline for XYZ Company

Data Determination

The fourth step involves the functional area owners determining which business information is appropriate for each role within the functional area. This involves answering difficult questions such as which roles within human resources should be able to view salary and review information. A key point for clarification is that a different functional owner(s) would typically be outlining the security implementation for each functional area. It is also important to note that the functional owner would know which data should be accessible by which business role; however, this is drastically different from outlining how functional breakdowns correlate to particular physical areas or tables within the data warehouse.

Technical Translation

The fifth step requires that the technical staff (typically the database administrators and developers) translate these business requirements into physical design specifications. This is essentially asking, for instance, which tables within the database can be viewed by the HR clerk. One further consideration is whether a given user role should just be able to read or view the data or whether they should be allowed to make changes to the data. The resulting document outlines each of the detailed roles and the specific access that they should be given to view or manipulate data within the data warehouse. A sample of this final document is illustrated in Figure 2.

Select (Read) data within the HR_EMPLOYEE table
Select (Read) data within the HR_SKILLS table
Select (Read) and Update data within the HR_EMPLOYEE table
Select (Read), Update, and Insert (Add) data within the HR_SKILLS table
Select (Read) and Update data within the HR_EMPLOYEE table
Select (Read), Update, and Insert (Add) data within the HR_SKILLS table
Select (Read), Update, and Insert (Add) data within the HR_EVALUATION table
Select (Read) and Update data within the HR_CONFIDENTIAL table

Figure 2: Detailed Security Architecture for XYZ Company

Cross- Functional Reporting

Within the data warehousing discipline, we must finally consider the security requirements for senior managers or analysts who develop reports that span multiple functional areas. Normally, this access would only allow for reading of the data and not for manipulation of the data. An additional section of the detailed security architecture for XYZ Company is illustrated in Figure 3.

Select (Read) data within the HR_EMPLOYEE table
Select (Read) data within the HR_SKILLS table
Select (Read) data within the HR_CONFIDENTIAL table
Select (Read) data within the HR_EVALUATION table
Select (Read) data within the OM_ORDER_MSTR table
Select (Read) data within the OM_ORDER_DETL table
Select (Read) data within the OM_SHIPMENTS table
Select (Read) data within the OM_PAYMENT_TERMS table
Select (Read) data within the GL_JOURNALS table
Select (Read) data within the GL_BALANCES table

Figure 3: Cross-Functional Reporting Section

By following this methodology, the architecture will be flexible enough to answer the critical questions needed for companies to gain return on their data warehouse investment. The success of this security approach is truly dependent on two elements: enlisting the help of the functional owners in determining the business requirements that the security architecture must support and accurately translating these business requirements into technical specifications related to the physical structure of the data warehouse. The planning of the security architecture should begin during the requirements phase of the data warehouse initiative. Too often data warehouse teams do not begin addressing the topic of security until a few days before the warehouse becomes "production." As a final word of caution, several of the steps in the methodology may be time-consuming. Those of us in the IT community know never to underestimate the effort involved in translating business requirements into technical specifications.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access