Thursday, March 29, 2012

Logon trigger to forbid unauthorized logins

There was a need to stop some unauthorized logins in our database, attempted by some specific users.
First, we create a table to record those logins:
CREATE TABLE HELPDESK.FAILED_LOGINS
(
   USERNAME   VARCHAR2 (30 BYTE),
   OSUSER     VARCHAR2 (30 BYTE),
   TERMINAL   VARCHAR2 (30 BYTE),
   IP         VARCHAR2 (64 BYTE),
   MODULE     VARCHAR2 (48 BYTE),
   TIME       DATE
);

CREATE INDEX HELPDESK.FAILED_LOGINS_IDX
   ON HELPDESK.FAILED_LOGINS (TIME);

The following trigger will check some database users and will allow only specific os users to login.
CREATE OR REPLACE TRIGGER SYS.AUTH_LOGIN
   AFTER LOGON
   ON DATABASE
DECLARE
BEGIN
   IF DBMS_STANDARD.LOGIN_USER IN
         ('APPS',
          'APPLSYS',
          'AP',
          'AR',
          'AX',
          'GL',
          'INV',
          'ONT',
          'PO',
          'HR',
          'XXFSM')
      AND SYS_CONTEXT ('USERENV', 'OS_USER') NOT IN ('oracle', 'oracol')
   THEN
      INSERT INTO HELPDESK.FAILED_LOGINS (USERNAME,
                                          OSUSER,
                                          TERMINAL,
                                          IP,
                                          MODULE,
                                          TIME)
           VALUES (DBMS_STANDARD.LOGIN_USER,
                   SYS_CONTEXT ('USERENV', 'OS_USER'),
                   SYS_CONTEXT ('USERENV', 'TERMINAL'),
                   SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),
                   SYS_CONTEXT ('USERENV', 'MODULE'),
                   SYSDATE);

      COMMIT;
      RAISE_APPLICATION_ERROR (-20001, 'Unauthorized login');
   END IF;
END;
/

We try to login as APPS and we are not allowed:
SQL> CONN APPS@COLDB_RP1
Enter password: ****
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Unauthorized login
ORA-06512: at line 31

The attempt is recorded:
SQL> SELECT * FROM HELPDESK.FAILED_LOGINS;

USERNAMEOSUSERTERMINALIPMODULELTIME
APPSetsekourasETSEKOUR10.100.25.60sqlplusw.exe29-MAR-12

If I add my os username in the trigger, I am allowed access:
SQL> CONN APPS@COLDB_RP1
Enter password: ****
Connected.

No comments:

Post a Comment