Friday, April 9, 2010

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

11 comments:

  1. Hi Pino, i have a question for u. with plain text password disabled i don't need another loginmodule?

    ReplyDelete
  2. Hi Rafael,
    Can you please elaborate on your question? I don't get it.

    regards,

    Pino

    ReplyDelete
  3. Hi Pino,
    Using SQLAuthenticator I can use plaintext passwords, or not. ADF applications can authenticate throug SQLAuthenticator provider with the default loginmodule when passwords are encripted?

    Regards, and thanks.

    Rafael.

    ReplyDelete
  4. Hi Rafael,
    You can disable plaintext password and yet able to authenticate using the SQLAuthenticator. Enabling plaintext is not desirable in production environment.
    Yes, ADF applications can authenticate through SQLAuthenticator provider using the default basic or form-based login even though that passwords are hashed(not plaintext).
    In my next post, I will show a SQLAuthenticatorAdapter that you can use to create users, which passwords will be salted-hashed.

    Regards,

    Pino


    To quote John Stegeman:
    "SHA-1 is "hashing" the password, not encrypting it. Hashes are not reversible, whereas encryption typically is reversible."

    ReplyDelete
  5. i'm getting crazy trying to use sqlauthenticator.

    I have a login page and i want to authenticate using SQLAuthenticator. I don't know how can i do to configure my webapp to use this. If i use weblogic user the authentication it's ok but if I use my database users i get loginerror.

    How can i configure this?

    ReplyDelete
  6. For detailed discussion on SQLAuthenticator, please see the following thread in OTN:
    http://forums.oracle.com/forums/thread.jspa?threadID=1056998

    ReplyDelete
  7. Hi,

    Can you take a look at my configuration. I am using ReadOnlySQLAuthentication. I can't see what is wrong with it:

    http://forums.oracle.com/forums/thread.jspa?messageID=4299027&#4299027

    ReplyDelete
  8. Hi Pino,

    I need your help. We have a scenario in my project where we can access LDAP only through the web services exposed. Hence in our adf based portal application we can not utilize container based security but I still want to use adf security to enable and disable specific ui components based on user role.
    I am thinking creating custom authentication provider could be a key here.

    Could you please provide your suggestion what could be the best to fix this issue

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  10. Hi Pino,

    I am trying to setup custom sql authenticator in weblogic 12c to authenticate and authorize about 300-400 users to access some services. I want to use custom table names in my schema which stores the users and group info. I have created a custom provider with authenticator as sqlauthenticator and changed all the default queries to my custom tables, provided my datasource name and restarted my admin and managed servers.

    I could not see the users created from securityrealms->myrealm->UsersandGroups. However when I create a new user, the user is being created in my custom table, but could not see in the em console.

    ReplyDelete
    Replies
    1. The sql that creates new user is good but maybe there is a problem with your sql that fetch the users.

      Delete