X

A blog about Oracle Technology Network Japan

  • August 3, 2014

しばちょう先生の試して納得!DBAへの道  第32回 標準監査の基本的な使い方

しばちょう先生の試して納得!DBAへの道 indexページ▶▶

 


みなさん、こんにちは。"しばちょう"こと柴田長(しばた つかさ)です。暑いなーと、気付けば今年も既に8月に入っていたんですねぇ。そんな暑い夏をもっともっとアツくするイベントをご紹介させて頂きます。

2014年9月7日(日)、Japan Oracle User Group(JPOUG)主催の大規模イベントJPOUG> SET EVENTS 20140907 が開催されます。Oracle DatabaseやMySQLなどを中心としたIT関連技術と歴史や技術者のキャリアに関する話題を扱う多種多様なセッションを予定していますので、技術者の交流の場に興味のある方はWebからのお申込みの上、ぜひご参加ください。私も参加予定です!

さて、今回はASM編を一時中断致しまして、Oracle Databaseが提供する標準監査機能の基本的な使い方を体験して頂きたいと思います。標準監査を有効化した場合には指定操作の監査レコードが記録される動作となる為、データベースに対する作業を常に監視して企業ポリシーから逸脱する可能性があるオペレーションを検出することが可能となります。

Oracle Database 11gにおいてDatabase Configuration Assistant(DBCA)を使用して新しいデータベースを作成した場合は、デフォルトの監査設定が有効化されており、特定の権限を使用したオペレーションやSQL文が監査対象となっています。これに対して、権限やSQL文、オブジェクトをベースとして監査対象を追加する標準監査を始めとして、SYSDBA又はSYSOPER権限を使用して接続したユーザーが発行したトップレベルのSQL文(ユーザーによって直接発行されたSQL文であり、PL/SQL内やファンクション内で実行されたSQL文は対象外)を記録するDBA監査、より細かく監査対象を絞り込むことが可能なファイングレイン監査も提供されています。最後のファイングレイン監査のみがEnterprise Editionでのみの提供になることにご注意くださいね。

  • デフォルト監査
  • 標準監査
  • DBA監査
  • ファイングレイン監査

今回の連載では標準監査の基本的な使い方を中心として、デフォルト監査とDBA監査も合わせて体験して頂きたいと考えています。以下の演習をOracle Database 11g Release 2 (11.2.0.4)のデータベースで試してみてください。

 

1. デフォルト監査機能とDBA監査が有効化されているか確認して下さい。

$ sqlplus / as sysdba
SQL> -- 初期化パラメータの設定確認
show parameter AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/orcl/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB


SQL> -- 監査ログのDBA監査の有効化
alter system set AUDIT_SYS_OPERATIONS=TRUE scope=SPFILE sid='*' ;
shutdown immediate
startup


SQL> -- 初期化パラメータの設定確認
show parameter AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/orcl/adump
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_trail                          string      DB


SQL> -- システム全体にわたるユーザー別の現在の文監査(USER_NAMEがNULLは全ユーザー対象)
select * from DBA_STMT_AUDIT_OPTS ;

USER_NAME PROXY_NAME AUDIT_OPTION                SUCCESS    FAILURE
--------- ---------- --------------------------- ---------- ----------
                     ALTER SYSTEM                BY ACCESS  BY ACCESS
                     SYSTEM AUDIT                BY ACCESS  BY ACCESS
                     CREATE USER                 BY ACCESS  BY ACCESS
                     ALTER USER                  BY ACCESS  BY ACCESS
                     DROP USER                   BY ACCESS  BY ACCESS
                     PUBLIC SYNONYM              BY ACCESS  BY ACCESS
                     DATABASE LINK               BY ACCESS  BY ACCESS
                     ROLE                        BY ACCESS  BY ACCESS
                     PROFILE                     BY ACCESS  BY ACCESS
                     DIRECTORY                   BY ACCESS  BY ACCESS
                     CREATE ANY TABLE            BY ACCESS  BY ACCESS
                     ALTER ANY TABLE             BY ACCESS  BY ACCESS
                     DROP ANY TABLE              BY ACCESS  BY ACCESS
                     CREATE PUBLIC DATABASE LINK BY ACCESS  BY ACCESS
                     GRANT ANY ROLE              BY ACCESS  BY ACCESS
                     SYSTEM GRANT                BY ACCESS  BY ACCESS
                     ALTER DATABASE              BY ACCESS  BY ACCESS
                     CREATE ANY PROCEDURE        BY ACCESS  BY ACCESS
                     ALTER ANY PROCEDURE         BY ACCESS  BY ACCESS
                     DROP ANY PROCEDURE          BY ACCESS  BY ACCESS
                     ALTER PROFILE               BY ACCESS  BY ACCESS
                     DROP PROFILE                BY ACCESS  BY ACCESS
                     GRANT ANY PRIVILEGE         BY ACCESS  BY ACCESS
                     CREATE ANY LIBRARY          BY ACCESS  BY ACCESS
                     EXEMPT ACCESS POLICY        BY ACCESS  BY ACCESS
                     GRANT ANY OBJECT PRIVILEGE  BY ACCESS  BY ACCESS
                     CREATE ANY JOB              BY ACCESS  BY ACCESS
                     CREATE EXTERNAL JOB         BY ACCESS  BY ACCESS


SQL> --システム全体にわたってユーザー別に監査されている現行の権限監査
select * from DBA_PRIV_AUDIT_OPTS ;

