FREE DM Review Site Registration!
Sign-up today and access DM Review on the Web!

Your FREE registration entitles you to:
FREE email newsletters

FREE access to all DM Review content

FREE access to web seminars, resource portals, our white paper library and more!

   

Multifaceted Data Access in the Data Warehouse, Part 1

Enterprise Architecture View

What types of data security controls does your BI environment offer?

Controlling user access to information in the organization is a major challenge in the data warehouse. Users may have many roles and responsibilities in an organization that allow them to view information at a very detailed level in one business area but at a very restricted level in another. Most data warehouses allow users to access information or perform analysis on their own while attempting to maintain control of the data presented based on the security profile of the operator.

This column is the first portion of a two-part series examining various access control methods that can be deployed in a data warehouse environment to ensure security, accessibility and integrity of this information resource.

Environments offer users many methods for accessing the information in the data warehouse, from dashboards, subscription reporting (push) and prompted reports using wizards for guidance to analytical cubes and ad hoc query. The data warehouse environment must provide security that controls access to the appropriate level of data for each user and functional area of information. Additionally, the environment must provide a recurring means of adding to and maintaining this security as the organization grows and changes. This presents a major challenge for the data warehouse architect to design access controls in a manner that avoids possible compromise of system security and exposure to unauthorized information. This is further complicated by trying to apply a single security model across the various delivery methods and products available to users in today's business intelligence (BI) environments.

As an example, a department manager runs a personnel report that should provide information on only the manager's reports or, in the case of an OLAP cube, a drill down should only display employees that roll up to that manager's organizational node. In another case, a user needs access to employees that directly and/or indirectly report to him across a multitude of organizational nodes. In some instances, the security model needs to be reactive and modify access based on changes to a user's responsibility (promotion) or employee status (leave or termination). Additionally, access to certain types of information deemed private or confidential may be granted to individuals based on their roles in the firm (compensation or affirmative action). Finally, the daily activities of a business need to continue unimpeded even though a member of the organization is unavailable due to an extended leave or other reason. This may require the security model of the warehouse to have the flexibility to allow a user to proxy some or all of his/her access to a peer or manager for a selected period of time. All of these types of scenarios illustrate how access control in the data warehouse can be a complicated task.

Access security to a data warehouse environment is usually implemented through the use of role- and row-level security. Role-level security provides general access to the data warehouse. It determines whether a user can access a particular table or report in the data warehouse environment. This security is different than database management security because it is used for controlling access through the front-end tools. Role-level security can be applied at a report, report link, folder or page level in a warehouse delivery environment. Assignment of users to roles can be controlled by the data warehouse environment or may be extracted from a source transactional system for security consistency (e.g., enterprise resource planning, customer relationship management). Examples include roles around management, compensation, payroll, human resources and planning. Users can have multiple roles that give them access to different objects or reports. The union of all the roles a user has is typically used in granting security in these types of cases. The reporting role security is typically defined differently than access to a source transactional system, making its use even more challenging.

Row-level security provides a greater level of security by restricting the data a user can view based on dimensional constraints. For example, row-level security can be implemented as a relationship between a user and an organizational node (e.g., departments). When a user requests information from the data warehouse, the list of organizational nodes a user has access to can be used to constrain the organizational nodes (list of employees in a company) he/she can view. In this example and in other row-level security models, a method to capture and apply exception to row-level security is needed. Real-world implementations are seldom this straightforward and may involve more than one constraint.

Part 2 of this series will explore some implementation examples of both role- and row-level security in the data warehouse.


Michael Jennings is a recognized expert with more than 20 years of information technology experience and speaks frequently on business intelligence/architecture issues at major industry conferences and has been an instructor at the University of Chicago's Graham School. He is a co-author of the book Universal Meta Data Models and a contributing author of the book Building and Managing the Meta Data Repository. He works for EWSolutions, a GSA schedule and Chicago-headquartered strategic partner and systems integrator dedicated to providing companies and large government agencies with best-in-class business intelligence solutions using data warehousing, enterprise architecture and managed meta data environment technologies (www.EWSolutions.com). He may be reached directly via e-mail at MJennings@EWSolutions.com.

For more information on related topics, visit the following channels:



Industry Vendors