TechBlog_AuditTrail

Size: px

Start display at page:

Download "TechBlog_AuditTrail"

かずひろおなか
5 years ago
Views:

1 Supercharge your analytics Zoom past competitors with a SQL in Hadoop Analytics Platform that s 0X faster and light years ahead. that form Marathon Oil s supply chain." "Using [Actian], we know that we can rapidly exchange efficient, accurate business documents with the critical partners

4 audit session; Audit succeeded.

5 select * from dba_stmt_audit_opts; USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE CREATE SESSION BY ACCESS BY ACCESS ^^^^^^^^^ ^^^^^^^^^^ 8 select os_username,username,terminal,to_char(timestamp,'hh:mi:ss') timestamp,action_name,to_char(logoff_time,'hh:mi:ss') logoff_time,returncode from dba_audit_session; OS_USERNAME USERNAME TERMINAL TIMESTAMP ACTION_NAME LOGOFF_TIME RETURNCODE kshinkub SAMPLE_A KSHINKUB :9: LOGOFF :9: 0 <-() ( 省略 ) kshinkub SAMPLE_Y KSHINKUB :0:0 LOGOFF :0: 0 oracle XPRT :0: LOGON 0 <-() kshinkub SAMPLE_Z KSHINKUB :0:9 LOGON 0 kshinkub SAMPLE_A KSHINKUB :0: LOGON 0 conn / as sysdba

6 truncate table aud$; audit session whenever not successful; ^^^^^^^^^^^^^^^^^^^^^^^ Audit succeeded. select * from dba_stmt_audit_opts; USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE CREATE SESSION NOT SET BY ACCESS ^^^^^^^ 8 select os_username,username,terminal,to_char(timestamp,'hh:mi:s s') timestamp,action_name,to_char(logoff_time,'hh:mi:ss') logoff_time,returncode from dba_audit_session; OS_USERNAME USERNAME TERMINAL TIMESTAMP ACTION_NAME LOGOFF_TIME RETURNCODE kshinkub SAMPLE_Z KSHINKUB :0:9 LOGON 0 kshinkub SAMPLE_A KSHINKUB :0: LOGON 0 noaudit session; ^^^^^^^ Noaudit succeeded. select * from dba_stmt_audit_opts; no rows selected

7 TIMESTAMP,LOGOFF_TIME : ログイン - ログオフの時間が長いセッションがいないか監視することが大事 LOGOFF_LREAD,LOGOFF_PREAD : セッション内での論理読込物理読込の量を監視し大量の読込であればそのオペレーションの妥当性を確認しなければならない RETURNCODE : 0 以外のものを監視する必要がある SESSION_CPU : セッション内でのCPU 使用時間を監視することによりループプロセスなどの発見に繋がる可能性がある select os_username osuname,username dbuname,sessionid sesid,(logoff_time-timestamp)**0*0 conn_time,logoff_lread lread,logoff_pread pread,action_name act_name,returncode retcd,session_cpu ses_cpu from dba_audit_session; OSUNAME DBUNAME SESID CONN_TIME LREAD PREAD ACT_NAME RETCD SES_CPU kshin SYSTEM 8 LOGOFF 0 8 <-() ^^^^ oracle ORABM LOGOFF 0 09

8 8 9 0 select vs.sql_text,du.username,vs.executions execs,vs.loads loads,vs.disk_reads d_reads,vs.buffer_gets buf_gets,vs.rows_processed row_proc,vs.cpu_time cpu_t from v$sql vs, dba_users du, dba_audit_session da where da.sessionid in (8,8) and vs.parsing_user_id = du.user_id and du.username = da.username and vs.last_load_time between to_char(da.timestamp,'yyyy-mm-dd/hh:mi:ss') and to_char(da.logoff_time,'yyyy-mm-dd/hh:mi:ss'); SQL_TEXT DBUNAME EXECS LOADS D_READS BUF_GETS ROW_PROC CPU_T declare a varchar ORABM SELECT * FROM DBA_ SYSTEM SELECT * FROM V$SE SYSTEM 0 ( 省略 ) 結局ユーザのアカウンティング ( 誰がいつどこからログインしたのかを監視 ) 以外にセッション内での操作履歴を見ようとするとセッション監査だけでは無理ということ!

9 () 監査無しの場合 TPS () 監査有りの場合 TPS 普通の OLTP 処理においてセッション監査を実行してもパフォーマンスには影響を与えないということ!