USER_NAME PROXY_NAME PRIVILEGE                   SUCCESS    FAILURE
--------- ---------- --------------------------- ---------- ----------
                     CREATE EXTERNAL JOB         BY ACCESS  BY ACCESS
                     CREATE ANY JOB              BY ACCESS  BY ACCESS
                     GRANT ANY OBJECT PRIVILEGE  BY ACCESS  BY ACCESS
                     EXEMPT ACCESS POLICY        BY ACCESS  BY ACCESS
                     CREATE ANY LIBRARY          BY ACCESS  BY ACCESS
                     GRANT ANY PRIVILEGE         BY ACCESS  BY ACCESS
                     DROP PROFILE                BY ACCESS  BY ACCESS
                     ALTER PROFILE               BY ACCESS  BY ACCESS
                     DROP ANY PROCEDURE          BY ACCESS  BY ACCESS
                     ALTER ANY PROCEDURE         BY ACCESS  BY ACCESS
                     CREATE ANY PROCEDURE        BY ACCESS  BY ACCESS
                     ALTER DATABASE              BY ACCESS  BY ACCESS
                     GRANT ANY ROLE              BY ACCESS  BY ACCESS
                     CREATE PUBLIC DATABASE LINK BY ACCESS  BY ACCESS
                     DROP ANY TABLE              BY ACCESS  BY ACCESS
                     ALTER ANY TABLE             BY ACCESS  BY ACCESS
                     CREATE ANY TABLE            BY ACCESS  BY ACCESS
                     DROP USER                   BY ACCESS  BY ACCESS
                     ALTER USER                  BY ACCESS  BY ACCESS
                     CREATE USER                 BY ACCESS  BY ACCESS
                     AUDIT SYSTEM                BY ACCESS  BY ACCESS
                     ALTER SYSTEM                BY ACCESS  BY ACCESS


SQL> -- (おまけ)パスワード管理ポリシーの確認
select PROFILE, RESOURCE_NAME, RESOURCE_TYPE, LIMIT
from DBA_PROFILES
 where RESOURCE_TYPE='PASSWORD'
order by RESOURCE_NAME ;

PROFILE RESOURCE_NAME             RESOURCE LIMIT
------- ------------------------- -------- ---------
DEFAULT FAILED_LOGIN_ATTEMPTS     PASSWORD 10
DEFAULT PASSWORD_GRACE_TIME       PASSWORD 7
DEFAULT PASSWORD_LIFE_TIME        PASSWORD 180
DEFAULT PASSWORD_LOCK_TIME        PASSWORD 1
DEFAULT PASSWORD_REUSE_MAX        PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME       PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION  PASSWORD NULL



冒頭の繰り返しとなりますが、Database Configuration Assistant(DBCA)を使用して新しいデータベースを作成した場合は、デフォルトで監査設定が有効化(AUDIT_TRAIL=DB)されてお り、パスワード管理ポリシーやDBA_STMT_AUDIT_OPTSビューやDBA_PRIV_AUDIT_OPTSビューで確認できる文 監査や権限監査が追加されているとご認識頂けると思います。これらの監査設定は、後述するsecconf.sqlを明示的に実行することで、CREATE DATABASE文を使用して手動でデータベースを作成した後にも実装することが可能です。ただし、初期化パラメータAUDIT_TRAIL の設定に関しては、secconf.sqlを実行しても変更されません。ちなみに、オブジェクト監査についてはDBA_OBJ_AUDIT_OPTSビューで確認可能です が、このsecconf.sqlを実行しても何も登録されていません。

ここでは初期化パラメータを3つご紹介しておきましょう。

・audit_trail : データベースの監査の有効化(NONE以外)、無効化(NONE)を制御する初期化パラメータ
   ・NONE : 標準監査の使用禁止(デフォルト)
   ・OS : 全ての監査レコードをオペレーティング・システム・ファイルへ書き込む
   ・DB : OSファイルへ常に書き込まれる監査レコードを除いてSYS.AUD$へ書き込む
   ・DB,EXTENDED : DBの設定に追加して、SQL文やバインド変数情報が付随される
   ・XML : XML形式でオペレーティング・システム・ファイルへ書き込む
   ・XML,EXTENDED : XMLの設定に追加して、SQL文やバインド変数情報が付随される

・audit_file_dest : AUDIT_TRAIL=OS、XML、XML,EXTENDEDに設定されている場合の監査ファイルの出力先ディレクトリを指定する初期化パラメータ。デフォルトは$ORACLE_BASE/admin/$ORACLE_SID/adumpであり、ASM Diskgroupは指定不可

・audit_sys_operations : SYSDBA権限、SYSOPER権限で接続しているユーザーのトップレベルの操作の監査を有効化(TRUE)と無効化(FALSE)を制御する初期化パラメータ。デフォルトはFALSE

 

この演習1では、AUDIT_SYS_OPERARIONS=TRUEに設定してDBA監査が有効化しており、AUDIT_TRAIL=DBの設定により、監査ログがSYS.AUD$へ書き込まれる構成であることが理解して頂けると思います。まずは、この構成でどのような監査ログが記録されるのかを以降の演習で確認していくことにしましょう。

 

補足として、デフォルト監査機能を有効化(パスワード管理ポリシーの設定を含む)及び無効化する為には、次のSQLを実行します。SEC_CASE_SENSITIVEは、パスワード・ファイル認証以外のユーザーのパスワードの大文字と小文字を区別するか(デフォルト:TRUE)、区別しないか(FALSE)を設定する初期化パラメータなので、パスワード管理ポリシーと共に覚えておきましょう。

