SQL Authenticator Select Statement Reference

Learn options available for creating SQL statements when implementing a SQL authentication provider.

When you create a SQL Authenticator in the Provider Specific tab, you specify the SQL statements used to query, and authenticate against, your database tables. See Configuring a SQL Authenticator Using the Oracle WebLogic Server Administration Console.

The table shows SQL statements for the sample schema outlined in Create a Sample Schema for Users and Groups.

If you are using a different table structure, you might need to adapt these SQL statements with the table or column names of your schema. You should use the question mark (?) as a runtime query placeholder rather than hard coding a user or group name.

Query	SQL	Notes
SQL Get Users Password	SELECT U_PASSWORD FROM USERS WHERE U_NAME = ?	This SQL statement looks up a user's password. The SQL statement requires a single parameter for the username and must return a resultSet containing at most a single record containing the password.
SQL User Exists	SELECT U_NAME FROM USERS WHERE U_NAME = ?	This SQL statement looks up a user. The SQL statement requires a single parameter for the username and must return a resultSet containing at most a single record containing the user.
SQL List Users	SELECT U_NAME FROM USERS WHERE U_NAME LIKE ?	This SQL statement retrieves users that match a specific wildcard search. The SQL statement requires a single parameter for the usernames and returns a resultSet containing matching usernames.
SQL List Groups	SELECT G_NAME FROM GROUPS WHERE G_NAME LIKE ?	This SQL statement retrieves group names that match a wildcard. The SQL statement requires a single parameter for the group name and returns a resultSet containing matching groups.
SQL Group Exists	SELECT G_NAME FROM GROUPS WHERE G_NAME = ?	This SQL statement looks up a group. The SQL statement requires a single parameter for the group name, and must return a resultSet containing at most a single record containing the group.
SQL Is Member	SELECT G_MEMBER FROM GROUPMEMBERS WHERE G_NAME=? AND G_MEMBER LIKE ?	This SQL statement looks up members of a group. The SQL statement requires two parameters, a group name and a member or group name. This SQL statement must return a resultSet.
SQL List Member Groups	SELECT G_NAME FROM GROUPMEMBERS WHERE G_MEMBER = ?	This SQL statement looks up the group membership of a user or group. The SQL statement requires a single parameter for the username or group name, and returns a resultSet containing the names of the groups that matched the criteria.
SQL Get User Description	SELECT U_DESCRIPTION FROM USERS WHERE U_NAME = ?	This SQL statement retrieves the description of a specific user. The SQL statement is valid only if Descriptions Supported is enabled. The SQL statement requires a single parameter for the username and must return a resultSet containing at most a single record containing the user description.
SQL Get Group Description	SELECT G_DESCRIPTION FROM GROUPS WHERE G_NAME = ?	This SQL statement retrieves the description of a group. The SQL statement is valid only if Descriptions Supported is enabled. The SQL statement requires a single parameter for the group name and must return a resultSet containing at most a single record containing the group description.