JainSys Inc. The basic out of box security available with OBIEE is via the administration tool. To administer the users/groups one logs in to the RPD and open Security Manager (Manage/Security). Groups, users and their relations are managed via this.
The drawback to this method is that every time we need to add users or manage groups, this becomes an admin task involving IT. In an enterprise application, there are other means of maintaining user access like SSO or custom admin screens which allow for the designate admin roles in the user community to manage access to users. This generally runs off of a custom profile implementation.
A typical layout for this type of security implementation is done via a set of three tables:
- USER
- ROLE
- UserInRole
To take advantage of this in OBIEE we have to configure the system to query the database on login. The steps required to do so are as follows:
- Setup database connection.
This Connection pool will be used later in the process.2. Once we have the connection pool ready we now need to set up an Initialization Block. An IB is executed every time a user logs in and a session is being created. The idea is to get teh User name and password passed on through the login page and use that information to query the database and authenticate the user.
3. Open the RPD and Create an new IB by clicking the menu
and then on the Variable Manager :
We need to do this on a "Session" IB not a "Repository" as this will be done as each user logs in.
Select the Data Source Type as "Database".
(If you do not see Database as an option then you may not have a valid ODBC or Oracle client on the server. If you have been able to complete the import step above then this shold not be an issue. Else this is about time you install a client to connect to the data store.)
4. Initialization string: We now need to define a query which will take the UserId and Password passed on to the login screen and check the tables to see if this user exists and have access to this application or not. This is also our chance to grab items from the user profile like Display Name, groups they are assigned to etc.
Typical sql at this point is:
select username, grp_name, SalesRep, 2 from securitylogons where username = ':USER' and pwd = ':PASSWORD'
:USER is a way to indicate that the value of that variable will be substituted on run time.
5. Variable Target: This section allows us to define some variables to contain the results. There are some predefined session level variables:
USER ; PASSWORD; DISPLAYNAME; LOGLEVEL; EMAIL etc
See this for details
The variables should be lined up in the order they are being returned from the query to allow proper assignment.
Check the "Required for authentication" check box to indicate that this IB is used for authenticating users.
Save.
6. Testing : The logs for any issues with this test can be found at {OBIEE Dir}\OracleBI\server\Log\NQServerLogs.log.
I kept getting :
[nQSError: 13011] Query for Initialization Block 'Authentication' has failed.
[nQSError: 17001] Oracle Error code: 911, message: ORA-00911: invalid character at OCI call OCIStmtExecute.
[nQSError: 17010] SQL statement preparation failed.
because I had the semicolon at the end of the query. Apparently statement preparation did not like it.
Once there is data in underlying tables, this should be a easy!
7. Next tasks: setting up groups and managing RPD level and Report level security. That's calls for another blog i guess!! Sachin
JainSys Inc.
