オラクる。

oracle専門ブログにしてみようかな~っと

2016年07月

unified audit機能で様々なactionを監査証跡として記録することが出来ます。

[oracle@DB01 ~]$ sqlplus sys/password@localhost/pdb1.test.local as sysdba

SQL> CREATE USER test2 IDENTIFIED BY password QUOTA UNLIMITED ON users;

ユーザーが作成されました。

SQL> GRANT CREATE SESSION TO test2;

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


まず、テスト用にユーザーを作成します。

[oracle@DB01 ~]$ sqlplus test/password@localhost/pdb1.test.local

SQL> DROP TABLE tab1 PURGE;

表が削除されました。

SQL> DROP SEQUENCE tab1_seq;

順序が削除されました。

SQL> CREATE TABLE tab1 (
  2  id NUMBER,
  3  CONSTRAINT tab1_pk PRIMARY KEY(id)
  4  );

表が作成されました。

SQL> CREATE SEQUENCE tab1_seq;

順序が作成されました。

SQL> CREATE TABLE tab2 (
  2  id NUMBER,
  3  CONSTRAINT tab2_pk PRIMARY KEY(id)
  4  );

表が作成されました。

SQL> CREATE SEQUENCE tab2_seq;

順序が作成されました。


さらにテスト用にテーブルと順序をそれぞれ2つ作成します。

SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON tab1 TO test2;

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

SQL> GRANT SELECT ON tab1_seq TO test2;

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

SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON tab2 TO test2;

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

SQL> GRANT SELECT ON tab2_seq TO test2;

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


それぞれのオブジェクトに対してテスト用ユーザーの権限を与えます。

[oracle@DB01 ~]$ sqlplus sys/password@localhost/pdb1.test.local as sysdba

CREATE AUDIT POLICY test_audit_policy
ACTIONS DELETE ON test.tab1,
    INSERT ON test.tab1,
    UPDATE ON test.tab1,
    SELECT ON test.tab1_seq,
    ALL ON test.tab2,
    SELECT ON test.tab2_seq
WHEN    'SYS_CONTEXT(''USERENV'',''SESSION_USER'') =''TEST2'''
EVALUATE PER SESSION
CONTAINER = CURRENT;

監査ポリシーが作成されました。


新しく監査ポリシーを作成します。
テスト用ユーザー「test2」が行なうアクションを細かく監査しています。

AUDIT POLICY test_audit_policy;

監査が成功しました。


ポリシーを有効化します。

[oracle@DB01 ~]$ sqlplus test/password@localhost/pdb1.test.local

SQL> INSERT INTO tab1 (id) VALUES (tab1_seq.NEXTVAL);

1行が作成されました。

SQL> INSERT INTO tab2 (id) VALUES (tab2_seq.NEXTVAL);

1行が作成されました。


監査ポリシーで指定しなかったユーザーでinsertと順序に関するアクションを行ないます。

[oracle@DB01 ~]$ sqlplus test2/password@localhost/pdb1.test.local

SQL> UPDATE test.tab1 SET id = test.tab1_seq.NEXTVAL;

1行が更新されました。

SQL> UPDATE test.tab2 SET id = test.tab2_seq.NEXTVAL;

1行が更新されました。

SQL> DELETE FROM test.tab1;

1行が削除されました。

SQL> DELETE FROM test.tab2;

1行が削除されました。

SQL> COMMIT;

コミットが完了しました。


監査対象のユーザーで色々なアクションを実行してみます。

[oracle@DB01 ~]$ sqlplus sys/password@localhost/pdb1.test.local as sysdba

SET LINESIZE 200
COLUMN event_timestamp FORMAT A30
COLUMN dbusername FORMAT A10
COLUMN action_name FORMAT A20
COLUMN object_schema FORMAT A10
COLUMN object_name FORMAT A20

SELECT event_timestamp,
       dbusername,
       action_name,
       object_schema,
       object_name
FROM   unified_audit_trail
WHERE  dbusername LIKE 'TEST%'
ORDER BY event_timestamp;

EVENT_TIMESTAMP                DBUSERNAME ACTION_NAME          OBJECT_SCH OBJECT_NAME
------------------------------ ---------- -------------------- ---------- --------------------
16-07-14 00:54:12.383998       TEST2      SELECT               TEST       TAB1_SEQ
16-07-14 00:54:12.384101       TEST2      UPDATE               TEST       TAB1
16-07-14 00:54:24.204044       TEST2      SELECT               TEST       TAB2_SEQ
16-07-14 00:54:24.204085       TEST2      UPDATE               TEST       TAB2
16-07-14 00:55:19.633921       TEST2      DELETE               TEST       TAB1
16-07-14 00:55:27.427667       TEST2      DELETE               TEST       TAB2


