Monday, June 21, 2010

ApEx - using multiple database schema's

The parsing schema for an ApEx application

For an ApEx application, you can define one parsing schema per application. For smaller applications, this usually will be sufficient. For larger applications however, there might be the need to address multiple database schema's from one application. Because there will always be one parsing schema per application, you would have to grant the appropriate privileges to a schema in order to be able to use DML on all objects needed. This can be a daunting task, especially when you have many objects in various schema's.

The alternative
Instead of using only one application, you can use multiple applications. This may sound rediculous, but using more than one application, you can use a different parsing schema for each of these applications. The real trick is to get these applications to work as one. And this is really very simple. By explicitly setting the cookie name in the authentication scheme (via shared components), you can link various applications in a single workspace so they will use the same cookie. This way, you don't have to authenticate with each application, but authenticating once will give you access to all applications with the same cookie.

As a simple example, you can create two applications in the same workspace. Set the cookie name (and the path and domain if you like) to the same value (e.g. myApexCookie) for both applications. After authenticating with one application, change the application ID on the URL, and you should be able to access the other application without authenticating. Trying this without the cookie, Apex will ask you to login again.

Things to consider
You should consider your authorization scheme carefully when implementing this SSO for ApEx applications. Each application, and thus each parsing schema, should have an authorization in place in the application, so not everyone can access all schema's.
When creating different applications, it would be a developer's nightmare to maintain the navigation for each application. So consider creating one menu, referencing the right application and page for each menu item and then sharing this menu with all the applications.


  1. Hi Erik,

    I have implemented SSO using cookie management. It works great if I am using it for apex application in the same domain but as the domain changes the page sentry function is not able to get the cookie. Can you help me on this? Is there anyway to mention where the cookie is stored and read from that location.

  2. Hi "Anonymous",

    If you are using different domains, then I assume that you are trying to link multiple application servers. This probably means that you will have more than one database behind these application servers. And that is where the trouble starts...

    The way cookies are used and session ID's are assigned, you cannot use the technique described in this post. When you login, a session is created and the details are stored in the database (FLOWS_030100.wwv_flows.sessions$). This table contains (among others) the session ID you'll also see in the Apex URL. You will get a session cookie in your browser, and this cookie will contain the hashed session id and the name of the cookie will contain the security group ID (of the workspace) and the application ID.

    Cookies from the browser will be sent to the hostname/domain that originally created it (this is standard browser functionality). This way, the server will know what session ID you got assigned, look it up in the database table and verify that you own a valid session.

    This would also mean that if you create the cookie with the right name and the right session ID as content, you should be able to persuade the server to accept your session and connect to the database, as long as that session already exists (because you connected succesfully before). In the database table, there is also a column ip_address and remote_addr, which suggests that this session will be valid for your computer only.

    Because the session is stored in a database table, you can only sign in to the same database, and this kind of defeats the original assumption of having more than one domain, because you would use this if you have more than one database...

    Like with Portal SSO, you could also define a partner application and use the original username/password to login to the other domain(s), but then you should configure the domains to contain the same users (and passwords) or use a single source (LDAP) for authentication.

    If you have any information of what you are trying to accomplish, I can delve a litlle deeper into that scenario specifically.

  3. Hi Erik,

    Thanks for the reply!
    You are correct...I have different databases and different application servers. The way I have designed it is : Have one application SSO_APP where the user is authenticated against the Microsoft Active Directory(where all username/password are stored). Once user is authenticated I send the cookie with the name SSO_LOGIN_SESSION and after that whenever user try to access other application he will click on the link provided on the home screen of SSO_APP and he will be redirected to that application. Now in that application a Page sentry function executes which looks for the cookie SSO_LOGIN_SESSION. If cookie is present user will be logged in. Yesterday I set in the owa_cookie.send call domain value to the SSO_APP domain value and it has started to work for two domains....but I am not sure this is the correct implementation...could you please help me understand why we set domain value in owa_cookie.send? And can we use it to login into multiple applcations in different domains?

  4. Hey Erik,

    I referenced the following document for implementation:


  5. Hi "Anonymous",

    The above article is a great way of creating a SSO implementation for Apex. However, for multiple domains, the major problem would be sharing the cookie. As stated in the article (just before the discussion of the Single Sign-Off) this will work for a single domain:

    "With the SENTRY function added to all of the suite’s authentication schemes, any application *in the same domain* as the suite cookie will respect the single-sign-on functionality"

    As far as I know, a cookie set by one domain will NOT be available to another domain. So, the suite cookie that is set by your SSO_APP will only be available to other servers within the same domain. Cookies are stored by your browser as they are received from a server. When communicating with the server again, the browser will send the cookies for that server or domain. So, when connecting to a different domain, these cookies will not be available (and authentication will fail).

    You say that "it has started to work for two domains". I'm not entirely sure what you mean by this. Do you mean you try to create a cookie with domain=dom2 from a server in domain dom1? I'm no cookie expert, but this seems not possible to me by design (or at least, it should be). Otherwise, any site can set cookies for other sites. Like setting cookies for or other sites that should not be related like that.

    Setting the domain for a cookie like you can in Apex, will restrict the cookie sending just as setting the path will. See also "" for an example. This means that you can further restrict the sharing of cookies, but not broaden it.



  6. Further thoughts on this:

    The only way I can think of to access multiple domains is to make use of the most basic form of SSO (somewhat like the Oracle Portal Application SSO): create the URL including username and password for the partner application. If you look at the source code for a standard Apex Login page, this page contains a few fields besides the username and password fields. Using these in combination with the target, you can easily login with a single URL.

    The stripped version of a login page might be something like this (form action and onclick or the buton are modified, the rest is taken from the actual login page):
    <form action="http://hostname:port/lnvvjo01/wwv_flow.accept" method="post">
    <input type="hidden" name="p_flow_id" value="12345"/>
    <input type="hidden" name="p_flow_step_id" value="101"/>
    <input type="hidden" name="p_instance" value="5125877480153145"/>
    <input type="hidden" name="p_page_submission_id" value="3495215283124713"/>
    <input type="hidden" name="p_request" value=""/>
    <input type="hidden" name="p_arg_names" value="34778070516994496"/>
    <input type="text" name="p_t01" size="40" maxlength="100" value="abc_admin"/>
    <input type="hidden" name="p_arg_names" value="34778165951994496"/>
    <input type="password" name="p_t02" size="40" maxlength="100" value="abc_admin"/>
    <input type="button" onclick="javascript:document.forms[0].submit();" value="Inloggen"/>
    <input type="hidden" name="p_md5_checksum" value=""/>

    And this can be converted to an auto-submit form (by including an onLoad trigger, replacing the button). Or worse: you can supply all the fields on the URL, creating a URL with clear-text password and username. This obviously is NOT a recommended way to do this!
    But with the right attention, this can be used in conjunction with HTTPS and the above POST request to make it somewhat more secure. Your SSO_APP should generate these pages for signing in to another domain. Again: be extremely carefull with this, as you are transmitting all kinds of sensitive information!

    Have fun! ;-)