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 < 10</wls:sql-list-users>
<wls:sql-list-groups>SELECT NAME FROM ROLE WHERE NAME LIKE ? and rownum
< 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 <
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:
