Policy Server Guides › Policy Server Configuration Guide › User Directories › SQL Query Schemes

SQL Query Schemes

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 SQL Query Scheme dialog.

Note: The “SM_” prefix in column names is reserved for special names. Do not begin column names in your user directory with the “SM_” prefix.

Configure a SQL Query Scheme

You can configure a SQL Query Scheme that finds user data in the user store relational database.

Follow these steps:

1. Click Infrastructure, Directory.
2. Click SQL Query Scheme.
3. Click Create SQL Query Scheme.
4. Verify that the Create New Object option is selected and click OK.
5. Type a name and description in the fields in the General area.
6. Update the contents of the query fields to correspond to your database schema.

   Configure each of the queries to work with your relational database. Replace the following database table and column names with the table and column names from your relational database:

   • Name

     The Name parameter for a user is unique.

   • SmUser—table
   • SmGroup—table
   • Password
   • SmUserGroup—table
   • Id
   • UserID
   • FirstName
   • LastName
   • TelephoneNumber
   • EmailAddress
   • Mileage
   • PIN
   • GroupID
   • Disabled
7. Select a query type and click Submit.

   The query is saved. You can associate the query scheme with a user directory connection.

8. Restart the Policy Server.

Add SQL Query Schemes to ODBC User Directory Connections

You can select an SQL Query Scheme using the User Directory Dialog.

Follow these steps:

1. Open the User Directory pane for an existing user directory connection object.
2. Select the SQL query scheme from the SQL Query Scheme list, and click Submit.
3. Restart the Policy Server using the Policy Server Management Console.

Note: If your queries are returning names that include the apostrophe character (for example, O’Neil), replace any instance of ‘%s’ in the query strings to ‘’%s’’. To avoid this problem, base your queries on user IDs that do not include apostrophes, or modify the query strings that include ‘%s’.

Configure SQL Query Schemes for Authentication Using Stored Procedures

When stored procedures are required for authentication with ODBC user directories, configure the SQL query scheme to call the stored procedure as follows:

SQLServer

Syntax: Call Procedure_Name %s , %s

Example: Call EncryptPW %s , %s

The Stored procedures in SQLServer must meet the following requirements:

• The first parameter must be the username, and the second parameter must be the password.
• All parameters must be defined using the keyword OUT.
• The stored procedure must return an integer value.

The following example shows how to create a stored procedure for a SQLServer user directory:

CREATE PROCEDURE EncryptPW
@UserName varchar(20) OUT ,
@PW  varchar(20) OUT
AS
SELECT Smuser.name from Smuser where Smuser.name= @UserName and  password = @PW
SELECT Smuser.password from Smuser where name= @UserName and  password = @PW
return 0

MySQL

Syntax: Call Procedure_Name %s, %s

Example: Call EncryptPW %s, %s

The Stored procedures in MySQL must meet the following requirements:

• The first parameter must be the username, and the second parameter must be the password.
• The stored procedure does not return a value.

The following example shows how to create a stored procedure for a MySQL user directory:

CREATE PROCEDURE EncryptPW(INOUT p_UserName varchar(20), INOUT p_PW varchar(20))
BEGIN
SELECT SmUser.Name into p_UserName from test.SmUser where SmUser.Name =
p_UserName and SmUser.Password = p_PW;
SELECT SmUser.Password into p_PW from test.SmUser where SmUser.Name =
p_UserName and SmUser.Password = p_PW;
END;

Oracle Functions

For Oracle user directories, you can create the following functions using the templates:

• EncryptPW
• ChangePW

The stored procedures in Oracle functions must meet the following requirement:

• The first parameter must be the username, and the second parameter must be the password.

EncryptPW Function

The EncryptPW function must return an integer value, as follows:

• The value of zero specifies success.
• The value of one specifies failure.

You can use the following template to create the EncryptPW function:

CREATE OR REPLACE FUNCTION EncryptPW(p_UserName IN OUT SmUser.Name%type, p_PW IN OUT SmUser.Password%type)
RETURN INTEGER IS
nRet INTEGER :=1;
nCount NUMBER := 0;
BEGIN
select count(*) into nCount
from SmUser
where SmUser.Name = p_UserName and SmUser.Password = p_PW;
IF (nCount = 1) THEN
SELECT SmUser.Name  into p_UserName
from SmUser
where SmUser.Name = p_UserName and SmUser.Password = p_PW;
SELECT SmUser.Password into p_PW
from SmUser
where SmUser.Name = p_UserName and SmUser.Password = p_PW;
RETURN 0;
END IF;
RETURN nRet;
END EncryptPW;

