|
|
|
The SQL Query Scheme Dialog contains fields that describe SQL queries that that Policy Server uses to access user and group information. The default values for each query field correspond to the SiteMinder sample relational database schema called SmSampleUsers. You must change the table and column names if you are using a different database schema.
You can use * for multi-character SQL searches and & for single character searches. When the Policy Server performs the search, it replaces:
The “%s— expression in a query is a place-holder for a parameter to be supplied by the Policy Server when the query is executed.
Unique name of the SQL query scheme.
Brief description of the SQL query scheme.
Defines a query that fetches a list of the names of group objects in the database. The Policy Server User Interface lists these, along with the class name (User or Group) if desired. It is advisable to enumerate only the groups and not the users (omit the union in this query). Individual users can still be entered by typing them into the Users/Groups Dialog.
Default value:
select Name, 'Group' as Class from SmGroup order by Class
Defines a query that is used when a SiteMinder administrator specifies a value without an attribute name in a User Directory search query.
The query looks in the default user name and group name columns of the database and tries to match the value specified in the query. The result of the query is the combination of all of the values that match the search criteria from the user and group columns.
For example, if an administrator searches for J*, this query returns a list of all users and groups that begin with the letter J.
Default value:
select Name, 'User' as Class from SmUser where Name %s Union select Name, 'Group' as Class from SmGroup where Name %s order by Class
Defines a query that
Default value:
select Name, 'User' as Class from SmUser where %s
This query is used when a SiteMinder administrator specifies an attribute from the user table in the database in a User Directory query. It is also used by the Policy Server during directory mappings, and when authorizing users against an ODBC database.
For example, if an administrator specifies an attribute of name=J*, this query returns a list of all users whose Name attribute begins with J.
SiteMinder executes this query along with Lookup Groups to provide a combined list of results that includes users and groups. In the example above, the Policy Server would return a list of users and groups whose Name attributes begin with the letter J.
Defines a query that
Default value:
select Name, 'Group' as Class from SmGroup where %s
This query is used when a SiteMinder administrator specifies an attribute from the group table in the database in a User Directory query.
For example, if an administrator specifies an attribute of name=J*, this query returns a list of all groups whose Name attribute begins with J.
The Policy Server executes this query along with Lookup Users to provide a combined list of results that includes users and groups. In the example above, the Policy Server would return a list of users and groups whose Name attributes begin with the letter J.
Defines a query that
Default value:
select Name, 'User' from SmUser where Name = '%s' Union select Name, 'Group' from SmGroup where Name = '%s'
The purpose of this query is to discover the class of a given object. The %s parameter is the name of the object that can be a User or a Group.
Defines a query that
Default value:
select Name from SmUser where Name = '%s'
This query is used when a SiteMinder administrator specifies a User Directory query to find out if a user with a given name exists in the database. The %s parameter represents the user name.
If the manual entry query does not contain a WHERE statement, the WHERE statement from Init User is appended. For example:
Init User: select Name from SmUser where Name = ’%s’
Manual Entry: Select Name from customers
Result: select Name from customers where Name = ’%s’
If the manual entry query contains a WHERE statement, the portion of the query following the Init User WHERE statement is appended. For example:
Init User: select Name from SmUser where Name = ’%s’
Manual Entry: Select Name from customers where balance >1000
Result: select Name from customers where balance >1000 and Name = ’%s’
Defines a query that is executed to get a password for a user. The %s parameter represents the user name.
If you are configuring a query scheme for an Oracle database and you are using Oracle’s encrypted password feature, replace the entire query string with the word connect. Using the word connect for this query indicates to the Policy Server that a user’s name and password should be evaluated by the Oracle encrypted password feature.
Default value:
select Name from SmUser where Name = '%s' and Password = '%s'
Defines a query that should answer the question of group membership for a particular user. The first %s parameter is the user name and the second is the group name.
Default value:
select Id from SmUserGroup where UserId = (select UserId from SmUser where Name = '%s') and GroupId = (select GroupId from SmGroup where Name = '%s')
Defines a query that fetches the names of the groups of which the user is a member. The %s parameter represents the user name.
select SmGroup.Name from SmGroup, SmUser, SmUserGroup where SmUser.Name = '%s' and SmUser.UserId = SmUserGroup.UserId and SmGroup.GroupId = SmUserGroup.GroupId
Defines a comma-separated list of user attributes. These are expected to reside in the same table as the user name.
Default value:
Name, UserId, FirstName, LastName, TelephoneNumber, EmailAddress, PIN, Mileage, Disabled
Defines a query that fetches the value of a user property. The first %s parameter is the property name, the second is the user name. All properties are assumed to have string values.
The property must be one of the properties specified in the Get User Properties field.
Default value:
select %s from SmUser where Name = '%s'
Defines a query that sets the value of a user property. The first %s parameter is the property value, the second is the property name, and the third is the user name. Note that this is a SQL 'update' rather than a SQL 'select'. Database write access is required. All properties are assumed to have string values.
The property must be one of the properties specified in the Get User Properties field.
Default value:
update SmUser set %s = '%s’ where Name = '%s'
Defines a query that sets the value of a user password is set. Note that this is a SQL 'update' rather than a SQL 'select'. Database write access is required. All properties are assumed to have string values.
Default value:
update SmUser set Password = '%s' where Name = '%s'
Defines a comma separated list of group attributes. These attributes are used to search the contents of a group, or to bind policies to group attributes. The attributes are expected to reside in the same table as the group name.
Default value:
Name, GroupId
Defines a query that is similar to that in the Get User Property field, except that it returns a property of the group to which a user belongs. The first %s parameter is the property name, the second is the group name. All properties are assumed to have string values.
The property must be one of the properties specified in the Get Group Properties field.
Default value:
select %s from SmGroup where Name = '%s'
Defines a query that sets the value of a property. The first %s parameter is the property value, the second is the property name, and the third is the group name. Note that this is a SQL 'update' rather than a SQL 'select'. Database write access is required. All properties are assumed to have string values.
The property must be one of the properties specified in the Get Group Properties field.
Default value:
update SmGroup set %s = '%s' where Name = '%s'
Select to use the literal query type.
Default: This is the default query type.
Select to use bind-based parameters in the query. Replace each instance of '%s' with a question mark (?) to use bind-based parameters in the query. Any instance not changed should remain as '%s'.
Restrictions:
| Copyright © 2011 CA. All rights reserved. | Email CA Technologies about this topic |