OBIEE and Row-Level Security

During the past week I've been working on getting row-level security working with Oracle BI Suite Enterprise Edition. Like most OBIEE topics, typing the subject into google brings up zero results and so my first port of call was the online documentation on OTN. I'm not a particularly big fan of the online docs for OBIEE - in my opinion, they seem to tell you a lot of detail without actually telling you what it is you need to do, and unfortunately picking a broad subject like row-level security - setting the system up so that User A gets to see a different set of invoices, say, compared to user B, without actually writing different reports for them - is just a bit too general for the docs as they stand at the moment. So, I metaphorically rolled my sleeves up and did a bit of digging.

Sifting through the docs, and remembering back to the various Siebel Analyics and OBIEE presentations I've been to, there are many aspects to security with this suite of products. OBIEE is always presented as being ready to integrate with LDAP servers such as Oracle Internet Directory, and it's also being positioned as being compatable with Oracle Single Sign-On. When you work with BI Administrator and select Manage > Security from the application menu, you can set up users and groups, and there's a similar function available in Presentation Services, which is kept separate as it's possible that users may deploy their own query tools (BO, Cognos etc) on top of the BI Server. Looking through past presentations on Siebel Analytics Server, you can also set up security using things called Initialization Blocks and Variables, which seems to set up table and column access on a dynamic, retrieved-from -a-database basis. So where do I start when I just want to set up simple users and groups, and have them have different filters applied to the core tables in my repository? Where does LDAP come in, can I authenticate using database usernames and passwords, what's the best way to approach this?

In the end, all of these security features come down to two key aspects: Authentication, and Authorization. Authentication is the process of determining a user's identity, and in OBIEE can be done by the BI Server, or by using an external authentication method such as an LDAP server, a table of usernames and passwords in a database, or by using a database such as Oracle to check a submitted password against it's list of usernames and passwords. By default, OBIEE stores its own list of usernames and passwords and checks incoming credentials against this store. As an alternative, you can have an LDAP server such as Oracle Internet Directory or OpenLDAP do the authentication, or have a database do this, but all they're doing is checking and OK'ing a password - it's the Authorization process that then determines what resources that user can access.

