Managing IQ user accounts and connections

The Sybase IQ Login Management facility helps you manage users and connections to a database. There are two ways to use the facility:

Enabling Sybase IQ Login Management updates the system tables with changes made since Login Management was last enabled.

Sybase IQ Login Management requires that the LOGIN_PROCEDURE database option be set to dba.sp_iq_process_login. With this option setting, when Sybase IQ Login Management is enabled, each time a user tries to connect to the database, the sp_iq_process_login procedure executes. This procedure determines whether the user is allowed to connect. It checks that the user is not locked out, that the maximum number of connections for that user and for the database are not exceeded, and that the user’s password has not expired. It then allows login to proceed or sends an error message if any login condition is not met.

When Sybase IQ Login Management is disabled, user login proceeds without these checks.

While you can also use the GRANT CONNECT and REVOKE CONNECT commands or Sybase Central to add or drop users, you cannot manage logins by those users with the Sybase IQ Login Management stored procedures, unless you also add them with sp_iqaddlogin. When you enable Sybase IQ Login Management, existing users are automatically added to the system table used by the Sybase IQ Login Management procedures.

For more information, see the Sybase IQ Reference Manual:

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 has been exceeded. This example will keep track of 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 user.

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’s 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 following is the login procedure.

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;

To enable all users to run the login stored procedure and set the login procedure option, enter the following.

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.