--防止误删除表
CreateOrReplaceTrigger sys_trg_drop_ddl
BeforedropOnDatabase
/*
NAME : sys_trg_drop_ddl.sql
PURPOSE : FOR sys trigger dropobject
AUTHOR :
OUTPUT :
REVISIONS
VER DATE AUTHOR DESCRIPTION
---- ---------- ----------- --------------------------
0.1 2013-06-01 - Init Version
*/
Declare
dll_exception Exception;
PragmaException_Init(dll_exception, -1031);
Begin
--for testing then comma the return
--Return;
--the table needed to drop must startwith the tmp_ (underscore)
If (ora_dict_obj_type ='TABLE')And ora_dict_obj_nameNotLike'TMP\_%'
Escape'\'Then
Raise dll_exception;
EndIf;
Exception
When dll_exceptionThen
raise_application_error(-20000,
'Can not drop the table ' || ora_dict_obj_name ||
' ,Only the table name start with thetmp_ ,then can droped!');
End;
/
--监控用户登入登出日志
-- Create table
createtable T_LOG_AUDIT
(
D_DATE DATE,
S_SESSION_USER VARCHAR2(50),
S_IP_ADDRESS VARCHAR2(50),
S_TERMINAL VARCHAR2(50),
S_DB_NAME VARCHAR2(50),
S_HOST VARCHAR2(50),
S_OS_USER VARCHAR2(50),
S_SESSIONID VARCHAR2(50),
S_INSTANCE VARCHAR2(50),
N_STATUS NUMBER
);
-- Add comments to the table
commentontableT_LOG_AUDIT
is'进出日志监控';
-- Add comments to the columns
commentoncolumnT_LOG_AUDIT.D_DATE
is'日期';
commentoncolumnT_LOG_AUDIT.S_SESSION_USER
is'登陆的用户';
commentoncolumnT_LOG_AUDIT.S_IP_ADDRESS
is'IP地址';
commentoncolumnT_LOG_AUDIT.S_TERMINAL
is'终端';
commentoncolumnT_LOG_AUDIT.S_DB_NAME
is'DB名字';
commentoncolumnT_LOG_AUDIT.S_HOST
is'HOST';
commentoncolumnT_LOG_AUDIT.S_OS_USER
is'操作系统用户';
commentoncolumnT_LOG_AUDIT.S_SESSIONID
is'SESSIONID';
commentoncolumnT_LOG_AUDIT.S_INSTANCE
is'实例ID';
commentoncolumnT_LOG_AUDIT.N_STATUS
is'1登入 2登出';
CreateOrReplaceTrigger sys_trg_logon_audit
After logonOnDatabase
/*
NAME :sys_trg_logon_audit.sql
PURPOSE : FOR sys trigger logonaudit
AUTHOR :
OUTPUT :
REVISIONS
VER DATE AUTHOR DESCRIPTION
---- ---------- ----------- --------------------------
0.1 2013-06-01 - Init Version
*/
Begin
InsertInto system.t_log_audit
SelectSysdate,
sys_context('USERENV','SESSION_USER') session_user,
sys_context('USERENV','IP_ADDRESS') ip_address,
sys_context('USERENV','TERMINAL') terminal,
sys_context('USERENV','DB_NAME') db_name,
sys_context('USERENV','HOST')host,
sys_context('USERENV','OS_USER') os_user,
sys_context('USERENV','SESSIONID') sessionid,
sys_context('USERENV','INSTANCE')instance,
1
From dual;
End;
/
CreateOrReplaceTrigger sys_trg_logoff_audit
Before logoffOnDatabase
/*
NAME :sys_trg_logoff_audit.sql
PURPOSE : FOR sys triggerlogoff audit
AUTHOR :
OUTPUT :
REVISIONS
VER DATE AUTHOR DESCRIPTION
---- ---------- ----------- --------------------------
0.1 2013-06-01 - Init Version
*/
Begin
InsertInto system.t_log_audit
SelectSysdate,
sys_context('USERENV','SESSION_USER') session_user,
sys_context('USERENV','IP_ADDRESS') ip_address,
sys_context('USERENV','TERMINAL') terminal,
sys_context('USERENV','DB_NAME') db_name,
sys_context('USERENV','HOST') host,
sys_context('USERENV','OS_USER') os_user,
sys_context('USERENV','SESSIONID') sessionid,
sys_context('USERENV','INSTANCE')instance,
2
From dual;
End;
/