How to implement Security to ADF application using Custom DB Tables (SQL Authenticator)

This blog explains about configuring 'ReadOnlySQLAuthenticator' in Weblogic console and giving access to ADF applications.

Overview :

SQL Read only authenticator connects to configured database based on the data source and pull the users, groups from the respective database tables based on the queries we configure part of 'SQL ReadOnlyAuthenticator' Provider. This will allow only to read the data from database tables/ views, but no way user can update any information related to users/groups into database tables, whereas 'SQL Authenticator' can allow to modify Users/Groups related information to database tables.

Implementation :

- Login to Weblogic Console.
- Choose Security Realms -> myRealm->Providers->New ->
- Give the proper name to the Provider
- Choose Type as 'ReadOnlySQLAuthenticator'
- After creating new provider, Select the newly created provider in Authentication Providers list
- Choose 'Control Flag' as 'OPTIONAL' Below is the brief information about various possible values for 'Control Flag'

- Enter the below information in 'Provider Specific' , This is very important section and must be validated queries thoroughly. Must have three different database tables

- USERS - This table maintain about users related information such as user name, password, description etc
- GROUPS - This table maintain about groups related information such as group name, description etc.
- USER_GRP- This table maintain association between users and groups.

- Give valid data source name

SQL Get Users Password:  SELECT U_PASSWORD FROM USERS WHERE U_NAME = ?

SQL User Exists : SELECT U_NAME FROM USERS WHERE U_NAME = ?

SQL List Users : SELECT U_NAME FROM USERS WHERE U_NAME LIKE ?

SQL List Groups : SELECT G_NAME FROM GROUPS WHERE G_NAME LIKE ?

SQL Group Exists : SELECT G_NAME FROM GROUPS WHERE G_NAME = ?

SQL Is Member : SELECT u.U_NAME FROM USER_GRP g ,USERS u WHERE u.USER_ID = g.USER_ID and GROUP_ID = ( select G_ID from GROUPS where G_NAME = ? ) AND USER_ID = ( select USER_ID from USERS where U_NAME = ? )

SQL List Member Groups : SELECT G_NAME FROM USER_GRP g ,GROUPS r,USERS u WHERE g.USER_ID = u.USER_ID and g.GROUP_ID = r.G_ID and u.U_NAME = ?

SQL Get User Description : SELECT U_DESCRIPTION FROM USERS WHERE U_NAME = ?

SQL Get Group Description : SELECT G_DESCRIPTION FROM GROUPS WHERE G_NAME = ?

- After configuring all the above values, Need to restart the server , You should see all the users and groups based on above queries in 'myrealm' .




Below blog explains about configuring Users and Groups in Weblogic console using SQLAuthenticator


http://biemond.blogspot.com/2008/12/using-database-tables-as-authentication.html