10 レスポンスへの影響. AUDIT 無し TPS :. AUDIT BY SESSION TPS : 08. AUDIT BY ACCESS TPS : 9 8 select os_username os_usrnm,terminal,owner '.' obj_name obj,ses_actions,count(*) cnt from dba_audit_object group by owner '.' obj_name, ses_actions order by obj,cnt

11 OS_USRNM TERMINAL OBJ SES_ACTIONS CNT ORACLE pts/0 ORABM.CUSTOMER SS----- <-() ORACLE pts/0 ORABM.CUSTOMER S ORACLE pts/0 ORABM.DISTRICT SS----- ORACLE pts/0 ORABM.HISTORY F <-() ORACLE pts/0 ORABM.NEW_ORDER ---S-----S ORACLE pts/0 ORABM.ORDER_LINE S ORACLE pts/0 ORABM.WAREHOUSE SS----- ORACLE pts/0 ORABM.WAREHOUSE S------

14 SELECT FUNC_SCHEMA,FUNC_PACKAGE,FUNC_PROC,CALLARG,OBJ_TYPE,USER_ARG FROM SYS.EXPACT$ WHERE SYS.EXPACT$.OWNER = :b AND SYS.EXPACT$.NAME = :b AND SYS.EXPACT$.CODE = :b ORDER BY SYS.EXPACT$.CALLORDER; audit all on sys.expact$ by session; 監査が成功しました $ exp userid=sample_a/sample_a file=sample.dmp エクスポートは警告なしで正常終了しました col os_username for a col export_user for a col terminal for a0 col export_time for a select os_username,username export_user,terminal,to_char(timestamp,'yyyy/mm/dd hh:mi:ss') export_time from dba_audit_object where obj_name = 'EXPACT$' ; OS_USERNAME EXPORT_USER TERMINAL EXPORT_TIME oracle SAMPLE_A pts/0 00/08/ :0: Administrator SAMPLE_A WINK 00/08/ 8:: connect system/****

15 insert into product_user_profile (product,userid,attribute,scope,numeric_value,char_value,date_value,long_value) values ('SQL*Plus','SAMPLE_A','SPOOL',null,null,'DISABLED',null,null); commit; spool test SP-0: コマンド : spool が無効です

16 列の指定ファイングレイン監査では指定した列に対するアクセスのみを監査することができます条件の指定ファイングレイン監査では SQL 文の条件句 (WHERE 句 ) の形式で監査条件を指定することができますまたファイングレイン監査では詳細な監査結果を記録することができます SYS.DBA_FGA_AUDIT_TRAIL ビュー監査結果は監査レコードとして SYS.DBA_FGA_AUDIT_TRAIL ビューに記録されますイベントハンドラ SYS.DBA_FGA_AUDIT_TRAIL ビューとは別に監査を行なった際にどのような記録を残すかをイベントハンドラとして自由に定義することができます

17 監査ポリシー ORABM.CUSTOMER 表に対して C_ID=0 を WHERE 句で指定した SELECT 文に対して監査レコードを生成するとともに詳細情報を監査ファイルに出力するイベントハンドラ監査ファイル aud_obj.aud に以下の内容を出力するファイル作成日付監査対象になった DB ユーザ名監査対象になった OS ユーザ名 SQL を発行したホスト名 SQL を発行したホストの IP アドレス実行した SQL FGA のポリシー名監査対象のスキーマ名監査対象のオブジェクト名初期化パラメータの utl_file_dir に適切な値を設定しておいてください analyze table customer compute statistics; 表が分析されました create or replace procedure aud_obj_proc( () schema_name varchar, table_name varchar, policy_name varchar) is file_id utl_file.file_type; sdate varchar(9) := to_char(sysdate,'yyyy/mm/dd hh:mi:ss'); aud_file_path varchar() := '/home/oracle/orahome/rdbms/audit'; file_name varchar(0) := 'audit_obj.aud'; begin file_id := utl_file.fopen(aud_file_path,file_name,'a'); utl_file.put_line(file_id,''); utl_file.put_line(file_id,sdate); utl_file.put_line(file_id,'action : SELECT'); utl_file.put_line(file_id,'db USER: ' SYS_CONTEXT('USERENV','SESSION_USER')); utl_file.put_line(file_id,'os USER: ' SYS_CONTEXT('USERENV','OS_USER')); utl_file.put_line(file_id,'host : ' SYS_CONTEXT('USERENV','HOST')); utl_file.put_line(file_id,'ipaddr : ' SYS_CONTEXT('USERENV','IP_ADDRESS')); utl_file.put_line(file_id,'sql_txt: ' SYS_CONTEXT('USERENV','CURRENT_SQL')); utl_file.put_line(file_id,'policy : ' policy_name); utl_file.put_line(file_id,'schema : ' schema_name); utl_file.put_line(file_id,'object : ' table_name); utl_file.fclose(file_id); end; /

18 8 9 execute dbms_fga.add_policy(- () object_schema => 'orabm',- -- 監査対象スキーマ指定 object_name => 'customer',- -- 監査対象オブジェクト指定 policy_name => 'aud_obj_policy',- -- 監査ポリシー指定 audit_condition => 'c_id=0',- -- 監査条件指定 audit_column => 'c_id',- -- 監査カラム指定 handler_schema => 'orabm',- --ハンドラプロシージャのスキーマ指定 handler_module => 'aud_obj_proc',- --ハンドラプロシージャ名指定 enable => true); -- 監査ポリシーを有効にする ().select c_id from customer where c_id = 0; ().select c_id from customer where c_id = ; ().select c_data from customer where c_id = 0; ().select c_data from customer where c_id = ; 8 9 select to_char(timestamp,'yyyy/mm/dd hh:mi:ss') timestamp,db_user,os_user,object_schema,object_name,policy_name,sql_text from dba_fga_audit_trail order by timestamp; TIMESTAMP DB_US OS_US OBJECT_SCH OBJECT_NAM POLICY_NAM SQL_TEXT /09/0 8:09: ORABM kshin ORABM CUSTOMER AUD_OBJ_PO select c_i kub LICY d from cus tomer wher e c_id = 0 00/09/0 8:0:0 ORABM kshin ORABM CUSTOMER AUD_OBJ_PO select c_d kub LICY ata from c ustomer wh ere c_id = 0

19 $ cat $ORACLE_HOME/rdbms/audit/aud_obj.aud 00/09/0 8:09: ACTION : SELECT DB USER: ORABM OS USER: kshinkub HOST : INSIGHTKSHINKUB IPADDR : xxx.xxx.xxx.xxx SQL_TXT: select c_id from customer where c_id = 0 POLICY : AUD_OBJ_POLICY SCHEMA : ORABM OBJECT : CUSTOMER 00/09/0 8:0:0 ACTION : SELECT DB USER: ORABM OS USER: kshinkub HOST : INSIGHTKSHINKUB IPADDR : xxx.xxx.xxx.xxx SQL_TXT: select c_data from customer where c_id = 0 POLICY : AUD_OBJ_POLICY SCHEMA : ORABM OBJECT : CUSTOMER

20 DB サーバ AP サーバクライアント Windows000 Oracle9..0 TOMCAT + + IE.0 JDBC 8 select os_username,username,terminal,to_char(timestamp,'hh:mi:ss') timestamp,action_name,to_char(logoff_time,'hh:mi:ss') logoff_time,returncode from dba_audit_session ;

21 OS_USERNAM USERNAME TERMINAL TIMESTAMP ACTION_NAM LOGOFF_TIME RETURNCODE SYSTEM ORABM unknown :: LOGOFF :: 0 SYSTEM ORABM unknown :: LOGOFF :: 0 SYSTEM ORABM unknown :: LOGOFF :: 0 SYSTEM ORABM unknown :: LOGOFF :: 0 SYSTEM ORABM unknown :: LOGOFF :: 0 SYSTEM ORABM unknown ::0 LOGOFF ::0 0 SYSTEM ORABM unknown ::0 LOGOFF ::0 0 SYSTEM ORABM unknown ::0 LOGOFF ::0 0 SYSTEM ORABM unknown ::0 LOGOFF ::0 0 SYSTEM ORABM unknown ::0 LOGOFF ::0 0 ^^^^^^ ^^^^^^^ 00 行が選択されました 8 select os_username,username,terminal,to_char(timestamp,'hh:mi:ss') timestamp,action_name,to_char(logoff_time,'hh:mi:ss') logoff_time,returncode from dba_audit_session ; OS_USERNAM USERNAME TERMINAL TIMESTAMP ACTION_NAM LOGOFF_TIME RETURNCODE SYSTEM ORABM unknown :: LOGOFF :: 0 SYSTEM ORABM unknown :: LOGOFF :: 0 SYSTEM ORABM unknown :: LOGOFF :: 0 SYSTEM ORABM unknown :: LOGOFF :: 0 SYSTEM ORABM unknown :: LOGOFF :: 0 SYSTEM ORABM unknown ::0 LOGOFF ::0 0 SYSTEM ORABM unknown ::0 LOGOFF ::0 0 SYSTEM ORABM unknown ::0 LOGOFF ::0 0 SYSTEM ORABM unknown ::0 LOGOFF ::0 0 SYSTEM ORABM unknown ::0 LOGOFF ::0 0 SYSTEM ORABM unknown ::0 LOGON 0<-() ^^^^^^ ^^^^^^^ ^^^^^ 0 行が選択されました

23 () WEB を使った社内システム () 社内の階級ごとに DB ユーザを作成例 )employee chief manager () 階級にはそれぞくロールが付与されセキュリティを確保する () AP から MANAGER.HYOUKA テーブルの参照は許可するが同テーブルに対して更新は許可しない

24 alter user employee grant connect through proxy with role role,role ; audit session by proxyon behalf of employee ; alter user employee grant connect through proxy ; audit session by proxy on behalf of any ; select * from dba_stmt_audit_opts; USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE EMPLOYEE PROXY CREATE SESSION BY ACCESS BY ACCESS <--(c) ^^^^^

25 8 select os_username,username,terminal,to_char(timestamp,'hh:mi:ss') timestamp,action_name,to_char(logoff_time,'hh:mi:ss') logoff_time,returncode from dba_audit_session ; OS_USERNAM USERNAME TERMINAL TIMESTAMP ACTION_NAM LOGOFF_TIME RETURNCODE SYSTEM EMPLOYEE KSHINKUB 09:0: LOGOFF 09:0: 0 SYSTEM EMPLOYEE KSHINKUB 0:0: LOGOFF 0:0: 0 SYSTEM EMPLOYEE KSHINKUB 0:: LOGOFF 0:: 0 <--(f) ^^^^^^^^ ^^^^^^^^. ロールを使用してユーザごとのセキュリティが確保できる.AP サーバから必要最小限の権限をもつユーザで接続できる.DB ユーザに対して正確なアカウンタビリティを確保できる.DB からロールの管理によるセキュリティに加え AP からの設定 ( 接続時に使用するロール ) により DB AP 両面からセキュリティ対策が行える

26 /* * ProxyConnSample * */ import java.sql.*; import javax.sql.*; import java.util.properties; import oracle.jdbc.*; import oracle.jdbc.pool.*; import oracle.jdbc.oci.*; class ProxyConnSample { public static void main (String args []) throws SQLException { DriverManager.registerDriver(new oracle.jdbc.oracledriver()); String url = "jdbc:oracle:oci8:@nas"; OracleOCIConnectionPool cp = new OracleOCIConnectionPool("proxy","proxy", url, null); Properties prop = new Properties(); String[] roles = {"role"}; prop.put(oracleociconnectionpool.proxy_user_name,"employee" ); prop.put(oracleociconnectionpool.proxy_roles, roles); // Get the proxy connection OracleOCIConnection conn = (OracleOCIConnection) cp.getproxyconnection(oracleociconnectionpool.proxytype_user_name, prop); } } Statement stmt = conn.createstatement (); ResultSet rs = stmt.executequery ("select * from manager.hyouka"); System.out.println ("-- select from the proxy connection --"); while (rs.next ()) System.out.println (rs.getstring ()); rs.close(); rs = null; System.out.println ("-- insert with the proxy connection --"); try { stmt.execute("insert into manager.hyouka values ()"); } catch (SQLException e) { System.out.println ("Exception : " + e.getmessage()); } finally { if (stmt!= null) stmt.close(); } conn.close(); conn = null; cp.close(); cp = null;

28 audit session by AAA; connect aaa/aaa ERROR: ORA-00: 再帰 SQL レベル string でエラーが発生しました

Agenda

Agenda Oracle データベースの監査機能株式会社アクアシステムズアプリケーション統括部 PAU001J-00-00SE 株式会社アクアシステムズ Oracle データベースを専門とする技術者集団 Oracle データベースチューニング & 監視ツール Performance Analyzer の開発 / 販売 Oracle データベース診断及びパフォーマンスチューニング Oracle データベースに関するコンサルティング