SQL Query Scheme Dialog

The Policy Server uses SQL Query Schemes to build queries that find user data in a relational database. You create and edit SQL Query Schemes using the stmndr SQL Query Scheme Dialog.
casso1283
HID_odbc-query-scheme
The Policy Server uses SQL Query Schemes to build queries that find user data in a relational database. You create and edit SQL Query Schemes using the
SiteMinder
SQL Query Scheme Dialog.
The “SM_ -- prefix in column names is reserved for additional special names required by
SiteMinder
. Column names in your user directory should not begin with the “SM_ -- prefix. Policy Server errors occur during user lookups if this prefix appears in column names.
SQL Query Scheme Settings
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:
  • * with the SQL wild character % in a multi-character SQL query
  • & with the SQL wild character _ in a single character SQL query
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.
  • Name
    Defines the SQL query scheme name.
  • Description
    Describes the SQL query scheme.
  • Enumerate
    Defines a query that fetches a list of the names of group objects in the database. The Administrative UI 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
    :
    select Name, 'Group' as Class from SmGroup order by Class
  • Authenticate User
    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
    :
    select Name from SmUser where Name = '%s' and Password = '%s'
  • Get Group Property
    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
    :
    select %s from SmGroup where Name = '%s'
  • Get Group Properties
    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
    :
    Name, GroupId
  • Get User Groups
    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
  • Get User/Group Info
    Discovers the class of a given object. The %s parameter is the name of the object that can be a User or a Group
    Default
    :
    select Name, 'User' from SmUser where Name = '%s' Union select Name, 'Group' from SmGroup where Name = '%s'.
  • Get User Property
    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
    :
    select %s from SmUser where Name = '%s'
  • Get User Properties
    Defines a comma-separated list of user attributes. These are expected to reside in the same table as the user name.
    Default
    :
    Name, UserId, FirstName, LastName, TelephoneNumber, EmailAddress, PIN,
    Mileage, Disabled
  • Init User
    Default
    :
    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’
  • Is Group Member
    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
    :
    select Id from SmUserGroup where UserId = (select UserId from SmUser where
    Name = '%s') and GroupId = (select GroupId from SmGroup where Name = '%s')
  • Lookup
    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
    :
    select Name, 'User' as Class from SmUser where Name %s Union select Name,
    'Group' as Class from SmGroup where Name %s order by Class
  • Lookup Groups
    Specifies an attribute from the group table in the database in a User Directory query.
    Default
    :
    select Name, 'Group' as Class from SmGroup where %s
    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
    .
  • Lookup Users
    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.
    Default
    :
    select Name, 'User' as Class from SmUser where %s
    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.
  • Set Group Property
    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
    :
    update SmGroup set %s = '%s' where Name = '%s'
  • Set User Password
    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
    :
    update SmUser set Password = '%s' where Name = '%s'
  • Set User Property
    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
    :
    update SmUser set %s = '%s’ where Name = '%s'
  • Use Literal Query
    Select to use the literal query type.
    Default
    : This is the default query type.
  • Use Bind Query
    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
    :
    • Advanced Password Services (APS) does not support bind-based parameters.
    • A query uses bind-based parameters if it contains a question mark (?) and literal parameters if it contains '%s'.
    • A query cannot contain '%s' and question marks (?) together.