ChangePW Function

The ChangePW function must return an integer value, as follows:

• The value of one specifies success.
• The value of zero specifies failure.

You can use the following template to create the ChangePW function:

CREATE OR REPLACE FUNCTION ChangePW(p_PW IN SmUser.Password%type, p_UserName IN SmUser.Name%type)
RETURN INTEGER IS
nRet INTEGER :=1;
nCount NUMBER := 0;
BEGIN
select count(*) into nCount 
from SmUser 
where SmUser.Name = p_UserName;
IF (nCount = 1) THEN
UPDATE SmUser 
SET SmUser.Password = p_PW 
where SmUser.Name = p_UserName;
COMMIT;
RETURN 0;
END IF;

Asynchronous Call Support During Failover and Connection Pooling

Synchronous calls return only after the request is complete. Asynchronous calls return immediately. To avoid the delays that are associated with network failures, a caller can abandon an asynchronous call.

You can make asynchronous calls to the following databases:

• SQLServer
• Oracle 8 through Oracle 11g on Windows NT and Solaris

Asynchronous Call Support Configuration

The following registry options are stored under the registry sub-key Netegrity\SiteMinder\CurrentVersion\Database.

AsynchronousCalls

Determines whether database calls are made asynchronously.

Values: 0 (no); 1 (yes)

Default: 0

AsynchronousSleepTime

Specifies the amount of time between calls to wait before checking the status of an outstanding SQL call.

Values: 0 to n milliseconds

Default: 15 milliseconds

LoginTimeout

The amount of time to allow for a connection to log in to the database.

Values: minimum of 1 second

Default: 15 seconds

QueryTimeout

The amount of time to allow for a query to complete before canceling it.

Values: minimum of 1 second

Default: 15 seconds

Note: When SQL Server is running on Windows NT, an asynchronous call support causes a small memory leak. You may choose to extend the timeouts to reduce the number of failovers in an unreliable network by adjusting the settings.

Configure Oracle 8 on Solaris for Asynchronous Calls

The Merant ODBC driver for Oracle on Solaris 2.6 and 2.7 may cause a core dump when asynchronous calls are supported. This is due to an Oracle bug which is fixed as follows:

Oracle 8.0.5

To each data source in system_odbc.ini in the <install_directory>/db directory, add the entry:

ArraySize=1

Note: This change turns off multi-row fetches and will affect performance when loading large policy stores.

Oracle 8.1.5

1. Remove or rename libclntsh.so in siteminder/bin and/or
   siteminder/odbc/lib.
2. Verify that the Oracle client has libclntsh.so installed in $ORACLE_HOME/lib. Refer to the Oracle documentation for installation and rebuilding instructions.
3. Make sure that LD_LIBRARY_PATH references the Oracle client library directory $ORACLE_HOME/lib.

If you get the following log message:

[MERANT][ODBC Oracle 8 driver][Oracle 8] ORA-03106: fatal two-task communication protocol error

add an entry to the affected data source in system_odbc.ini in the <install directory>/db directory:

ArraySize=<value_greater_than 60000>

This increases the size of the multi-row fetch buffer to eliminate the error. The default value of this variable is 60000 bytes. The maximum allowed value is 4 Gigabytes.

ODBC Connection Pooling

The following criteria apply to ODBC connection pooling:

• The ODBC data source pools connections. When multiple directories use a data source, the number of connections available can reach the total number of directories and stores. The policy store must use the data source, and another store on the Policy Server must use the data source.
• SQL Server is more limited than Oracle in how connections are shared. Two callers cannot share an open result set. Concurrent activity can be limited, especially on multi-processor machines. Increasing the number of connections generally improves concurrency.
• Oracle allows for multiple callers to share a connection, but can serialize calls internally.
• When the connections are shared, the number of active requests are balanced across the connections in a pool.
• When Policy Server shuts down, it closes open connections.