ちなみに、パスワード・ファイル認証においてパスワードの大文字と小文字を区別したくない場合は、orapwdコマンドでIGNORECASE=Yオプションを指定してパスワード・ファイルを再作成する必要があります。IGNORECASEオプションを指定しない場合は、IGNORECASE=N(パスワード・ファイル認証で大文字と小文字を区別する)として扱われますのでご注意下さいね。

$ sqlplus / as sysdba
SQL> -- 有効化
-- デフォルト監査機能の有効化(パスワード管理ポリシーの設定を含む)
alter system set AUDIT_TRAIL=DB scope=SPFILE sid='*' ; --NONE以外に設定(動的変更不可)
alter system reset SEC_CASE_SENSITIVE_LOGON scope=SPFILE sid='*' ; --動的変更可
shutdown immediate
startup
@?/rdbms/admin/secconf.sql


SQL> -- 無効化
-- デフォルト監査機能の無効化(パスワード管理ポリシーの設定を含まない)
@?/rdbms/admin/undoaud.sql

--パスワード管理ポリシーの設定の無効化と確認
@?/rdbms/admin/undopwd.sql
alter system set SEC_CASE_SENSITIVE_LOGON=FALSE scope=BOTH sid='*' ;

select PROFILE, RESOURCE_NAME, RESOURCE_TYPE, LIMIT
from DBA_PROFILES
 where RESOURCE_TYPE = 'PASSWORD'
order by RESOURCE_NAME ;

PROFILE RESOURCE_NAME                    RESOURCE LIMIT
------- -------------------------------- -------- ---------
DEFAULT FAILED_LOGIN_ATTEMPTS            PASSWORD 10
DEFAULT PASSWORD_GRACE_TIME              PASSWORD UNLIMITED
DEFAULT PASSWORD_LIFE_TIME               PASSWORD UNLIMITED
DEFAULT PASSWORD_LOCK_TIME               PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
 

2. 次のSQLを実行し、TRYスキーマ上に約100MBの監査用の表TAB32およびCOL1列に対する主キーを作成して下さい。

$ sqlplus / as sysdba
SQL> 
-- TRYユーザーと表領域TBS32の削除
drop user TRY cascade ;
drop tablespace TBS32 ;

-- TBS32表領域と、この表領域をデフォルト表領域とするTRYユーザーの作成
create tablespace TBS32 datafile '+DATA(DATAFILE)' size 200m ;
create user TRY identified by TRY default tablespace TBS32 ;
grant CONNECT, RESOURCE, DBA to TRY ;

-- TAB32表の作成
connect TRY/TRY
create table TAB32 (COL1 number not null, COL2 date, COL3 number, COL4 char(1000)) ;

-- 約100MBのレコードを挿入
insert /*+append */ into TAB32
select LEVEL, SYSDATE, mod(LEVEL,10), 'initial'
from DUAL
connect by LEVEL <= 7 * 128 * 100 ;
commit ;

-- COL1列にユニーク索引を作成し、主キーとして登録
create unique index PK_TAB32 on TAB32(COL1) ;
alter table TAB32 add primary key(COL1) using index PK_TAB32 ;

-- TAB32表と主キーのセグメント・サイズを確認
col SEGMENT_NAME for a16
select SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024 from USER_SEGMENTS ;

SEGMENT_NAME     SEGMENT_TYPE       BYTES/1024/1024
---------------- ------------------ ---------------
PK_TAB32         INDEX                            2
TAB32            TABLE                          104

はい、これから監査ログを記録、確認していく上で必要なオブジェクトを作成して頂きました。いつもの準備運動ですから問題無いですよね。

とは言え、ここで気付いて頂きたいのは、CREATE TABLESPACE文で表領域を作成してCREATE USER文でユーザーを作成したオペレーションは監査ログに記録されているのかどうかが気になりませんか?気になりますよね。と言う事で、次の演習3で確認してみましょう。

 

3. 演習2で実行したオペレーションが監査ログにどのように記録されているかを確認して下さい。

$ sqlplus / as sysdba
SQL> 
set linesize 150 pages 5000
col EXTENDED_TIMESTAMP for a20
col USERNAME for a8
col USERHOST for a20
col TERMINAL for a8
col OBJ_NAME for a16
col ACTION_NAME for a16
alter session set NLS_TIMESTAMP_TZ_FORMAT='YYYY/MM/DD HH24:MI:SS' ;
select EXTENDED_TIMESTAMP, USERNAME, USERHOST, OBJ_NAME, ACTION_NAME
  from DBA_AUDIT_TRAIL
 order by 1 ;

EXTENDED_TIMESTAMP   USERNAME USERHOST             OBJ_NAME         ACTION_NAME
-------------------- -------- -------------------- ---------------- ----------------
2014/08/03 15:44:58  TRY      vm11204.localdomain                   LOGON
2014/08/03 15:46:55  TRY      vm11204.localdomain                   LOGOFF

AUDIT_TRAIL=DBに設定してある環境ですから、監査レコード(監査ログ)はSYS.AUD$に記録されているはずです。しかし、マニュアル「Oracle Database リファレンス」を検索して頂いてもAUD$の説明は見つからないのです。SYS.AUD$の内容を参照するには、もちろん直接アクセスも出来ますが、DBA_AUDIT_TRAILビューを使用することをお勧めします。特に理由は無いですけどね。。。

