如何禁止远程exp

来至http://www.itpub.net/thread-1773173-1-1.html

根据版主及楼上各位提示，我也成功实现了这个触发器，代码如下：

create or replace trigger tr_exp
after logon on database
declare
v_pro varchar2(50);
v_ip varchar2(300);
begin
select upper(substr(program,1,3)),sys_context('userenv','ip_address') into v_pro,v_ip from v$session where audsid=userenv('sessionid');
if v_pro='EXP' and v_ip!='192.168.1.166' then
raise_application_error(-20021, 'Prohibit remote execution exp or export');
end if;
end tr_exp;

之所以费了一些时间，是在代码结尾习惯性的写了个例外，如下：
exception
when others then
null;
结果总也不能触发，去掉例外后，马上好使了。

已知触发器不能限制具有DBA角色的用户，经测试：system同样不受以上触发器限制。

具有DBA角色的用户是不受触发器限制的

应该是可以的,因为我查询我以sys,system,等其他dba的用户登录.
视图v$session中 client_info都有IP地址.

-----------------------

我拿我自己的脚本修改的:

CREATE OR REPLACE TRIGGER SYS.enable_para_cursor_sharing
   AFTER LOGON ON DATABASE
--  10046 TRACELEVELS
-- 0  - Turn off tracing.
-- 1  - Basic SQL_TRACE.
-- 4  - Level 1 plus Bind Variables.
-- 8  - Level 1 plus wait events.
-- 12 - Level 1 plus Bind/Wait event information.
DECLARE
   v_exe       v$session.program%TYPE;
   v_sid       v$session.SID%TYPE;
   v_osuser    v$session.osuser%TYPE;
   v_machine   v$session.machine%TYPE;
   v_ok        VARCHAR (10);
BEGIN
-- add and set clint_ip to application_info
   DBMS_APPLICATION_INFO.set_client_info (SYS_CONTEXT ('userenv', 'ip_address'));

-- Get user SID information
   SELECT SID
     INTO v_sid
     FROM v$mystat
    WHERE ROWNUM < 2;

-- Get Program executable,OSUSER Details,Machine Details for this session
   SELECT program, osuser, machine
     INTO v_exe, v_osuser, v_machine
     FROM v$session
    WHERE SID = v_sid;

   IF ((LOWER (v_exe) = 'exp.exe'))
   THEN
      raise_application_error(-20011, '禁止exp');
   --   EXECUTE IMMEDIATE 'alter session set cursor_sharing =force';
   --EXECUTE IMMEDIATE 'alter session set timed_statistics=true';
   --EXECUTE IMMEDIATE 'alter session set max_dump_file_size=unlimited';
   --EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''session_trace_trigger''';
   --EXECUTE IMMEDIATE 'alter session set events ‘’10046 trace name context forever,    level 8'' ';
   --DBMS_SESSION.set_identifier ('SQL TRACE ENABLED VIA LOGIN TRIGGER');
   END IF;
END;
/
-----------------------------------------应该都有的.
CREATE OR REPLACE TRIGGER SYS.on_logon_trigger
   AFTER LOGON ON DATABASE
DECLARE
   v_client_info   v$session.client_info%TYPE;
   v_sid           v$session.SID%TYPE;
BEGIN
   SELECT SYS_CONTEXT ('userenv', 'ip_address')
     INTO v_client_info
     FROM DUAL;

   DBMS_APPLICATION_INFO.set_client_info (v_client_info);
   --增加的代码
   DBMS_SESSION.set_identifier (v_client_info);

--   -- Get user SID information
--   SELECT SID
--     INTO v_sid
--     FROM v$mystat
--    WHERE ROWNUM < 2;

   ---- Get Program executable,OSUSER Details,Machine Details for this session
--   SELECT nvl(replace(client_info,'.','_'),'local')
--     INTO v_client_info
--     FROM v$session
--    WHERE SID = v_sid;
   EXECUTE IMMEDIATE 'alter session set tracefile_identifier = ''' || REPLACE (NVL (v_client_info, 'local'), '.', '_') || '''';
END;
/

这个是我在测试环境的脚本.
我查询show parameter tracefile_identifier .都变化了.

---------------------------------------------------------------------

类似于这样的触发器
create or replace trigger block_tools_from_prod
after logon on database
declare
  vi_sid      int;
  vv_username dba_users.username%type;
  vv_program  varchar2(100);
begin
  select userenv('SID') into vi_sid from dual;
  select s.username, s.program
    into vv_username, vv_program
    from v$session s
   where s.sid = vi_sid;
  if upper(vv_username) = 'EPOLICE' and UPPER(vv_program) LIKE '%PLSQLDEV%' then
    insert into sys.log
      select  vv_program||'_errr',SYS_CONTEXT('USERENV','ip_address') from dual;
    commit;
    raise_application_error(-20001, 'Development tools are not allowed here.');
  
  end if;

/*exception
  when others then
    insert into a
      select vv_username, vv_program from dual;
    commit;*/
end;

-----------------------------------------------------