以前有看过一个数据库级的触发器 ,可以让你参考参考打算做一个ddl触发器, 对于所有的ddl的操作进行记录,同时 针对不允许删除的表不允许删除 编写如下。---ddl日志 --drop table sys_ddl_log; create table sys_ddl_log ( c_ora_sysevent varchar2(100), c_login_user varchar2(100), c_dictionary_obj_type varchar2(100), c_dictionary_obj_name varchar2(100), c_dictionary_obj_owner varchar2(100), c_oper_date date ); --- 受保护的对象 drop table sys_protect_user_obj; create table sys_protect_user_obj( c_obj_owner varchar2(100), c_obj_type varchar2(100), c_obj_name varchar2(100) ); INSERT INTO sys_protect_user_obj (c_obj_owner, c_obj_type ,c_obj_name ) select T.OWNER,T.OBJECT_TYPE,T.OBJECT_NAME from dba_objects t where t.owner in ('SYSUSER') AND OBJECT_TYPE IN ('TABLE','VIEW','MATERIALIZED VIEW','PROCEDURE'); COMMIT;---记录ddl的事件 create or replace trigger trig_log_afterddl after ddl on database declare COU NUMBER;begin if ora_sysevent='DROP' then select COUNT(*) into COU from sys_protect_user_obj where c_obj_owner = ora_dict_obj_owner and c_obj_name = ora_dict_obj_name and c_obj_type=ora_dict_obj_type ; if COU>0 then raise_application_error(-20001,'该对象受到保护,禁止删除。请联系管理员!'); end if; elseinsert into sys_ddl_log(c_ora_sysevent ,c_login_user, c_dictionary_obj_type, c_dictionary_obj_name ,c_dictionary_obj_owner ,c_oper_date ) values(ora_sysevent , ora_login_user,ora_dict_obj_type, ora_dict_obj_name ,ora_dict_obj_owner,sysdate);end if ;end;
比如A用户创建的表,只要不用grant授权给其他用户,其他数据库用户都是看不到的
--drop table sys_ddl_log;
create table sys_ddl_log (
c_ora_sysevent varchar2(100),
c_login_user varchar2(100),
c_dictionary_obj_type varchar2(100),
c_dictionary_obj_name varchar2(100),
c_dictionary_obj_owner varchar2(100),
c_oper_date date
);
--- 受保护的对象
drop table sys_protect_user_obj;
create table sys_protect_user_obj(
c_obj_owner varchar2(100),
c_obj_type varchar2(100),
c_obj_name varchar2(100)
);
INSERT INTO sys_protect_user_obj (c_obj_owner, c_obj_type ,c_obj_name )
select T.OWNER,T.OBJECT_TYPE,T.OBJECT_NAME from dba_objects t where t.owner in ('SYSUSER') AND OBJECT_TYPE IN ('TABLE','VIEW','MATERIALIZED VIEW','PROCEDURE');
COMMIT;---记录ddl的事件
create or replace trigger trig_log_afterddl
after ddl on database
declare
COU NUMBER;begin
if ora_sysevent='DROP' then
select COUNT(*) into COU from sys_protect_user_obj where
c_obj_owner = ora_dict_obj_owner and
c_obj_name = ora_dict_obj_name and
c_obj_type=ora_dict_obj_type ;
if COU>0 then
raise_application_error(-20001,'该对象受到保护,禁止删除。请联系管理员!');
end if;
elseinsert into sys_ddl_log(c_ora_sysevent ,c_login_user, c_dictionary_obj_type, c_dictionary_obj_name ,c_dictionary_obj_owner ,c_oper_date )
values(ora_sysevent , ora_login_user,ora_dict_obj_type, ora_dict_obj_name ,ora_dict_obj_owner,sysdate);end if ;end;
比如A用户创建的表,只要不用grant授权给其他用户,其他数据库用户都是看不到的
同意~
比如A用户创建的表,只要不用grant授权给其他用户,其他数据库用户都是看不到的
通过给用户授权。