DBA_AUDIT_TRAILビューには数多くの列が存在しますが、上記回答例では必要最低限の列(日時、ユーザー名、クライアント・ホスト名、アクセスしたオブジェクト名、アクション)のみの表示とさせて頂いておりますが、いかがでしょうか?AUDIT_SYS_OPERATIONS=TRUEに設定しているにも関わらず、期待していたSYSユーザーによるオペレーション(表領域作成やユーザー作成)が出力されていませんね。ここでは、えー!監査できていないじゃん。と無理やりにでも思って下さい(笑)。その印象が強烈なほど、後の説明がより鮮明に記憶されますからね。

さて、準備は良いですか?では、私なりの理解を説明させて頂きます。

演習1の解説部分において、AUDIT_TRAIL初期化パラメータの各設定値について簡単に説明をさせて頂きましたが、設定値"DB"の解説で少々引っかかりを覚えた方もいらっしゃったかもしれませんね。私はマニュアルの記述をベースとして次のように記述しました。「AUDIT_TRAIL=DB : OSファイルへ常に書き込まれる監査レコードを除いてSYS.AUD$へ書き込む」と。この記述とこの演習でDBA_AUD_TRAILビューにSYSのオペレーションが表示されなかった現象を考えてみると・・・そうです。SYSのオペレーションはOSファイルに書き出されているのではないか?と気付いた方、鋭いですね!では、監査ログの出力先を制御するAUDIT_FILE_DEST初期化パラメータに設定されているディレクトリ内を確認してみましょう。

$ pwd
/u01/app/oracle/admin/orcl/adump

$ view orcl_ora_4927_20140803154408677909143795.aud

(省略)
...

Sun Aug  3 15:44:57 2014 +09:00
LENGTH : '215'
ACTION :[61] 'create tablespace TBS32 datafile '+DATA(DATAFILE)' size 200m '
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '1353634468'

Sun Aug  3 15:44:57 2014 +09:00
LENGTH : '211'
ACTION :[57] 'create user TRY identified by * default tablespace TBS32 '
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '1353634468'

Sun Aug  3 15:44:57 2014 +09:00
LENGTH : '190'
ACTION :[36] 'grant CONNECT, RESOURCE, DBA to TRY '
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
...

はい、見事に、SYS(上記のファイル出力では、DATABASE USER: '/')で実行した表領域の作成やユーザーの作成、さらにはユーザーへの権限付与のSQL文が記録されていることが確認出来ました。なるほどなるほど。しかしこれだと管理面で苦労しそうな感じがしますよね。DBA_AUDIT_TRAILビューとOSファイルの両方を監視する必要がありますし、OSファイルから不正アクセスの疑いがあるオペレーションを抽出するのはSELECT文が使用できません。このような課題は、出力形式をXMLに変更することで解決することが出来ますよ。と言う事実確認を次の演習4で体験して頂きたいと思います。

 

4. XML形式でオペレーティング・システム・ファイルへ監査レコードを書き込む設定に変更して下さい。ただし、監査証跡にはSQL文およびSQLバインド変数の情報を含める設定とします。また、一つのXMLファイル出力監査ログの上限サイズを1MB、有効日数を1日間に設定して下さい。

$ sqlplus / as sysdba
SQL> -- AUDIT_TRAIL初期化パラメータの変更
alter system set AUDIT_TRAIL=XML,EXTENDED scope=SPFILE sid='*' ;
shutdown immediate 
startup


-- 初期化パラメータの設定確認
show parameter AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/orcl/adump
audit_sys_operations                 boolean     TURE
audit_syslog_level                   string
audit_trail                          string      XML, EXTENDED


-- XMLファイル出力監査ログの上限サイズ指定(このサイズを超えると新たなファイルへ切り替わる)
BEGIN
  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
   AUDIT_TRAIL_TYPE            =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
   AUDIT_TRAIL_PROPERTY        =>  DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
   AUDIT_TRAIL_PROPERTY_VALUE  =>  102400);
END;
/

-- XMLファイル出力監査ログの有効日数指定(この日数を越えると新たなファイルへ切り替わる)
BEGIN
  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
   AUDIT_TRAIL_TYPE            =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
   AUDIT_TRAIL_PROPERTY        =>  DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
   AUDIT_TRAIL_PROPERTY_VALUE  =>  1 );
END;
/


-- XMLファイル出力監査ログの設定確認
set linesize 150 pages 50000
col PARAMETER_NAME for a30
col PARAMETER_VALUE for a16
col AUDIT_TRAIL for a30
select * from DBA_AUDIT_MGMT_CONFIG_PARAMS
where AUDIT_TRAIL='XML AUDIT TRAIL';

PARAMETER_NAME                 PARAMETER_VALUE  AUDIT_TRAIL
------------------------------ ---------------- ------------------------------
AUDIT FILE MAX SIZE            102400           XML AUDIT TRAIL
AUDIT FILE MAX AGE             1                XML AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000             XML AUDIT TRAIL

演習1の解説部で既にご紹介させて頂いたように、XML形式でOSファイルへ監査レコードを出力する設定は初期化パラメータAUDIT_TRAIL=XML もしくは、XML,EXTENDEDのどちらかに設定することで可能となります。そして、演習問題ではSQL文、およびSQLバインド変数の情報も含めるように指示されていますので、XML,EXTENDEDが最適な設定値となりますね。ここまでは、それほど難しくないと思います。

