Create weblogic user and assign roles from database to weblogic server

Question

Hi I have table called user_data, which contains user_id, passwd and user_priv ( role for weblogic user). I need to map my database user_data with weblogic server.

I have tried to create a provider with sqlAuthenticator with my datasource which contains this table.

I have modified all queries to achieve the results.

Suppose to get users to weblogic server from table:

    <wls:sql-get-users-password>SELECT PASSWD FROM USER_DATA where user_id=?</wls:sql-get-users-password>
    <wls:sql-user-exists>SELECT USER_ID FROM USER_DATA whre user_id=?</wls:sql-user-exists>
    <wls:sql-list-users>SELECT USER_ID FROM USER_DATA whre user_id=?</wls:sql-list-users>

I will save it sucessfully and again restart the application server.

Before restarting the server. I will add data in user_data table to check weather it is fetching from table or not.

I have inserted data and restarted the server, but not able to see the data in weblogic server.

But If I will try to delete data from weblogic server, It will delete the data from user_data, but vice versa will not work.

Another approach, I tried is to create readSqlAuthenticator to make it only readable. It will have only 3 statements mentioned as above.

But It will throw exception for

    java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.OraclePreparedStatement.setFormOfUseInternal(OraclePreparedStatement.java:16268)
at oracle.jdbc.driver.OraclePreparedStatement.setFormOfUseInternal(OraclePreparedStatement.java:16238)
at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:7895)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:314)
at weblogic.jdbc.wrapper.PreparedStatement.setString(PreparedStatement.java:914)
Truncated. see log file for complete stacktrace
    > 
    <24 Jan, 2017 1:48:03 PM IST> <Error> <Security> <BEA-000000> <[Security:090759]A SQLException occurred while retrieving password information
    java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.OraclePreparedStatement.setFormOfUseInternal(OraclePreparedStatement.java:16268)
at oracle.jdbc.driver.OraclePreparedStatement.setFormOfUseInternal(OraclePreparedStatement.java:16238)
at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:7895)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:314)
at weblogic.jdbc.wrapper.PreparedStatement.setString(PreparedStatement.java:914)
Truncated. see log file for complete stacktrace
    > 
    <24 Jan, 2017 1:48:03 PM IST> <Error> <Security> <BEA-000000> <[Security:090759]A SQLException occurred while retrieving password information
    java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.OraclePreparedStatement.setFormOfUseInternal(OraclePreparedStatement.java:16268)
at oracle.jdbc.driver.OraclePreparedStatement.setFormOfUseInternal(OraclePreparedStatement.java:16238)
at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:7895)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:314)
at weblogic.jdbc.wrapper.PreparedStatement.setString(PreparedStatement.java:914)
Truncated. see log file for complete stacktrace
    > 
    <24 Jan, 2017 1:48:03 PM IST> <Error> <Security> <BEA-000000> <[Security:090759]A SQLException occurred while retrieving password information
    java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.OraclePreparedStatement.setFormOfUseInternal(OraclePreparedStatement.java:16268)
at oracle.jdbc.driver.OraclePreparedStatement.setFormOfUseInternal(OraclePreparedStatement.java:16238)

at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:7895)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:314)
at weblogic.jdbc.wrapper.PreparedStatement.setString(PreparedStatement.java:914)
Truncated. see log file for complete stacktrace

On launching the application server and clicking on users and group, I will get following exception

   weblogic. security.providers.authentication.DBMSSQLAuthenticatorDelegateException: [Security:090279]Error listing users *

For this I got a suggestion as to remove filters such as :

    <wls:sql-get-users-password>SELECT PASSWD FROM USER_DATA</wls:sql-get-users-password>
    <wls:sql-user-exists>SELECT USER_ID FROM USER_DATA</wls:sql-user-exists>
    <wls:sql-list-users>SELECT USER_ID FROM USER_DATA</wls:sql-list-users>

But this also didn't work. Is there any other approach that I can map my database to weblogic server for all user id, password and role.

Please suggest me on this.

Answer 1

If I understood it correctly, you are trying to use database table as your LDAP user store. If this understanding is correct then please note that on successful implementation, you should be able to login to weblogic console with the credentials present in your db table.

Please ensure your providers order has been changed in a way that SQLAuthentication comes first in order as the priority is given from top to bottom. Also see what LDAP flags have been set for the providers among required,requisite and sufficient.