oracle
12c19c

Oracle ログイン・ログアウトトリガー

2022.01.23

先日DBへの接続状況をトリガーを使って調査したので以下サンプルとして掲載します。ＤＤＬトリガーはおまけです。

ADMINISTER DATABASE TRIGGER システム権限が必要です

今回のトリガーには何れもADMINISTER DATABASE TRIGGERが必要です。

grant ADMINISTER DATABASE TRIGGER to sooni
;
-- 権限付与
SQL> grant ADMINISTER DATABASE TRIGGER to sooni
  2  ;

権限付与が成功しました。

-- 権限の確認
SQL> set lin 200
SQL> COLUMN GRANTEE FORMAT A10
SQL> COLUMN PRIVILEGE FORMAT A30
SQL> select s.GRANTEE,s.PRIVILEGE from dba_sys_privs s where s.GRANTEE='SOONI'
  2  ;

GRANTEE    PRIVILEGE
---------- ------------------------------
SOONI      CREATE MATERIALIZED VIEW
SOONI      CREATE TABLE
SOONI      CREATE VIEW
SOONI      CREATE SYNONYM
SOONI      SELECT ANY SEQUENCE
SOONI      CREATE SESSION
SOONI      SELECT ANY DICTIONARY
SOONI      ADMINISTER DATABASE TRIGGER
SOONI      ALTER TABLESPACE

9行が選択されました。

SQL>

ログイントリガー

create_MNT_LOGON_TRIG.sql
CREATE OR REPLACE TRIGGER LOGON_TRIG
 AFTER LOGON ON DATABASE

DECLARE
 -- 自立型トランザクション宣言
PRAGMA AUTONOMOUS_TRANSACTION;
 
BEGIN
  insert into dabada.MNT_EVENT
(
   EVENT_ID
  ,EVENT_TYPE
  ,EVENT_DATE
  ,CIL_OBJ_TYPE
  ,COL_OWNER
  ,COL_OBJ_NAME
  ,INST_ID
  ,SID
  ,AUDSID
  ,USERNAME
  ,SERVICE_NAME
  ,OSUSER
  ,MACHINE
  ,IP_ADDRESS
  ,PROGRAM
  ,NOTE
)
SELECT
    to_number(to_char(systimestamp,'yyyymmddhh24missff'))  EVENT_ID
  ,'LOGON' EVENT_TYPE
  ,systimestamp EVENT_DATE
  ,null CIL_OBJ_TYPE
  ,null COL_OWNER
  ,null COL_OBJ_NAME
  ,SYS_CONTEXT('USERENV', 'INSTANCE') INST_ID
  ,SYS_CONTEXT('USERENV', 'SID') sid
  ,SYS_CONTEXT('USERENV', 'SESSIONID') SESSIONID
  ,SYS_CONTEXT('USERENV', 'SESSION_USER') SESSION_USER
  ,SYS_CONTEXT('USERENV', 'SERVICE_NAME') SERVICE_NAME
  ,SYS_CONTEXT('USERENV', 'OS_USER') OS_USER
  ,SYS_CONTEXT('USERENV', 'HOST') HOST
  ,SYS_CONTEXT('USERENV', 'IP_ADDRESS') IP_ADDRESS
  ,SYS_CONTEXT('USERENV', 'MODULE') MODULE
  ,null note
  FROM DUAL
;
commit;

END ;
/

ログアウトトリガー

create_MNT_LOGOFF_TRIG.sql
CREATE OR REPLACE TRIGGER LOGOFF_TRIG
 BEFORE LOGOFF ON DATABASE

DECLARE
 -- 自立型トランザクション宣言
 PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
  insert into dabada.MNT_EVENT
