Using database tables as authentication provider in WebLogic

In WebLogic you can use database tables as authentication provider for your web applications. In these tables you can store your application users with its roles. WebLogic will use these tables for your application authentication. WebLogic even provides you a web interface where you can add or change users / roles.
You can use this SQL authenticator for your container security or use it for your JDeveloper 11G ADF security. For more info over ADF security see my previous blog. This SQL authenticator replaces the dbloginmodule of the OC4J container which was available in the Technical Previews of JDeveloper 11g.
First we need to have some authorization tables. I will use the user and roles tables of JHeadstart. Here is the ddl with some sample users.

CREATE TABLE JHS_ROLES
(
ID NUMBER(*, 0) NOT NULL,
ORG_KEY VARCHAR2(30) DEFAULT 'DEFAULT' NOT NULL,
SHORT_NAME VARCHAR2(10) NOT NULL,
NAME VARCHAR2(40) NOT NULL
);

CREATE TABLE JHS_USER_ROLE_GRANTS
(
ID NUMBER(*, 0) NOT NULL,
USR_ID NUMBER(*, 0) NOT NULL,
RLE_ID NUMBER(*, 0) NOT NULL
);

CREATE TABLE JHS_USERS
(
ID NUMBER(*, 0) NOT NULL,
EMAIL_ADDRESS VARCHAR2(240),
USERNAME VARCHAR2(240) NOT NULL,
ORG_KEY VARCHAR2(30) DEFAULT 'DEFAULT',
PASSWORD VARCHAR2(240),
DISPLAY_NAME VARCHAR2(240),
LOCALE VARCHAR2(10)
);

ALTER TABLE JHS_ROLES
ADD CONSTRAINT JHS_RLE_PK PRIMARY KEY
( ID ) ENABLE;

ALTER TABLE JHS_ROLES
ADD CONSTRAINT JHS_RLE_UK1 UNIQUE
( SHORT_NAME,ORG_KEY ) ENABLE;

ALTER TABLE JHS_USER_ROLE_GRANTS
ADD CONSTRAINT JHS_URG_PK PRIMARY KEY
( ID ) ENABLE;

ALTER TABLE JHS_USER_ROLE_GRANTS
ADD CONSTRAINT JHS_URG_UK1 UNIQUE
( RLE_ID, USR_ID ) ENABLE;

ALTER TABLE JHS_USERS
ADD CONSTRAINT JHS_USR_PK PRIMARY KEY
( ID ) ENABLE;

CREATE SEQUENCE JHS_SEQ INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20 ;

-- Create two users SKING and AHUNOLD
insert into jhs_users (ID, EMAIL_ADDRESS, USERNAME, ORG_KEY, PASSWORD, DISPLAY_NAME)
select jhs_seq.nextval,'SKING','SKING','DEFAULT','SKING', 'Steven King'
from dual
where not exists (select '1' from jhs_users where username='SKING');

insert into jhs_users (ID, EMAIL_ADDRESS, USERNAME, ORG_KEY, PASSWORD, DISPLAY_NAME)
select jhs_seq.nextval,'AHUNOLD','AHUNOLD','DEFAULT','AHUNOLD', 'Alexander Hunold'
from dual
where not exists (select '1' from jhs_users where username='AHUNOLD');

-- set up two roles: Administrator and User
insert into jhs_roles(id, SHORT_NAME, name)
select jhs_seq.nextval, 'ADMIN','Administrator'
from dual
where not exists (select '1' from jhs_roles where short_name='ADMIN');

insert into jhs_roles(id, SHORT_NAME, name)
select jhs_seq.nextval, 'USER','User'
from dual
where not exists (select '1' from jhs_roles where short_name='USER');

-- Make Steven King Administrator
insert into jhs_user_role_grants (id,rle_id,usr_id)
select jhs_seq.nextval, rle.id, usr.id
from jhs_roles rle, jhs_users usr
where rle.short_name='ADMIN'
and usr.username='SKING'
and not exists (select '1' from jhs_user_role_grants urg2
          where urg2.usr_id = usr.id
          and   urg2.rle_id = rle.id);

-- Make Alexander Hunold User
insert into jhs_user_role_grants (id,rle_id,usr_id)
select jhs_seq.nextval, rle.id, usr.id
from jhs_roles rle, jhs_users usr
where rle.short_name='USER'
and usr.username='AHUNOLD'
and not exists (select '1' from jhs_user_role_grants urg2
          where urg2.usr_id = usr.id
          and   urg2.rle_id = rle.id);

commit;

Now we can add the SQL authenticator provider in WebLogic. First we need to create a datasource for the database connection and remember the datasource name ( not the jndi name) We needs this value for the provider.

