2.5 Creating SQL Authentication Provider

The following section details the steps to create SQL authentication provider.
  1. Login to WebLogic server 14c em and navigate to WebLogic Domain > Security > Security Realms in left panel.
  2. Click myrealm.
  3. Navigate to Providers tab > Authentication and click Create.
    The following window is displayed.

    Figure 2-48 SQL Authentication 3


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

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

    Figure 2-49 SQL Authentication 4


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

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

    Figure 2-50 SQL Authentication 5


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

  7. Authentication order should be maintained as mentioned in the below screen. ‘OFSLLDBAuthenticator’ will be displayed as above.
    The following window is displayed.

    Figure 2-51 SQL Authentication 6


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

  8. Select SUFFICIENT as the Control Flag and click Apply.
    The following window is displayed.

    Figure 2-52 SQL Authentication 7


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

  9. Click Provider Specific sub tab under Configuration tab and click System MBean Browser.
  10. Specify the following values in corresponding fields:
    • Data Source Name: OFSLL
    • Password Style Retained: Uncheck
    • Password Algorithm: SHA-512
    • Password Style: SALTEDHASHED
    The following window is displayed.

    Figure 2-53 SQL Authentication 8


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

    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-54 SQL Authentication 9


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

  11. Click Apply.

    Note:

    Application server needs to be restarted for these changes to take effect.
  12. Click Activate Changes on the left panel.

Parent topic: Create Domains, Repositories, Data Sources