Continue in 2 seconds

Multifaceted Data Access in the Data Warehouse, Part 1

  • January 01 2004, 1:00am EST

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.

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