1. Application Installation Guide
  2. Create Domains, Repositories, Data Sources
  3. Creating SQL Authentication Provider

2.5 Creating SQL Authentication Provider

The following section details the steps to create SQL authentication provider.
  1. Login to WebLogic server administration console and click Security Realms in left panel.
    The following window is displayed.

    Figure 2-50 SQL Authentication 1


    Description of Figure 2-50 follows
    Description of "Figure 2-50 SQL Authentication 1"

  2. Click myrealm on right panel.
    The following window is displayed.

    Figure 2-51 SQL Authentication 2


    Description of Figure 2-51 follows
    Description of "Figure 2-51 SQL Authentication 2"

  3. Click on Providers tab.
    The following window is displayed.

    Figure 2-52 SQL Authentication 3


    Description of Figure 2-52 follows
    Description of "Figure 2-52 SQL Authentication 3"

  4. Click Lock & Edit to unlock the screen and click New button in Authentication Providers sub tab.
    The following window is displayed.

    Figure 2-53 SQL Authentication 4


    Description of Figure 2-53 follows
    Description of "Figure 2-53 SQL Authentication 4"

  5. Create Authentication provider with following values:
    • Name: OFSLLDBAuthenticator
    • Type: SQLAuthenticator
  6. Click OK.
    The following window is displayed.

    Figure 2-54 SQL Authentication 5


    Description of Figure 2-54 follows
    Description of "Figure 2-54 SQL Authentication 5"

  7. Click on Activate Changes.
    The following window is displayed.

    Figure 2-55 SQL Authentication 6


    Description of Figure 2-55 follows
    Description of "Figure 2-55 SQL Authentication 6"

    Authentication order should be maintained as mentioned in the above screen. ‘OFSLLDBAuthenticator’ will be displayed as above.

  8. Click on OFSLLDBAuthenticator.
    The following window is displayed.

    Figure 2-56 SQL Authentication 7


    Description of Figure 2-56 follows
    Description of "Figure 2-56 SQL Authentication 7"

  9. Select SUFFICIENT as the Control Flag and click Save.
  10. Click Provider Specific sub tab under Configuration tab.
    The following window is displayed.

    Figure 2-57 SQL Authentication 8


    Description of Figure 2-57 follows
    Description of "Figure 2-57 SQL Authentication 8"

  11. Specify the following values in corresponding fields:
    • Data Source Name: OFSLL
    • Password Style Retained: Uncheck
    • Password Algorithm: SHA-512
    • Password Style: SALTEDHASHED
    • Provide the SQL Queries from the column Corresponding SQL Queries as per OFSLL Tables as given below.

    Table 2-1 SQL Queries

    Operation Default SQL Query from Weblogic Corresponding SQL Queries as per our Tables
    SQL Get Users Password: SELECT U_PASSWORD FROM USERS WHERE U_NAME = ? SELECT UAU_USR_PASSWORD FROM USER_AUTHORISATIONS WHERE UAU_USR_CODE = ?
    SQL Set User Password: UPDATE USERS SET U_PASSWORD = ? WHERE U_NAME = ? UPDATE USER_AUTHORISATIONS SET UAU_USR_PASSWORD = ? WHERE UAU_USR_CODE = ?
    SQL User Exists: SELECT U_NAME FROM USERS WHERE U_NAME = ? SELECT UAU_USR_CODE FROM USER_AUTHORISATIONS WHERE UAU_USR_CODE = ?
    SQL List Users: SELECT U_NAME FROM USERS WHERE U_NAME LIKE ? SELECT UAU_USR_CODE FROM USER_AUTHORISATIONS WHERE UAU_USR_CODE LIKE ?
    SQL Create User: INSERT INTO USERS VALUES ( ? , ? , ? ) INSERT INTO USER_AUTHORISATIONS(UAU_USR_CODE, UAU_USR_PASSWORD,UAU_DESC) VALUES(?,?,?)
    SQL Remove User: DELETE FROM USERS WHERE U_NAME = ? DELETE FROM USER_AUTHORISATIONS WHERE UAU_USR_CODE= ?
    SQL List Groups: SELECT G_NAME FROM GROUPS WHERE G_NAME LIKE ? SELECT UGR_GROUP_CODE FROM USER_GROUPS WHERE UGR_GROUP_CODE LIKE ?
    SQL Group Exists: SELECT G_NAME FROM GROUPS WHERE G_NAME = ? SELECT UGR_GROUP_CODE FROM USER_GROUPS WHERE UGR_GROUP_CODE = ?
    SQL Create Group: INSERT INTO GROUPS VALUES ( ? , ? ) INSERT INTO USER_GROUPS(UGR_GROUP_CODE,UGR_GROUP_DESC) VALUES(?,?)
    SQL Remove Group: DELETE FROM GROUPS WHERE G_NAME = ? DELETE FROM USER_GROUPS WHERE UGR_GROUP_CODE = ?
    SQL Is Member: SELECT G_MEMBER FROM GROUPMEMBERS WHERE G_NAME = ? AND G_MEMBER = ? SELECT UGM_MEMBER_USR_CODE FROM USER_GROUP_MEMBERS WHERE UGM_MEMBER_GROUP_CODE= ? AND UGM_MEMBER_USR_CODE = ?
    SQL List Member Groups: SELECT G_NAME FROM GROUPMEMBERS WHERE G_MEMBER = ? SELECT UGM_MEMBER_GROUP_CODE FROM USER_GROUP_MEMBERS WHERE UGM_MEMBER_USR_CODE= ?
    SQL List Group Members: SELECT G_MEMBER FROM GROUPMEMBERS WHERE G_NAME = ? AND G_MEMBER LIKE ? SELECT UGM_MEMBER_USR_CODE FROM USER_GROUP_MEMBERS WHERE UGM_MEMBER_GROUP_CODE= ? AND UGM_MEMBER_USR_CODE LIKE ?
    SQL Remove Group Memberships: DELETE FROM GROUPMEMBERS WHERE G_MEMBER = ? OR G_NAME = ? DELETE FROM USER_GROUP_MEMBERS WHERE UGM_MEMBER_USR_CODE= ? OR UGM_MEMBER_GROUP_CODE= ?
    SQL Add Member To Group: INSERT INTO GROUPMEMBERS VALUES( ?, ?) INSERT INTO USER_GROUP_MEMBERS (UGM_MEMBER_GROUP_CODE,UGM_MEMBER_USR_CODE) VALUES(?,?)
    SQL Remove Member From Group: DELETE FROM GROUPMEMBERS WHERE G_NAME = ? AND G_MEMBER = ? DELETE FROM USER_GROUP_MEMBERS WHERE UGM_MEMBER_GROUP_CODE= ? AND UGM_MEMBER_USR_CODE= ?
    SQL Remove Group Member: DELETE FROM GROUPMEMBERS WHERE G_NAME = ? DELETE FROM USER_GROUP_MEMBERS WHERE UGM_MEMBER_GROUP_CODE= ?
    SQL Get User Description: SELECT U_DESCRIPTION FROM USERS WHERE U_NAME = ? SELECT UAU_DESC FROM USER_AUTHORISATIONS WHERE UAU_USR_CODE = ?
    SQLSet User Description: UPDATE USERS SET U_DESCRIPTION = ? WHERE U_NAME = ? UPDATE USER_AUTHORISATIONS SET UAU_DESC= ? WHERE UAU_USR_CODE= ?
    SQL Get Group Description: SELECT G_DESCRIPTION FROM GROUPS WHERE G_NAME = ? SELECT UGR_GROUP_DESC FROM USER_GROUPS WHERE UGR_GROUP_CODE= ?
    SQL Set Group Description: UPDATE GROUPS SET G_DESCRIPTION = ? WHERE G_NAME = ? UPDATE USER_GROUPS SET UGR_GROUP_DESC= ? WHERE UGR_GROUP_CODE= ?
    Provider Name OFSLLDBAuthenticator  

    Figure 2-58 SQL Authentication 9


    Description of Figure 2-58 follows
    Description of "Figure 2-58 SQL Authentication 9"

  12. Click Save.

    Note:

    Application server needs to be restarted for these changes to take effect.

Parent topic: Create Domains, Repositories, Data Sources