早速、監査証跡 unified_audit_trailを確認します。
監査対象外のユーザーtestのアクションは一切記録されていません。
その一方で今回作成した監査対象のユーザーtest2は記録されています。

Oracle 12cで非常に使いやすくなった監査機能「Unified Audit(統合監査)」を検証してみましょう。
どのように監査するかは全てポリシーで定義され、監査証跡はどのような内容でも、「unified_audit_trail」ビューで共通で確認することが出来ます。

[oracle@DB01 ~]$ sqlplus sys/password@localhost/pdb1.test.local as sysdba

SQL> SELECT value FROM v$option WHERE parameter='Unified Auditing';

VALUE
----------------------------------------------------------------
FALSE


デフォルトでは統合監査は有効になっていません。

[root@DB01 ~]# su - oracle
[oracle@DB01 ~]$ cd $ORACLE_HOME/rdbms/lib
[oracle@DB01 lib]$ make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

有効にするにはoracleユーザーでライブラリをmakeします。

SQL> SELECT value FROM v$option WHERE parameter='Unified Auditing';

VALUE
----------------------------------------------------------------
TRUE


統合監査が有効になりました。

SQL> CREATE USER test IDENTIFIED BY password QUOTA UNLIMITED ON users;

ユーザーが作成されました。

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE TO test;

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


統合監査確認用のtestユーザーを作成します。
create tableとcreate sequenceの権限をtestに付与します。
これらの権限がtestによって利用された時に監査ログを出力するように設定してみます。

CREATE AUDIT POLICY test_audit_policy
PRIVILEGES CREATE TABLE,CREATE SEQUENCE
WHEN 'SYS_CONTEXT(''USERENV'',''SESSION_USER'')=''TEST'''
EVALUATE PER SESSION
CONTAINER = CURRENT;

監査ポリシーが作成されました。


まずポリシーを作成します。
「WHEN 'SYS_CONTEXT(''USERENV'',''SESSION_USER'')=''TEST''」→ユーザーがtestの時、
「PRIVILEGES CREATE TABLE,CREATE SEQUENCE」→create tableとcreate sequenceの権限が利用されたら監査ログを出力する。
こんな感じのポリシーです。

AUDIT POLICY test_audit_policy;

監査が成功しました。


監査ポリシーを有効化します。

SET LINESIZE 200
COLUMN audit_option FORMAT A15
COLUMN condition_eval_opt FORMAT A10
COLUMN audit_condition FORMAT A50

SELECT audit_option,
       condition_eval_opt,
       audit_condition
FROM   audit_unified_policies
WHERE  policy_name = 'TEST_AUDIT_POLICY';

AUDIT_OPTION    CONDITION_ AUDIT_CONDITION
--------------- ---------- --------------------------------------------------
CREATE SEQUENCE SESSION    SYS_CONTEXT('USERENV','SESSION_USER')='TEST'
CREATE TABLE    SESSION    SYS_CONTEXT('USERENV','SESSION_USER')='TEST'


作成したポリシーはaudit_unified_policiesビューで確認できます。

[oracle@DB01 ~]$ sqlplus test/password@localhost/pdb1.test.local

SQL> CREATE TABLE tab1 (id NUMBER);

表が作成されました。

SQL> CREATE SEQUENCE tab1_seq;

順序が作成されました。

実際にtestユーザーで該当の権限を利用してみます。

[oracle@DB01 ~]$ sqlplus sys/password@localhost/pdb1.test.local as sysdba

SET LINESIZE 200
COLUMN event_timestamp FORMAT A30
COLUMN dbusername FORMAT A10
COLUMN action_name FORMAT A20
COLUMN object_schema FORMAT A10
COLUMN object_name FORMAT A20

SELECT event_timestamp,
       dbusername,
       action_name,
       object_schema,
       object_name
FROM   unified_audit_trail
WHERE  dbusername = 'TEST'
ORDER BY event_timestamp;

EVENT_TIMESTAMP                DBUSERNAME ACTION_NAME          OBJECT_SCH OBJECT_NAME
------------------------------ ---------- -------------------- ---------- --------------------
16-07-13 00:37:58.913061       TEST       CREATE TABLE         TEST       TAB1
16-07-13 00:38:27.946879       TEST       CREATE SEQUENCE      TEST       TAB1_SEQ


unified_audit_trailビューを確認しています。
しっかりとログが出力されていました。

SQL> NOAUDIT POLICY test_audit_policy;

監査取消しが成功しました。

SQL> DROP AUDIT POLICY test_audit_policy;

監査ポリシーが削除されました。