とは言え、監査ログを出力させるのであればこれで充分かもしれませんが、本番での運用を想定した場合に重要になってくるのは、そのXML形式のOSファイル自体の管理方法になります。一つのセッションに対して一つのXMLファイルが生成されることになりますが、もし、その一つのセッションにおいて非常に沢山の監査対象のSQL文を実行すると想定すると、対象の一つのXMLファイルが非常に大きなサイズとなってしまう可能性があります。また、そのセッションが数日間接続を継続するような特性を持っている場合、対象のXMLファイルは書込みが継続され続けることになるので削除対象(後述)にすることは出来ません。よって、一つのXMLファイルのサイズの上限、有効日数をDBMS_AUDIT_MGMTパッケージSET_AUDIT_TRAIL_PROPERTYプロシージャで設定することが出来る事を覚えておいて下さいね。プロシージャ名を覚えて欲しいとは言っていません。こういう設定が出来るという事を覚えておいてくださいと言う事です。ちなみに、CLEAR_AUDIT_TRAIL_PROPERTYプロシージャを使用すれば、設定を削除することが可能です。

 

5. TRYユーザーと表領域TBS32を削除後、演習2を再実行して下さい。その後、XMLファイルに記録された監査ログをDBA_COMMON_AUDIT_TRAILビューから参照して下さい。

$ sqlplus / as sysdba
SQL> 
-- TRYユーザーと表領域TBS32の削除
drop user TRY cascade ;
drop tablespace TBS32 ;

-- TBS32表領域と、この表領域をデフォルト表領域とするTRYユーザーの作成
create tablespace TBS32 datafile '+DATA(DATAFILE)' size 200m ;
create user TRY identified by TRY default tablespace TBS32 ;
grant CONNECT, RESOURCE, DBA to TRY ;

-- TAB32表の作成
connect TRY/TRY
create table TAB32 (COL1 number not null, COL2 date, COL3 number, COL4 char(1000)) ;

-- 約100MBのレコードを挿入
insert /*+append */ into TAB32
select LEVEL, SYSDATE, mod(LEVEL,10), 'initial'
from DUAL
connect by LEVEL <= 7 * 128 * 100 ;
commit ;

-- COL1列にユニーク索引を作成し、主キーとして登録
create unique index PK_TAB32 on TAB32(COL1) ;
alter table TAB32 add primary key(COL1) using index PK_TAB32 ;

まずは、演習2を再実行して頂きました。演習3においては、このオペレーションの監査レコードがOSファイルに書き出されてしまっていましたよね。さあ、演習3の解説部の最後で述べた課題が、AUDIT_TRAIL=XML,EXTENDEDに設定することで解決したのでしょうか?

勢いあまってDBA_AUDIT_TRAILビューを検索して頂いた方、ありがとうございます。演習3と同じように、SYSのオペレーションは出力されなかった事かと思います。DBA_AUDIT_TRAILビューはSYS.AUD$に格納されている監査レコードを参照するビューです。そして、AUDIT_TRAIL=XML,EXTENDEDを設定した場合、監査レコードの書込み先はSYS.AUD$では無く、あくまでXML形式のOSファイルをAUDIT_FILE_DEST初期化パラメータに指定したOSのディレクトリ上に出力するだけですからね。どうも私の文章は自分でも分かりづらい感が否めないので混沌としてきてしまったかもしれませんが、シンプルに言います。

DBA_COMMON_AUDIT_TRAILビューへアクセスすることで、AUDIT_FILE_DEST初期化パラメータに指定したOSのディレクトリ上に保存されているXML形式のOSファイルの中身を参照することが出来るのです。つまりは、外部表と言う事ですね。さあ、ものは試しで確認してみましょう。

$ sqlplus / as sysdba
SQL> -- DBA_COMMON_AUDIT_TRAILビューを使用した監査ログの確認
set linesize 150 pages 5000
col EXTENDED_TIMESTAMP for a20
col DB_USER for a8
col SQL_TEXT for a70
alter session set NLS_TIMESTAMP_TZ_FORMAT='YYYY/MM/DD HH24:MI:SS' ;
select EXTENDED_TIMESTAMP, DB_USER, SQL_TEXT
  from DBA_COMMON_AUDIT_TRAIL
 order by 1 ;

EXTENDED_TIMESTAMP   DB_USER  SQL_TEXT
-------------------- -------- ------------------------------------------------------------
2014/08/03 15:48:55  /        drop user TRY cascade
2014/08/03 15:48:56  /        drop tablespace TBS32
2014/08/03 15:49:01  /        create tablespace TBS32 datafile '+DATA(DATAFILE)' size 200m
2014/08/03 15:49:01  /        create user TRY identified by * default tablespace TBS32
2014/08/03 15:49:01  /        grant CONNECT, RESOURCE, DBA to TRY

YES! YES! YES!  これで、デフォルト監査、標準監査、DBA監査の監査ログが一元管理され、かつ、SELECT文で確認することが出来るようになりますので、運用も楽になると思います。は言え、標準監査の設定はここからの本番ですので、もう少し頑張ってついて来て下さいね。

とは言え・・・(繰り返し使わせて頂きますが)、正直ここまでの演習はデータベースの標準監査を使用する上での準備に位置づけられることかもしれませんが、実は意外にも難しいです。設定すべき初期化パラメータが複数存在し、特にAUDIT_TRAIL初期化パラメータに設定する値の種類が多い。何を設定すれば、何がどこに出力されるのか、どのビューで監査ログを確認出来るのか、そういった部分は、いくらマニュアルをじっくり読んでみてもクリアに理解することは厳しいです。マニュアルに記載が無い動作もあるので、やはり実機で確認することが、正確に機能を理解する上では大切なことを改めて考えさせられますね。

