第１１回 ２０１４．６．６
初めて司会を務めました。
百戦錬磨の皆様に、発表するって大変だなあ。。。と終わってみて感じています。
さて、今回の内容は「ビッグデータ」の話です。
昨今、「ビッグデータ」と言葉をよく効きます。
なんとなく、とてつもない大きなデータなのかな。。。程度にしか思っていませんでした。
そこで、注目されているキーワードでもあるので、いっちょ調べてみるかって感じで始まったこの数週間でした。
目次
１ ビッグデータとは
２ ビッグデータの活用事例
３ ビッグデータを活用するためには
１ ビッグデータとは
ビッグデータの定義を話しました。（とは言っても、解釈はケースバイケースなのですが）
なかなか、皆様にビッグデータのイメージが伝えきれずにいたところ、リーダーよりイメージが描けるような例えをして頂き、無事にビッグデータの概要が理解出来た所存です。
このあたりは、もう少しイメージしやすい説明が必要だなと感じました。
また、ビッグデータの歴史についてもお話しました。
２ ビッグデータの活用事例
実際の活用例を説明しました。
また、「お父さんがオムツを買いにいくとビールが売れる」なんて話も出てきました。ビッグデータを使ってこのような分析結果が分かったおかげで、オムツの横にビールを陳列しておくと言うことですね。
うーん。こっちのほうが事例としては面白い。
また、ネットで、「ビッグデータ活用例」で検索すると結構面白い事例があるので、みていて面白かったです。
３ ビッグデータを活用するためには
ビッグデータ解析に使われる「ビッグデータソリューション」を紹介しました。
しかし、細かい仕様等は説明しきれず、勉強不足を痛感しております。
ここの部分はもう少し突っ込んだ説明が出来ていれば面白かったなと思いました。
ここで、発表が終了しましたが、
まだ、１時間ちょっとしか経っていない！
早く終わってしまった。どうしよう。
と思っていたら、リーダーが、「ビッグデータ」と「個人情報保護」の観点からいろいろとお話をして頂きました。
次回からは、時間配分を考えて構成していきたいと思います。
さて、次回は７月１１日。
TBK１周年ということです。
第１０回目
DB関係の勉強会が続いてましたが
今回はMVCモデルの話です。
MVCモデルの簡単な話を終えたところでまだ３０分経っていない
MVCモデルの概念を漫画で解説してみる | hijiriworld Web
ここのサイトを参考にさせてもらいました。
PHPで簡単なMVCのサンプルを作ったのでコードを見てもらいました。
キーノートで説明して、実際のコードをみて説明してみたがうまくできなかった。
ここらへんは、勉強会に参加して発表の仕方を勉強しないといかんな
次のサンプルでは
第５回目 - とある会社の勉強会で作成したサンプル
をMVCで作成しなおしてコードがどう変わったのかを見てもらいました。
MVCやるならフレームワークも知ってもらいたいというのがあったので
Googleトレンドで有名どころのフレームワークを入れて調べてみました。
１位がCakePHP、２位がFuelPHP、３位がSymfony、よく使われてそうなZendFrameworkは４位という結果でした
今回は、FuelPHPとCakePHPでHello Worldのサンプルを紹介しました。
FuelPHPとCakePHPで書き方の違いがあったりして、CakePHPのほうがいいとか
FuelPHPの書き方のほうがいいと、書き方で好みが分かれてました
途中ASP.NETの話が出ていたので、最後に
ASP.NET MVC Frameworkというのもあるという紹介をしました
今回はサンプル作成で時間がかかってしまい内容が薄いものになってしまった
今度、担当した時はフレームワークを使ったサイト作成なんかがいいのかな
第９回 2014.04.11
今回も前回に引き続きの担当に相成ってしまいますた…(>_<)
去年の年末からデータベースのお勉強シリーズでお送りしておりましたが
今回は「大人のSQL講座」と題してSQL文特にDMLについて深く掘り下げましょうって
感じで進めようと思っていました。
…まあ、結果として大人の階段を上るってぐらいの基本の「き」になってしまいましたが。
アジェンダとしては
- SQLとは
- DMLの基本構文
- ちょっと小洒落たSQL
- SQLの効率化・高速化のするには
みたいな感じです。
多少、大風呂敷を広げてしまった事を後悔しております(T_T)
SQLってパズルみたいな感覚なんですよね。
なもので、今回はクイズ形式のように進めていきました。
皆さん、ノートやPCにSQL文を書きながら小さく唸りつつ…。
ちょっとは頭の体操くらいにはなったかもしれません。
実際、聞くだけではなく、考えて手を動かすって良いもんですね。
相関副問い合わせについては、難しかったようで、
ワタクシも上手に説明できなくて反省しております(>_<)
まぁ、相関副問い合わせってループの様なイメージとかを持っていただければ
良いんですけど、パッと見？？ってなっちゃいますよね。
理解すると格段にSQLスキルはアップしますので、
ぜひ覚えて欲しいものです。
そして、質問コーナーで出ました逆順(降順)インデックスについて、
ちょっとその場では回答できないため、持ち帰りで調査をしてみましたが、
いろいろ興味深い結果になってきて、もうちょっと深く調べたくなってきましたので、
後日、調査結果報告をいたしますm(_ _)m
え〜お詫びですが、作ったスライドにてスペルをミスっちゃいました(>_<)
一旦アップしたんですが、間違えに気付いて、修正版を再アップしています。
という事でお詫びの印(?)として…
自己結合での相関副問い合わせはグルーピングされると
説明しましたが、その際に最大値のデータを取りたい等
お話も出てましたので、その辺りのパフォーマンスを調べてみましたよ！
事業所別の最大給与受給者の従業員コードと名前と給与額をとるイメージ
データベース：Oracle 11gR2 Express Edition
OS:Cent OS 6.5 (VirtualBox)
テーブルサンプル 約600万レコード
tab_c （サンプルテーブル 約600万レコード） 項目名 説明 備考 ------- --------------- --------------------------- empno 従業員コード 1からの連番 name 従業員名 deptno 事業所コード 1~13までランダムで入ってます num 給与額 データとしてはempnoと同値
[SQL]
自己結合を使った場合
select c1.empno,c1.name,c1.num from tab_c c1 where c1.num = (select max(c2.num) from tab_c c2 where c2.deptno = c1.deptno );
MAXを取るインラインビューで結合した場合
select c1.empno,c1.name,c1.num from tab_c c1, (select deptno,max(num) num from tab_c group by deptno) c2 where c1.deptno = c2.deptno and c1.num = c2.num ;
OLAP関数を使った場合
select e.empno,e.name,e.num from ( select empno,name,num,max(num) over(partition by deptno) maxnum from tab_c ) e where e.num = e.maxnum ;
[AUTO TRACE実行結果]
自己結合を使った場合
実行計画
----------------------------------------------------------
Plan hash value: 2010716975
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57677 | 3379K| 50227 (3) | 00:10:03 |
|* 1 | HASH JOIN | | 57677 | 3379K| 50227 (3) | 00:10:03 |
| 2 | VIEW | VW_SQ_1 | 13 | 338 | 25348 (4) | 00:05:05 |
| 3 | HASH GROUP BY | | 13 | 208 | 25348 (4) | 00:05:05 |
| 4 | TABLE ACCESS FULL| TAB_C | 5767K| 88M| 24828 (2) | 00:04:58 |
| 5 | TABLE ACCESS FULL | TAB_C | 5767K| 187M| 24828 (2) | 00:04:58 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"."NUM"="MAX(C2.NUM)" AND "ITEM_1"="C1"."DEPTNO")
統計
----------------------------------------------------------
138 recursive calls
0 db block gets
371818 consistent gets
185978 physical reads
0 redo size
1092 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
34 sorts (memory)
0 sorts (disk)
13 rows processed
MAXを取る内部ビューで結合した場合
実行計画
----------------------------------------------------------
Plan hash value: 595688616
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57677 | 3379K| 50227 (3)| 00:10:03 |
|* 1 | HASH JOIN | | 57677 | 3379K| 50227 (3)| 00:10:03 |
| 2 | VIEW | | 13 | 338 | 25348 (4)| 00:05:05 |
| 3 | HASH GROUP BY | | 13 | 208 | 25348 (4)| 00:05:05 |
| 4 | TABLE ACCESS FULL| TAB_C | 5767K | 88M| 24828 (2)| 00:04:58 |
| 5 | TABLE ACCESS FULL | TAB_C | 5767K | 187M| 24828 (2)| 00:04:58 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"."DEPTNO"="C2"."DEPTNO" AND "C1"."NUM"="C2"."NUM")
統計
----------------------------------------------------------
138 recursive calls
0 db block gets
371818 consistent gets
185978 physical reads
0 redo size
1092 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
34 sorts (memory)
0 sorts (disk)
13 rows processed
OLAP関数を使った場合
実行計画
----------------------------------------------------------
Plan hash value: 3486227064
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5767K| 280M | | 77679 (2)| 00:15:33 |
|* 1 | VIEW | | 5767K| 280M | | 77679 (2)| 00:15:33 |
| 2 | WINDOW SORT | | 5767K| 187M | 265M | 77679 (2)| 00:15:33 |
| 3 | TABLE ACCESS FULL| TAB_C | 5767K| 187M | | 24828 (2)| 00:04:58 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."NUM"="E"."MAXNUM")
統計
----------------------------------------------------------
294 recursive calls
26 db block gets
185964 consistent gets
170624 physical reads
0 redo size
1092 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
30 sorts (memory)
1 sorts (disk)
13 rows processedう〜ん…自己結合とインラインビューは全く同じ結果ですね〜。
OLAP関数は一番遅いですねwww
では、今度は特定の事業所での結果を確認してみます。
[SQL]
自己結合を使った場合
select c1.empno,c1.name,c1.num from tab_c c1 where c1.num = (select max(c2.num) from tab_c c2 where c2.deptno = c1.deptno ) and c1.deptno = 10 ;
MAXを取るインラインビューで結合した場合
select c1.empno,c1.name,c1.num from tab_c c1, (select deptno,max(num) num from tab_c where deptno = 10 group by deptno) c2 where c1.deptno = c2.deptno and c1.num = c2.num ;
select c1.empno,c1.name,c1.num from tab_c c1, (select deptno,max(num) num from tab_c group by deptno) c2 where c1.deptno = c2.deptno and c1.num = c2.num and c1.deptno = 10 ;
※今回の環境では同一の結果にしかなりませんでした(>_<)
OLAP関数を使った場合
select e.empno,e.name,e.num from ( select empno,name,num,max(num) over(partition by deptno) maxnum from tab_c where deptno = 10 ) e where e.num = e.maxnum ;
[AUTO TRACE実行結果]
自己結合を使った場合
実行計画
----------------------------------------------------------
Plan hash value: 3229880088
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4437 | 259K| 48471 (1)| 00:09:42 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 4437 | 259K| 48471 (1)| 00:09:42 |
| 3 | VIEW | VW_SQ_1 | 1 | 26 | 24759 (1)| 00:04:58 |
| 4 | HASH GROUP BY | | 1 | 16 | 24759 (1)| 00:04:58 |
|* 5 | TABLE ACCESS FULL | TAB_C | 443K| 6932K| 24759 (1)| 00:04:58 |
|* 6 | INDEX RANGE SCAN | IDX01 | 23693 | | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| TAB_C | 4437 | 147K| 23712 (1)| 00:04:45 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("C2"."DEPTNO"=10)
6 - access("C1"."NUM"="MAX(C2.NUM)")
7 - filter("C1"."DEPTNO"=10)
統計
----------------------------------------------------------
5 recursive calls
0 db block gets
185812 consistent gets
92991 physical reads
116 redo size
698 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
MAXを取るインラインビューで結合した場合
実行計画
----------------------------------------------------------
Plan hash value: 2458970658
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4437 | 259K| 48471 (1)| 00:09:42 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 4437 | 259K| 48471 (1)| 00:09:42 |
| 3 | VIEW | | 1 | 26 | 24759 (1)| 00:04:58 |
| 4 | HASH GROUP BY | | 1 | 16 | 24759 (1)| 00:04:58 |
|* 5 | TABLE ACCESS FULL | TAB_C | 443K| 6932K| 24759 (1)| 00:04:58 |
|* 6 | INDEX RANGE SCAN | IDX01 | 23693 | | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| TAB_C | 4437 | 147K| 23712 (1)| 00:04:45 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("DEPTNO"=10)
6 - access("C1"."NUM"="C2"."NUM")
7 - filter("C1"."DEPTNO"="C2"."DEPTNO")
統計
----------------------------------------------------------
1 recursive calls
0 db block gets
185808 consistent gets
92989 physical reads
0 redo size
698 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
OLAP関数を使った場合
実行計画
----------------------------------------------------------
Plan hash value: 2709613132
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 443K| 21M| 28821 (1)| 00:05:46 |
|* 1 | VIEW | | 443K| 21M| 28821 (1)| 00:05:46 |
| 2 | WINDOW BUFFER | | 443K| 14M| 28821 (1)| 00:05:46 |
|* 3 | TABLE ACCESS FULL| TAB_C | 443K| 14M| 24759 (1)| 00:04:58 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."NUM"="E"."MAXNUM")
3 - filter("DEPTNO"=10)
統計
----------------------------------------------------------
1 recursive calls
0 db block gets
185803 consistent gets
92989 physical reads
0 redo size
698 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
おぉぉ〜、OLAP関数が面目躍如ですねwww
Bytesとかは掛かっていますが、CostとTimeが他の２つの約半分です。
自己結合とインラインビューは相変わらず同じ実行結果ですwww
まぁ、今回はこのような結果になりましたが、
同じ値を取るSQLでも色々な書き方があり、それによって実行速度が変わるんだなぁ〜
と理解いただければ良いのかと(^^)
７月でこの勉強会も１周年ですね〜。
色々紆余曲折はありましたが、皆さんのお力添えあっての事だと思いますし、
やはり、継続していく事が大事なのかなと改めて思う次第であります。
では、次回はGW空け５月９日です！
第８回 2014.03.14
前回の大雪で延期された第８回のリベンジマッチが開催されました。
今回は
- MySQLのかる〜い説明
って流れでお話させていただきました。
全体についてはアップしたスライドを参照していただくとして、SQLインジェクションについては実際にWebアプリケーションを作ってみて気づいたりするところなのかも知れませんが、やはり間違った方向で覚えてしまってもいけないのかな…なんて思いまして、この機会に行った次第です。
実際のサンプルも用意しておりますので、色々攻撃をしてもらって(笑)気付いていただければと思います。
基本はプリペアードステートメントを使うって事なんですけど、、、
この機能はSQLインジェクション用にある機能ではなく、SQL文のリテラルを変数に置き換えて準備しておく事によって、リテラルの内容が変更されてもRDBMSが同一のSQL文として取り扱っていただけるという機能なのですよね…。
この機能を使わないと、リテラルが置き換わったSQL文はRDBMSでは別なSQL文として取り扱っちゃうので、何度もSQL解析が走っちゃってサーバ負荷、レスポンス低下を招いちゃうんですよね…。
と言う事で、質問で出てきた事なんですけど、プリペアードステートメントの本来の
用途にスポットを当てた解説を少々…
サンプルがOracle+Javaで今回のと真逆？になっちゃいますけどご了承くださいw
1000件のデータのSELECT文でプリペアードステートメントを使った場合と
使わなかった場合のOracleのSQLトレースの違いをみてみます。
プリペアードステートメントを使わない場合…
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestNotBind { public static void main(String[] args) { String strPara = null; try { Class.forName("oracle.jdbc.OracleDriver"); Connection con = DriverManager.getConnection("jdbc:oracle:thin:@111.111.111.111:1521:HOGE",+ "scott", "tiger"); //SQLTRACE取得用 Statement alt = con.createStatement(); alt.execute("ALTER SESSION SET SQL_TRACE=TRUE"); //SQL実行 Statement st = con.createStatement(); for(int i=0;i<1001;i++){ strPara = String.valueOf(i); String sql = "SELECT EMP_NAME FROM EMP WHERE EMP_NO = '"+ strPara+"'"; ResultSet rs = st.executeQuery(sql); while(rs.next()){ System.out.println(param+":"+rs.getString("EMP_NAME")); } rs.close(); } st.close(); alt.execute("ALTER SESSION SET SQL_TRACE=FALSE"); alt.close(); con.close(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } }
プリペアードステートメントを使った場合…
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestBind { public static void main(String[] args) { String strPara2 = null; try { Class.forName("oracle.jdbc.OracleDriver"); Connection con = DriverManager.getConnection("jdbc:oracle:thin:@111.111.111.111:1521:HOGE",+ "scott", "tiger"); //SQLTRACE取得用 Statement alt = con.createStatement(); alt.execute("ALTER SESSION SET SQL_TRACE=TRUE"); //SQL実行 String binds = "SELECT EMP_NAME FROM EMP WHERE EMP_NO = ?"; PreparedStatement ps = con.prepareStatement(binds); for(int j=0;j<1001;j++){ strPara2 = String.valueOf(j); ps.setString(1, strPara2); ResultSet rs2 = ps.executeQuery(); while(rs2.next()){ System.out.println(param2+":"+rs2.getString("EMP_NAME")); } rs2.close(); } ps.close(); alt.execute("ALTER SESSION SET SQL_TRACE=FALSE"); alt.close(); con.close(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } }
んでSQLトレースの違い
プリペアードステートを使わなかった場合
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1002 1.07 0.83 0 0 0 0
Execute 1003 0.00 0.08 0 0 0 0
Fetch 1001 0.01 0.01 0 3003 0 1001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3006 1.09 0.94 0 3003 0 1001
Misses in library cache during parse: 1002
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
1003 user SQL statements in session.
0 internal SQL statements in session.
1003 SQL statements in session.
********************************************************************************プリペアードステートメントを使った場合
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 1003 0.03 0.19 0 0 0 0
Fetch 1001 0.00 0.01 0 3003 0 1001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2006 0.03 0.20 0 3003 0 1001
Misses in library cache during parse: 0
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
3 user SQL statements in session.
0 internal SQL statements in session.
3 SQL statements in session.
********************************************************************************特に最初のParse(解析)のcountの箇所に注目して欲しいんですけど、
プリペアードステートメント使わない場合は、1000回以上解析されてますけど、
プリペアードステートメント使った場合は、２回しか解析がされてませんよね。
これが、プリペアードステートメントの本来の目的なんですよ。
でもって、変数化される事で、変数に入る値がリクエストパラメータで悪意のある値がわたったとしても
もうSQL文として解釈してるから、それ以上の解釈をしない…
つまり、別の意味のSQL文が実行される事がなくなるってことになり、
結果として、SQLインジェクション対策になるって事になります。
エスケープ関数などを使っても完璧でないと脆弱性が入り込んだままになってしまいます。
リテラルについては、もっとも簡単で且つほぼ間違いなく防げるのがプリペアードステートメントなんですね。
※識別子などではエスケープも必要な場合がありますので！
んで、今回は飛び込みでの参加もあり、色々ためになる話も聞けました。
てか、プレゼンが予定時間の半分で終了してしまい、焦りましたが…。
そのときにPHPでリクエストパラメータの汚染チェックtaintなるものもあるって知りました。
今後、htmlspecialcharsやPDO::quoteみたいな、エスケープ系のことも必要であるとは思っていたのですが、
こういったのが漏れたときのチェックとして使えるんですね、、、
やっぱり経験値が高い人とのぶつかり稽古？は楽しい。
では、次回４月１１日…
どうしましょ？俺だ俺だ俺だって人がいなかったら、大人のSQL講座にしようかな…
すみだセキュリティ勉強会に行ってきた
２月に予定していた勉強会が大雪のためお流れ…。
そんな訳で、ブログを書くことがなくなっちゃったんですが、せっかくなので、すみだセキュリティ勉強会なる会に参加してきましたので、簡単に感想などを書いてみます。
この会は２ヶ月に１回くらいの間隔で開催されていて、今回で３回目。
第１回目開催後にふとしたネットサーフィン中に知って、本当は第２回から参加したかったのですが、年末ということもあり断念…。
そしたら、第２回目の内容がはてなブックマークの上位に来て一気に注目度が高くなり、なんとか参加できましたが、キャンセル待ちも多かった模様ですね。
今回は主催者のさくらVPSにやってくる攻撃者の攻撃手法を解析したのとTwitter APIを使った情報分析方法の紹介とハニーポット導入しての雑感って感じでした。
第２回目が注目を浴びた理由が、SSHハニーポットで攻撃者の攻撃内容を再現した動画だったりします。
さてハニーポットって何ぞやって？ことなんですけど、要は攻撃者に対してわざと攻撃しやすい場所を提供して誘導しながら行動や攻撃パターンを監視するサーバや機器のことになります。
そして今回はハニーポットでおびき寄せた攻撃者の攻撃用スクリプト等を攻撃者のサーバからダウンロードして解析していました。
攻撃の目的が、DDoS攻撃なんかではなくBitcoinって言うのがなるほど〜と感心してしまいました。やっぱりトレンドってのがあってそう言うモノを知っておく必要もあるなと痛感した次第です。
Twitter APIを利用した情報収集方法等も紹介しており、これもこれで非常に興味深くて、あぁこんな風に収集できるんだって感心しました。
クラウド上に簡単に仮想サーバを構築できる便利な時代になりましたが、その分攻撃に晒される可能性も高まっています。
一応？AWSを題材にしていますので、こういったサーバセキュリティについても今後の勉強会で取り上げていきたいですね。
では次回はリベンジ戦、3/14でごわす…
漢のDB講座２
新年あけましておめでとう御座います。（だいぶ遅いですが）
昨年もいろいろありましたが、今年もいろいろありそうです。
がんばっていきましょう！
さて、前回の漢のDB講座の第二弾！
と、いう事で、漢のDB講座２が開催されました。
内容は以下となります。。
１．DBオブジェクトのおさらい
２．実践
社員名簿を作ってみましょう！（正規化のおさらい）
DBカラムの日本語化
うん。前回の復習みたいな感じです。
DBオブジェクトのおさらいは、すんなり流して・・・。
（ビュー・スナップショットなどの活用の仕方などを議論）
【社員名簿を作ってみましょう！】
社員情報・所属事業所・同好会情報を如何に効率よく作るか？を考えるというものです。
これについては、なかなかの議論が繰り広げられました。
正規化の最大の目的は、「情報の一貫性を保つこと」です。
(データが重複したり、データ更新の際に矛盾が生じたりしないようにすることが目的です。)
結果を見ればなるほどね。。となるものの、自分で設計するとちょっと悩むところもあります。
ちなみに、第１正規化とは，繰り返しの部分を複数のレコードにして，繰り返しを排除する操作です。
第２正規化とは，部分関数従属する項目を分離することです。
第３正規化は，主キー以外のキーに関数従属する項目を分離します。
うーん。いまいち理解が出来ません。。
私の場合、とりあえず必要な情報をひとつのテーブルに詰め込みます。
そして目を細めてその情報を見ると、重複しているデータがなんとなく見えてきます。
その情報をマスタテーブルとして管理するようにしていくと、第三正規化の状態になりました。
これは、知識プラス少々の経験が必要なのかもしれませんね。
（あまり再分化するとパフォーマンスに悪影響の可能性もあるので）
ということで、奥が深いなと改めて認識された新春の夜でありました。
どのように正規化するかは、あなた次第。。。
漢のDB講座 番外編
今回は、先日のDB講座でOracleのクエリーリライトに軽く説明してみましたが、
ログにはどのように出力されるのか？と言った質問を受けたので、
調査してみました。
ログと一言で言ってもOracleには様々なログがあります。
REDOログ、監査ログ、アーカイブログ…。
でも、今回の質問の意図がクエリーリライト機能が使われた事を
どのように知れるかってことなので、身近なAUTOTRACEとSQLトレースでどのような
出力になるかの観点で調査しております。
環境は以下の通り。
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production Windows 2012 R2 (on Windows Azure)
なお、構成については、以下を参照させていただきました。
クエリー・リライト - オラクル・Oracleをマスターするための基本と仕組み
今回利用するテーブル
DESC SALES; Name Null? Type --------------------- -------- -------------- USER_NAME VARCHAR2(5) SALES NUMBER SALES_DATE DATE
サンプルデータとして４０万ちょっとの件数を用意しました。
SELECT * FROM SALES;
... BOB 16300 24-FEB-11 SAM 19300 24-FEB-11 USER_ SALES SALESE_DA ----- ---------- --------- DAVE 19400 24-FEB-11 CAS 22900 24-FEB-11 TOM 28700 24-FEB-11 BOB 14400 24-FEB-11 SAM 12100 24-FEB-11 DAVE 17600 24-FEB-11 CAS 16300 24-FEB-11 TOM 13600 24-FEB-11 BOB 20000 24-FEB-11 SAM 26400 24-FEB-11 DAVE 1200 08-DEC-10 412280 rows selected.
さて、簡単に現在の状態でAUTOTRACEを確認してみます。
set autotrace on SELECT USER_NAME, SUM(SALES) SUM_SALES FROM SALES WHERE USER_NAME = 'TOM' GROUP BY USER_NAME ;
USER_ SUM_SALES
----- ----------
TOM 1233431500
Execution Plan
----------------------------------------------------------
Plan hash value: 1729043503
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT| | 1 | 17 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | SALES | 1 | 17 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("USER_NAME"='TOM')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1213 consistent gets
0 physical reads
0 redo size
621 bytes sent via SQL*Net to client
543 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedまあ、普通にテーブルアクセスフルが出てきます。
続いて、現在の状態でSQLトレースを確認してみます。
ALTER SESSION SET SQL_TRACE = TRUE; SELECT USER_NAME, TRUNC(SALES_DATE, 'MONTH') SALES_DATE, SUM(SALES) SUM_SALES FROM SALES GROUP BY USER_NAME, TRUNC(SALES_DATE, 'MONTH') ; ALTER SESSION SET SQL_TRACE = FALSE;
以下、tkprofの結果です。
該当する箇所だけ抜き取ってみます。
SELECT
USER_NAME, TRUNC(SALES_DATE, 'MONTH') SALES_DATE, SUM(SALES) SUM_SALES
FROM
SALES
GROUP BY
USER_NAME, TRUNC(SALES_DATE, 'MONTH')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 39 0.29 0.31 0 1213 0 565
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 41 0.29 0.33 0 1213 0 565
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 104
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
565 565 565 HASH GROUP BY (cr=1213 pr=0 pw=0 time=310196 us cost=3 size=26 card=1)
412280 412280 412280 TABLE ACCESS FULL SALES (cr=1213 pr=0 pw=0 time=195467 us cost=3 size=26 card=1)
********************************************************************************
ふむふむ、こちらもテーブルフルスキャンになっていますね。
さて、いよいよ書き換え用のマテリアライズドビューを作成します。
CREATE MATERIALIZED VIEW SALES_SUM_PRODUCT_MONTH BUILD IMMEDIATE REFRESH COMPLETE ENABLE QUERY REWRITE AS SELECT USER_NAME, TRUNC(SALES_DATE, 'MONTH') SALES_DATE, SUM(SALES) SUM_SALES FROM SALES GROUP BY USER_NAME, TRUNC(SALES_DATE, 'MONTH') ;
ENABLE QUERY REWRITE
ってところがクエリーリライトのための記述っすね。
では、実行してみましょう。
セッション内でクエリーリライトの使用を可能にします。
ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;
トレースの出力を確認するため、強制的にクエリーリライトを使うよう
ヒント句を付与してSQLを実行します。
SELECT /*+ REWRITE(SALES_SUM_PRODUCT_MONTH) */ USER_NAME, SUM(SALES) SUM_SALES FROM SALES WHERE USER_NAME = 'TOM' AND TRUNC(SALES_DATE, 'MONTH') = TO_DATE('2005/10','YYYY/MM') GROUP BY USER_NAME, TRUNC(SALES_DATE, 'MONTH');
AUTOTRACEの実行結果です。
Execution Plan
----------------------------------------------------------
Plan hash value: 2900729471
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 |
|* 1 | MAT_VIEW REWRITE ACCESS FULL| SALES_SUM_PRODUCT_MONTH | 1 | 17 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SALES_SUM_PRODUCT_MONTH"."SALES_DATE"=TO_DATE(' 2005-10-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "SALES_SUM_PRODUCT_MONTH"."USER_NAME"='TOM')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
620 bytes sent via SQL*Net to client
543 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQLトレースの結果です。
********************************************************************************
SELECT /*+ REWRITE(SALES_SUM_PRODUCT_MONTH) */
USER_NAME, SUM(SALES) SUM_SALES
FROM
SALES
WHERE
USER_NAME = 'TOM'
AND TRUNC(SALES_DATE, 'MONTH') = TO_DATE('2005/10','YYYY/MM')
GROUP BY
USER_NAME, TRUNC(SALES_DATE, 'MONTH')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 5 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 5 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 104
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 MAT_VIEW REWRITE ACCESS FULL SALES_SUM_PRODUCT_MONTH (cr=5 pr=0 pw=0 time=53 us cost=3 size=17 card=1)
********************************************************************************おぉぉ、どちらの結果も「MAT_VIEW REWRITE」「SALES_SUM_PRODUCT_MONTH」
となっていて、クエリーが書き換わったのが出力されている事が分かります。
クエリーリライトはかなり効果的な機能ではありますが、
あくまでも暫定処置、緊急の応急処置と捉えるべきでしょう。
実行されるはずのSQLをOracleが書き換えているため、環境が換わったら遅くなったって
こともあり得ます。
修正すべき箇所が分かっていれば、誰が見てもわかるコードにしないと、保守性も低下しますし。
さて、上にも書きましたが、今回、サンプルデータを作成するのに苦労しました。
JDBCの簡単なプログラムでサンプルデータを作ろうと思いましたが、
ローカルからOracleへの接続がうまくできず、結果、ローカルのMySQLにデータを作成し、
強引にINSERT文を作り、Azure上で実行という力技でした。
この辺りも、ちょっと研究をしなければと思う年の瀬でありんす。
では、みなさん Merry X'mas!!
