Weblogic Server, by default stores the user and group information in an Embedded LDAP Server.

If we want to store the user and group information in a database, Weblogic Server provides an Out of the Box Provider, SQL Authenticator.

The SQL Authenticator uses a default schema, which can be modified.
To create the tables required by the SQL Authenticator using the default schema, execute the following query.

CREATE TABLE USERS (
U_NAME VARCHAR(200) NOT NULL,
U_PASSWORD VARCHAR(50) NOT NULL,
U_DESCRIPTION VARCHAR(1000))
;
ALTER TABLE USERS
ADD CONSTRAINT PK_USERS
PRIMARY KEY (U_NAME)
;
CREATE TABLE GROUPS (
G_NAME VARCHAR(200) NOT NULL,
G_DESCRIPTION VARCHAR(1000) NULL)
;
ALTER TABLE GROUPS
ADD CONSTRAINT PK_GROUPS
PRIMARY KEY (G_NAME)
;
CREATE TABLE GROUPMEMBERS (
G_NAME VARCHAR(200) NOT NULL,
G_MEMBER VARCHAR(200) NOT NULL)
;
ALTER TABLE GROUPMEMBERS
ADD CONSTRAINT PK_GROUPMEMS
PRIMARY KEY (
G_NAME,
G_MEMBER
)
;
ALTER TABLE GROUPMEMBERS
ADD CONSTRAINT FK1_GROUPMEMBERS
FOREIGN KEY ( G_NAME )
REFERENCES GROUPS (G_NAME)
ON DELETE CASCADE

Insert the User and Group records into the database.

insert into USERS (U_NAME,U_PASSWORD,U_DESCRIPTION) values(’system’,’weblogic’,’admin user’);

insert into GROUPS (G_NAME,G_DESCRIPTION) values(‘Adminsitrators’,’Adnministrators’);

insert into GROUPMEMBERS (G_NAME,G_MEMBER) values(‘Administrators’,’system’);

Create a datasource on Weblogic Server.

Create an SQLAuthenticator

myrealm > Providers > new SQLAuthenticator

Under myrealm > Providers > SQLAuthenticator > Provider Specific

Check Plaintext Passwords Enabled
Data Source Name: DS1

Leave the rest as default since we are using default schema.

In my example I have used plain text password.

If you want to log in to the console from users in the database, change the default authenticator flag as OPTIONAL.

Restart the server and log in as system.

Go to myrealm > Users and Groups to see the user and groups from the database.

Comments

comments

10 Thoughts on “Configuring SQL Authenticator with Weblogic Server.

  1. Eamonn on March 23, 2010 at 9:48 pm said:

    Hi Faisal
    Is there an api that lets you programmatically add users?

  2. Eamonn on March 26, 2010 at 3:25 pm said:

    thanks a lot Faisal you were a big help. Many thanks

  3. Arunbaalajee R on June 23, 2011 at 12:18 pm said:

    Awesome document, thanks a ton :)

    Regards,
    Arunbaalajee R

  4. Mark Thompson on February 10, 2012 at 2:06 pm said:

    Nice document except for the typos. The ‘insert into groups’ command is wrong.

  5. I created users and groups in database following the above steps. All are listed in the myrealm in the WebLogic console.

    I could NOT see the users and groups in the Oracle BPM Workspace, while doing a ‘reassign’ for any task, please advice.

  6. I could not logon to Workspace with the database users

Leave a Reply

Post Navigation