在我们对Oracle数据库日常管理维护当中,有时候需要审计或监控数据库的连接登录情况,若是非合法的应用程序账号或非合法的IP主机等信息登录,会触发告警,让管理员及时了解一些异常登录的操作,下面是通过设计存储过程+自动作业的形式进行定时监控:

create or replace procedure audit_login_molarac_alert as
  v_content varchar2(500);
  CURSOR c_content IS
    Select Distinct machine || '主机以操作系统用户' || osuser || '在' ||
                    to_char(Login_On_Time, 'yyyy-mm-dd hh24:mi:ss') ||
                    '用程序' || Run_Program || '以数据库用户' || User_In_Db ||
                    '异常登录' || instance_name || ',请注意!'
      From system.Login_Log a
     Where a.Login_On_Time > Sysdate - 1 / 48 and User_In_Db not in ('SMS_LINK') and Run_Program not in('rman@oracle03 (TNS V1-V3)','oraagent.bin@oracle03 (TNS V1-V3)')and to_char(Login_On_Time, 'hh24:mi') not in('05:00','06:00');
     --这里对指定条件的登录会话进行过滤,以上主要是监控在过去半小时内,非应用账号SMS_LINK,并且非Oracle系统账号的进程进行监控,满足条件即触发告警,然后插入短信告警表,触发短信发送!
BEGIN
  OPEN c_content;
  LOOP
    FETCH c_content
      INTO v_content;
    EXIT WHEN c_content%NOTFOUND;
    insert into mbs7_msg.msg_sendsms
      (SSID,
       STID,
       TEMPLETCODE,
       VERSIONCODE,
       CUSCODE,
       MOBILE,
       CONTENT,
       PRIORINDEX,
       CREATETIME)
      select MBS7_MSG.SEQ_MSG_SENDSMS.NEXTVAL,
             0,
             'AlarmSms',
             0,
             0,
             '13926108245',
             v_content,
             10,
             sysdate
        from dual;
    insert into mbs7_msg.msg_sendsms
      (SSID,
       STID,
       TEMPLETCODE,
       VERSIONCODE,
       CUSCODE,
       MOBILE,
       CONTENT,
       PRIORINDEX,
       CREATETIME)
      select MBS7_MSG.SEQ_MSG_SENDSMS.NEXTVAL,
             0,
             'AlarmSms',
             0,
             0,
             '18620798467',
             v_content,
             10,
             sysdate
        from dual;
    insert into mbs7_msg.msg_sendsms
      (SSID,
       STID,
       TEMPLETCODE,
       VERSIONCODE,
       CUSCODE,
       MOBILE,
       CONTENT,
       PRIORINDEX,
       CREATETIME)
      select MBS7_MSG.SEQ_MSG_SENDSMS.NEXTVAL,
             0,
             'AlarmSms',
             0,
             0,
             '13729826939',
             v_content,
             10,
             sysdate
        from dual;
  END loop;
  commit;
  close c_content;
end;
/

当上述存储过程创建完后,新建ORACLE JOB自动任务,每半小时调用一次即可!