Oracle預警使用者登入鎖定、過期及其審計資訊

文章版權所有 Jusin Hao（luckyfriends） ，支援原創，轉載請註明
----建立儲存過程（預警使用者登入鎖定、過期及其審計資訊）
CREATE OR REPLACE PROCEDURE CUX_USERLOCK_ALERT as
  SUBJECT       varchar2(100);
  INFO_USER1    varchar2(4000);
  INFO_USER2    varchar2(4000);
  v_lock_status number;


BEGIN
  ------根據使用者狀態是否鎖定
  select count(1)
   into v_lock_status
    from dba_users a
   where a.username in
         ('SCOTT')
     and a.account_status in ('LOCKED', 'LOCKED(TIMED)', 'EXPIRED',
                         'EXPIRED & LOCKED(TIMED)', 'EXPIRED & LOCKED');


  ---輸出鎖定使用者的相關資訊（鎖定時間、審計資訊）
  if v_lock_status <> 0 then
    SUBJECT    := 'ERP-SIT-DB User Lock Status Alert Info';
    INFO_USER1 := 'Oracle EBS資料庫使用者鎖定相關資訊:' || '%' || chr(13) || chr(10) ||
                  '使用者名稱       使用者狀態      鎖定時間      使用者主機       操作物件     動作';
    for cur1 in (select dat.username,
                        du.account_status,
                        du.lock_date,
                        dat.userhost,
                        dat.timestamp,
                        dat.obj_name,
                        dat.action_name
                   from dba_audit_trail dat, dba_users du
                  where (dat.obj_name = du.username or
                        dat.username = du.username)
                    and trunc(dat.timestamp) = trunc(sysdate)
                    and du.username in ('SCOTT')
                    and du.account_status in
                        ('LOCKED', 'LOCKED(TIMED)', 'EXPIRED',
                         'EXPIRED & LOCKED(TIMED)', 'EXPIRED & LOCKED'))


     loop
      INFO_USER2 := cur1.username || ','|| cur1.account_status || ','|| cur1.lock_date || ','||  cur1.userhost || ','|| cur1.timestamp || ','||  cur1.obj_name || ','||  cur1.action_name;
      insert into t_audit1 values(INFO_USER2);
      commit;
      --INFO_USER1 := INFO_USER1 || chr(13) || chr(10) || INFO_USER2;


    end loop;
        --send_mail(SUBJECT,INFO_USER1); ---傳送郵件過程
  end if;
end;

---顯示授權
grant select to dba_users to system;
grant select to  dba_audit_trail dat to system;


----建立job（每個兩小時執行一次）
begin
  sys.dbms_job.submit(job => :job,
                      what => 'begin CUX_USERLOCK_ALERT; end;',
                      next_date => to_date('09-06-2014 18:25:34', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'SYSDATE+2/24');
  commit;
end;
/