APEX, Oracle Maps and Secure Mapping

One interesting feature in the latest MapViewer kit (10.1.3.3 for WebLogic Server) is the built-in support for secure mapping.

So what is it? Basically, it is a way for you to pass the name of an authenticated Web user to a database context where a theme's query will be executed. Your database session can then utilize this piece of information to filter or modify what the theme's query returns. As you probably already know, every MapViewer theme (pre-defined or JDBC/dynamic) results in a SQL query being executed in the database. More specifically, MapViewer obtains a JDBC connection from the connection pool associated with a data source and executes the query in the session of that connection.

Since it is a shared connection pool, how do you pass a web user's name to each theme's connection or DB session? The way MapViewer does it, is to always execute a 'before' and an 'after' PL/SQL procedure around the actual execution of the theme query. In the 'before' PL/SQL procedure, MapViewer passes in the currently logged in Web user's name (or role, or any piece of info you desire). What the PL/SQL procedure does with this piece of information, is up to you, the author of this PL/SQL procedure. You could, for instance, set up some VPD policy or a view that filters the query result based on the web user name. The 'after' procedure simply lets you do some clean-up if necessary.

So where does MapViewer get this authenticated Web user in the first place? Typically it gets this piece of information from an authenticated J2EE session (that MapViewer can access with each and every incoming map request). Now, what happens if your user authenticates to APEX (Oracle Application Express), then communicates directly with the MapViewer server? In this case, there is no separate authentication with the J2EE server that MapViewer is running on, and the APEX authenticated user is not visible to the J2EE server (hence MapViewer). Don't worry, MapViewer also lets you pass an authenticated Web user's name through a Cookie. So in order to pass the APEX authenticated user's name to MapViewer, you simply setup a cookie within the APEX context so that this cookie accompanies every request going to MapViewer.

So how do you tell MapViewer to pick up this web user name from a named cookie (instead of the default place which is a J2EE session)? You do so by adding a few attributes to the MapViewer data source definition. In fact, you also mention the PL/SQL package name (containing the "before" and "after" procedures) in the same data source definition. Here is an example:

<map_data_source name="mvdemo"
jdbc_host="stadb32.us.oracle.com"
jdbc_sid="mv"
jdbc_port="25650"
jdbc_user="mvdemo"
jdbc_password="!mvdemo"
jdbc_mode="thin"
number_of_mappers="5"
allow_jdbc_theme_based_foi="true"
plsql_package="web_user_info"
web_user_type="MON_USER"
/>

The first bold-typed attribute, plsql_package, tells MapViewer which database package it should execute around theme queries. The second bold attribute, web_user_type, tells MapViewer where to pick up the authenticated Web user name.
The possible values for the web_user_type attribute are:
"J2EE_USER" : tells MapViewer to get the authenticated user name from a J2EE session
"OSSO_USER" : tells MapViewer to get the authenticated user from an OSSO (Oracle Single Sign On) session
"<Cookie Name>" : tells MapViewer to get the authenticated user from a cookie; cookie name is case insensitive. In the above example, the name of the cookie containing the authenticated user name is "MON_USER".

So that's the gist of it. For more detailed explaination on this new MapViewer feature, please check out the MapViewer User's Guide (section 1.8) that comes with MapViewer version 10.1.3.3 for WebLogic. Note that this version of MapViewer works equally well with Oracle App Server 10g or standalone OC4J even though it says "for WebLogic Server".


A secure MVDEMO mapping demo

If you want to run an actual secure mapping demo based on the MVDEMO data set, you should check out this short article describing all the required steps and how to run the demo itself. Note this demo runs in a standalone OC4J or Oracle App Server environment, not in APEX, although you can make simple modifications so that it works in APEX or any other environment. Note also that you will need to use the MVDEMO data set that comes with the latest MapViewer kit mentioned above as it includes the necessary setup scripts for this demo.

Setting up a cookie with user name in Apex

My colleague Carsten Czarski sent me this tip in case you are wondering how to actually setup a cookie in Apex so that it contains the login user name. Basically your Set Username Cookie process in Apex login page 101 should look like this:

begin
owa_util.mime_header('text/html', FALSE);
owa_cookie.send(
name=>'LOGIN_USERNAME_COOKIE',
value=>lower(:P101_USERNAME));
owa_cookie.send(
name => 'MON_USER',
value => :P101_USERNAME,
path => '/mapviewer',
domain => 'myhost.com');
exception when others then null;
end;

The above bold text creates a cookie named MON_USER in the current Apex session. This cookie will be sent to MapViewer along with every map/FOI request. Note especially the pieces about path and domain as they are very important, without these the cookie MON_USER won't reach MapViewer since it is likely running on a different path and domain from APEX.

Note also this will make sure the default LOGIN_USERNAME_COOKIE is still there (in case other pages of your APEX application depends on this cookie).