はい、少々脱線しましたが、次の演習からは標準監査の設定例を体験していきましょう。ここまで理解できている方であれば、簡単に使いこなすことが出来ると思います!

 

6. TRYスキーマの表TAB32に対して、SELECT文を実行した際に監査ログが記録されるオブジェクト監査の設定を実装して下さい。

$ sqlplus /nolog
SQL> -- オブジェクト監査を実装前の動作確認
connect TRY/TRY
set linesize 200 pages 0
select * from TAB32 ;

connect / as sysdba
set linesize 150 pages 5000
col EXTENDED_TIMESTAMP for a20
col DB_USER for a8
col STATEMENT_TYPE for a16
col SQL_TEXT for a50
alter session set NLS_TIMESTAMP_TZ_FORMAT='YYYY/MM/DD HH24:MI:SS' ;
select EXTENDED_TIMESTAMP, DB_USER, STATEMENT_TYPE, SCN, SQL_TEXT
  from DBA_COMMON_AUDIT_TRAIL
 where DB_USER = 'TRY'
 order by 1 ;

EXTENDED_TIMESTAMP   DB_USER  STATEMENT_TYPE          SCN SQL_TEXT
-------------------- -------- ---------------- ---------- ---------------------------
2014/08/03 16:04:13  TRY      LOGON                     0
2014/08/03 16:04:13  TRY      LOGOFF                    0


SQL>
-- オブジェクト監査(TRY.TAB32表へのSELECT文で監査ログを取得)の設定
connect / as sysdba
audit select on TRY.TAB32 by access ;

-- オブジェクト監査の設定がされたかの確認
select * from DBA_OBJ_AUDIT_OPTS ;

OWNER OBJECT_NAME OBJECT_TYPE ALT  AUD  COM  DEL  GRA  IND  INS  LOC  REN  SEL
----- ----------- ----------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
UPD        REF EXE       CRE       REA       WRI      
---- --------- --------- --------- --------- ---------
TRY   TAB100      TABLE       -/-  -/-  -/-  -/-  -/-  -/-  -/-  -/-  -/-  A/A
-/-        -/- -/-       -/-       -/-       -/-      


SQL> -- オブジェクト監査を実装後の動作確認
connect TRY/TRY
set linesize 200 pages 0
select * from TAB32 ;

connect / as sysdba
set linesize 150 pages 5000
col EXTENDED_TIMESTAMP for a20
col DB_USER for a8
col STATEMENT_TYPE for a16
col SQL_TEXT for a50
alter session set NLS_TIMESTAMP_TZ_FORMAT='YYYY/MM/DD HH24:MI:SS' ;
select EXTENDED_TIMESTAMP, DB_USER, STATEMENT_TYPE, SCN, SQL_TEXT
  from DBA_COMMON_AUDIT_TRAIL
where DB_USER = 'TRY'
 order by 1 ;

EXTENDED_TIMESTAMP   DB_USER  STATEMENT_TYPE          SCN SQL_TEXT
-------------------- -------- ---------------- ---------- ---------------------------
2014/08/03 16:04:13  TRY      LOGON                     0
2014/08/03 16:04:13  TRY      LOGOFF                    0
2014/08/03 16:08:24  TRY      LOGON                     0
2014/08/03 16:08:24  TRY      SELECT              1547640 select * from TAB32
2014/08/03 16:08:24  TRY      LOGOFF                    0

この演習のシナリオとしては、外部に絶対に流出してはならない重要機密データが格納されている表がTRYスキーマのTAB32表であるシチュエーションです。なので、この表へSELECT文を実行した全てのアクセス・ログを監査レコードとして記録しておきたいという要件を想定しています。

とは言え、それほど難しくなかったと思います。TRYスキーマのTAB32表に対してSELECT文を実行した場合に監査ログが記録されるように設定するには、「AUDIT SELECT on TRY.TAB32 BY ACCESS ;」を事前に実行しておくだけです。このオブジェクト監査の設定が正常に反映されているか否かは、DBA_OBJ_AUDIT_OPTSビューで簡単に確認出来ましたよね。そして、オブジェクト監査の設定後に実行したTAB32表へ対するSELECT文が見事に監査ログとして、DBA_COMMON_AUDIT_TRAILビューで確認することが出来ています。これが、正にOracle Databaseの標準監査となります。ちなみに、オブジェクト監査を取りやめる場合にはNOAUDIT文を実行して下さい。

今回は、さらにもう一歩踏み込んでみましょう。ここまでお客様に説明した後にほぼ必ず言われることは、「アクセスされたデータ、もしくはデータ件数を正確に把握したい」と言う事です。通常の表データはその瞬間で最新の状態しか保存されていません。これは何を意味するのかと言うと、不正アクセスが疑われるSELECT文が一ヶ月前に実行されていたことが分かった場合、今時点でそのSELECT文を実行してみても、一ヶ月前の時点からINSERTでデータが追加されているでしょうし、UPDATEで更新されている、さらにはDELETEで削除されてしまっているかもしれません。つまり、一般的には過去一ヶ月前のSELECT文でアクセスされたデータを正確に把握することが出来ないのです。しかし、Oracle Databaseはこの要望に確実にお応えすることが可能です。

まずは上記回答例のSELECT文の監査レコードの項目をじっくり観察してみて下さい。SCN列が監査ログに含まれていますよね。これを上手く使えば良いだけです。そう、思いつきましたよね。次の演習にチャレンジしてみてください。

 

7. Flashback Queryを使用して、演習6で記録された監査ログのSELECT文でアクセスされたデータ件数を特定して下さい。