(
   EVENT_ID
  ,EVENT_TYPE
  ,EVENT_DATE
  ,CIL_OBJ_TYPE
  ,COL_OWNER
  ,COL_OBJ_NAME
  ,INST_ID
  ,SID
  ,AUDSID
  ,USERNAME
  ,SERVICE_NAME
  ,OSUSER
  ,MACHINE
  ,IP_ADDRESS
  ,PROGRAM
  ,NOTE
)
SELECT
    to_number(to_char(systimestamp,'yyyymmddhh24missff'))  EVENT_ID
  ,'LOGOFF' EVENT_TYPE
  ,sysdate EVENT_DATE
  ,null CIL_OBJ_TYPE
  ,null COL_OWNER
  ,null COL_OBJ_NAME
  ,SYS_CONTEXT('USERENV', 'INSTANCE') INST_ID
  ,SYS_CONTEXT('USERENV', 'SID') sid
  ,SYS_CONTEXT('USERENV', 'SESSIONID') SESSIONID
  ,SYS_CONTEXT('USERENV', 'SESSION_USER') SESSION_USER
  ,SYS_CONTEXT('USERENV', 'SERVICE_NAME') SERVICE_NAME
  ,SYS_CONTEXT('USERENV', 'OS_USER') OS_USER
  ,SYS_CONTEXT('USERENV', 'HOST') HOST
  ,SYS_CONTEXT('USERENV', 'IP_ADDRESS') IP_ADDRESS
  ,SYS_CONTEXT('USERENV', 'MODULE') MODULE
  ,null note
  FROM DUAL
;
commit;

END ;
/

DDLトリガー

create_MNT_DDL_TRIG.sql
CREATE OR REPLACE TRIGGER MNT_DDL_TRIG
BEFORE TRUNCATE OR DROP OR CREATE OR ALTER
 ON DATABASE

DECLARE
    -- 自立型トランザクション宣言
    PRAGMA AUTONOMOUS_TRANSACTION;

    V_SYSEVENT         VARCHAR2(200);
    V_DICT_OBJ_OWNER   VARCHAR2(30);
    V_DICT_OBJ_NAME    VARCHAR2(30);
    V_DICT_OBJ_TYPE    VARCHAR2(20);
    -- V_LOGIN_USER       VARCHAR2(30);

BEGIN
    /** イベントの取得 **/
    V_SYSEVENT        := ora_sysevent;
    /** オブジェクトの所有者の取得   **/
    V_DICT_OBJ_OWNER  := ora_dict_obj_owner;
    /**  オブジェクト名の取得  **/
    V_DICT_OBJ_NAME   := ora_dict_obj_name;
    /** オブジェクトのタイプの取得   **/
    V_DICT_OBJ_TYPE   := ora_dict_obj_type;
    -- V_LOGIN_USER      := ORA_LOGIN_USER;
    
insert into dabada.MNT_EVENT
(
   EVENT_ID
  ,EVENT_TYPE
  ,EVENT_DATE
  ,CIL_OBJ_TYPE
  ,COL_OWNER
  ,COL_OBJ_NAME
  ,INST_ID
  ,SID
  ,AUDSID
  ,USERNAME
  ,SERVICE_NAME
  ,OSUSER
  ,MACHINE
  ,IP_ADDRESS
  ,PROGRAM
  ,NOTE
)
SELECT
   to_number(to_char(systimestamp,'yyyymmddhh24missff'))  -- EVENT_ID
  ,V_SYSEVENT                                  -- EVENT_TYPE
  ,sysdate                                     -- EVENT_DATE
  ,V_DICT_OBJ_TYPE                             -- CIL_OBJ_TYPE
  ,V_DICT_OBJ_OWNER                            -- COL_OWNER
  ,V_DICT_OBJ_NAME                             -- COL_OBJ_NAME
  ,SYS_CONTEXT('USERENV', 'INSTANCE')          -- INST_ID
  ,SYS_CONTEXT('USERENV', 'SID')               -- sid
  ,SYS_CONTEXT('USERENV', 'SESSIONID')         -- SESSIONID
  ,SYS_CONTEXT('USERENV', 'SESSION_USER')      -- SESSION_USER
  ,SYS_CONTEXT('USERENV', 'SERVICE_NAME')      -- SERVICE_NAME
  ,SYS_CONTEXT('USERENV', 'OS_USER')           -- OS_USER
  ,SYS_CONTEXT('USERENV', 'HOST')              -- HOST
  ,SYS_CONTEXT('USERENV', 'IP_ADDRESS')        -- IP_ADDRESS
  ,SYS_CONTEXT('USERENV', 'MODULE')            -- MODULE
  ,null                                        -- note
  FROM DUAL
