Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - Database Administration » Database configuration » Database options » Alphabetical list of database options

post_login_procedure option

Specifies a procedure whose result set contains messages that should be displayed by applications when a user connects.

Allowed values

String

Default

dbo.sa_post_login_procedure

Scope
  PUBLIC role For current user For other users
Allowed to set permanently? Yes, with SET ANY SECURITY OPTION Yes, with SET ANY SECURITY OPTION Yes, with SET ANY SECURITY OPTION
Allowed to set temporarily? Yes, with SET ANY SECURITY OPTION Yes (current connection only), with SET ANY SECURITY OPTION No
Remarks

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 value should be of the form owner.function-name to prevent a user from overriding the function.

The SQL Anywhere 17 plug-in for SQL 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 might 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:

  • 0

    Display the message (if any).

  • 1

    Display the message and prompt the user for a password change.

  • 2-99

    Reserved.

  • 100 and greater

    User defined.

The SQL Anywhere 17 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.

Example

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';