$ sqlplus TRY/TRY
SQL>
-- 演習目的の為に、1レコードをINSERT後に件数確認
insert into TAB32 values (0, SYSDATE, 0, 'hoge') ;
commit ;
select count(*) from TAB32 ;

  COUNT(*)
----------
     89601

-- 演習6で記録された監査ログのSELECT文でアクセスしたデータ件数を確認
select count(*) from (select * from TAB32 as of scn 1547640) ;

  COUNT(*)
----------
     89600

そうです。監査ログにはSQL文とそのSQL文を実行する際に使用したバインド変数のデータだけではなく、実行時のSCNが含まれていますので、これを指定したFlashback Queryを使用することで、不正の疑いがあるSELECT文がアクセスしたデータを正確に再現することが出来ますよね。

Flashback Queryの効果が分かりやすいように、直前に1レコード追加することでTAB32表は89,601レコードが格納されている状態ですが、演習6で監査ログに記録されたSELECT文を実行した際(SCN=1547640時点)では、89,600レコードであったことが確認できます。これまた本当に素晴らしい機能です。

このような形で、もし不正アクセスが疑われるSELECT文が実行されていたら、Flashback Queryでアクセスされたデータ件数やデータ自体を抽出することが可能です。通常、Flashback QueryはUNDOレコードが保持されている期間内(UNDO_RETENTION初期化パラメータで制御。数十分~長くても1日程度。UNDOの詳細については、第28回 UNDO表領域の管理~保存期間の自動チューニング~を参照)でしか問合せができないことに注意が必要です。数週間前、数ヶ月前と言った範囲で過去データを検索したい場合は、Flashback Data Archiveの実装が必要となります。また、Active Data Guard環境が存在する場合はStandby Database側で検索処理をオフロードすることが可能ですので、例えば、監査ログをほぼリアルタイムで監視して不正アクセスの疑いがあるSQL文が発生した直後に、Standby Database側でアクセスされたデータ件数を集計しておくといった実装も効果的かと思います。

ちなみに、Flashback Data Archiveは表単位で設定することが可能で、更新履歴(更新されたデータと日時、もちろんSCN)が自動的に記録されることになります。誰が更新したのかまでは記録されていないので要件によっては不十分かもしれませんが、標準監査側ではSELECT文のみ監査し、Flashback Data Archive側では更新処理(DDL文)を監査すると言った使い分けも可能になります。

 

8. 現時点より5分前に生成されたXML監査ログを削除して下さい。

$ sqlplus / as sysdba
SQL> -- 現時点(SYSDATE)より5分前に生成されたXML監査ログの削除
BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
    last_archive_time => TO_TIMESTAMP(SYSDATE-5/24/60,'YYYY/MM/DD HH24:MI:SS')) ;
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
    audit_trail_type  => dbms_audit_mgmt.audit_trail_files) ;
END;
/


-- 演習6で確認した(5分以上前の)監査レコードが削除されているかの確認
set linesize 150 pages 5000
col EXTENDED_TIMESTAMP for a20
col DB_USER for a8
col STATEMENT_TYPE for a16
alter session set NLS_TIMESTAMP_TZ_FORMAT='YYYY/MM/DD HH24:MI:SS' ;
select EXTENDED_TIMESTAMP, AUDIT_TYPE, DB_USER, STATEMENT_TYPE
  from DBA_COMMON_AUDIT_TRAIL
where DB_USER = 'TRY' and AUDIT_TYPE like '%XML Audit'
 order by 1 ;

EXTENDED_TIMESTAMP   AUDIT_TYPE           DB_USER  STATEMENT_TYPE
-------------------- -------------------- -------- ----------------
2014/08/03 16:16:45  Standard XML Audit   TRY      LOGON
2014/08/03 16:22:25  Standard XML Audit   TRY      LOGOFF

データベースの運用を経験された方であれば、この演習の重要性は百も承知だと思います。監査ログを取ることだけに集中し過ぎてしまい、取得された監査ログを何もしないで放置していたらどうなるでしょうか?そうですよね。きっと、OSのファイルシステム領域を全て食いつぶしてしまい、OSがハングするような事態が想定されますので、別ディレクトリへの退避や不要となった過去の監査ログの削除の運用を必ず実装して下さいね。

上記の解答例では、DBMS_AUDIT_MGMTパッケージSET_LAST_ARCHIVE_TIMESTAMPプロシージャをコールして、現時点よりも5分前に生成された(書込みが終了した)XML形式の監査ログ・ファイルを削除対象に設定し、実際の削除処理はCLEAN_AUDIT_TRAILプロシージャをコールしています。また、回答例には含まれていませんが、DBMS_AUDIT_MGMTパッケージCREATE_PURGE_JOBプロシージャ等を使用すれば、CLEAN_AUDIT_TRAILプロシージャをコールする間隔を指定して自動実行されるジョブを作成することも可能ですので、興味のある方は試してみると良いと思います。

演習の都合上、非常に短い数字であることはご了承ください。本運用ではシステム要件に合わせて数日、数週間、数ヶ月単位で保持するべきだと思いますが、保持期間が長ければ長いほど必要とされるファイルシステムの容量が大きくなりますのでご注意ください。また、監査ログを統合的に集約し、監視、レポーティングする環境が必要となった場合は、Oracle Audit Vaultの採用を検討してみてくださいね。

 

