ADF Security: SQLAuthenticator is Simply the Best!

When choosing authentication repositories, something that is more familiar can be the best choice. And what is that "something familiar" for us developers?- Of course, the relational database (unless, if you know LDAPs more :D). Relational databases (RDMS) has encryption capability while authentication providers supports cryptographic hashing, so what else could you ask?
Plus the recommendation below of an an expert, who is previously from Bea Weblogic Portal Team:

When it comes to Authentication repositories, my experience tells me that you are safest performance-wise with a database backed authentication store. While customers have certainly been successful with other types of authentication repositories, if you want to minimize risk, the database approach trumps all others.
-- Peter Laird, Architect for Tendril Networks

In our case, relational database is simply the choice. We need not only know - what roles our users have, but also what data they can access based on the organizations that were assigned to them (plus a lot more...).

In this post I will share the knowledge that I have acquired related to the best database-based authentication provider, the SQLAuthenticator.

---

Some tips on SQLAuthenticator to avoid being miserable :D :

1. Stick as much as possible to the default schema. With the default schema, you need not worry tweaking the SQL select and insert statements defined on the SQLAuthenticator provider details. For your convenience, below is the script:

   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
   ;
   

2. If tip# 1 is not possible, be wary that aside from Users, Groups can also be a member of a given Group. GROUPMEMBERS table is not simply a join table between Users and Groups but also can be a recursion between groups. I believe this is the reason why the default schema did not use surrogate keys (meaningless Ids). Imagine what will happen if you have a User with Long id 1 and Group with the same id 1, then what would the following record from the GROUPMEMBERS table mean?

   GROUP_ID          MEMBER_ID 
      2                 1
   

   Does above mean Group 1 is a member of Group 2?
   Or does it mean USer 1 is a member of Group2?

   Another thing to note - a Group Membership or Grants table implemented like the following script will NOT support membership of groups into other groups which defeats some aspects of weblogic authorization:

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

3. Do not enable Plaintext Passwords. This is to ensure that users are created in the right process. You would not like to see your secret password. Do you?
4. Be sure to set the "Group Membership Searching" to "limited" and set the "Max Group Membership Search Level" to a value like "5". This is to avoid infinite loop when the in situations where for example Group A is a member of Group B, while Group B is also a member of Group A.
5. You do not need to change the identity store in Jdeveloper. jazn.xml is perfectly fine.
6. Andrejus was right -you need not modify the role mapping in weblogic.xml. The default like below is perfectly fine:

   <?xml version = '1.0' encoding = 'windows-1252'?>
   <weblogic-web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                     xsi:schemaLocation="http://www.bea.com/ns/weblogic/weblogic-web-app http://www.bea.com/ns/weblogic/weblogic-web-app/1.0/weblogic-web-app.xsd"
                     xmlns="http://www.bea.com/ns/weblogic/weblogic-web-app">
     <security-role-assignment>
       <role-name>valid-users</role-name>
       <principal-name>users</principal-name>
     </security-role-assignment>
   </weblogic-web-app>
   

7. For each application role you defined in jazn.xml, create an equivalent group in your SQLAuthenticator provider in weblogic console, and recreate those roles in the Enterprise Roles in jazn.xml. In jazn, make the corresponding enterprise role as member of the appropriate application role.

With these tips, I believe that you could already setup SQLAuthenticator easily. Given enough time, I am planning to consolidate the steps in other blogs to give a one-stop shop in configuring SQLAuthenticator.

In the next post, I will share how to play with weblogic APIs to access our security realm and to do tasks such adding user, letting user change password, listing users and roles, and more using Java (not the WLST):D

Kudus to Edwin Biemond for introducing to us the SQLAuthenticator!
Cheers!

Useful Resources

• Using database tables as authentication provider in WebLogic
• WebLogic Security: Configuring the Database Authentication Providers (SQL, Custom, DBMS)
• Part II - Working with WLS 10.3.1 SQLAuthenticator password algorithms