SQL Authentication Provider for Oracle ADF


Using SQL authentication provider turns out to be a much needed feature for applications which have been transformed into ADF web applications from legacy forms. For applications with really large user bases, it becomes quite tedious to transfer users from database tables to other providers like LDAP.

We do have quite a lot of discussions on this topic. This article will be an extension of two great blogs by Edwin Biemond and Chris Muir, and intends to provide a complete solution with this approach.

The detailed initial steps of this approach are mentioned in the above two blogs. So I will quickly run through them.

I set up a new auth provider from WebLogic console - I will call it CustomSQLAuthenticator.

I would want this authentication system to take precedence over the DefaultAuthenticator. In order to do that, I would do the following:

1) Re-order the providers, to bring up CustomSQLAuthenticator above DefaultAuthenticator.

2) Set the control flag for both CustomSQLAuthenticator and DefaultAuthenticator to SUFFICIENT. In case you are sure that the SQL authentication system is the only provider you intend to use, you may set its flag to REQUIRED. But in my case, I would want both of them to continue to provide authentication for my applications. So I play safe with SUFFICIENT flag for both.

Next it is time to create the default tables for the provider to store users, groups and group members.

You may choose to have different names for these tables, as well as extra columns. But keep in mind, if you intend to create users or groups or group members from the console, you would need to provide default values for the extra columns, as the WebLogic console does not provide any flexibility to pass values for those extra columns. One good use-case I see is to add start date and end dates for USERS table. You would then need to modify the provider specific SQLs to incorporate these columns. Or you may replace the SQL Remove User's DELETE statement with an UPDATE statement. The only point you need to keep in mind is that you cannot add extra bind parameters than the ones which are already provided in the default SQL statements, as they will not be provided from the console.

With the default tables created, I create a user (SKING), I create a group (HR_MANAGER) and I assign this group to the user - all from the WebLogic console. When I query the 3 tables, viz., USERS, GROUPS and GROUPMEMBERS, the appropriate records are created.

This is all you need to do. You can actually start using this provider for your application. Since the CustomSQLAuthenticator is placed above DefaultAuthenticator in provider precedence, the ADF security will hit the CustomSQLAuthenticator provider first. If the username provided is not found, then DefaultAuthenticator will be used. There is no extra configuration required from the application end. The enterprise roles from your jazn-data will automatically be mapped to the G_NAME column from GROUPMEMBERS table.

I have a sample application with a login form and a home page. I login with sking, and I get re-directed to the home page.

But wait a minute! What about user management? We cannot expect to use WebLogic console to serve the purpose of managing users for an enterprise solution! We certainly need an external system which would add or remove application users.

The major challenge in having such a system working in tandem with our authentication system is externalizing the password hashing system to a package, which would mimic the exact nature of hashing used by WebLogic. This would allow us to create or update passwords, which would be recognized by the J2EE container, i.e., WebLogic.

As per Chris Muir's blog (referenced at the beginning of the article), the WLS system has a three step process to generate the final hash.
1) Hash the password using SHA-1 algorithm
2) Convert the above hash into a base64 encoded string
3) Concatenate this string with {SHA-1} - not sure why this step is done though!

The following package mimics the above behaviour, and generates the exact hashed password. I created a couple of users using this package, and I was able to login through all of them.

Before you compile this package, make sure that you grant execute privilege on DBMS_CRYPTO package to this schema from SYS.

I create another user and group combination and see that login works for the new user as well!

However, this works for password algorithm SHA-1 and password style HASHED. I am eager to know if anyone can shed some light on externalizing SHA-2 algorithm or SALTEDHASHED password style.



    should work for SHA-256.

    should work for SHA-384.

    should work for SHA-512.


    The expressions above will work in Oracle 12c and newer and will not work in Oracle 11gR2 or older, because DBMS_CRYPTO supports SHA-2 hashing algorithms since Oracle 12c.


Post a Comment