Dylan's BI Study Notes

My notes about Business Intelligence, Data Warehousing, OLAP, and Master Data Management

How to support Team based security

Posted by Dylan Wan on December 16, 2015

Team-based security is referring to a specific data security requirement scenario.

Team-based security means that the object being secured has a “team” associated with it.  The team members can access the objects.

M:M relationship

There is a many-to-many relationship.   A person may be added to multiple teams.  A team can be multiple members.

In Siebel Analytics, the team-based security is also called visibility support.  It is a common requirement to secure Accounts and Opportunities.  The business requirement is that sales from different teams should not be able to see each other accounts or opportunities to avoid or somewhat encourage internal competitions.

EXISTS / IN subquery

If we look at the SQL generated, the data security filter can be implemented as a subquery.

SELECT OPPTY.NUM
FROM OPPTY
WHERE OPPTY.NUM IN (
SELECT OPPTY.NUM
FROM OPPTY_TEAM
WHERE OPPTY_TEAM.PERSON = :PERSON)

Concerns

The major concern of supporting team-based security is the query performance.  Even the “equal filter”, like Multi-Org support in EBS, has overhead and need to carefully designed to minimize the performance overhead.  The EXISTS or IN subquery has much higher overhead.  For this reason, Siebel Analytics disabled the team-based security OOTB.

The other concern is the grain of the data.  If the team table, the OPPTY_TEAM table, cannot guarantee return one record per opportunity, we may have double counting issue.

How to implement this in OBIEE

Data Security access list in OBIEE was implemented as session variables.  To avoid the subquery, the list of object instances is fetched and cached in the session.  So the IN subquery can be transformed to IN cached list.

However, both ACCOUNT and OPPTY are something that cannot be cached since the number of object instances is huge.

Here is a design pattern we developed:

  1. Create the OPPTY_TEAM as a opaque view
  2. SELECT OPPTY.NUM,’Y’ FROM OPPTY_TEAM GROUP BY OPPTY.NUM
  3. Physical join the OPPTY table with OPPTY_TEAM
  4. Add the security filter using the constant “Y” on the opaque view
  5. Add the security filter to OPPTY logical table

The approach can avoid the double count issue.  However, performance overhead is still a big issue.

The opaque view cannot be cached as the data is context specific.

The query has to pass through all the way to the database.

 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s