Wherever possible, APS will use the queries defined in the SiteMinder ODBC Query Scheme associated with the User Directory. However, there are some additional queries that APS needs and there will be times that it is not appropriate for APS to use queries defined to SiteMinder.
In addition to the APS-specific queries, every query defined in the SiteMinder Query Scheme can be overridden for use by APS.
Each query has replacement parameters, or placeholders, embedded within them that indicate where APS (or SiteMinder) is to place values before using the query. Each query will replace these parameters with specific values in the order defined by the query. Thus, the first parameter for a given query may be replaced by the User's ID. It is not possible to change the order of the parameters (the choice of placeholders and their order is defined by SiteMinder's Query Schemes). Placeholders are indicated in a query by "%s".
All of these queries are defined or overridden in the APS.cfg file in this section.
Default: ' + CHAR(39) + '
Recommended: not used
Complexity Level: Advanced
This keyword can be used to specify the exact sequence that single quotes (apostrophes) should be replaced with when embedded inside of other quotes in a SQL query. By default, APS will use
' + CHAR(39) + '
This is the standard supported by SQL. However, some SQL implementations may use other sequences (Microsoft Access, for example, wants CHR instead of CHAR).
Default: off
Recommended: If needed
Complexity Level: Advanced
This setting was created to work around a problem with the base release of SiteMinder 5 and only affects ODBC User Directories. If you are getting "Invalid Handle" errors in your Authentication Log, put this setting into your APS.cfg file. APS will then test any handle passed to it from SiteMinder. If the handle is invalid, APS will open its own handle to the ODBC directory.
Default: n/a
Recommended: not used
Complexity Level: Advanced
The Enumerate query overrides the query by the same name defined to SiteMinder. APS does not use this query at this time.
Default: n/a
Recommended: not used
Complexity Level: Advanced
The Get Object Info query overrides the query by the same name defined to SiteMinder. APS does not use this query at this time.
Default: n/a
Recommended: not used
Complexity Level: Advanced
The Lookup query overrides the query by the same name defined to SiteMinder. APS does not use this query at this time.
Default: n/a
Recommended: not used
Complexity Level: Advanced
The Init User query overrides the query by the same name // defined to SiteMinder. APS does not use this query at this time.
Default: SELECT Name FROM SmUser
WHERE Name='%s' AND Password='%s'
Recommended: Defaults from SiteMinder, required
Complexity Level: Basic
The Authenticate User query overrides the query by the same name defined to SiteMinder. APS uses this query to determine if the old password entered during a password change is valid.
The first parameter is the user's name (entered to SiteMinder) and the second value is the (clear-text) old password as entered during the change password process.
This query can be a stored procedure, but the replacement parameters must retain their order and meaning. If the password is encrypted, then this query almost must be a stored procedure.
Default: SELECT %s FROM SmUser
WHERE Name='%s'
Recommended: Defaults from SiteMinder, required
Complexity Level: Basic
The Get User Property query overrides the query by the same name defined to SiteMinder. APS uses this query to retrieve the attribute values defined for the user.
The first parameter is always replaced with an asterisk (retrieve all defined columns), the second parameter is the user's id. If all columns should not be returned, this query should be overridden to reference a stored view in the database that returns fewer columns. Note that only columns returned on this query can be used in overrides or as macros in mail or redirections.
The first parameter is always replaced as a constant asterisk. A query could be defined with this, but then APS could not use the query defined to SiteMinder (which contains a replacement parameter in this position).
This query can be a stored procedure (if the underlying RDBMS supports rows returned from stored procedures), but the replacement parameters must retain their order and meaning.
Default: UPDATE SmUser
SET %s='%s'
WHERE Name='%s'
Recommended: Defaults from SiteMinder, required
Complexity Level: Intermediate
The Set User Property query overrides the query by the same name defined to SiteMinder. APS uses this query to set attribute values for the user.
The first parameter is the (mapped) name of the attribute (column) to modify, the second is the value to set it to. The third parameter is the user's name.
APS does special processing when using this query. If the query defined to APS (or SiteMinder) uses UPDATE, then APS will build a query to update all columns at once, using standard SQL syntax.
If, however, a stored procedure is used for this query, APS will call the stored procedure for each change. Parameters must appear in the same order for stored procedures.
The use of the UPDATE query is for higher performance.
If column access is to be restricted, create a stored VIEW in the database and use an UPDATE query to the stored view.
Default: n/a
Recommended: not used
Complexity Level: Advanced
The Get User Properties query overrides the query by the same name defined to SiteMinder. APS does not use this query at this time.
Default: n/a
Recommended: not used
Complexity Level: Advanced
The User Properties setting overrides the setting by the same name defined to SiteMinder. APS does not use this query at this time.
Default: SELECT Name, 'User' AS Class
FROM SmUser
WHERE %s
Recommended: Defaults from SiteMinder, required
Complexity Level: Basic
The Lookup User query overrides the query by the same name defined to SiteMinder. FPS and APSExpire use this query to locate users in the directory.
The parameter is the WHERE clause built up by FPS or APSExpire.
Default: See Description
Recommended: Defaults from SiteMinder, required
Complexity Level: Basic
The Get User Groups query overrides the query by the same name defined to SiteMinder. APS uses it to return the list of group in which the current user is a member. The default query is:
SELECTSmGroup.Name FROMSmGroup, SmUser, SmUserGroup WHERE SmUser.Name='%s' AND SmUser.UserID=SmUserGroup.UserID AND SmGroup.GroupID=SmUserGroup.GroupID
The parameter is the user's name.
Each row returned represents a group name of which the user is a member.
Default: See Description
Recommended: Defaults from SiteMinder, required
Complexity Level: Basic
The Is Group Member query overrides the query by the same name defined to SiteMinder. APS uses it to determine if the user is in a specific group, both for internal purposes and to determine the result of IsInGroup() calls in an override expression. The default query is:
SELECT ID FROMSmUserGroup WHERE UserID= (SELECT UserID FROM SmUser WHERE Name='%s') AND GroupID= (SELECT GroupID FROM SmGroup WHERE Name='%s')
The first parameter is the user's name, the second is the name of the group of interest.
If the result of the query contains any rows or data, the user is considered a member of the group.
Default: See Description
Recommended: Defaults from SiteMinder, required
Complexity Level: Basic
The Set Password query overrides the query by the same name defined to SiteMinder. APS uses it to actually change the user's password. Typically, it is a stored procedure, but it need not be. The default query is:
UPDATE SmUser SET Password='%s' WHERE Name='%s'
The first parameter is the new password, the second is the name of the user.
Note that the password may be encrypted, if SmAPSEx encrypted it.
This query can be a stored procedure (and should be), but the replacement parameters must retain their order and meaning.
Passwords will always be set using this query, never the Set User Properties query above.
Default: None
Recommended: Required if login history to be kept
Complexity Level: Intermediate
The Get Login History query does not exist in the SiteMinder Query Scheme and has no default. Therefore, a query must be defined if Login History is to be maintained.
Login History contains an entry for each login attempt by a user. Typically, it will be a separate table containing two fields, the history entry and the user's name.
The next three queries are also used to manipulate login history.
The query has one parameter that is the user's name.
The Get Login History query must return a single column with the login history entry. Its name is irrelevant. The entries should be returned in date order.
The actual names of the table and columns are defined by the query and do not matter to APS.
An example query might be:
SELECT smapsLoginHistory FROM LoginHistory WHERE Name='%s' ORDER BY smapsLoginHistory
Default: None
Recommended: Required if login history to be kept
Complexity Level: Intermediate
The Set Login History query does not exist in the SiteMinder Query Scheme and has no default. Therefore, a query must be defined if Login History is to be maintained.
Login History contains an entry for each login attempt by a user. Typically, it will be a separate table containing two fields, the history entry and the user's name.
The query has two parameters. The first is the Login History value and the second is the user's name.
The actual names of the table and columns are defined by the query and do not matter to APS.
An example query might be:
INSERT INTO LoginHistory (smapsLoginHistory, Name) VALUES ('%s','%s')
Default: None
Recommended: Required if login history to be kept
Complexity Level: Intermediate
Note: The Delete Login History query does not exist in the SiteMinder Query Scheme and has no default. Therefore, a query must be defined if Login History is to be maintained.
Login History contains an entry for each login attempt by a user. Typically, it will be a separate table containing two fields, the history entry and the user's name.
Delete Login History query is used by APS to clean out old history values (before a specific date and time).
The query has two parameters. The first is the date and time to delete before and the second is the user's name.
The actual names of the table and columns are defined by the query and do not matter to APS.
An example query might be:
DELETE FROM LoginHistory WHERE LEFT(smapsLoginHistory, 15)<'%s' AND Name='%s'
Default: None
Recommended: Required if login history to be kept
Complexity Level: Intermediate
The Clear Login History query does not exist in the SiteMinder Query Scheme and has no default. Therefore, a query must be defined if Login History is to be maintained.
Login History contains an entry for each login attempt by a user. Typically, it will be a separate table containing two fields, the history entry and the user's name.
The Clear Login History query is used by APSAdmin to clean out all of the login history for a specific user.
The query has a single parameter used to identify the user.
The actual names of the table and columns are defined by the query and do not matter to APS.
An example query might be:
DELETE FROM LoginHistory WHERE Name='%s'
Default: None
Recommended: Required if FPS log to be kept
Complexity Level: Advanced
The Get FPS Log query does not exist in the SiteMinder Query Scheme and has no default. Therefore, a query must be defined if FPS Logging is to be maintained.
The FPS Log contains an entry for each FPS usage attempt by a user. Typically, it will be a separate table containing two fields, the log entry and the user's name.
The next two queries defined are also used to manipulate the FPS Log.
The query has one parameter that is the user's name.
The Get FPS Log query must return a single column with the log entry. Its name is irrelevant. The entries should be returned in date order.
The actual names of the table and columns are defined by the query and do not matter to APS.
An example query might be:
SELECT smfpsLog FROM FPSHistory
WHERE Name='%s'
ORDER BY smfpsLog
Default: None
Recommended: Required if FPS log to be kept
Complexity Level: Advanced
The Add FPS Log query does not exist in the SiteMinder Query Scheme and has no default. Therefore, a query must be defined if FPS Logging is to be maintained.
The FPS Log contains an entry for each FPS usage attempt by a user. Typically, it will be a separate table containing two fields, the log entry and the user's name.
The query has two parameters. The first is the FPS Log value and the second is the user's name.
The actual names of the table and columns are defined by the query and do not matter to APS.
An example query might be:
INSERT INTO FPSHistory (smfpsLog, Name) VALUES ('%s','%s')
Default: None
Recommended: Required if FPS log to be kept
Complexity Level: Advanced
The Clear FPS Log query does not exist in the SiteMinder Query Scheme and has no default. Therefore, a query must be defined if FPS Logging is to be maintained.
The FPS Log contains an entry for each FPS usage attempt by a user. Typically, it will be a separate table containing two fields, the log entry and the user's name.
The query has a single parameter used to identify the user.The Clear FPS Log query is used by APSAdmin to clean out all of the FPS Log entries for a specific user.
The actual names of the table and columns are defined by the query and do not matter to APS.
An example query might be:
DELETE FROM FPSHistory WHERE Name='%s'
Default: None
Recommended: Required if Auto Force Change used
Complexity Level: Advanced
Note: The Set Password Checksum query does not exist in the SiteMinder Query Scheme and has no default. Therefore, a query must be defined if Auto Force Change functionality is required.
The Auto Force Change keyword in this file tells APS to check for password changes outside of APS and, if detected, force the user to change their password at next login. It is used to automatically treat external (administrative) password changes as Force Immediate Change situations without requiring changes to the administration utility.
Under LDAP, APS uses the smapsPassword attribute to handle this functionality. Under ODBC, this is not necessarily possible. Databases are typically protected so that passwords cannot be read back.
If Auto Force Change is to be used, this and the following query must be defined. They are almost always stored procedures.
The query has a single parameter used to identify the user (the user's password has already been changed).
Typically, the implementation of this functionality uses some special attribute to store the checksum (or the entire password value, for that matter).
An example query might be:
CALL SetPasswordChecksum('%s')
Default: None
Recommended: Required if Auto Force Change used
Complexity Level: Advanced
Note: The Test Password Checksum query does not exist in the SiteMinder Query Scheme and has no default. Therefore, a query must be defined if Auto Force Change functionality is required.
The Auto Force Change keyword in this file tells APS to check for password changes outside of APS and, if detected, force the user to change their password at next login. It is used to automatically treat external (administrative) password changes as Force Immediate Change situations without requiring changes to the administration utility.
Under LDAP, APS uses the smapsPassword attribute to handle this functionality. Under ODBC, this is not necessarily possible. Databases are typically protected so that passwords cannot be read back.
If Auto Force Change is to be used, this and the previous query must be defined. They are almost always stored procedures.
The query has a single parameter used to identify the user. The function should return a numeric or boolean value, where non-zero indicates that the checksum is invalid.
Typically, the implementation of this functionality uses some special attribute to store the checksum (or the entire password value, for that matter).
An example query might be:
?=CALL TestPasswordChecksum('%s')
Default: None
Recommended: Required if FPS Answers are encrypted
Complexity Level: Advanced
Note: The Compare FPS Answer query does not exist in the SiteMinder Query Scheme and has no default. Therefore, a query must be defined if FPSí ODBC Encrypt functionality is required.
The answers to FPS questions can be encrypted in an ODBC database. This is indicated to APS using the ODBC Encrypt keyword in the [FPS-Verify] section of the APS.cfg file. This query is one way that sites can implement this encryption.
There is no default for this query. If not defined and ODBC Encrypt was specified in APS.cfg, FPS will generate an error, since it won't know how to compare an encrypted answer (this is assuming that the encryption is not being performed by SmAPSEx).
The query must be a stored procedure that takes three arguments (or, at least, three substitution parameters) and return a numeric or boolean value (non-zero indicates that the compare is true).
The first parameter is the user name. The second parameter is the name of the attribute, as configured to APS. The third parameter is the user-entered answer (in clear text).
The implementation of this function usually encrypts (or hashes) the user supplied value (the third parameter) and compares it to the value stored in the user entry.
An example query might be:
?=CALL CompareFPSAnswer('%s', '%s', '%s')
Default: None
Recommended: Required if groups maintained by APSAdmin
Complexity Level: Advanced
Note: The Add To Group query does not exist in the SiteMinder Query Scheme and has no default. Therefore, a query must be defined if the APSAdmin API is to be used to maintain group memberships.
The APSAdmin API functions use this query to add users to an existing group (creation of groups is not supported).
There is no default query for this purpose. If not specified and a user must be added to a group, an error will be logged and the update will fail.
An example query might be:
INSERT INTO SmUserGroup (UserID, GroupID) VALUES ('%s', '%s')
Default: None
Recommended: Required if groups maintained by APSAdmin
Complexity Level: Advanced
Note: The Remove From Group query does not exist in the SiteMinder Query Scheme and has no default. Therefore, a query must be defined if the APSAdmin API is to be used to maintain group memberships.
The APSAdmin API functions use this query to remove users from an existing group.
There is no default query for this purpose. If not specified and a user must be removed from a group, an error will be logged and the update will fail.
An example query might be:
DELETE FROM SmUserGroup
WHERE UserID='%s' AND
GroupID='%s'APSAdmin
Default: None
Recommended: Required in certain APSAdmin scenarios.
Complexity Level: Advanced
Note: The Admin Translation query does not exist in the SiteMinder Query Scheme and has no default. Therefore, a query must be defined if the APSAdmin API is to be used in certain scenarios.
This query takes a single parameter that is the value entered by the administrator on the APSAdmin user selection form. The query is expected to return a single record with at least one column that is the user's name (the one used in all of the other queries). If not specified, then the query is not used and the data entered is expected to be the user's name. If multiple records or no records are returned, a "User record could not be found" error is displayed to the administrator. If multiple records are returned, an error is issued to the console log as well.
This query is typically used if the administrator is expected to identify users to APSAdmin using something other than userid, such as membership number.
An example query might be:
SELECT Name FROM Users WHERE MemberID='%s'
|
Copyright © 2014 CA.
All rights reserved.
|
|