For my purposes then, to create a row-level security demo, the authentication part is not the key element. For my purposes, I'll just have BI Server do the authentication, then if I get time later, I'll tie it in with an LDAP server (the more obvious choice, database authentication against database usernames and passwords, apparently doesn't play well with Oracle Delivers). Assuming we've got authentication covered, then, how do we then go about applying row-level security?

From looking through the docs and online training material, it appears to me that there's two main ways of placing users in to groups and assigning table filters to those groups. One way, as demonstrated in the OBI EE Oracle By Example exercises, is to have the BI Server perform lookups on database tables that are then placed in to session variables and used to create dynamic filters on tables. A more simpler approach, to my mind though, is to create the groups explicitely using the Security Manager in BI Administrator, add filters to those groups, and assign users to those groups. This has the disadvantage of making you go through another step in setting up these users and groups in OBIEE (when they may already be stored in database tables, or in an LDAP server) but it's a simple, straightforward method that will probably cover most customers' initial implementations. Later on, when we're all comfortable with how things work, I presume it's fairly straighforward to sync these internal BI Server users and groups with master information in an LDAP server, and also to dynamically generate the table filters - but that's for another day. For me, I wanted to get simple row-level security set up, and also leave the door open to doing it in a more "enterprise" way later on.

The first step then was to go in to the Security Manager in BI Administrator and set up the groups I wanted to create. As I'm using the migrated Videostore data, I set up Central, Eastern, Western and Management groups, like this:

Then, I created four users, and assigned them to groups in the following ways:

• Mr A West, assigned to the Videostore_Western group only
• Miss B East, assigned to both the Videostore_Eastern and Western groups
• Miss C Central, assigned to the Videostore_Central group
• Mr M Superuser, assigned to the Videostore_Managers group

Then, I created filters on each of the groups. The Videstore_Western group could only see data in the Store folder for stores in the West region, Eastern could only see data for East and so on, like this:

As Miss B East belongs to two regions, hopefully she should see data for these two regions on her reports. The Superuser user should see all data, as the Videostore_Manager group doesn't have any filters applied to it. Finally, I set up the dashboard so that all users can see the Regional Analysis page, but only Managers can see the Overview page, like this:

OK, so lets go over to Answers, log in as A West who should only see data for West region, and then run a report.

Ok, so far so good. What about for B East, who should see data for both East and West region?

Excellent. Running the same report for C Central (who should only see Central data) and for M Superuser (who should see all) returns the expected results.

Looking at the logical and physical queries in the NQSQUERY.LOG file (I've got all users running at logging level 2), I can see the original query issued by the user, with no filters applied, turned in to the physical query against the VIDEO5 schema with the table filters tacked on (and in the case of the B East user, turned in to an IN() clause as more than one filter is applied):

-------------------- SQL Request:
SET VARIABLE QUERY_SRC_CD='Report';
SELECT Store.City saw_0, Store.Region saw_1,
"Sales fact view".Sales saw_2
FROM Videostore ORDER BY saw_0, saw_1
+++b_east:320000:320004:----2007/05/13 15:57:35
-------------------- General Query Info:

Repository: Star, Subject Area: Videostore, Presentation: Videostore
+++b_east:320000:320004:----2007/05/13 15:57:35
-------------------- Sending query to database named

video_ORA10g (id: <<38848>>):
select T8114.CITY as c1,

T8114.REGION as c2,

sum(T8123.SALES) as c3

from

STORE T8114,

SALES_FACT_VIEW T8123

where  ( T8114.STORE_KEY = T8123.STORE_KEY

and (T8114.REGION in ('East', 'West')) )

group by T8114.CITY, T8114.REGION

order by c1, c2

In fact, its a process very much like Virtual Private Database in Oracle, where the database this time adds an extra predicate on to the SQL query to enforce row-level security, except in this case it's the BI Server that adds the extra predicate.

Moving over to the dashboard now, I log in as the Miss C Central user and see what I can view. As expected, they only get to see the Regional Analysis page of the dashboard, and they're limited to just the Central region for their analysis:

Whereas if I log in as M Superuser, who's not part of any regional group but is part of the managers group, I can see both pages of the dashboard (the Overview pages is only visible to manager, whilst the regional page is visible to all regional users and managers), and on the regional page, I can see all regions, like this:

Not bad. Now obviously, in reality you're not going to want to set up a whole new security regime within OBIEE, you're going to want to bring in users and roles, and ideally filters, from your existing identity management setup, and if you're a migrating Discoverer user, you'll want these users, roles and ideally E-Business Suite responsibilities to be picked up from the database and from Oracle Applications respectively. From working through this example and through the docs, the way I'd see this happen is the following:

1. Firstly, you'd either create your users in Oracle BI Server either yourself, or you'd use the LDAP import feature in the BI Administrator tool to have these created for you automatically - I'm not sure whether this applies to both groups and users, or just users. Discoverer users who currently use database users and roles will have to create these manually in BI Server (or through the OBIEE API) as there's no "import users and roles from databases" utility, or not as far as I can see.
2. For each of the groups you've created, either explicitely add folder filters using the OBIEE Security Manager, or (and this is an area I'm a bit hazy on) create the filters dynamically using initialization blocks and session variables
3. From that point onwards, the BI Server applies the filters automatically for you, in a similar way to Oracle VPD, regardless of the query tool used (Oracle Answers/Dashboard, or any other query tool plugged in to the BI Server ODBC interface

For Discoverer users migrating to OBIEE, the only fly in the ointment is if they still want to have the Oracle database authenticate users, but they also want to use Delivers. From reading the docs, there's an incompatibility between Delivers and database authentication (it's all to do with something called user impersonation, Delivers "impersonates" the user when talking to the scheduler which won't work when the database performs the authentication), but given that Discoverer seems to be a bit of a halfway house between database authentication and LDAP authentication anyway (the OID LDAP server performs the SSO authentication, but then data authentication is done by database users and password) this seems as good a time as any to move the whole shooting match over to LDAP and be done with it.

For my next trick, the neat thing now would be to link this in with Oracle Internet Directory (as the LDAP server) and all the new super-sexy Oracle Identity Management tools - the Thor Xcellerate stuff, all the provisioning tools and so on - to make authorization and authentication on my whole Oracle Fusion Middleware setup a "one-stop-shop". Don't hold your breath, as I also need to get all the SOA Suite stuff set up, including the Order Bookings demo, in time for a workshop the week after next, but at least I've got a clear path set out now - although of course if you've done this before, and you've got a better or simpler way, make sure you add a comment and let me know.