Oracle记录用户DDL操作脚本.pdf
文本预览下载声明
ZZhhoonngg WWeeiicchheenngg SSttuuddyy NNootteess
IInnssiisstt oonn hhiiss oowwnn iiddeeaall,, aanndd ttoo aattttaaiinn iitt..
OOrraaccllee记记录录用用户户DDDDLL 作作脚脚本本
分类： Oracle Oracle scripts 2013-11-28 18:38 42人阅读 评论 (0) 收藏 举报
OracleOracle scripts
--当前普通用户创建 issgzt
create table TAB_DDL
(
LOGIN_USER VARCHAR (60),
AUDSID NUMBER,
MACHINE VARCHAR (60),
IPADDRESS VARCHAR ( 0),
SCHEMA_USER VARCHAR (60),
SCHEMA_OBJECT VARCHAR (60),
DDL_TIME DATE,
DDL_SQL VARCHAR (4000),
MODULE VARCHAR (100),
SCHEMA_OBJECT_TYPE VARCHAR (60)
)
;
--sys用户创建,监视用户对Oracle进行DDL 作
CREATE OR REPLACE TRIGGER SYS.tr_issgzt_ddl
AFTER ddl ON database
DECLARE
sql_text ora_name_list_t;
state_sql issgzt.TAB_DDL.ddl_sql%TYPE;
v_program issgzt.TAB_DDL.module%TYPE;
error1 exception;
BEGIN
IF SUBSTR(SYS_CONTEXT(USERENV, SESSION_USER), 1, 1) = T AND
SYS_CONTEXT(USERENV, SESSION_USER) NOT IN (username) AND
lower(state_sql) not like alter user t% THEN
raise error1;
end if;
FOR i IN 1 .. nvl(ora_sql_txt(sql_text), ) LOOP
begin
state_sql := state_sql || sql_text(i);
Exception
when others then
null;
end;
END LOOP;
select nvl(s.program, s.MODULE)
into v_program
from v$session s
where s.SID = (select distinct sid from v$mystat);
INSERT INTO issgzt.TAB_DDL
(login_user,
audsid,
machine,
1
ipaddress,
schema_user,
schema_object,
SCHEMA_OBJECT_TYPE,
ddl_time,
ddl_sql,
module)
VALUES
(ora_login_user,
userenv (SESSIONID),
SYS_CONTEXT(USERENV, TERMINAL),
sys_context(userenv, ip_address),
ora_dict_obj_owner,
ora_dict_obj_name,
ora_dict_obj_type,
SYSDATE,
substr
显示全部