Implements custom user authentication.
In the following table, the description indicates the SQL data type. If you are writing your script in Java or .NET, you should use the appropriate corresponding data type. See SQL-Java data types and SQL-.NET data types.
In SQL scripts, you can specify event parameters by name or with a question mark. Using question marks has been deprecated and it is recommended that you use named parameters. You cannot mix names and question marks within a script. If you use question marks, the parameters must be in the order shown below and are optional only if no subsequent parameters are specified (for example, you must use parameter 1 if you want to use parameter 2). If you use named parameters, you can specify any subset of the parameters in any order.
Parameter name for SQL scripts | Description | Order (deprecated for SQL) |
---|---|---|
s.authentication_status | INTEGER. This is an INOUT parameter. | 1 |
s.remote_id | VARCHAR(128). The MobiLink remote ID. You can only reference the remote ID if you are using named parameters. | Not applicable |
s.username | VARCHAR(128). The MobiLink user name. | 2 |
s.password | VARCHAR(128). The password for authentication purposes. If the user does not supply a password, this value is null. | 3 |
s.new_password | VARCHAR(128). The new password, if this is being used to reset the password. If the user does not change their password, this value is null. | 4 |
s.new_remote_id | VARCHAR(128). The MobiLink remote ID, if the remote ID is new in the consolidated database. If the remote ID is not new, the value is null. | |
s.new_username | VARCHAR(128). The MobiLink user name, if the username is new in the consolidated database. If the user name is not new, the value is null. |
Use MobiLink built-in user authentication mechanism.
The MobiLink server executes this event upon starting each synchronization. It is executed in a transaction before the begin_synchronization transaction.
You can use this event to replace the built-in MobiLink authentication mechanism with a custom mechanism. You may want to call into the authentication mechanism of your DBMS, or you may want to implement features not present in the MobiLink built-in mechanism, such as password expiry or a minimum password length.
The parameters used in an authenticate_user event are as follows:
authentication_status The authentication_status parameter is required. It indicates the overall success of the authentication, and can be set to one of the following values:
Returned Value | authentication_status | Description |
---|---|---|
V <= 1999 | 1000 | Authentication succeeded. |
2000 <= V <= 2999 | 2000 | Authentication succeeded: password expiring soon. |
3000 <= V <= 3999 | 3000 | Authentication failed: password expired. |
4000 <= V <= 4999 | 4000 | Authentication failed. |
5000 <= V <= 5999 | 5000 | Unable to authenticate because the remote ID is already in use. Try the synchronization again later. |
6000 <= V | 4000 | If the returned value is greater than 5999, MobiLink interprets it as a returned value of 4000. |
The value is sent to the client so it can be used to customize authentication behavior at the client.
See:
username This optional parameter is the MobiLink user name.
remote_id The MobiLink remote ID. You can only reference the remote ID if you are using named parameters.
password This optional parameter indicates the password for authentication purposes. If the user does not supply a password, this is null.
new_password This optional parameter indicates a new password. If the user does not change their password, this is null.
new_remote_id This optional parameter indicates a new remote ID. If the remote ID is not new, this is null.
new_username This optional parameter indicates a new user name. If the user name is not new, this is null.
SQL scripts for the authenticate_user event must be implemented as stored procedures.
When the two authentication scripts are both defined, and both scripts return different authentication_status codes, the higher value is used.
The authenticate_user script is executed in a transaction along with all authentication scripts. This transaction always commits.
There are predefined scripts that you can use for the authenticate_user event to simplify authentication using LDAP, IMAP and POP3 servers.
A typical authenticate_user script is a call to a stored procedure. The order of the parameters in the call must match the order above. The following example uses ml_add_connection_script to assign the event to a stored procedure called my_auth.
CALL ml_add_connection_script( 'ver1', 'authenticate_user', 'call my_auth ( {ml s.authentication_status}, {ml s.username} )' ) |
The following SQL Anywhere stored procedure uses only the user name to authenticate—it has no password check. The procedure ensures only that the supplied user name is one of the employee IDs listed in the ULEmployee table.
CREATE PROCEDURE my_auth( inout @auth_status int, in @user_name varchar(128) ) BEGIN IF EXISTS ( SELECT * FROM ulemployee WHERE emp_id = @user_name ) THEN MESSAGE 'OK' type info to client; SET @auth_status = 1000; ELSE MESSAGE 'Not OK' type info to client; SET @auth_status = 4000; END IF END |
The following call to a MobiLink system procedure registers a Java method called authenticateUser as the script for the authenticate_user event when synchronizing the script version ver1. This syntax is for SQL Anywhere consolidated databases.
CALL ml_add_java_connection_script( 'ver1', 'authenticate_user', 'ExamplePackage.ExampleClass.authenticateUser' ) |
The following is the sample Java method authenticateUser. It calls Java methods that check and, if needed, change the user's password.
public String authenticateUser( ianywhere.ml.script.InOutInteger authStatus, String user, String pwd, String newPwd ) throws java.sql.sqlException { // A real authenticate_user handler would // handle more authentication code states. _curUser = user; if( checkPwd( user, pwd ) ) { // Authentication successful. if( newPwd != null ) { // Password is being changed. if( changePwd( user, pwd, newPwd ) ) { // Authentication OK and password change OK. // Use custom code. authStatus.setValue( 1001 ); } else { // Authentication OK but password // change failed. Use custom code. java.lang.System.err.println( "user: " + user + " pwd change failed!" ); authStatus.setValue( 1002 ); } } else { authStatus.setValue( 1000 ); } } else { // Authentication failed. authStatus.setValue( 4000 ); } return ( null ); } |
The following call to a MobiLink system procedure registers a .NET method called AuthUser as the script for the authenticate_user connection event when synchronizing the script version ver1. This syntax is for SQL Anywhere consolidated databases.
CALL ml_add_dnet_connection_script( 'ver1', 'authenticate_user', 'TestScripts.Test.AuthUser' ) |
The following is the sample .NET method AuthUser. It calls .NET methods that check and, if needed, change the user's password.
namespace TestScripts { public class Test { string _curUser = null; public string AuthUser( ref int authStatus, string user, string pwd, string newPwd ) { // A real authenticate_user handler would // handle more authentication code states. _curUser = user; if( CheckPwd( user, pwd ) ) { // Authentication successful. if( newPwd != null ) { // Password is being changed. if( ChangePwd( user, pwd, newPwd ) ) { // Authentication OK and password change OK. // Use custom code. authStatus = 1001; } else { // Authentication OK but password // change failed. Use custom code. System.Console.WriteLine( "user: " + user + " pwd change failed!" ); authStatus = 1002; } } else { authStatus = 1000 ; } } else { // Authentication failed. authStatus = 4000; } return ( null ); }}} |
For a more detailed example of an authenticate_user script written in C# in .NET, see .NET synchronization example.
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |