Auditing Oracle logins and logoff step by step trigger

SQL> create tablespace audit_test datafile '/data/DB/DEVDB/audit_test.dbf' size 10G;

Tablespace created.

SQL> create user audit_test identified by test_1 default tablespace audit_test;

User created.

SQL> grant dba to audit_test;

Grant succeeded.

SQL> grant select on v_$session to audit_test;

Grant succeeded.

Now we come to the table and trigger creation steps:

SQL> conn audit_test
Enter password:
Connected.
SQL> CREATE TABLE logonaudittable
(
  event       VARCHAR2(10),
  sid         NUMBER,
  serial#     NUMBER,
  timestamp   DATE,
  username    VARCHAR2(30),
  osuserid    VARCHAR2(30),
  machinename VARCHAR2(64)
)
/
  2    3    4    5    6    7    8    9   10   11
Table created.

SQL> CREATE OR REPLACE TRIGGER logonauditing AFTER LOGON ON database
DECLARE
  machinename VARCHAR2(64);
  osuserid    VARCHAR2(30);
  v_sid       NUMBER(10);
  v_serial    NUMBER(10);
  2    3    4    5    6    7
  8    CURSOR c1 IS
    SELECT sid, serial#, osuser, machine
  9        FROM v$session WHERE audsid = userenv('sessionid');
 10   11  BEGIN
  OPEN c1;
 12   13    FETCH c1 INTO v_sid, v_serial, osuserid, machinename;
 14
 15    INSERT INTO audit_test.logonaudittable VALUES ( 'LOGON', v_sid, v_serial, sysdate,
 16        user, osuserid, machinename );
 17
 18    CLOSE c1;
 19  END;
 20  /

Trigger created.

3. Create LOGOFF trigger

CREATE OR REPLACE TRIGGER logoffauditing
BEFORE LOGOFF ON database
DECLARE
  machinename VARCHAR2(64);
  osuserid    VARCHAR2(30);
  v_sid       NUMBER(10);
  v_serial    NUMBER(10);
  
  CURSOR c1 IS
    SELECT sid, serial#, osuser, machine
      FROM v$session WHERE audsid = userenv('sessionid');
BEGIN
  OPEN c1;
  FETCH c1 INTO v_sid, v_serial, osuserid, machinename;

  INSERT INTO audit_test.logonaudittable VALUES ( 'LOGOFF', v_sid, v_serial, sysdate,
    user, osuserid, machinename );

  CLOSE c1;
END;
/

End of the lesson