The Problem

We were in well on the way to convert all our Application Express (APEX) applications to use the Oracle identity directory supplied with the Oracle Application Server. This allowed us to use Oracle Single Sign-On (SSO) for authentication, but didn't meet the need for limiting access to certain parts of the applications to groups of users.

The Solution

The credit for this solution lies with another member of our development team. He's happy for me to post his work her, but I couldn't convince him to take the credit. I'm sure you'll agree with me that he's done a great job.

You can download the source code for both the package header and the package body.

Here's the header:

create or replace package MY_SECURITY is

-------------------------------------------------------------------------------------
-- CHECK_USER_IN_GROUP 
--
-- This function is used to check to see if the named user is a member of the specified
-- group.  It checks groups in both the native OID and Portal.
--
-- Parameters:
--   USER_ID  - the user to check
--   GROUP_ID - name of the group
-- Returns TRUE or FALSE
--
function CHECK_USER_IN_GROUP(USER_ID VARCHAR2, GROUP_ID VARCHAR2) RETURN BOOLEAN; 

-------------------------------------------------------------------------------------
-- CHECK_USER_IN_GROUP_YN 
-- 
-- Identical to the above function except it returns a Y or N value (for inclusion in
-- SQL select statements).
--
function CHECK_USER_IN_GROUP_YN(USER_ID VARCHAR2, GROUP_ID VARCHAR2) RETURN VARCHAR2; 

-------------------------------------------------------------------------------------
-- ADD_USER_TO_GROUP
--
-- Adds the named user to the specified group.
--
-- Parameters:
--   USER_ID  - the user to check
--   GROUP_ID - name of the group
--
procedure ADD_USER_TO_GROUP(USER_ID VARCHAR2, GROUP_ID VARCHAR2);

-------------------------------------------------------------------------------------
-- GET_USER_GROUPS
--
-- Returns a comma seperated list of the groups the user is a member of.
--
-- Parameters:
--   USER_ID  - the user to check
-- Returns a comma seperated list.
-- 
function GET_USER_GROUPS(USER_ID VARCHAR2) RETURN VARCHAR2; 

END;

Before you can use this, you will have to make a few changes to the package body. In the first section of the package, there are a few global variables that you need to set for your particular installation. These are things like server names. Here is the section of the package body:

-- This package uses a call to a logging package called MY_LOG_MESSAGE.
-- This has two main procedures, one called LOG and one called ERR.
-- They write the specified messages to tables called MY_LOG and MY_ERRORS.

-- Global variables - set for your setup
--
LDAP_HOST VARCHAR2(256)   := 'url.for.my.server';
LDAP_PORT VARCHAR2(256)   := 'port'; -- normally 3060
LDAP_USER VARCHAR2(256)   := 'cn=orcladmin,cn=users,dc=myserver,dc=com'; -- or a user with appropriate privs.
LDAP_PASSWD VARCHAR2(256) := 'password'; -- the correct password
LDAP_BASE VARCHAR2(256)   := 'dc=myserver,dc=com';

You may notice the dependency on another package called MY_LOG_MESSAGE. I've created a package with two null procedures in it for you to download. This does nothing but allows this package to compile and run. You can always make this into something more useful if you need.

You within the body of the package is another setting you will have to change. It's in two places (I know, we should have put it in as a global variable, but we didn't. You can do this in your own version if it makes you happy), so you'll have to find both entries and change them. Follow the instructions in the code below to get the value to set them to:

/*** 
  WHEN A GROUP IS CREATED WITHIN PORTAL THIS IS LOCAL WITHIN PORTAL.

  TO FIND OUT THE VALUE OF WHAT YOU SHOULD USE BELOW PLEASE CONNECT 
  TO THE APPLICATION SERVER AND RUN THE FOLLOWING   
  QUERY:
	 
  SELECT wwsec_oid.get_group_install_base FROM dual; 
 
  THIS WILL RETURN WHAT YOU SHOULD USE FOR YOUR VALUE WHEN LOOKING 
  FOR A GROUP THAT WAS CREATED IN PORTAL	 
***/	    
	 
  LV_PORTAL_GROUP_ID := 'cn=' || GROUP_ID || ',cn=portal.123456.1234,cn=groups,' || LDAP_BASE;

To use this as an authorisation scheme in APEX you have to first create an OID group in the Oracle Application server, either directly into OID or using Portal. Then you have to add the users to the group. In APEX, create a new authorisation schema of type function returning boolean, with the following code:

return MY_SECURITY.CHECK_USER_IN_GROUP(:APP_USER, 'YOUR_GROUP_NAME');

Here's a screenshot of what it looks like in APEX:

That's all there is to it! It levarages the power of application server OID to a whole new level: we can now use the OID security architecture for our Portal applications, our web PL/SQL applications and now our APEX ones. We will also be able to easily adapt this code to our web forms applications, allowing us to consolidate our security architecture in one place.

Patrick Haston
15 Demcember 2007