Implements a custom user authentication mechanism.
In the following table, the description provides 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.hashed_password | BINARY(32). If the user does not supply a password, this value is null. | 3 |
s.hashed_new_password | BINARY(32). If this event is not being used to change the user's password, this value is null. | 4 |
Use MobiLink built-in user authentication mechanism.
This event is identical to authenticate_user except for the passwords, which are in the same hashed form as those stored in the ml_user.hashed_password column. Passing the passwords in hashed form provides increased security.
A one-way hash is used. A one-way hash takes a password and converts it to a byte sequence that is (essentially) unique to each possible password. The one-way hash lets password authentication take place without having to store the actual password in the consolidated database.
Due to incremental improvements in the quality of the hash across MobiLink versions, this script can be called multiple times during an authentication sequence for a user.
When authenticate_user and authenticate_user_hashed are both defined, and both scripts return different authentication_status codes, the higher value is used.
A typical authenticate_user_hashed script is a call to a stored procedure. The order of the parameters in the call must match the order above. The following example calls ml_add_connection_script to assign the event to a stored procedure called my_auth.
CALL ml_add_connection_script( 'ver1', 'authenticate_user_hashed', 'call my_auth ( {ml s.authentication_status}, {ml s.username}, {ml s.hashed_password})' ) |
The following SQL Anywhere stored procedure uses both the user name and password to authenticate. The procedure ensures only that the supplied user name is one of the employee IDs listed in the ULEmployee table. The procedure assumes that the Employee table has a binary(20) column called hashed_pwd.
CREATE PROCEDURE my_auth( inout @authentication_status integer, in @user_name varchar(128), in @hpwd binary(32) ) BEGIN IF EXISTS ( SELECT * FROM ulemployee WHERE emp_id = @user_name and hashed_pwd = @hpwd ) THEN message 'OK' type info to client; RETURN 1000; ELSE message 'Not OK' type info to client; RETURN 4000; END IF END |
The following call to a MobiLink system procedure registers a Java method called authUserHashed as the script for the authenticate_user_hashed event when synchronizing the script version ver1.
CALL ml_add_java_connection_script( 'ver1', 'authenticate_user_hashed', 'ExamplePackage.ExampleClass.authUserHashed') |
The following is the sample Java method authUserHashed. It calls Java methods that check and, if needed, change the user's password.
public String authUserHashed( ianywhere.ml.script.InOutInteger authStatus, String user, byte pwd[], byte newPwd[] ) throws java.sql.SQLException { // A real authenticate_user_hashed handler // would handle more auth code states. _curUser = user; if( checkPwdHashed( user, pwd ) ) { // Authorization successful. if( newPwd != null ) { // Password is being changed. if( changePwdHashed( user, pwd, newPwd ) ) { // Authorization OK and password change OK. // Use custom code. authStatus.setValue( 1001 ); } else { // Auth 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 { // Authorization failed. authStatus.setValue( 4000 ); } return ( null ); } |
The following call to a MobiLink system procedure registers a .NET method called AuthUserHashed as the script for the authenticate_user_hashed 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_hashed', 'TestScripts.Test.AuthUserHashed' ) |
The following is the sample .NET method AuthUserHashed.
namespace TestScripts { public class Test { string _curUser = null; public string AuthUserHashed( ref int authStatus, string user, byte[] pwdHash, byte[] newPwdHash ) { // A real authenticate_user_hashed handler // would handle more auth code states. _curUser = user; if( CheckPwdHashed( user, pwdHash ) ) { // Authorization successful. if( newPwdHash != null ) { // Password is being changed. if( ChangePwdHashed( user, pwdHash, newPwdHash ) ) { // Authorization OK and password change OK. // Use custom code. authStatus = 1001; } else { // Auth OK but password change failed. // Use custom code System.Console.WriteLine( "user: " + user + " pwd change failed!" ); authStatus = 1002; } } else { authStatus = 1000; } } else { // Authorization failed. authStatus = 4000; } return ( null ); }}} |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |