Specifies a login procedure that sets connection compatibility options at startup.
String
Can be set for an individual connection or the PUBLIC group. DBA authority is required to set the option. Takes effect immediately.
DBA.sp_iq_process_login
“sp_iq_process_login procedure”
“Managing IQ user accounts and connections” in Chapter 12, “Managing User IDs and Permissions” of the Sybase IQ System Administration Guide
The default login procedure, sp_iq_process_login, executes when a user attempts to connect.
When Sybase IQ User Administration 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, and then either allows login to proceed or sends an error message.
When Sybase IQ User Administration 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 Sybase IQ User Administration 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 needs to be created in every database you might use.
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
