Monday, February 4, 2013

Custom SQL authenticator configurations on weblogic -Part1



I had a scenario where we were not able to authenticate users against LDAP and access to LDAP was not provided. So the next option was to get export of the user data and have a custom SQL authentication configured on the weblogic server.
So we had a person table with all user tables. But there were couple of issues when we tried to configure custom sql authentication. In our scenario the user gets automatically authenticated in the J2ee application, so the authentication to Human worklist application was not required, so we had the option of hardcoding a single password so that its easy for j2ee web app to call worklist api to access tasks based the user id alone.
The steps for configuring the custom sql authenticator can be found in reference blogs listed below.


Issue#1
Since the PERSON table didn’t have password column, I tried assigning the query select welcome1 from dual query to retrieve the password. So for all users it will return welcome1 as password. But it didnt work.
<wls:sql-get-users-password>SELECT select ‘welcome1’ from dual </wls:sql-get-users-password>

Error stackTrace
------------------------------
<Jan 15, 2013 9:19:53 AM IST> <Notice> <WebLogicServer> <BEA-000360> <Server started in RUNNING mode>
<Jan 15, 2013 9:22:39 AM IST> <Error> <Security> <BEA-000000> <[Security:090759]A SQLException occurred while retrieving password informatio
n
java.sql.SQLException: Invalid column index
        at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:6336)
        at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:6306)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:303)
        at weblogic.jdbc.wrapper.PreparedStatement.setString(PreparedStatement.java:908)
        at weblogic.security.providers.authentication.shared.DBMSSQLRuntimeQueryImpl.passwordStringQuery(DBMSSQLRuntimeQueryImpl.java:88)
        Truncated. see log file for complete stacktrace

Issue #2
I tried using PERSON and SOA_PASSWORD tables with emailaddress and Password column respectively .Email address from PERSON and password from SOA_PASSWORD table.
This also throws error

Error Stacktrace
--------------------------
java.sql.SQLException: Invalid column index
        at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:6336)
        at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:6306)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:303)
        at weblogic.jdbc.wrapper.PreparedStatement.setString(PreparedStatement.java:908)
        at weblogic.security.providers.authentication.shared.DBMSSQLRuntimeQueryImpl.passwordStringQuery(DBMSSQLRuntimeQueryImpl.java:88)
        Truncated. see log file for complete stacktrace

This works properly.
Conclusion :-- User and Password queries have to be on same table.

Solution
----------
finally created a VIEW -- SOA_PASSWORD_VIEW with à  SELECT person.EMAILADDRESS, 'welcome1' FROM PERSON person;

CREATE OR REPLACE FORCE VIEW "SOA_PASSWORD_VIEW" ("EMAILADDRESS", "PASSWORD") AS   SELECT person.EMAILADDRESS, 'welcome1' FROM PERSON person;

Sample queries you add in config.xml

Code snippet from config.xml
--------------------------------------------------------------

<wls:sql-get-users-password>SELECT PASSWORD FROM SOA_PASSWORD_VIEW WHERE EMAILADDRESS = ?</wls:sql-get-users-password>
        <wls:sql-user-exists>SELECT EMAILADDRESS FROM SOA_PASSWORD_VIEW WHERE EMAILADDRESS = ?</wls:sql-user-exists>
        <wls:sql-list-member-groups>SELECT ROLES_NAME FROM PERSON_ROLE WHERE PERSON_EMAILADDRESS = ?</wls:sql-list-member-groups>
        <wls:sql-list-users>SELECT EMAILADDRESS FROM SOA_PASSWORD_VIEW WHERE EMAILADDRESS LIKE ? and rownum &lt; 10</wls:sql-list-users>
        <wls:sql-list-groups>SELECT NAME FROM ROLE WHERE NAME LIKE ? and rownum &lt; 10</wls:sql-list-groups>
        <wls:sql-group-exists>SELECT NAME FROM ROLE WHERE NAME = ?</wls:sql-group-exists>
        <wls:sql-is-member>SELECT PERSON_EMAILADDRESS FROM PERSON_ROLE WHERE ROLES_NAME = ? AND PERSON_EMAILADDRESS = ?</wls:sql-is-member>
        <wls:password-style>PLAINTEXT</wls:password-style>
        <wls:sql-create-user>INSERT INTO SOA_PASSWORD_VIEW (EMAILADDRESS,PASSWORD) VALUES ( ? , ? )</wls:sql-create-user>
        <wls:sql-remove-user>DELETE FROM SOA_PASSWORD_VIEW WHERE EMAILADDRESS = ?</wls:sql-remove-user>
        <wls:sql-remove-group-memberships>DELETE FROM PERSON_ROLE WHERE PERSON_EMAILADDRESS = ? OR ROLES_NAME = ?</wls:sql-remove-group-memberships>
        <wls:sql-set-user-password>UPDATE SOA_PASSWORD_VIEW SET PASSWORD = ?</wls:sql-set-user-password>
        <wls:sql-create-group>INSERT INTO ROLE(NAME) VALUES ( ? )</wls:sql-create-group>
        <wls:sql-add-member-to-group>INSERT INTO PERSON_ROLE (ROLES_NAME,PERSON_EMAILADDRESS) VALUES( ?, ?)</wls:sql-add-member-to-group>
        <wls:sql-remove-member-from-group>DELETE FROM PERSON_ROLE WHERE ROLES_NAME = ? AND PERSON_EMAILADDRESS = ?</wls:sql-remove-member-from-group>
        <wls:sql-remove-group>DELETE FROM ROLE WHERE NAME = ?</wls:sql-remove-group>
        <wls:sql-remove-group-member>DELETE FROM PERSON_ROLE WHERE ROLES_NAME = ?</wls:sql-remove-group-member>
        <wls:sql-list-group-members>SELECT PERSON_EMAILADDRESS FROM PERSON_ROLE WHERE ROLES_NAME = ? AND PERSON_EMAILADDRESS LIKE ? and rownum &lt; 10</wls:sql-list-group-members>
      </sec:authentication-provider>

This basically covers the initial DB setup and issues faced while configuring the Custom SQL authenticator. Thanks to Bhupinder,Ram and Venkat for helping with this configuration

Resources:

No comments: