monitor sys and system user(轉自http://www.oracle.com)

Tip of the Week Tip for Week of February 23, 2003 Monitor SYS and SYSTEM Accounts This tip comes from Daniel Stanley, Oracle DBA/Developer at ASIX, Inc., in Bellevue, WA. Create a procedure to monitor and log users logging onto the database with the SYS and SYSTEM accounts. Create two new SYS objects: --trigger sys$logtrig-- This logon trigger fires the procedure if you are logged on with the SYS or SYSTEM accounts. --procedure sys$logproc-- This procedure writes an ascii file to either the utl_file_dir or the c:winnt directory if the utl_file_dir is set to *. This parameter must be set in the init.ora file prior to installing and running the procedure. It moves and makes a backup every 8k. First create the sys$logproc procedure. create or replace procedure sys$logproc as /* ******************************* ** SYS/SYSTEM LOG MONITOR ***** ******************************* CREATED BY: Dan Stanley daniels@asix.com FILENAME: SYS_sys$log.src COMPANY: ASIX, Inc. DATE: 01/28/2003 OBJECTIVE: stores log of all successful SYS and SYSTEM connections to database; recycle and backup done at 8k. INPUT: SQL> exec sys$logproc OUTPUT: [v$%SID%.log] VERSION TESTING: This script was tested on Oracle EE 8.1.6 to 9.2.x and is intended to be used in SQL*Plus. MODS: 11/16/2002 drs - description. */ v_date char(30); v_user char(30); v_osuser char(40); v_term char(30); v_host char(40); v_program char(40); v_exists boolean := FALSE; v_file_len number := 0; v_blk_sz binary_integer; squote char(1) := chr(39); dquote char(2) := chr(34); comma char(1) := chr(44); v_file utl_file.file_type; v_utl_file_path varchar2(40); v_write_path varchar2(40); v_instance varchar2(16); v_file_name varchar2(40); v_old varchar2(40); v_writeappend char(1) := 'A'; begin select to_char(sysdate, 'mm-dd-yyyy HH:MI:SSam'), user, sys_context('userenv','OS_USER'), userenv('TERMINAL'), (select trim(value) from v$parameter where name = 'utl_file_dir'), (select trim(upper(instance_name)) from v$instance), (select program from v$session where sid = (select distinct sid from v$mystat)) into v_date, v_user, v_osuser, v_term, v_utl_file_path, v_instance, v_program from dual; -- NOTE: this section is for debugging purposes only if nvl(v_utl_file_path,' ') = ' ' then dbms_output.put_line('*************************************************'); dbms_output.put_line('*** ERROR: utl_file_dir parameter is not set.'); dbms_output.put_line('*************************************************'); elsif v_utl_file_path = '*' then v_write_path := 'c:winnt'; else v_write_path := v_utl_file_path; end if; -- END NOTE: v_file_name := 'v$'||v_instance||'.log'; v_old := 'v$' ||v_instance||'.old'; utl_file.fgetattr( location => v_write_path, filename => v_file_name, fexists => v_exists, file_length => v_file_len, block_size => v_blk_sz); if v_exists then if v_file_len >= 8192 then utl_file.frename( src_location => v_write_path, src_filename => v_file_name, dest_location => v_write_path, dest_filename => v_old, overwrite => true); v_writeappend := 'W'; end if; end if; v_file := utl_file.fopen(''||v_write_path||'', v_file_name, v_writeappend); utl_file.put_line(v_file, trim(v_date) || '|' || trim(v_user) || '|' || trim(v_osuser) || '|' || trim(v_term) || '|' || trim(v_program)); utl_file.fclose(v_file); end sys$logproc; / Now create the sys$logtrig trigger. CREATE OR REPLACE TRIGGER sys$logtrig AFTER LOGON ON DATABASE declare v_user char(30); begin select user into v_user from dual; if v_user = 'SYS' or v_user = 'SYSTEM' then sys$logproc; end if; end; /[@more@]