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.

Sunday, March 18, 2012

ORA-01114: File reported not found in dba_data_files and v$datafile

We got an ORA-01114 error:
ORA-01114: IO error writing block to file  (block # )
ORA-01114: IO error writing block to file 5006 (block # 755027)

There is no file with FILE_ID = 5006 in dba_ data_files of with file#=5006 in v$datafile:
SQL> SELECT * FROM DBA_DATA_FILES WHERE FILE_ID = 5006;
no rows selected

SQL> SELECT * FROM V$DATAFILE WHERE FILE# = 5006;
no rows selected

That's, because the datafile associated with this error belongs to a temporary tablespace (temp file) and information about temp files is stored in dba_temp_files and v$tempfile.

To find your temp file, you need to find the value of initialization parameter "db_files":
SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'db_files';
NAME          VALUE
------------  -----
db_files      5000

In our case, the temp file is the one with file_id equal to 5006-5000 = 6.
The following queries will return temp file's info:
SELECT *
FROM DBA_TEMP_FILES
WHERE FILE_ID = 5006 - (SELECT VALUE FROM V$PARAMETER WHERE NAME='db_files');

SELECT *
FROM V$TEMPFILE
WHERE FILE# = 5006 - (SELECT VALUE FROM V$PARAMETER WHERE NAME='db_files');