不要になったポリシーは無効化、削除します。
必ず、この順番です。

作成したredactionのポリシーはdrop_policyプロシージャで削除することが出来ます。

BEGIN
    DBMS_REDACT.drop_policy (
        object_schema => 'test',
        object_name   => 'payment_details',
        policy_name   => 'redact_card_info'
    );
END;
/


前回、作成して色々と利用したポリシーを指定して、drop_policyします。

ALTER SESSION SET nls_date_format='DD-MON-YYYY';
COLUMN card_no FORMAT 9999999999999999
SET LINESIZE 200

SELECT *
FROM payment_details
ORDER BY id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE        SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ---------------- ---------- ----------------
         1        4000  1234123412341234 1234-1234-1234-1234 06-7月 -2017            123
         2        4001  2345234523452345 2345-2345-2345-2345 06-7月 -2017            234
         3        4002  3456345634563456 3456-3456-3456-3456 06-7月 -2017            345
         4        4003  4567456745674567 4567-4567-4567-4567 06-7月 -2017            456
         5        4004  5678567856785678 5678-5678-5678-5678 06-7月 -2017            567


再度、表をselectしてみます。
元通りの表が出力されたことが確認できます。

12cの新機能db redactionは表を一切書き換えることなく、クライアントへの見た目を変えることが出来ます。
またアプリ側での対応は一切必要なく、DB側の対応だけで足りるという利点もあります。

reductionポリシーのexpression句を工夫することでユーザーごとにポリシーの適用、非適用を制御することが可能です。
下記の例ではスキーマユーザーのtestとtest2が存在しているとします。

[oracle@DB01 ~]$ sqlplus test/password@localhost/pdb1.test.local

ALTER SESSION SET nls_date_format='DD-MON-YYYY';
COLUMN card_no FORMAT 9999999999999999
SET linesize 300

SELECT *
FROM test.payment_details
ORDER BY id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE        SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ---------------- ---------- ----------------
         1        4000  1111111111111234 ####-####-####-1234 01-1月 -2017            123
         2        4001  1111111111112345 ####-####-####-2345 01-1月 -2017            234
         3        4002  1111111111113456 ####-####-####-3456 01-1月 -2017            345
         4        4003  1111111111114567 ####-####-####-4567 01-1月 -2017            456
         5        4004  1111111111115678 ####-####-####-5678 01-1月 -2017            567

[oracle@DB01 ~]$ sqlplus test2/password@localhost/pdb1.test.local

ALTER SESSION SET nls_date_format='DD-MON-YYYY';
COLUMN card_no FORMAT 9999999999999999
SET linesize 300

SELECT *
FROM test.payment_details
ORDER BY id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE        SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ---------------- ---------- ----------------
         1        4000  1111111111111234 ####-####-####-1234 01-1月 -2017            123
         2        4001  1111111111112345 ####-####-####-2345 01-1月 -2017            234
         3        4002  1111111111113456 ####-####-####-3456 01-1月 -2017            345
         4        4003  1111111111114567 ####-####-####-4567 01-1月 -2017            456
         5        4004  1111111111115678 ####-####-####-5678 01-1月 -2017            567


通常はtestユーザーもtest2ユーザーも同じポリシーが適用されます。

