oracle 触发器脚本

一、 限定仅能从指定服务器/工具连接

create or replace trigger logon_ip_module_control
  after logon on database
/*限制DATAFIX用户仅允许通过192.168.1.3的ip使用plsqldev.exe工具登录至数据库*/
declare
  v_ip      Varchar2(50);
  v_user    Varchar2(50);
  v_module  Varchar2(50);
  v_log     Varchar2(300);
  v_message Varchar2(300);
begin
  SELECT SYS_CONTEXT('USERENV', 'SESSION_USER'),
         SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
         SYS_CONTEXT('USERENV', 'MODULE')
    into v_user, v_ip, v_module
    from dual;
  if v_user = 'DATAFIX' THEN
    IF (v_ip = '192.168.1.3' and v_module = 'plsqldev.exe') THEN
      -- Access Gateway 
      null;
    else
      v_log := 'LOGON_IP_MODULE_CONTROL_TRIGGER -- User: ' || v_user ||
               ', IP: ' || v_ip || ', Logon tool:' || v_module;
      sys.dbms_system.ksdwrt(2, v_log);
    
      v_message := 'User ' || v_user ||
                   ' is not allowed to connect from this IP address or logon tool.Please contact your DBA to help.';
      raise_application_error(-20001, v_message);
    END IF;
  END IF;
end;

或者

**/
Create Or Replace Trigger DENY_LOGIN  
 After Logon On Database
Declare
 v_Program Varchar2(48);
 v_Message Varchar2(1000);
 v_deny_Client Exception;
 v_deny_User Exception;
 v_deny_Ip Exception;
Begin
 Select Program Into v_Program From V$session Where Audsid = Sys_Context('USERENV', 'SESSIONID') And Rownum < 2;
 If  Lower(v_Program) = 'plsqldev.exe' Then
     Raise v_deny_Client;
 End If;
 If  User In ('SCOTT','scott') Then
     Raise v_deny_User;
 End If;
 If Sys_Context('USERENV', 'ip_address') = '10.117.196.52' Then
     Raise v_deny_Ip;
 End If;
Exception
 When v_deny_Client Then
   v_Message := 'Sorry！You cannot access database using this software client!';
   Raise_Application_Error(-20001, v_Message);
 When v_deny_User Then
   v_Message := 'Sorry！Database deny you('||User||') access,Contact DBAs please!';
   Raise_Application_Error(-20002, v_Message);
 When v_deny_Ip Then
   v_Message := 'Sorry！Database deny you('||Sys_Context('USERENV', 'SESSIONID') ||') access,Contact DBAs please!';
   Raise_Application_Error(-20003, v_Message);
 When Others Then
   v_Message := 'ERROR – NOT_LOGON TRIGGER- Please Contact Your DBA!!' || Sqlerrm;
   Raise_Application_Error(-20004, v_Message);
End;
/

二、 监控指定用户登录

其实这个可以开登录审计，这里只作为多了解一种方法。

create or replace trigger sysdba_to_alert
after logon on database
declare
message varchar2(256);
IP varchar2(15);
v_os_user varchar2(80);
v_module varchar2(50);
v_action varchar2(50);
v_type varchar2(50);
v_sql_id varchar2(50);
v_pid varchar2(10);
v_sid number;
v_program varchar2(48);
v_client_id VARCHAR2(64);
begin
IF user ='SYS' THEN

-- get IP for remote connections:
if sys_context('userenv','network_protocol') = 'TCP' then
IP := sys_context('userenv','ip_address');
end if;

select distinct sid into v_sid from sys.v_$mystat;
SELECT p.SPID, v.PROGRAM,v.type,v.sql_id into v_pid, v_program,v_type,v_sql_id
FROM V$PROCESS p, V$SESSION v
WHERE p.ADDR = v.PADDR AND v.sid = v_sid;

v_os_user := sys_context('userenv','os_user');
dbms_application_info.READ_MODULE(v_module,v_action);

v_client_id := sys_context('userenv','client_identifier');

message:= to_char(sysdate,'Dy Mon dd HH24:MI:SS YYYY')||
' SYSDBA logon from '||nvl(IP,'localhost')||' '||v_sid||' '||v_sql_id||
' '||v_os_user||' '||v_client_id||' '||v_type||
' with '||v_program||' '||v_module||' '||v_action;

sys.dbms_system.ksdwrt(2,message);

end if;
end;
/

三、 只允许用户连到从库不允许连到主库

Create Trigger DENY_LOGIN
  After Logon On Database
Declare
  v_db_role Varchar2(20);
  v_Message Varchar2(1000);
  v_deny_User Exception;
Begin
  select DATABASE_ROLE into v_db_role from v$database;
  If User In ('TESTERP', 'testerp') Then
    if v_db_role = 'PRIMARY' then
      Raise v_deny_User;
    End If;
  End If;
Exception
  When v_deny_User Then
    v_Message := 'Sorry! Primary database denies your user (' || User ||
                 ') to access.Please connect to the standby database.';
    Raise_Application_Error(-20002, v_Message);
  When Others Then
    v_Message := 'ERROR – NOT_LOGON TRIGGER- Please Contact Your DBA.' ||
                 Sqlerrm;
    Raise_Application_Error(-20004, v_Message);
End;
/

四、 限制用户在某时间段内登陆数据库

• 被限制的用户不能拥有dba权限
• 需要通过sys用户创建触发器

CREATE OR REPLACE TRIGGER limit_connection
AFTER LOGON ON DATABASE
BEGIN
IF USER='TM1' THEN
IF to_number(TO_CHAR (SYSDATE,'HH24')) BETWEEN 8 AND 22
THEN
RAISE_APPLICATION_ERROR(-20998,'CONT LOGIN BETWEEN 8-20');
END IF;
END IF;
END limit_connection;
/

五、 dg broker，让应用可以自动切换的trigger（高可用的）

create trigger myapptrigg
  after startup on database
declare
  v_role varchar(30);
begin
  select database_role into v_role from v$database;
  if v_role = 'PRIMARY' then
    DBMS_SERVICE.START_SERVICE('myapp');
  else
    DBMS_SERVICE.STOP_SERVICE('myapp');
  end if;
end;
/

五、 禁止DROP、TRUNCATE操作

CREATE OR REPLACE TRIGGER TR_DISABLE_DDL
BEFORE DDL ON DATABASE
BEGIN
  DECLARE 
    L_FLAG INT;
    L_TEMPORARY VARCHAR2(50);
  BEGIN
   IF ORA_SYSEVENT = 'TRUNCATE' OR ORA_SYSEVENT='DROP' THEN
/*       DBMS_OUTPUT.put_line('操作类型:' || ORA_SYSEVENT || CHR(13) ||
                           '操作对象:' || ORA_DICT_OBJ_TYPE || CHR(13) || 
                           '所属用户:' || ORA_DICT_OBJ_NAME || CHR(13) || 
                           '对象名称:' || ORA_DICT_OBJ_OWNER  );*/

     SELECT COUNT(1) INTO L_FLAG 
     FROM USER_TABLES 
     WHERE TABLE_NAME=UPPER(ORA_DICT_OBJ_NAME)
       AND TEMPORARY='N' ;
     
     IF L_FLAG=1 THEN
       RAISE_APPLICATION_ERROR(-20000,'You cannot TRUNCATE or DROP table: ' || ORA_DICT_OBJ_NAME);
     END IF;
                             
    END IF;
  END;
END;
/

参考

https://blog.csdn.net/blogliu/article/details/79559395

ORACLE 触发器控制用户登录之权限限制_kissweety的博客-CSDN博客