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.
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.
WHERE OPPTY.NUM IN (
WHERE OPPTY_TEAM.PERSON = :PERSON)
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:
- Create the OPPTY_TEAM as a opaque view
- SELECT OPPTY.NUM,’Y’ FROM OPPTY_TEAM GROUP BY OPPTY.NUM
- Physical join the OPPTY table with OPPTY_TEAM
- Add the security filter using the constant “Y” on the opaque view
- 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.