BEGIN
    DBMS_REDACT.alter_policy (
        object_schema    => 'test',
        object_name    => 'payment_details',
        policy_name    => 'redact_card_info',
        action        => DBMS_REDACT.modify_expression,
        column_name    => 'card_no',
        expression    => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''TEST'''
    );
END;
/


alter_policy句で既存のポリシーを変更します。
「SYS_CONTEXT(''USERENV'',''SESSION_USER'')」は現在sessionを保持しているユーザーを表します。
このユーザーが「test」ではないときにポリシーが実行されます。

[oracle@DB01 ~]$ sqlplus test/password@localhost/pdb1.test.local

ALTER SESSION SET nls_date_format='DD-MON-YYYY';
COLUMN card_no FORMAT 9999999999999999
SET linesize 300

SELECT *
FROM test.payment_details
ORDER BY id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE        SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ---------------- ---------- ----------------
         1        4000  1234123412341234 1234-1234-1234-1234 06-7月 -2017            123
         2        4001  2345234523452345 2345-2345-2345-2345 06-7月 -2017            234
         3        4002  3456345634563456 3456-3456-3456-3456 06-7月 -2017            345
         4        4003  4567456745674567 4567-4567-4567-4567 06-7月 -2017            456
         5        4004  5678567856785678 5678-5678-5678-5678 06-7月 -2017            567


testユーザーではポリシーが適用されていません。

[oracle@DB01 ~]$ sqlplus test2/password@localhost/pdb1.test.local

ALTER SESSION SET nls_date_format='DD-MON-YYYY';
COLUMN card_no FORMAT 9999999999999999
SET linesize 300

SELECT *
FROM test.payment_details
ORDER BY id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE        SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ---------------- ---------- ----------------
         1        4000  1111111111111234 ####-####-####-1234 01-1月 -2017            123
         2        4001  1111111111112345 ####-####-####-2345 01-1月 -2017            234
         3        4002  1111111111113456 ####-####-####-3456 01-1月 -2017            345
         4        4003  1111111111114567 ####-####-####-4567 01-1月 -2017            456
         5        4004  1111111111115678 ####-####-####-5678 01-1月 -2017            567


test2ユーザーでポリシーが適用されました。

DBMS_REDACTパッケージのadd_policyで作成したredactionのポリシーはalter_policyで変更することが出来ます。
すでに定義した列の修正や、別の列に定義を追加することが可能です。

BEGIN
    DBMS_REDACT.alter_policy (
        object_schema    => 'test',
        object_name    => 'payment_details',
        policy_name    => 'redact_card_info',
        action        => DBMS_REDACT.modify_column,
        column_name    => 'card_no',
        function_type    => DBMS_REDACT.partial,
        function_parameters => '1,1,12'
    );
END;
/

ALTER SESSION SET nls_date_format='DD-MON-YYYY';
COLUMN card_no FORMAT 9999999999999999
SET linesize 300

SELECT *
FROM payment_details
ORDER BY id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE        SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ---------------- ---------- ----------------
         1        4000  1111111111111234 1234-1234-1234-1234 06-7月 -2017            123
         2        4001  1111111111112345 2345-2345-2345-2345 06-7月 -2017            234
         3        4002  1111111111113456 3456-3456-3456-3456 06-7月 -2017            345
         4        4003  1111111111114567 4567-4567-4567-4567 06-7月 -2017            456
         5        4004  1111111111115678 5678-5678-5678-5678 06-7月 -2017            567


既にポリシーが定義された列の定義を修正しています。
「function_parameters => '1,1,12'」→1文字目から12文字目を1という数字に置き換えます。

BEGIN
    DBMS_REDACT.alter_policy (
        object_schema    => 'test',
        object_name    => 'payment_details',
        policy_name    => 'redact_card_info',
        action        => DBMS_REDACT.add_column,
        column_name    => 'card_string',
        function_type    => DBMS_REDACT.partial,
        function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,#,1,12'
    );
END;
/

ALTER SESSION SET nls_date_format='DD-MON-YYYY';
COLUMN card_no FORMAT 9999999999999999
SET linesize 300

SELECT *
FROM payment_details
ORDER BY id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE        SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ---------------- ---------- ----------------
         1        4000  1111111111111234 ####-####-####-1234 06-7月 -2017            123
         2        4001  1111111111112345 ####-####-####-2345 06-7月 -2017            234
         3        4002  1111111111113456 ####-####-####-3456 06-7月 -2017            345
         4        4003  1111111111114567 ####-####-####-4567 06-7月 -2017            456
         5        4004  1111111111115678 ####-####-####-5678 06-7月 -2017            567


新たにcard_string列に定義を追加しています。
「 function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,#,1,12'」→数字の部分の1文字目から12文字目を#に置き換えています。

BEGIN
    DBMS_REDACT.alter_policy (
        object_schema    => 'test',
        object_name    => 'payment_details',
        policy_name    => 'redact_card_info',
        action        => DBMS_REDACT.add_column,
        column_name    => 'expiry_Date',
        function_type    => DBMS_REDACT.partial,
        function_parameters => 'm1d1Y'
    );
END;
/

ALTER SESSION SET nls_date_format='DD-MON-YYYY';
COLUMN card_no FORMAT 9999999999999999
SET linesize 300

SELECT *
FROM payment_details
ORDER BY id;

        ID CUSTOMER_ID           CARD_NO CARD_STRING         EXPIRY_DATE        SEC_CODE VALID_DATE
---------- ----------- ----------------- ------------------- ---------------- ---------- ----------------
         1        4000  1111111111111234 ####-####-####-1234 01-1月 -2017            123
         2        4001  1111111111112345 ####-####-####-2345 01-1月 -2017            234
         3        4002  1111111111113456 ####-####-####-3456 01-1月 -2017            345
         4        4003  1111111111114567 ####-####-####-4567 01-1月 -2017            456
         5        4004  1111111111115678 ####-####-####-5678 01-1月 -2017            567


上記の例ではexpiry_dateに記載されている日付を同年の1月1日に置き換えています。

このページのトップヘ