9. ログイン・トリガーを作成し、特定セッションに限定して全てのSQL文を監査するよう実装して下さい。
ただし、この演習における特定のセッションとは、次の2つの条件のどちらかを満たすセッションとします。
条件(1) ホスト名:APP_SERVER、IPアドレス:APP_IP_ADDRESSからのTRYユーザーでの接続以外
条件(2) sqlplusを使用した接続

$ sqlplus / as sysdba
SQL> 
create or replace trigger ENABLE_STANDARD_AUDITING
  after logon on database
  declare
    strUserName   VARCHAR2(32) ;
    strHostName   VARCHAR2(64) ;
    strIP_Address VARCHAR2(32) ;
    strModule     VARCHAR2(64) ;
    strQuery      VARCHAR2(64) ;
    intCursor     INTEGER ;
    intResult     INTEGER ;
  begin
    strUserName   := upper(sys_context('USERENV','CURRENT_USER')) ;
    strHostName   := upper(sys_context('USERENV','HOST')) ;
    strIP_Address := upper(sys_context('USERENV','IP_ADDRESS')) ;
    strModule     := upper(sys_context('USERENV','MODULE')) ;
    if   ((strUserName = 'TRY')             and ((strHostName NOT like 'APPS_SERVERS')                   or (strIP_Address != 'APPS_IP_ADDRESS')))
         or ((strModule like 'SQLPLUS%') or (strModule = 'SQL*PLUS')) then
      strQuery  := 'audit all statements in session current' ;
      intCursor := dbms_sql.open_cursor ;
      dbms_sql.parse(intCursor, strQuery, dbms_sql.native) ;
      intResult := dbms_sql.execute(intCursor) ;
    end if ;
  end;
/

さて、最後の演習です。演習6で体験して頂いたオブジェクト監査の設定では、TRY.TAB32表をSELECTする全てのユーザーが対象となっていましたが、この演習9のログイン・トリガーを活用した監査設定であれば、特定セッションに限定して全てのSQL文を監査ログとして記録可能になります。という素晴らしい実装方法です。と個人的には思っています。

文監査、権限監査、オブジェクト監査は、それぞれAUDIT文を実行して事前に有効化させるのが一般的ですが、実はセッション単位で監査を有効化させる方法があるのです。それがAUDIT文の「IN SESSION CURRENT」オプションです。これはマニュアル「Oracle Databaseセキュリティ・ガイド」に一文だけ記載されているので見逃してしまう可能性が高いですが、監査ログの出力を必要最低限に抑え込むには非常に有効な監査設定の仕方だと私は思っています。このAUDIT文を特定のセッションで自動実行させる仕組みが、ログオン・トリガーになります。ご存知の通り、トリガーはイベント・ドリブン型で動作します。セッションを作成直後に自動実行されるトリガーを一般的にログオン・トリガーと読んでいて、セッション情報(どのサーバー(ホスト名、IPアドレス)のどのアプリから接続しに来たのか、ユーザー名は何か等)をSYS_CONTEXT関数で取得して、アクションを起こさせることが出来ます。つまり、今回のケースでは、IF文でセッションを選別することで条件に合致する怪しいセッションに限定して「AUDIT ALL STATEMENTS IN SESSION CURRENT ;」文を実行できることになり、このセッションがログアウトするまでの間に発行した全てのSQL文の監査ログを取得することが可能となります。

この演習9のシナリオとしては、あくまで参考レベルとして(本番運用に適用する際には、もう少しきめ細やかに厳しい条件設定を実装して下さい。ここで書き切ってしまえば、それはそれでセキュリティ・ホールに成り得てしまいますからね)、2つの条件のどちらかを満たすセッションに限定して監査ログを取得する例です。条件(1)はアプリケーション・サーバーからの通常接続されたセッションは監査しないと言う意味。条件(2)は、SQL*Plusを使用して接続してきたセッションは全て監査対象とすると言う意味です。

もちろん、このTRIGGERを無効化されてしまえば監査が無効化されることと同義ですから、ALTER TRIGGER権限の取り扱いが重要になってきます。とは言え、SYSDBA権限を持ったユーザーだったら何でも出来ちゃうしーとお悩みの方は、データベース管理の職務分掌を実現するOracle Database Vaultの採用をご検討下さいね。

 

 

さて、Oracle Databaseの標準監査の基本的な使い方について体験して頂きましたが、いかがでしたでしょうか?

「基本的な使い方」ではありますが、正直、難しい部分もあったのではないかと推察しております。XML形式での監査ログの出力方法と削除方法、特定の重要機密データが格納されている表に対するオブジェクト監査の設定方法、特定の怪しいセッションに限定した全SQL文の監査設定方法は、どのデータベースにおいても共通して利用可能なものだと思っていますので、是非とも皆様の実装にお役に立てれば幸いです。ただし、監査ログを取得するということは取得していなかった場合よりもデータベースのリソースを追加で消費することになる点をご留意ください。監査対象のオブジェクトやセッションが多い場合には、それなりのオーバーヘッドが生まれる可能性がありますので充分な性能テストの実施を心掛けて下さいね。

今回は、ASM編を一時中断してセキュリティ機能をご紹介させて頂くことになり、ASM編を楽しみにされていた方には大変申し訳ございませんでした。次回からはまたASM編へ戻りまして、リバランスの内部挙動、障害発生時の挙動等を公開可能な限りご紹介していく予定ですので、是非とも体験に参加して頂けると嬉しいです。今回も最後まで体験して頂きましてありがとうございました。次回以降もどうぞよろしくお願い致します。


ページトップへ戻る▲

 

しばちょう先生の試して納得!DBAへの道 indexページ▶▶

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.