Previous Topic: Add SQL Query Schemes to ODBC User Directory Connections

Next Topic: Asynchronous Call Support During Failover and Connection Pooling

How to Configure SQL Query Schemes for Authentication via 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

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:

EncryptPW Function

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

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:

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;


Copyright © 2010 CA. All rights reserved. Email CA about this topic