CREATE TABLE T_FGA_AUDIT_LOG
(
  TB_USER    VARCHAR2(30),
  TB_TABLE   VARCHAR2(30),
  ZEITPUNKT  TIMESTAMP(6),
  REMARK     VARCHAR2(80)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
/

CREATE TABLE T_FGA_CONFIGUARTION
(
  "SCHEMA"            VARCHAR2(30),
  TABELLE             VARCHAR2(30),
  AUDIT_YN            VARCHAR2(1),
  STATUS              VARCHAR2(80),
  ZEITSTEMPEL_STATUS  DATE                      DEFAULT SYSDATE,
  REMARK              VARCHAR2(250),
  CONSTRAINT T_FGA_CONFIGUARTION_PK
  PRIMARY KEY
  ("SCHEMA", TABELLE)
  USING INDEX T_FGA_CONFIGUARTION_PK
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
/



CREATE OR REPLACE PACKAGE pkg_fga_audit AUTHID CURRENT_USER
AS
   
   PROCEDURE add_policy (p_schema IN VARCHAR2, p_table IN VARCHAR2);

   PROCEDURE drop_policy (p_schema IN VARCHAR2, p_table IN VARCHAR2);

   PROCEDURE setup;

   PROCEDURE watch;
END pkg_fga_audit;
/



CREATE OR REPLACE PACKAGE BODY pkg_fga_audit
AS
   
--   needs:
--      GRANT EXECUTE =>  DBMS_FGA
--      GRANT SELECT ON dba_fga_audit_trail;

  
   gv_sqlcode                    NUMBER;
   --
   --
   --
   policynotexist                EXCEPTION;
   PRAGMA EXCEPTION_INIT (policynotexist, -28102);

--------------------------------------------------------------------------------
   --
   -- add an new policy for: INSERT,UPDATE,DELETE,SELECT
   --
   PROCEDURE add_policy (p_schema IN VARCHAR2, p_table IN VARCHAR2)
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      --
      -- DBMS_FGA.DB = 2
      -- DBMS_FGA.any_columns = 0
      --
      DBMS_FGA.add_policy (object_schema          => p_schema
                         , object_name            => p_table
                         , policy_name            => p_table
                         , audit_condition        => NULL
                         , audit_column           => NULL
                         , handler_schema         => NULL
                         , handler_module         => NULL
                         , ENABLE                 => TRUE
                         , statement_types        => 'INSERT,UPDATE,DELETE,SELECT'
                         , audit_trail            => 2
                         , audit_column_opts      => 0
                          );

      INSERT INTO t_fga_audit_log
                  (tb_user, tb_table, remark, zeitpunkt
                  )
           VALUES (p_schema, p_table, 'add_policy ', SYSDATE
                  );

      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         gv_sqlcode    := SQLCODE;

         INSERT INTO t_fga_audit_log
                     (tb_user, tb_table
                    , remark, zeitpunkt
                     )
              VALUES (p_schema, p_table
                    , 'Error ORA-' || TO_CHAR (gv_sqlcode), SYSDATE
                     );

         COMMIT;
         ROLLBACK;
   END;

--------------------------------------------------------------------------------
--
--
--
   PROCEDURE drop_policy (p_schema IN VARCHAR2, p_table IN VARCHAR2)
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      DBMS_FGA.drop_policy (object_schema      => p_schema
                          , object_name        => p_table
                          , policy_name        => p_table
                           );

      INSERT INTO t_fga_audit_log
                  (tb_user, tb_table, remark, zeitpunkt
                  )
           VALUES (p_schema, p_table, 'drop_policy ', SYSDATE
                  );

      COMMIT;
   EXCEPTION
      WHEN policynotexist
      THEN
         --
         -- EXCEPTION for : ORA-28102: policy does not exist
         --
         INSERT INTO t_fga_audit_log
                     (tb_user, tb_table
                    , remark, zeitpunkt
                     )
              VALUES (p_schema, p_table
                    , 'policy to drop does not exist ORA-28102', SYSDATE
                     );

         COMMIT;
      WHEN OTHERS
      THEN
         gv_sqlcode    := SQLCODE;

         INSERT INTO t_fga_audit_log
                     (tb_user, tb_table
                    , remark, zeitpunkt
                     )
              VALUES (p_schema, p_table
                    , 'Error ORA-' || TO_CHAR (gv_sqlcode), SYSDATE
                     );

         COMMIT;
   END;

--------------------------------------------------------------------------------
--
-- setup
--
   PROCEDURE setup
   IS
      CURSOR c_config
      IS
         SELECT        *
                  FROM t_fga_configuartion
                 WHERE audit_yn IN ('Y', 'N')
         FOR UPDATE OF audit_yn
              ORDER BY SCHEMA, tabelle;
   --
   --
   BEGIN
      --
      FOR rec IN c_config
      LOOP
         IF rec.audit_yn = 'Y'
         THEN
            add_policy (rec.SCHEMA, rec.tabelle);

            UPDATE t_fga_configuartion
               SET audit_yn = '+'
                 , status = 'watched'
             WHERE CURRENT OF c_config;
         ELSIF rec.audit_yn = 'N'
         THEN
            drop_policy (rec.SCHEMA, rec.tabelle);

            UPDATE t_fga_configuartion
               SET audit_yn = '-'
                 , status = 'deactivated'
                 , zeitstempel_status = SYSDATE
             WHERE CURRENT OF c_config;
         END IF;
      END LOOP;

      COMMIT;
   END;

--------------------------------------------------------------------------------
--
--
--
   PROCEDURE watch
   IS
      CURSOR c_watch
      IS
         SELECT     *
               FROM t_fga_configuartion
              WHERE audit_yn = '+'
         FOR UPDATE;

      lv_zeitstempel                DATE;
   BEGIN
      INSERT INTO t_fga_audit_log
                  (tb_user, tb_table, remark, zeitpunkt
                  )
           VALUES ('INTERNAL', 'INTERNAL', 'watch runing', SYSDATE
                  );

      FOR rec IN c_watch
      LOOP
         BEGIN
            SELECT MAX (TIMESTAMP)
              INTO lv_zeitstempel
              FROM SYS.dba_fga_audit_trail
             WHERE TIMESTAMP > rec.zeitstempel_status
               AND object_schema = rec.SCHEMA
               AND object_name = rec.tabelle;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               NULL;
         END;

         IF lv_zeitstempel IS NOT NULL
         THEN
            UPDATE t_fga_configuartion
               SET audit_yn = '-'
                 , status = 'used'
                 , zeitstempel_status = lv_zeitstempel
             WHERE CURRENT OF c_watch;

            --
            INSERT INTO t_fga_audit_log
                        (tb_user, tb_table
                       , remark, zeitpunkt
                        )
                 VALUES (rec.SCHEMA, rec.tabelle
                       , 'used - not monitored anymore', lv_zeitstempel
                        );

            --
            drop_policy (rec.SCHEMA, rec.tabelle);
         --
         ELSE
            --
            INSERT INTO t_fga_audit_log
                        (tb_user, tb_table, remark, zeitpunkt
                        )
                 VALUES (rec.SCHEMA, rec.tabelle, 'checked', SYSDATE
                        );
         END IF;
      END LOOP;

      COMMIT;
   END;
END;
/