Automatic login lockout [CR 359391]

The following example shows how you can prevent a user from connecting after five failed login attempts.

This example uses a ConnectFailed event handler to total failed connect attempts. The totals are stored in table dba.event_table. The first time a user fails a connection attempt, a row will be inserted to dba.event_table for that user. On subsequent failed connections, the count in that user’s row will be updated. A ConnectFailed event cannot prevent a user from continuing to try to connect. The login procedure must be used to deny access if the allowed number of failed login attempts is exceeded. This example tracks failed login attempts for all users including those who may be database administrators.

--First, create the table to hold the user 
--information and insert a row for each use.

CREATE TABLE dba.event_table ( username 
CHAR(128) NOT NULL, failed_login_attempts 
INTEGER, PRIMARY KEY(username) );

--Create the event handler that will 
--increment the number of failed login 
--attempts.

CREATE EVENT ev_badlogin type ConnectFailed 
handler 
BEGIN
   DECLARE uid CHAR(128);
   DECLARE xx INTEGER;

SET uid = event_parameter ('User')

IF EXISTS(SELECT * FROM dba.event_table
WHERE ucase( uid) = ucase(username)) THEN

-- The user is already in the table.

UPDATE event_table 
SET failed_login_attempts =
failed_login_attempts+1
   WHERE ucase(username) = ucase(uid);

ELSE

-- Insert the user for the first time.

INSERT dba.event_table 
VALUES (ucase(uid), 1);

END IF;

SELECT failed_login_attempts INTO xx 
FROM dba.event_table
WHERE ucase(username)=ucase(uid);

-- It is not possible to stop the user from 
-- attempting to connect after 5 tries.  
-- Instead, send a message to the server 
-- console to notify the database
-- administrator that a user has exceeded 
-- the allowable connect attempts.

IF xx > 5 THEN

RAISERROR 17001 uid + 'has had more
than 5 failed login attempts.'

END IF;

END

--The login procedure follows:

CREATE PROCEDURE dba.check_logins()

BEGIN

   DECLARE xx integer;
   DECLARE uid char(128);

-- See if the connected user is in the 
-- event_table and proceed accordingly.

IF EXISTS(SELECT * from dba.event_table
WHERE ucase(username) = 
ucase(current user)) THEN

SELECT failed_login_attempts INTO xx 
FROM dba.event_table 
   WHERE ucase(username) = 
ucase(current user);

IF ( xx >= 5 ) THEN

RAISERROR 17010 current user + 'has been 
locked out by the Database Administrator.'

ELSE

-- The user has connected.
-- Remove the user's row from 
-- dba.event_table and call
-- the default login procedure 
-- for the database.

SET uid=current user;

DELETE DBA.event_table 
WHERE ucase(username) = ucase(uid);

CALL sp_login_environment();

   end if;

ELSE

-- The user is not in dba.event_table, 
-- but has connected. Call the default 
-- login procedure for the database.

CALL sp_login_environment();

END IF;

END; 

--The following enables all users to run 
--the login stored procedure and set the 
--login procedure option.

GRANT EXECUTE ON dba.check_logins to PUBLIC;
SET OPTION PUBLIC.Login_Procedure = 
'dba.check_logins';

To enable a user that has exceeded the number of allowed failed connection attempts to connect, a database administrator must delete the row for that user from dba.event_table.

This example was omitted from the Sybase IQ System Administration Guide, Chapter 12, “Managing User IDs and Permissions.”