ddl删除控制

最新推荐文章于 2024-07-23 08:51:29 发布

最新推荐文章于 2024-07-23 08:51:29 发布 · 82 阅读

本文介绍了一个Oracle数据库触发器实现的安全性控制逻辑，该触发器在表被删除或截断前检查操作者的IP地址，如果发现异常则记录日志并阻止操作。

CREATE OR REPLACE TRIGGER db_drop_trigger
BEFORE truncate or drop ON ylfwjk.schema
DECLARE
n NUMBER;
str_stmt VARCHAR2(4000);
sql_text ora_name_list_t;
n_trace NUMBER;
str_session v$session%ROWTYPE;
BEGIN
SELECT COUNT(*)
INTO n_trace
FROM dual
WHERE utl_inaddr.get_host_address IS NOT NULL
AND sys_context('userenv', 'ip_address') IS NOT NULL
AND sys_context('userenv', 'ip_address') <>
utl_inaddr.get_host_address;

IF n_trace > 0 THEN

n := ora_sql_txt(sql_text);

FOR i IN 1 .. n LOOP
str_stmt := substr(str_stmt || sql_text(i), 1, 3000);
END LOOP;

SELECT *
INTO str_session
FROM v$session
WHERE audsid = userenv('sessionid');

sys.dbms_system.ksdwrt(2,
to_char(SYSDATE, 'yyyymmdd hh24:mi:ss') ||
' ORA-20000 user: ' || USER || ' program: ' ||
str_session.program || ' IP: ' ||
sys_context('userenv', 'ip_address') ||
' object: ' || ora_dict_obj_name || ' DDL: ' ||
str_stmt);

raise_application_error(-20000, '不许删除，有事请打110。');

END IF;