;

commit;

END ;
/

トリガーが書き込むテーブル

好みに応じて変更してお使いください。


CREATE TABLE "DABADA"."MNT_EVENT"
 (    
  "EVENT_ID" NUMBER,
  "EVENT_TYPE" VARCHAR2(30),
  "EVENT_DATE" DATE,
  "CIL_OBJ_TYPE" VARCHAR2(30),
  "COL_OWNER" VARCHAR2(30),
  "COL_OBJ_NAME" VARCHAR2(30),
  "INST_ID" NUMBER,
  "SID" NUMBER,
  "AUDSID" NUMBER,
  "USERNAME" VARCHAR2(30),
  "SERVICE_NAME" VARCHAR2(30),
  "OSUSER" VARCHAR2(30),
  "MACHINE" VARCHAR2(64),
  "IP_ADDRESS" VARCHAR2(30),
  "PROGRAM" VARCHAR2(48),
  "NOTE" VARCHAR2(64)
 ) ;
ALTER TABLE "DABADA"."MNT_EVENT" ADD CONSTRAINT "MNT_EVENT_PK" PRIMARY KEY ("EVENT_ID")
USING INDEX  ENABLE;


COMMENT ON COLUMN "DABADA"."MNT_EVENT"."EVENT_ID" IS 'イベント管理No';
COMMENT ON COLUMN "DABADA"."MNT_EVENT"."EVENT_TYPE" IS 'イベントの種類';
COMMENT ON COLUMN "DABADA"."MNT_EVENT"."EVENT_DATE" IS 'イベント発生時間';
COMMENT ON COLUMN "DABADA"."MNT_EVENT"."CIL_OBJ_TYPE" IS 'イベント対象OBJタイプ';
COMMENT ON COLUMN "DABADA"."MNT_EVENT"."COL_OWNER" IS 'イベント対象OBJオーナ';
COMMENT ON COLUMN "DABADA"."MNT_EVENT"."COL_OBJ_NAME" IS 'イベント対象OBJ';
COMMENT ON COLUMN "DABADA"."MNT_EVENT"."INST_ID" IS 'インスタンスID';
COMMENT ON COLUMN "DABADA"."MNT_EVENT"."SID" IS 'セッション識別子';
COMMENT ON COLUMN "DABADA"."MNT_EVENT"."AUDSID" IS '監査セッションID';
COMMENT ON COLUMN "DABADA"."MNT_EVENT"."USERNAME" IS 'Oracleユーザー名';
COMMENT ON COLUMN "DABADA"."MNT_EVENT"."SERVICE_NAME" IS 'サービス名';
COMMENT ON COLUMN "DABADA"."MNT_EVENT"."OSUSER" IS 'OSユーザー名';
COMMENT ON COLUMN "DABADA"."MNT_EVENT"."MACHINE" IS 'クライアントマシン名';
COMMENT ON COLUMN "DABADA"."MNT_EVENT"."IP_ADDRESS" IS 'クライアントIPアドレス';
COMMENT ON COLUMN "DABADA"."MNT_EVENT"."PROGRAM" IS 'OSシステムのプログラム名';
COMMENT ON TABLE "DABADA"."MNT_EVENT"  IS 'イベント履歴';
スポンサーリンク
sooni
タイトルとURLをコピーしました