String

sa_post_login_procedure system procedure

Can be set for an individual connection, or for PUBLIC. You must have the SET ANY SECURITY OPTION system privilege to set this option.

When the post_login_procedure option is set to anything other than an empty string, applications can call the procedure specified by the option as part of the connection process to determine what messages should be displayed to the user, if any. The option values should be of the form owner.function-name to prevent a user from overriding the function.

The SQL Anywhere 16 plug-in for Sybase Central and Interactive SQL call the procedure if this option is set and display any messages returned by the procedure in a window. Other applications that are not included with SQL Anywhere should be modified to call the procedure given by this option and display messages if you need this functionality.

One case where an application may need to display a message on connection is to notify the user that their password is about to expire if a password expiry system is implemented. This functionality could be used to notify the user each time they connect if their password will expire in the next few days, and before it actually expires.

The procedure specified by this option must return a result set with one or more rows and two columns. The first column of type VARCHAR(255) returns the text of the message, or NULL if there is no message. The second column of type INT returns the action type. Allowed values for actions are:

The SQL Anywhere 16 plug-in and Interactive SQL (dbisql) display all non-NULL messages, regardless of the action value. If the action is set to 1, then these tools prompt the user to change the password, and then set the new password to the user-specified value.

For an example that uses post_login_procedure and includes advanced password rules and implementing password expiration, see Using a password verification function.

• login_procedure option
• verify_password_function option
• Security considerations: Passwords
• NewPassword (NEWPWD) connection parameter
• sa_post_login_procedure system procedure
• post_login_procedure connection property

The following example uses a procedure named p_post_login_check that warns users that their password is about to expire and then prompts them to change their password.

CREATE PROCEDURE DBA.p_post_login_check( )
RESULT( message_text VARCHAR(255), message_action INT )
BEGIN
  DECLARE message_text        CHAR(255);
  DECLARE message_action     INT;
  
  -- assume the password_about_to_expire variable was 
  -- set by the login procedure
  IF password_about_to_expire = 1 THEN
    SET message_text = 'Your password is about to expire';
    SET message_action = 1;
  ELSE
    SET message_text = NULL;
    SET message_action = 0;
  END IF;
  -- return message (if any) through this result set
  SELECT message_text, message_action;
END;

GRANT EXECUTE ON DBA.p_post_login_check TO PUBLIC;

SET OPTION PUBLIC.post_login_procedure = 'DBA.p_post_login_check';