LOGIN_PROCEDURE option

Specifies a login procedure that sets connection compatibility options at start-up.

String

DBA.sp_iq_process_login

Can be set for an individual connection or the PUBLIC group. Requires DBA permissions to set the option. Takes effect immediately.

The default login procedure, sp_iq_process_login, executes when a user attempts to connect.

• When Sybase IQ Login Management is enabled, this procedure checks that the user is not locked out, that the maximum number of connections for the user and database is not exceeded, and that the user’s password has not expired. It then either allows login to proceed, or sends an error message.

• When Sybase IQ Login Management is disabled, this procedure allows login to proceed.

• If sp_iq_process_login allows login to proceed, it calls the sp_login_environment procedure, which calls to determine the database connection settings.

• In its turn, sp_login_environment checks to see if the connection is being made over TDS. If it is, it calls the sp_tsql_environment procedure, which sets several options to new default values for the current connection.

To use the Login Management facility, LOGIN_PROCEDURE must be set to DBA.sp_iq_process_login.

You can also customize the default database option settings by creating a new procedure and setting LOGIN_PROCEDURE to call that new procedure. Do not edit sp_iq_process_login, sp_login_environment or sp_tsql_environment. The customized login procedure must be created in every database you might use.

The Sybase jConnect driver and the iAnywhere ODBC driver reset certain options in accordance with the ODBC specification. They will overwrite settings by the LOGIN_PROCEDURE option for the following:

• Time_format = 'hh:nn:ss'

• Timestamp_format = 'yyyy-mm-dd hh:nn:ss.ssssss'

• Date_format = 'yyyy-mm-dd'

• Date_order = 'ymd'

• Isolation_level = 0

These options will overwrite settings by the LOGIN_PROCEDURE database option. Because these option settings are mandated by the ODBC specification, ODBC applications, including dbisql applications, must explicitly set these options if they want different behavior. This could be done using the ODBC connection parameter InitString, for example:

iqdsn -wu foo -c "uid=dba;pwd=sql;eng=foo;InitString='SET OPTION PUBLIC.DATE_ORDER = ' ' DMY ' ' ' ”

The following example shows an alternative to sp_iq_process_login. This example disallows a connection by signaling the INVALID_LOGON error.

create procedure DBA.login_check()

begin

 declare INVALID_LOGON exception for sqlstate '28000';
 // Allow a maximum of 3 concurrent connections
 if( db_property('ConnCount') > 3 ) then
 signal INVALID_LOGON;
 else
 call sp_login_environment;
 end if;

end
go
grant execute on DBA.login_check to PUBLIC
go
set option PUBLIC.LOGIN_PROCEDURE='DBA.login_check'
go

An alternative means to disallow a connection is by using the RAISERROR statement:

CREATE MESSAGE 28000 AS 'User %1! is not allowed to connect there are already %2! users logged on';

ALTER procedure DBA.login_check()

begin

 declare INVALID_LOGON exception for sqlstate '28000';
 // Allow a maximum of 3 concurrent connections
 if( db_property('ConnCount') > 2 ) then

  RAISERROR 28000, connection_property('Userid'),
   db_property('ConnCount')

 else

  call sp_login_environment;
 end if;
end