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.
|
|
Regards,
John Garmany
Burleson Oracle Consulting
Kittrell, NC, USA, 27544
www.dba-oracle.com
www.remote-dba.net
|
|
|
