Set up database authentication

If you have a customized security system already in place or if you have your own security system, database authentication may be a good choice. There are two parts to this type of authentication:
  • RDBMS Table Authenticator
  • RDBMS table role group provider

The RDBMS table authenticator is used to authenticate user IDs and passwords that have been stored in a relational table in clear text, or in a database stored function. This is the class name for the RDBMS table authenticator:

com.lawson.efs.security.authenticationprovider.

DBTableAuthenticationProvider

The RDBMS table role provider is used for obtaining lists of users and role groups if the information is stored in a table, if the information is stored in a view, or if the information is expressed with standard SQL queries. This is the class name for the RDBMS table role group provider:

com.lawson.efs.security.roleprovider.DBTableRoleProvider

To modify database table authentication using the System Configuration Assistant

  1. Open the Tools dashboard and click System Settings. The System Configuration Assistant opens.
  2. Select Database Table Authentication as the authentication provider. A set of Database Table Authentication property fields opens. These properties are described here:
    DB Table JNDI Name

    jndiName

    This property is the JNDI (Java Naming and Directory Interface) name for the database that contains the table. This property enables you to supply parameters that tell the authenticator how to locate and talk to the database. You can use this property if the database that contains the security table or authentication function is not defined in the JNDI.

    DB Table Admin Role

    adminRole

    This property is the name of the user group that defines a user as an administrator for the Lawson application. You must use a varchar field for this property.

    During authentication, the list of assigned user groups is searched for this name. If it is found, the user is permitted to perform administrative functions in the Administrative Console.

    Authentication Stored Function Name

    authFunc

    If you check the Use Stored Function for Authentication check box, this property specifies the name of the database stored function that will authenticate users.

    This stored function must return an integer, and receive two variable-character (VARCHAR) parameters (the user ID and password).

    For example: CREATE FUNCTION somefunc (VARCHAR userid, VARCHAR password) RETURNS INTEGER.

    The result of this function is "1" if the user ID and password are valid. Any other return value is an authentication failure.

    If this property is specified, the authTable, uidColumn, and passColumn properties are ignored.

    Authentication Table Name

    authTable

    This property is required if the authFunc property is not specified. Enter the name of the table or view that has the column names specified in uidColumn and passColumn. When authenticating this table, this SQL is generated:

    SELECT COUNT(*) FROM authTableWHERE uidColumn = ? passColumn = ?

    Authentication Userid Column/Authentication Password Column

    passColumn, uidColumn

    These properties are required if the authFunc property is not specified. Specify the names of the columns in the table or view that store user IDs .

    Role Table

    roleTable

    This property is the name of the table or view that contains the column names specified in roleuseridColumn and roleGrouplColumn.

    This showshe SQL queries that are generated against this table:

    • SELECT DISTINCT roleUseridColumn FROM roleTableORDER BY roleUseridColumn

    • SELECT DISTINCT roleRoleColumn FROM roleTable ORDERBY roleRoleColumn

    • SELECT roleUseridColumn FROM roleTable WHEREroleRoleColumn = ? ORDER BY roleUseridColumn

    • SELECT roleUseridColumn FROM roleTable WHEREroleUseridColumn = ? ORDER BY roleUseridColumn

    Role table User Column/Role table Role Column

    roleUseridColumn and roleGroupColumn

    These properties are the column names in the table or view that contain user IDs and user groups.

    Power Designer Role

    PowerDesignerRole

    This optional property grants power designer rights to all users in the specified user group.

    All Users Query

    allUsersQuery

    This property overrides the dynamically generated query that lists all users. The first column in the return result set contains user IDs as strings (character or variable character). This property should be specified if the SQL for getting the list of all users needs to be more complex than simply selecting one column from a simple table or view.

    All Roles Query

    allRolesQuery

    This property overrides the dynamically generated query that lists all roles. The first column in the return result set contains user groups as strings (character or variable character). This property should be specified if the SQL for getting the list of all roles needs to be more complex than simply selecting one column from a simple table or view.

    Users in Role Query

    usersInRoleQuery

    This property overrides the dynamically generated query that lists all users within a specified role. The first column in the return result set contains user IDs as strings (character or variable character). The query must have a question mark (?) as a place holder for the role. This property should be specified if the SQL for getting the list of users in a role needs to be more complex than simply selecting one column from a simple table or view.

    Roles for User Query

    rolesForUserQuery

    This property overrides the dynamically generated query listing roles that a specified user belongs to. The first column in the return result set contains roles as strings (character or variable character). The query must have a question mark (?) as a place holder for the user. This property should be specified if the SQL for getting the list of roles that a user belongs to needs to be more complex than simply selecting one column from a simple table or view.

  3. Click Save Changes at the bottom of the System Configuration Assistant.
  4. In Websphere, stop and start your Framework Services application server.