Continue in 2 seconds

Multifaceted Data Access in the Data Warehouse, Part 2

  • February 01 2004, 1:00am EST

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

This column is the concluding portion of a two-part series on delivery of historical information in the data warehouse environment. This installment examines access control implementation methods for addressing this business requirement. In the first part of this series, we saw that various access control methods can be deployed in a data warehouse environment to ensure security, accessibility and integrity of this information resource. Now we will explore a possible implementation scenario.

For our implementation example, we have three types of reporting options. Pull reports allow authorized users to request information from the data warehouse environment on their own as needed using predefined report templates. Pull reports are selected by users through the reporting tool front end with processing in real time, typically in minutes, against the data warehouse environment. Push reports deliver information to users on a scheduled basis using predefined report templates. Push report processing occurs during scheduled batch cycles, and results are published to users through the reporting tool front end. Both push and pull reports are organized into functional categories or folders to meet business and role-level security needs. Role-level security is typically assigned to the reporting options and folders. Ad hoc reporting is the third option.

Role Security

Roles are defined as a set of reporting items associated with a specific reporting option (push, pull, ad hoc) with access to particular catalog of report folders, reports, queries links or combinations. Access to these reporting groups is accomplished by assigning roles to user IDs in the data warehouse environment. This association determines the reporting options and/or reporting categories a user can see. A manager may have role access to certain sets of pull reports such as employee information or time and attendance, but not to salaried planning reports. Only a limited set of specially trained users may have role access to ad hoc reporting due to the wide-ranging information and analysis capabilities inherent with this option.

All information about report tool types, user roles and reporting folders is stored in the reporting tool meta data store. A user would either access the data warehouse reporting environment directly or through some type of company or application portal (see Figure 1). The user ID of the individual would be validated directly against the reporting meta data store or passed through a Web variable (e.g., remote_user) from a single sign- on or other upstream application, and a determination as to whether the user can access any reporting options is made. In many data warehouse implementations, access to reporting is limited to specific functional users or managers due to the confidentiality of the information and/or the cost per user of the reporting tool. Once general access is validated, the type of reporting access is determined based on the roles assigned to the user ID. Reporting type access is validated against the reporting meta data store. Finally, determination is made as to which report categories or queries the user can access again based on his/her collective role assignment. For example, a line manager and a corporate pay administrator may have access to a variety of reports and information. Exactly what information users will see in particular reports is determined by their row-level security.

Figure 1: User Access to the Data Warehouse Reporting Environment

Row Security

Row-level security determines the information a user sees in a particular report. For example, per row-level data access constraints using department IDs, two line managers with the same role-level access would get different data results because they manage different departments. Typical methods of implementing row-level security in a data warehouse environment include utilizing organizational structure, lines of business, customers and geography. These methods of determining row-level security can also be used in combination. The subject area used for implementing row-level security needs to be able to be tied to all data in the data warehouse. Let us look at an example of how this would work.

Figure 2 outlines an example location structure for a company. We want to implement row-level security for all reporting options based on this location structure. This will require establishing a row-level security table built on this location structure. The row-level security table will be refreshed on a daily basis to capture any changes in the hierarchy. A partial view of the security table would look like Figure 3.

Figure 2: Example Location Structure

Figure 3: Example Partial View of Security Table

The corporate administrator would have access to all locations based on the entries in the row-level security table while the east region administrator has a more limited view of the locations he can see. To see how this security table would be used with reports and queries, let's look at the following pseudo SQL statement.

SELECT a.payroll_column1, a.payroll_column2, a.payroll_column3
FROM payroll_info a, row_levelsecurity b
WHERE a.location = b.location and b.userid = '001111'

The value of the authenticated user ID of the individual accessing the data warehouse reporting environment would be passed directly into the SQL statement (e.g., "001111") before execution through access to a Web variable for pull and ad hoc reporting. Scheduled reporting would access the row-level security table and populate the user ID into a secondary constraint table or other means.

The business need to restrict access to information from the data warehouse environment can be met through a combined application of role and row-level security to reports and queries. The method outlined provides an actual means of meeting this requirement but requires additional work to establish the roles and proper row-level constraints in an operational mode. Finally, in order for this to work, the reporting delivery tool/s must be able to easily access the row-level security table and the login ID.

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