ORACLE 11g implements a detailed record of pseudo-audit user < login exit >

A requirement is a record of logged-in logs logged by a user, including time and connection IP addresses, and connections, which can help keep track of analysis issues, especially if some human-induced DML operations cause data loss, data clutter, or very effective tracking.
When you know that Oracle Library has opened audit audit, there must be a way to find the problem, but the audit is more resource-intensive, so you can indirectly implement this function through triggers and the like.

1. Build Record table

-- CREATE table CREATE TABLE uc_logon_off (user_id VARCHAR2 ( -), session_id number (8), host VARCHAR2 ( -), Last_program VARCHAR2 ( -), Last_action VARCHAR2 ( +), Last_module VARCHAR2 ( +), Logon_day DATE, Logon_time VARCHAR2 (Ten), Logoff_day DATE, Logoff_time VARCHAR2 (Ten), elapsed_minutes number (8), sid number (8), serial number (8)  tablespace USERS pctfree //--pctfree: The percentage of space that is reserved for the update operation in the block, and the new data will not be inserted into this block when the data takes up the maximum space  Initrans 1//1--Parallel transaction control, 1 represents a maximum of one parallel transaction Maxtrans 255; --maxtrains defines the maximum value of a data slot in a data block. The transaction table expands dynamically as needed, up to Maxtrans entries (assuming there is enough free space on the block). Each assigned transaction entry needs to occupy the storage space of the 23~24 bytes in the block header. 

2. Set up a landing trigger

CREATE OR REPLACE TRIGGER trig_logon_audit after logon on DATABASE BEGIN INSERT INT O Timdba.uc_logon_off Select USER, Sys_context (    'USERENV','SESSIONID'), Sys_context ('USERENV','HOST'), NULL, NULL, NULL, sysdate, to_ CHAR (Sysdate,'YYYY-MM-DD Hh24:mi:ss'), NULL, NULL, NULL, NULL, Nullfrom dual; END; 

3. Create a logout Trigger
Log out here also uses the INSERT statement, is to consider that because the user if the update, in the login record to do the update operation, if the login user malicious, it can be modified record. It next landing, directly modify the last logged on the record IP address and so on information, this will give us misleading, of course, we can also according to the archive log to analyze, but it will be more troublesome, so the same record table Uc_logon_off table on the loss of the effectiveness of the record, Because every time we wonder if the record here is thought to have been repaired, not the actual original.
That now changed to insert, plus the following prohibit delete, update operation, then ensure that the Uc_logon_off table data authenticity, although it is possible to be malicious insert new records may be, but each time it connected after the last record is true, This allows us to make a record of the operation behavior of the user based on the analysis of the Uc_logon_off table.

CREATE OR REPLACE TRIGGER trig_logoff_audit before logoff on DATABASE DECLARE v_date date;     BEGIN SELECt T.logon_day into V_date from(SelectLogon_day from Timdba.uc_logon_offwhereSession_id= Sys_context ('USERENV','SESSIONID') and Logon_day are not NULL for order by logon_day ASC) Twhererownum<2; INSERT into Timdba.uc_logon_offSelectUSER, Sys_context ('Userenv','SESSIONID'), Sys_context ('Userenv','Host'), (SELECT action from V$session WHERE Sys_context ('USERENV','SESSIONID') =audsid), (SELECT program from V$session WHERE Sys_context ('USERENV','SESSIONID') =audsid), (SELECT module from V$session WHERE sys_context ('USERENV','SESSIONID') =audsid), V_date, To_char (V_date,'Hh24:mi:ss'), Sysdate, To_char (Sysdate,'Hh24:mi:ss'), TRUNC (To_number (sysdate-V_date) *1440,2), ROUND (To_number (sysdate-V_date) * -* -* -), NULL, Sys_context ('Userenv','IP_Address') fromdual;   Commit COMMIT; END; 

4, prohibit the deletion of login log out operation record

Create or replace trigger Timdba.trig_uc_logonoff    before update or delete on Timdba.uc_logon_off  DECLARE     BEGIN    IF Deleting then      raise_application_error (-20001 "   );   elsif updating then      raise_application_error (-20001"  );    END IF;  END;  

5, give all user authorization record operation
Because the trigger inside needs to query V$sql and v$session, but also to operate on the Timdba.uc_logon_off table, so you need to give these operations permissions, then if you want to record all users, you need to give all users to the operation permissions.

--(1 ) Statistics need to authorize the statement Grant create session,connect to DW; GrantSelectOn V_$sql to DW; GrantSelectOn v_$session to DW; GrantSelect, insert on Timdba.uc_logon_off to DW; --(2 Create a temporary table that records all user create table TIMDBA. Z_users as SelectDistinct owner fromall_objects; --(3 generates an authoritative SQL statement Select 'Grant Create Session,connect to'|| Owner | |'; '  fromTimdba. Z_users TwhereT.owner notinch('TIMDBA','SYS','SYSTEM') Union AllSelect  'Grant Select on V_$sql to'|| Owner | |'; '  fromTimdba. Z_users TwhereT.owner notinch('TIMDBA','SYS','SYSTEM') Union AllSelect 'Grant Select on V_$session to'|| Owner | |'; '    fromTimdba. Z_users TwhereT.owner notinch('TIMDBA','SYS','SYSTEM') Union AllSelect 'Grant Select,insert on Timdba.uc_logon_off to'|| Owner | |'; '  fromTimdba. Z_users TwhereT.owner notinch('TIMDBA','SYS','SYSTEM'); --(4) Copy the SQL result record to a file Grant_logonoff.sql, and put it in/home/ Oracle Directory, and then sqlplus login to execute SQL file authorization. SQL> @/home/oracle/Grant_logonoff.sql;  Grant succeeded.  Grant succeeded.  Grant succeeded. .... s

6. View some records
Query sql:

 select  t.elapsed_minutes,t.elapsed_seconds,t.ip,t.* from  uc_logon_off t 
 where  t.user_id in  ( " dw  "  ,  " logminer   ' ,  '  ys   ' ,  '  timdba   ' ,  '  plcrm   ' ) 
 and t.elapsed_minutes is  not null
     ORDER by T.logon_day desc,t.session_id desc; 

ORACLE 11g implements a detailed record of pseudo-audit user < login exit >