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 CA SiteMinder® SQL Query Scheme dialog.
Note: The “SM_” prefix in column names is reserved for additional special names required by CA SiteMinder®. Column names in your user directory should not begin with the “SM_” prefix. Policy Server errors will occur during user lookups if this prefix appears in column names.
You can configure a SQL Query Scheme that finds user data in the relational database that you are using as a user store.
Note: The following procedure assumes that you are creating an object. You can also copy the properties of an existing object to create an object.
To configure a SQL Query Scheme
Objects related to user directories appear on the left.
The SQL Query Scheme screen appears.
The Create SQL Query Scheme screen appears.
Note: Click Help for descriptions of settings and controls, including their respective requirements and limits.
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:
When you configure the Policy Server to use a user store residing in a relational database, the Name parameter for a user must be unique so that CA SiteMinder® can correctly identify the user. Thus, two users cannot have the same user name.
The query is saved. You can associate the query scheme with a user directory connection
You can select an SQL Query Scheme using the User Directory Dialog.
To select an SQL Query Scheme
The SQL query scheme is saved to the directory connection.
Note: If you are using MS SQL Server, and your queries are returning names that include the apostrophe character (for example, O’Neil), you must 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’.
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
Stored procedures in SQLServer must meet the following requirements:
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
Stored procedures in MySQL must meet the following requirements:
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 below:
Stored procedures in Oracle functions must meet the following requirement:
EncryptPW Function
The EncryptPW function must return an integer value, as follows:
Specifies success.
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:
Specifies success.
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;
Synchronous calls are reliable, returning only after the request is complete. Asynchronous calls return immediately. A caller can choose to abandon an asynchronous call and avoid delays associated with network failures.
CA SiteMinder® supports asynchronous calls to the following databases:
The following registry options are stored under the registry sub-key Netegrity\SiteMinder\CurrentVersion\Database.
Determines whether database calls are made asynchronously.
Values: 0 (no); 1 (yes)
Default: 0
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
The amount of time to allow for a connection to log in to the database.
Values: minimum of 1 second
Default: 15 seconds
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, asynchronous call support causes a very small memory leak per abandoned connection. You may choose to extend the timeouts to reduce the number of failovers in an unreliable network by adjusting the settings discussed in the table above.
The following criteria apply to ODBC connection pooling:
Copyright © 2013 CA.
All rights reserved.
|
|