There is no "standard" way to update an ODBC schema. Each vendor implements their own way to do it. In addition, most sites have policies or requirements for how schemas are updated, naming conventions and data layout issues.
APS tries to place as few restrictions as possible for the schema that it needs. The attributes described in the first section of this chapter must exist for every user entry in the User Directory that will be maintained by APS, with the exception of the attributes marked as optional and suppressed in the [MAPPINGS] section of the APS Configuration File.
How sites accomplish this is entirely up to the administrators at the site. There are a number of choices available; no tools are provided to do this for you. CA Professional Services has considerable experience in this area and can help or advise you, if you so desire.
The simplest way to organize the schema for an ODBC directory is to just add all of the non-multiple-valued attributes to the existing user table as columns, then add two new tables for the two multi-valued attributes.
However, this is not always possible due to naming policies and possible table size constraints.
Columns (attributes) need not have the names defined below. If the columns are to be renamed, the names must be remapped as described in the section starting on Unsupported "Page" Cross-Reference.
Unlike SiteMinder (which reads each column as a separate query), APS "wants" to read an entire row to retrieve the user. The contents of the row is cached so that it is available for settings overrides and mail replacement values. This means that the entire user row is retrieved. This may have both capacity and security implications.
This can be easily tuned by defining a stored view of the user that restricts APS to "seeing" only those columns that the site wishes APS to have access to. A site can use the query overrides in the [ODBC] section of the APS.cfg file to define APS-specific versions of these queries that use these stored views. The APS view of a user can be different from SiteMinder's view of the data.
Some sites have quite successfully stored the APS-specific information in a separate table from the rest of the user information. Those sites merely used a stored query to join the APS table with the user table to get the views that they want.
Many of the attributes described below are listed as "variable length". In each case, the length listed is the maximum length used by APS itself. However, these columns typically contain a "comment" that can be placed on the information by a site. The comment can be of any length allowed by that site.
Some attributes, specifically smapsGenerationalRedirects and smapsHistory, can become extremely large, depending on site usage.
smapsGenerationalRedirects contains a list of generational redirect information. Normally, this is actually a relatively small (or non-existent) amount of information. However, if sites use a large number of generational redirects, the data storage requirements of this column can increase. A site should review its expected use of this feature to determine the amount of storage required.
The smapsPassword column contains encrypted data containing information about previously used passwords. APS places a hard restriction on the maximum length of this data. That hard limit is listed with the attribute description. However, the reality is that it would take an automated password changer a considerable amount of time to enlarge the data to that size.
Since the information is encrypted, it is impossible to just truncate it; it must be stored in its entirety.
To reserve the full amount of storage for every user row may be unreasonable. What many sites do is set the length of this column to some smaller value (for example 2k), then run triggers within the database to alert administrators when a certain percentage of this space starts to get used (say 90% - the field grows very slowly, usually less than 100 bytes per password change). If a single test user starts to consume too much, approaching the maximum length defined, the site clears the value for that one account. If multiple users start to approach the value, either determined by triggers or by periodic examination of the data, the column is enlarged.
APS does not support native ODBC data formats at this time. This is currently under review by the product team for a future enhancement.
Two attributes are implemented as separate tables that will grow. The Login History table is maintained by APS and will be pruned as each user authenticates to prevent infinite growth. The FPS History table will grow indefinitely, records written each time a user runs FPS.
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 executing 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 might 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 the [ODBC] section.
The Enumerate query overrides the query by the same name defined to SiteMinder. APS does not use this query at this time.
The Get Object Info query overrides the query by the same name defined to SiteMinder. APS does not use this query at this time.
The Lookup query overrides the query by the same name defined to SiteMinder. APS does not use this query at this time.
The Init User query overrides the query by the same name // defined to SiteMinder. APS does not use this query at this time.
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 default query is:
SELECT Name FROM SmUser WHERE Name='%s' AND Password='%s'
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.
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 default query is:
SELECT %s FROM SmUser WHERE Name='%s'
The first parameter is always replaced by 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 overriden 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.
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 default query is:
UPDATE SmUser SET %s='%s' WHERE Name='%s'
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.
The Get User Properties query overrides the query by the same name defined to SiteMinder. APS does not use this query at this time.
The User Properties setting overrides the setting by the same name defined to SiteMinder. APS does not use this query at this time.
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 default query is:
SELECT Name, 'User' AS Class FROM SmUser WHERE %s
The parameter is the WHERE clause built up by FPS or APSExpire.
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
SELECT SmGroup.Name FROM SmGroup, 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 that the user is a member of.
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 FROM SmUserGroup 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.
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.
Note: 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
Note: 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')
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.
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'
Note: 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'
Note: 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
Note: 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')
Note: 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 Clear FPS Log query is used by APSAdmin to clean out all of the FPS Log entries 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 FPSHistory WHERE Name='%s'
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')
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')
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')
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')
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'
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.
The APSAdmin APS functions use this query to translate input user identity (on the APSAdmin user selection form) to a user name that can be used on future queries.
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 MemberNumber='%s'
If the account satisfies the conditions for any Ignore keyword in the APS.cfg file, APS will not perform any authentication time processing for this user, including detecting if the account is disabled or detecting disabling conditions (such as password expired).
APS will recognize that an account is disabled if any of the following are true (this is not necessarily the order in which APS actually does detection):
APS itself can disable an account for only four reasons (APS will never set the native account status bits):
If Auto Reset Failure Count setting is in effect:
smapsDisabledUntil is set to the current date and time, plus the number of minutes indicated by the Max Failures On Change setting followed by the text "Failure Count".
smapsDisabledUntil is set to "FOREVER Failure Count".
APS never enables an account. Some of the mechanisms used by APS to maintain the user status have built-in reset capabilities (dates). APS does not actually update a user record to re-enable it.
To re-enable an account, a site must ensure that all of the above criteria that APS uses to detect that it is already disabled are not true. In other words, the account cannot be a member of a disabled group, smapsDisableUntil must be set correctly, etc.
Even then, it may appear that an account remains disabled. The usual cause is that while the account does get enabled, the conditions that caused it to become disabled remain, thus causing APS to just disable it again. These reasons are:
APS does not support native ODBC (RDBMS) data formats, such as dates and binary formats. However, some sites have quite successfully used temporary tables and triggers to convert the APS maintained data into and out of native formats. CA Professional Services has some experience in this area and may be able to help, if this is required.
The Auto Force Change setting makes no sense for ODBC directories and is not support.
Disabled groups are not supported.
FPS fully supports ODBC (RDBMS) directories. There is special processing for handling encrypted FPS answers (See ODBC Encrypt.).
APSExpire has special settings for ODBC directories so that the user directory can be partitioned into smaller "chunks" for processing. See the chapter entitled Daily Processing (APSExpire) for details.
APSAdmin fully supports ODBC User Directories. Using the Admin Translation query, users can be selected using arbitrary identification.
APS does a few things that significantly improve performance and fault tolerance when dealing with ODBC User Directories. Note that if improperly configured, performance can actually be worse; some fine-tuning is always desirable.
However, this may come at a cost. If there are a large number of columns in the user table that APS should not access, they will be returned anyway. In this case, it may be desirable to use a stored view instead of accessing the raw table (this is generally considered good practice for SQL databases anyway).
Stored procedures are extremely difficult to implement and debug, since they are so implementation-dependent. APS uses the "standard" ODBC mechanism for invoking stored procedures. Please see the documentation for the directory vendor for any special formatting or escapes required to implement stored procedures or embedded function calls within queries. CA cannot provide support for writing these queries, as ever vendor differs in their implementation.
However, CA Professional Services may be contracted to help a site develop and troubleshoot such queries.Some ODBC implementations limit the number of characters allowed for a column name (or the number of significant characters). These implementations may require that the default attribute names be remapped to shorter names.
|
Copyright © 2014 CA.
All rights reserved.
|
|