Oracle Apps Server Tips

Mark Rittman

Integrating Discoverer 9iAS SSO Logins With 9i Database Logins

"When using Discoverer 9iAS (the one that comes with 9iASr2) and public connections, the row-level security i'd set up on the Oracle database doesn't work any more. How can we integrate the SSO logins and discoverer logins such that our database row-level security now works?"

I had a similar situation happen with a recent client, and this was my take on it.
The userids and logons that you get with Discoverer 9iAS (that is bundled with 9iAS Release 2) are single sign-on logins that work across all of the 9ias technology stack (portal, forms, reports, disco etc). They are separate and distinct from the database logins that you get with Oracle 9i, although (i'll come on to how later) it is possible to integrate the two.
In the past, when a user logged in to Discoverer (i.e. Disco 3,4 and the version that comes with 9iAS Release 1), they are effectively logging into the database, and if you have them log in to their own schema, as opposed to a central, shared EUL login, you can grant them access to the data warehouse tables through a combination of SELECT grants and public synonyms, and you can then implement row level security either through implementing Virtual Private Databases, or actually making the public synonyms map through to views over the warehouse tables, with the views having WHERE clauses in them to act as filters against the data.
With Discoverer 9iAS R2, instead of users directly logging into database accounts, they now have the concept of public and private connections. Public connections are set up by the system administrator, using the EM Website, and encapsulate a predefined userid, password and database connection, which is then presented to the user as a hyperlink which they can then click on to get direct access to Discoverer, without entering their own userid and password. Public connections are useful if you want everyone to access discoverer using the same userid and password, but as you've found out, as they don't log on with their own credentials, their filters are not applied to the data warehouse tables - everyone gets to see the same view of the data.
The way round this is to get users to create their own private connections, and they can create these at the Discoverer Plus or Viewer logon screen by clicking the 'create private connection' button. If SSO has been enabled for Discoverer (not the default setting), they first have to enter their SSO userid and password before this screen comes up; if SSO hasn't been enabled, their SSO userid and password is not asked for, and they can create private connections by just accessing the Discoverer Viewer or Plus homepage (http:<server_name>:port/discoverer/plus for example). One thing to bear in mind here is that, if SSO has not been enabled, the private connections users set up are only stored on that particular PC, through a stored cookie, and if they move to a new PC, it will not be available - enabling SSO means that their private connections move with them to whichever PC they 'single sign-on' to.
Therefore, to use your single sign-on login to filter data in discoverer, the key is to create a private connection for the user, and in this private connection, enter their database login and password. The private connection will then log them in to Discoverer, and the data will be filtered according to the row-level security you have set up in the database.
One twist to this is if you are using Discoverer Portlets to display workbooks and graphs to users in Oracle Portal. When you set these portlets up, you have to specify a public or private connection that the portlet will use to retrieve the data (private connections are only made available if you've enabled SSO for Discoverer). If you specify the private connection for the page owner when creating the portlet, the 'View Worksheet' link at the bottom of the portlet will try and connect to Discoverer Viewer with the page owner's credentials - which is not what you want if you require Discoverer Viewer to connect using the portal user's credentials.
There are two workarounds for this; firstly, when creating the portlet, you can specify that, for logged in portal users, the portlet will use one of the portal user's connections to display data in the portlet, and then the 'View Worksheet' link will log them in to Discoverer Viewer using their own credentials. The downside with this approach is that the user (a) must have a suitable private connection set up in advance, and (b) they have to conciously go through the process of 'activating' the portlet by choosing one of their connections, which may not suit the type of user you're rolling out the portlets to.
Another approach that we found worked was to disable the 'View Worksheet' link from the Discoverer Portlet, and then add a custom 'Query by SQL' Portal Report portlet under the Discoverer Portlet, that simulates the Discoverer Viewer URL that the portlet would have provided, but in this case substitutes the portlet creator's userid with the logged in user's userid, by using the PORTAL.WWCTX_API.GET_USER pl/sql function. They still need to have created a private connection that matches the connection name used by the Discoverer Portlet creator's connection, but the process of linking through to Discoverer Viewer is then 'seamless' to the user. The downside of this approach is (a) you still need to have gone in beforehand and created a private connection for each user, which means logging in as them, then creating the connection using their database login and password, and (b) your Discoverer portlet now comes in two parts which you need to bundle together each time.
Looking to the future, whilst the Discoverer Portlet issue isn't probably going to change, my understanding is that there are plans by Oracle to make it possible to integrate the logins for the database and the application server in the future. If you license the Advanced Security option for the Oracle database, you can store user logins and passwords in an LDAP directory, which it's possible to synchronise or even merge with the LDAP directory used to store SSO logins and password, by setting up the EUS feature in Advanced Security to work against the SSO LDAP directory, and then using OIDDAS or any other OID interface to manage the directory. With 9iR2 and 9iAS 9.0.2 this should work but is unsupported by Oracle; my understanding is that it should be much easier to set up using 9iAS 9.0.4, and in 10g you'll get a much more integrated solution.




John Garmany
Burleson Oracle Consulting

Kittrell, NC, USA, 27544 





Burleson Consulting
Kittrell, NC, 27544

Email: Phone (800)

Copyright © 1996, 1997, 1998, 1999, 2000, 2001, 2002 by Burleson , Inc. All rights reserved.