Skip to content

How to Automatically Lock Out Inactive Users in Sybase® IQ

July 8, 2011

 www.arbime.com

Introduction

Securing the database access is one important function of DBAs.  There are multiple tasks that are required to maintain security. In this article I will explain how to automate one of those tasks, explicitly, how to schedule an event that execute a procedure that automatically lock inactive user in Sybase IQ.

For this example, an inactive user is a user that has not been logged for a period of 30 days.

This procedure has been tested on Sybase IQ 15.2 ESD#3 running on Solaris 10.

Steps

 

1. Create a login policy with login policy option locked=ON

 

CREATE LOGIN POLICY locked_users locked=ON

You must have DBA privileges or USER ADMIN authority to create a new login policy.

 

2. Create a store procedure that verify and lock the inactive users

 

/* Procedure name: uspLockInactiveUsers
   Author        : Uvernes Somarriba
   Creation Date : July 7, 2011
*/

IF EXISTS (SELECT * FROM sysobjects
    WHERE name = ‘uspLockInactiveUsers’
    AND   type = ‘P’)
BEGIN
    DROP PROCEDURE DBA.uspLockInactiveUsers
END

go
CREATE PROCEDURE DBA.uspLockInactiveUsers @numberOfDays INTEGER
AS
BEGIN
    DECLARE
      @userName           varchar(128),
      @sqlString            varchar(8000)    

    SET @sqlString = ”

    DECLARE user_cursor CURSOR FOR
        SELECT u.user_name
          FROM sys.sysuser u, sys.sysuserauth a
         WHERE u.user_name = a.name
           AND a.USER_GROUP = ‘N’
           AND ((u.password_creation_time < (CURRENT DATE –  @numberOfDays) AND u.last_login_time IS NULL)
               OR u.last_login_time < (CURRENT DATE –  @numberOfDays))

    OPEN user_cursor

    FETCH user_cursor INTO @userName

    WHILE @@sqlstatus = 0
    BEGIN

        SET @sqlString = @sqlString + ‘ALTER USER ‘ + @userName + ‘ LOGIN POLICY locked_users; ‘

        FETCH user_cursor INTO @userName

    END

    CLOSE user_cursor
    DEALLOCATE user_cursor

    EXECUTE (@sqlString)

END

Notes:

  • The number of days required to identify a user as inactive is passed as a parameter.
  • The user’s accounts that have never been used but have been created after the last 30 days are not locked out.
  • The dynamic SQL is executed after closing the cursor due to cursors limitations when executing DML statements in Sybase IQ.
  • When using this procedure, you should define the size of the @sqlString variable according to your need.

 

3. Schedule the store procedure to run automatically every night

 
CREATE EVENT LockInactiveUsers
SCHEDULE Sched_LockInactiveUsers
START TIME ‘4:00AM’ EVERY 24 HOURS
HANDLER
  BEGIN
     CALL DBA.uspLockInactiveUsers (30)
  END

Notes:

  • When using this procedure, you should define the value of the @numberOfDays parameter according to your need.

If you need to unlock a user, use ALTER USER <username> LOGIN POLICY <root | a policy with  locked_users locked=OFF>

Conclusion

Locking out inactive users is only one of several tasks in user management.  The process exposed allows a DBA to automate that task. 

www.arbime.com

References

Sybase Software, System Administration Guide: Volume 1, Sybase IQ 15.2.  Duplin, California. April 2010.
Sybase Software, System Administration Guide: Volume 2, Sybase IQ 15.2.  Duplin, California. April 2010.
Sybase Software, Reference: Statements and Options, Sybase IQ 15.2.  Duplin, California. April 2010.

Advertisements

From → SAP IQ

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: