Transcript Fine Grained Transaction Log for Data Recovery in Database

Fine Grained Transaction
Log for Data Recovery in
Database Systems
Department of Computer Sci. & Tech.
Huazhong University of Sci. & Tech.
Ge Fu
[email protected]
Towards Database Security Ⅰ
• What does conventional database security
mechanism concern?
confidentiality, integrity, availability, survivability
• What we have done?
Authentication & Authorization
Access Control (DAC, MAC, FGAC et al.)
Inference Control
Multilevel Secure Databases
Data Encryption
2
Towards Database Security Ⅱ
• Disadvantages of these Methods?
a) Addresses primarily how to protect the
security of a database;
b) Preventative based methods can not
prevent all attacks.
E.g. SQL injection & cross site script attacks
c) Data recovery after attacks become an
important issue.
3
Existing Methods for data recovery
•
•
•
•
Complete rollback
Hard recovery
Flash back in Oracle 10g
Selective data recovery
keep a transaction log, find the read-write
dependency relationship between transactions.
And then undo the malicious (bad) and affected
(reads from malicious transactions) transactions,
keep the results of benign transactions.
4
Selective data recovery Ⅰ
t1
B1: x=x=2;
(Malicious transaction B1 modifies data item x)
t2
G1: y=x+9;
(Benign transaction G1 reads x and modifies y)
t3
G2: z=y+10;
(Benign transaction G2 reads y and modifies z)
time
y
x
z
Damage spread
B1
G1
G2
Read-Write Dependency
for transactions
when transaction B1 that updates x is identified malicious, the damage on x
can spread to every data item updated by a transaction that is dependent on
B1 directly or indirectly.
5
Selective data recovery Ⅱ
• Selective data recovery methods include:
a) Locate each affected transaction (damage
assessment). According to read-write
dependency between transactions (A
Dependency Graph), find transactions affected
by the malicious transactions;
b) Recover the database from the damage
caused on the data items updated by every
malicious or affected transaction (damage
recovery). Undo the malicious and affected
transaction.
6
Transaction Log in selective recovery
• What kind of log is required for selective
data recovery?
a) The log should record all read or write
operations towards data items.
b) The log can be a table. It records all operations
of transactions. Each row represents an
operation (read or write) towards a data item. It
can be this type: TRASATIONID, OPTYPE,
ITEM, BEFOREIMG, AFTERIMG, TIME
7
Does existing log in DBMS useful?
• Conventional undo/redo log and triggers in DBMS is
only for write operations, and can not capture
read operations for transactions;
• Existing auditing mechanisms are designed to audit
database statements, privileges, or schema objects.
The audit is based on the “table level”. It can not
obtain data items that are manipulated by the
operation.
8
Fine Grained Transaction Log
• The log system for selective data recovery is
devoted to following problems:
P1: The log should be created in the executing
period of transactions.
P2: The read operation in sub-query in a SQL
statement should be captured.
• If the log records all read and write operations in
transactions, including sub-queries in SQL
statements, we call this new kind of log Fine
Grained Transaction Log.
9
Fine Grained Transaction Log Generator
User
Transactions
SQL statements
DB Monitor
Sequence of SQL
statements
Client user
SQL statements
Trigger1
Trigger2
……
Read Log
Generator
TMPTABLE1
TMPTABLE2
……
TMPTABLEn
Triggerm
Write log entries
TABLE1
TABLE2
……
TABLEm
Read log
entries
Transaction Log
Database System
10
Read Log Generator
User
Transactions
DB Monitor
Read Log
Generator
Data items TMPTABLE1
TMPTABLE2
……
Read log
entries
Transaction Log
Client user
• Initial statement:
SELECT ol_qty FROM order_line WHERE ol_i_id IN
(SELECT i_id FROM item WHERE i_cost >100);
• Divided statements:
1) SELECT i_id INTO TMPTABLE1 FROM item WHERE
i_cost>100;
2) SELECT ol_qty INTO TMPTABLE2 FROM order_line
where ol_i_id IN (SELECT i_id from TMPTABLE1;
3) SELECT ol_qty from TMPTABLE2
11
Experiment results and analysis Ⅰ
• TPC-W Benchmark
TPC-W is a transactional web e-commerce
benchmark introduced by the Transaction
Processing Performance Council. TPC-W specifies
an ecommerce workload that simulates the
activities of a retail website which produces
workload on the backend databases.
• Why we choose TPC-W?
1) TPC-W is a common used benchmark;
2) Provide three web interaction patterns and use
WIPSb,WIPS, WIPSo to measure performances under
different web interaction patterns.
12
Experiment results and analysis Ⅱ
Test Environment
 DBMS: SQL Server 2000, a PC with Windows NT,
Pentium R 2.8GHZ CPU, 2GB main memory.
 FGTL Generator and TPC-W Platform: a PC with
Windows NT, Pentium R 2.8GHZ CPU, 1GB main
memory.
 10/100Mbps switch LAN.
 FGTL Generator is implemented using Eclipse on
JAVA platform.
13
Experiment results and analysis Ⅲ
 Results:
14
Experiment results and analysis Ⅳ
• The objective:
Study throughput of FGTL Generator under
different amounts of EBs.
• Conclusions:
a) With the number of EBs increasing, the
overhead of FGTL Generator become large.
b) In WIPSo, the overhead of throughput for
FGTL Generator is the lowest and in WIPSb is
the highest.
15
Questions?