Select the Security Realms link then I will select the default realm "myrealm" and go to providers tab. Here we can create a new authentication provider.
We need to select SQLAuthenticator as Type
Select your just created provider and change the Control flag to sufficient. After this we can go to the provider specific tab where we can add the details of the provider.
We need to fill in the datasource name, select a password algorithm and add many SQL statements.Here are my settings for the jheadstart tables.
Go to this folder MiddlewareJdev11g\jdeveloper\system\system11.1.1.0.31.51.88\DefaultDomain\config and change the config.xml file where you can replace your values with this

<sec:authentication-provider xsi:type="wls:sql-authenticatorType">
  <sec:name>DB_users</sec:name>
  <sec:control-flag>SUFFICIENT</sec:control-flag>
  <wls:enable-group-membership-lookup-hierarchy-caching>false</wls:enable-group-membership-lookup-hierarchy-caching>
  <wls:data-source-name>scott</wls:data-source-name>
  <wls:plaintext-passwords-enabled>true</wls:plaintext-passwords-enabled>
  <wls:sql-get-users-password>SELECT password FROM jhs_users WHERE username = ?</wls:sql-get-users-password>
  <wls:sql-user-exists>SELECT username FROM jhs_users WHERE username = ?</wls:sql-user-exists>
  <wls:sql-list-member-groups>SELECT short_name FROM jhs_user_role_grants g ,jhs_roles r,jhs_users u WHERE g.usr_id = u.id and g.rle_id = r.id and u.username = ?</wls:sql-list-member-groups>
  <wls:sql-list-users>SELECT username FROM jhs_users WHERE username LIKE ?</wls:sql-list-users>
  <wls:sql-get-user-description>SELECT display_name FROM jhs_users WHERE username = ?</wls:sql-get-user-description>
  <wls:sql-list-groups>SELECT short_name FROM jhs_roles WHERE short_name LIKE ?</wls:sql-list-groups>
  <wls:sql-group-exists>SELECT short_name FROM jhs_roles WHERE short_name = ?</wls:sql-group-exists>
  <wls:sql-is-member>SELECT u.username FROM jhs_user_role_grants g ,jhs_users u WHERE u.id = g.usr_id and rle_id = ( select id from jhs_roles where short_name = ? ) AND usr_id = ( select id from jhs_users where username = ? )</wls:sql-is-member>
  <wls:sql-get-group-description>SELECT name FROM jhs_roles WHERE short_name = ?</wls:sql-get-group-description>
  <wls:password-style>PLAINTEXT</wls:password-style>
  <wls:sql-create-user>INSERT INTO jhs_users ( id,username , password , display_name) VALUES (jhs_seq.nextval, ? , ? , ? )</wls:sql-create-user>
  <wls:sql-remove-user>DELETE FROM jhs_users WHERE username = ?</wls:sql-remove-user>
  <wls:sql-remove-group-memberships>DELETE FROM jhs_user_role_grants WHERE rle_id = ( select id from jhs_roles where short_name = ? ) or usr_id = ( select id from jhs_users where username = ? )</wls:sql-remove-group-memberships>
  <wls:sql-set-user-description>UPDATE jhs_users SET display_name  = ? WHERE username = ?</wls:sql-set-user-description>
  <wls:sql-set-user-password>UPDATE jhs_users SET password = ? WHERE username = ?</wls:sql-set-user-password>
  <wls:sql-create-group>insert into jhs_roles(id, short_name, name) values (jhs_seq.nextval, ?, ?)</wls:sql-create-group>
  <wls:sql-set-group-description>UPDATE jhs_roles SET name = ? WHERE short_name =  ?</wls:sql-set-group-description>
  <wls:sql-add-member-to-group>INSERT INTO jhs_user_role_grants (id,rle_id,usr_id) VALUES( jhs_seq.nextval , ( select id from jhs_roles where short_name = ?),(select id from jhs_users where username = ?))</wls:sql-add-member-to-group>
  <wls:sql-remove-member-from-group>DELETE FROM jhs_user_role_grants WHERE rle_id = ( select id from jhs_roles where short_name = ? ) AND usr_id = ( select id from jhs_users where username = ? )</wls:sql-remove-member-from-group>
  <wls:sql-remove-group>DELETE FROM jhs_roles WHERE short_name = ?</wls:sql-remove-group>
  <wls:sql-remove-group-member>DELETE FROM jhs_user_role_grants WHERE rle_id = ( select id from jhs_roles where short_name = ? )</wls:sql-remove-group-member>
  <wls:sql-list-group-members>SELECT username FROM jhs_user_role_grants g ,jhs_roles r,jhs_users u WHERE g.usr_id = u.id and g.rle_id = r.id and r.short_name = ? and u.username like ?</wls:sql-list-group-members>
</sec:authentication-provider>

We need to restart the WebLogic server. After the reboot we can go the User and Group tab of your default security realm where we can change or add users and roles. Here is an overview where we can see SKING
When we select SKING we can add roles to this user.
Now we can test it, ( see my previous blog for more details)

Here the result of the authentication