ASA Database Administration Guide
  Database Options
    Alphabetical list of options

LOGIN_PROCEDURE option [database]

Function 

A login procedure that sets connection compatibility options at startup. By default the procedure calls the sp_login_environment procedure to determine which options to set.

Allowed values 

String

Scope 

DBA authority required.

Default 

sp_login_environment

Description 

This login procedure calls the sp_login_environment procedure at run time to determine the database connection settings.

You can customize the default database option settings by creating a new procedure and setting LOGIN_PROCEDURE to call the new procedure. You should not edit either sp_login_environment or sp_tsql_environment.

Examples 

• The following example shows how you can disallow 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

For an alternate way to disallow connections, see RAISERROR statement [T-SQL].

• The following example shows how you can block connection attempts if the number of failed connections for a user exceeds 3 within a 30 minute period. All blocked attempts during the block out period receive an invalid password error and are logged as failures. The log is kept long enough for a DBA to analyze it.

create table DBA.ConnectionFailure(
    pk int primary key default autoincrement,
    user_name char(128) not null,
    tm timestamp not null default current timestamp
)
go

create index ConnFailTime on DBA.ConnectionFailure(
    user_name, tm )
go

create event ConnFail type ConnectFailed
handler
begin
    declare usr char(128);
    set usr = event_parameter( 'User' );
    // Put a limit on the number of failures logged.
    if (select count(*) from DBA.ConnectionFailure
        where user_name = usr
        and tm >= dateadd( minute, -30,
            current timestamp )) < 20 then
        insert into DBA.ConnectionFailure( user_name )
            values( usr );
        commit;
        // Delete failures older than 7 days
        delete DBA.ConnectionFailure
        where user_name = usr
        and tm < dateadd( day, -7, current timestamp );
        commit;
    end if;
end
go

create procedure DBA.login_check()
begin
    declare usr char(128);
    declare INVALID_LOGON exception for sqlstate '28000';
    set usr = connection_property( 'Userid' );
    // Block connection attempts from this user
    // if 3 or more failed connection attempts have occurred
    // within the past 30 minutes.
    if (select count(*) from DBA.ConnectionFailure
        where user_name = usr
        and tm >= dateadd( minute, -30,
            current timestamp ) ) >= 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