Everything Oracle

Home

Everything Oracle

 

 

 

 

 

 

Introduction

 

In a previous article (“The ‘Evaluate’ Security Loophole”) we described the security implications of allowing end users access to the “Evaluate” function, and we illustrated the ease with which this security loophole can be used to gain access to privileged data or, even worse, to delete database data.

 

And in another article (“‘Evaluate’ Security Loophole – Client Perspective”) we found that, in general, it is not possible to close this loophole from the perspective of BI Server clients, such as Answers & Dashboards.

 

In this article, we’ll see how the loophole can be closed by enforcing security in the data source using a virtual private database (VPD), and we’ll look at two methods of configuring the repository to pass the context user group to the VPD.

 

 

Closing the Loophole in the Database

 

Just as we can’t close the “Evaluate” security loophole in Answers & Dashboards, so too we can’t close it in the repository.  The repository functionality doesn’t give us access to the contents of an “Evaluate” function call, making it impossible to determine if a user is authorised to make the call or not.  Even if we had this information, we would still face the problem that we would have no information in the repository on which to make a decision: since “Evaluate” is usually used to reference tables, columns, and functionality that only exist in the data source and which have not been imported into the repository, the security settings within the repository would be of no help in the decision making process.

 

So, the only place where we can close the “Evaluate” security loophole is in the data source itself.  How we do this will depend on the security structures available within the data source.  If the only security structure available is a view, then we would need to create and import different views for different user groups, which would lead to an overly complex repository and duplication in the presentation layer.  However, most modern databases support a virtual private database (VPD) / fine grained access control.  When this security structure is available it is possible to customize an incoming select statement by appending a where clause that depends on the privileges of the associated database user.

 

One problem with the VPD approach is that by default the OBIEE repository uses a single shared logon and a single query connection pool to connect to the back-end data source.  Doing so improves performance as the overhead of opening and closing a database connection with each query is eliminated.  However, this configuration can’t be used with a VPD, as, from the VPD’s perspective, all queries appear to be coming from the same database user.  In the next two sections, we’ll examine two methods of modifying the default repository configuration to make use of a VPD, (1) by creating one connection pool per user group; and (2) by passing in the context user group on a “per query” basis using a connection script.

 

 

Multiple Connection Pools

 

The VPD setup for this example uses the default Oracle schema “Scott”, and is the same as that described in the article (“Virtual Private Database – Fine Grained Access Control”) (we are using a policy function predicate of “0 = 1”, and, in addition, we are granting the select privilege on table “dept”). 

 

Users and Groups

 

In the repository we create two user groups, “Clerk” and “Controller”:

 

We also create two users, “Clerk1” and “Controller1”, who belong, respectively to these user groups:

 

In the physical layer we create two connection pools, “GQ Clerk” and “GQ Controller”, one for each user group:

 

Connection Pools

 

Connection pool “GQ Clerk” only has permissions for user group “Clerk”:

 

and it connects to the database as oracle user “clerk”:

 

Connection pool “GQ Controller” only has permissions for user group “Controller”:

 

and it connects to the database as Oracle user “controller”:

 

Note that for each connection pool the “Require fully qualified table names” check box has been checked.  By default when the BI Server generates an SQL statement it assumes that the database schema to which the SQL statement is being function-shipped is the owner of the tables referenced in the SQL statement, so that table names do not need to be qualified; for example:

 

If the check box is ticked, then the table names in the SQL statement are qualified by the name of the schema from which they were imported; for example:

 

In the present case, while the tables are owned by Oracle user “Scott” we are connecting to the database as Oracle users “clerk” and “controller”.  So these two Oracle users won’t be able to access Scott’s tables unless table references are qualified by the owner’s name (as an alternative we could create synonyms in each Oracle user’s schema for each table name).

 

The Controller’s View

 

Let’s log into Answers as “Controller1” and create a request to display the department name, employee name, and the employee salary using “Evaluate”:

 

When we run the request we can see the employee salaries:

 

As “Controller1” belongs to the “Controller” user group and as this user group only has permission to use the “GQ Controller” connection pool, it connects to the database as Oracle user “controller”.  The current VPD setup places no restrictions on the data that may be viewed by this user, and so the employee salaries are displayed.

 

The Clerk’s View

 

If we logon to Answers as “Clerk1” and create the same request as before, then when we run the request we find that we cannot see the employee salaries:

 

As “Clerk1” belongs to the “Clerk” user group and this user group only has permission to use the “GQ Clerk” connection pool, it connects to the database as Oracle user “clerk”.  The VPD setup uses column masking to restrict the data visible to this user, and so only null salary values are returned to the BI Server.

 

 

Single Connection Pool

 

The VPD setup for this example also uses the default Oracle schema “Scott”, and is the same as that described in the article (“Virtual Private Database – Fine Grained Access Control”) (we are using a policy function predicate of “0 = 1”, and, in addition, we are granting the select privilege on table “dept”). 

 

Connection Pool

 

The setup for this example uses the same users and user groups as its predecessor.  But this time we start off from a single connection pool with a shared logon using Oracle user “Scott” – the default repository configuration:

 

And all user groups have permission to use this connection pool:

 

But the VPD needs information as to which group a user belongs to in order to enforce the appropriate restrictions for each incoming query.  Since different queries come from different OBIEE users, but appear to the database to come from the same Oracle user, the context user group must be passed to the database on a “per query” basis.  The only method of doing this is to pass in the name of the group to which the user belongs with a before-query connection script:

 

Database Package

 

The connection script makes a call to procedure, “kp_save_user_group”, in package “dk_cp”:

 

The procedure saves the supplied user group name to a session variable, “s_user_group”.  The package also contains a function, “kf_get_user_group”, which can be used to retrieve the value of the session variable.

 

Policy Function

 

In the last example, our policy function used the context user name from “sys_context” to decide on which predicate should be used to construct the where clause (see the section entitled “Policy Function” in the article “Virtual Private Database – Fine Grained Access Control”). Now we need to amend our policy function to use a call to our database package instead:

 

The Controller’s View

 

Now if we use the same request as before:

 

then when we log in as “Controller1” we can see the employee salaries:

 

The Clerk’s View

 

However, when we login as “Clerk1” and try the same request the salaries are masked as column “sal” is defined as a restricted column within the security policy:

 

 

Caching

 

The caching of query results by the BI Server always has the potential to facilitate a security breach, in that a result set returned by a user with one security profile might be used to satisfy a request issued by another.  The repository provides a “Virtual Private Database” check box on the database “General” tab and a “Security Sensitive” check box on the session variable pop-up to allow cache entries to be tagged with security sensitive values, so that they can be partitioned into mutually exclusive groups when it comes to matching cache entries to requests.

 

However, identical requests originating from members of different user groups are allocated separate cache entries, and, in any case, requests that contain “Evaluate” function calls are not cached by the BI Server, so that additional measures to tag the associated cache entries are not necessary. 

 

 

 

Everything Oracle

Home

Everything Oracle

 

Copyright © 2009 PWG Consulting, All Rights Reserved