Transactions,%20Logging%20and%20Security

1
Transactions, Logging and Security
2
Transactions What and Why?

• A set of operations on a database must appear as
  one unit.
• Example Consider flight reservation, which
  consists of 2 steps.
• Check if a seat is available
• Book the seat
• Consider 2 users simultaneously reserving seats.
• The sequence of operations could be
• User 1 finds seat A empty
• User 2 finds seat A empty
• User 1 reserves A and sets it occupied
• User 2 reserves A and sets it occupied

3
Solution

• We need to consider ltfind an empty seat, reserve
  the seatgt as one atomic and isolated operation.
  
• ACID properties of transactions
• Atomicity A transaction if one atomic unit
• Consistency A transaction ensures DB is
  consistent
• Isolation A transaction is considered as if no
  other transaction was executing simultaneously.
• Durability Changes made by a transaction must
  persist.

4
User Specifying Transactions

• START TRANSACTION
• Statements
• COMMIT/ROLLBACK
• In Oracle SQLPlus, you can do the following
• SQLgt set autocommit off
• SQLgt statements
• SQLgt commit/rollback

5
Different isolation levels

• Usually unless a transaction commits, none of the
  changes made by that transaction are visible to
  other transactions.
• There are isolation levels that can be defined
• READ UNCOMMITTED allow dirty reads, i.e.,
  data written by uncommitted Xactions are visible
• READ COMMITTED does not allow dirty reads,
  but one transaction can get different results for
  the same query.
• REPEATABLE READ whatever tuple is retrieved
  once will be again retrieved, however additional
  tuples may also be retrieved.
• SERIALIZABLE this is default.

6
READ UNCOMMITTED
Session 2 -------BEGIN TRANSACTION----- select
color from cust where id500 color ------ red
select color from cust where id500 color
----- blue select color from cust where
id500 color ----- blue -----------COMMIT----
--------
Session 1 -------BEGIN TRANSACTION----- update
cust set color'blue' where id500
-----------COMMIT------------
V Time
7
READ COMMITTED
Session 2 -------BEGIN TRANSACTION----- select
color from cust where id500 color ------ red
select color from cust where id500 color
----- red select color from cust where
id500 color ----- blue -----------COMMIT----
--------
Session 1 -------BEGIN TRANSACTION----- update
cust set color'blue' where id500
-----------COMMIT------------
V Time
8
READ COMMITTED
Session 2 -------BEGIN TRANSACTION----- select
color from cust where id500 color ------ red
select color from cust where id500 color
----- red select color from cust where
id500 color ----- -----------COMMIT--------
----
Session 1 -------BEGIN TRANSACTION----- delete
cust where id500 -----------COMMIT-------
-----
V Time
9
REPEATABLE READ
Session 2 -------BEGIN TRANSACTION----- select
color from cust where id500 color ------ red
select color from cust where id500 color
----- red select color from cust where
id500 color ----- red -----------COMMIT------
------
Session 1 -------BEGIN TRANSACTION----- delete
cust where id500 -----------COMMIT-------
-----
V Time
10
REPEATABLE READ
Session 2 -------BEGIN TRANSACTION----- select id
from cust where colorblue id -- select
id from cust where colorblue id --
select id from cust where colorblue
id -- 500 -----------COMMIT------------
Session 1 -------BEGIN TRANSACTION----- update
cust set colorblue where id500 ---------
--COMMIT------------
V Time
11
SERIALIZABLE
Session 2 -------BEGIN TRANSACTION----- select id
from cust where colorblue id -- select
id from cust where colorblue id --
select id from cust where colorblue
id -- -----------COMMIT------------
Session 1 -------BEGIN TRANSACTION----- update
cust set colorblue where id500 ---------
--COMMIT------------
V Time
12
SERIALIZABLE
Session 2 -------BEGIN TRANSACTION----- select
color from cust ORA-01955 snapshot
too old (rollback segment too small)
Session 1 -------BEGIN TRANSACTION----- update
cust set colorblue where id gt 0 and id lt
2000 -----------COMMIT------------ -------BEGIN
TRANSACTION----- update cust set
colorblue where id gt 2000 and id lt
4000 -----------COMMIT------------ -------BEGIN
TRANSACTION----- update cust set
colorblue where id gt 4000 and id lt
6000 -----------COMMIT------------
V Time
13
Logging

• We need the DB to withstand crashes etc, in the
  middle of a transaction. This is done by logging.
• Undo logging consider transaction T.
• Before any updates as part of T, write ltstart Tgt
  to log
• If T changes the value of X from v to a new
  value, write to the log ltT, X, vgt, this says
  previous value for X in T was v.
• Log record such as ltT, X, vgt are written to disk
  before the data is updated.
• All updates are made to data, and then the log
  record ltcommit Tgt is written to disk.

14
Undo Logging Example

• Consider a transaction T that sets a 2 a, b
  2 b before T, let value of a 4, b 8.
• These are possible steps written to disk
• Log record ltstart Tgt
• Log record ltT, a, 4gt
• Data record (set a 8)
• Log record ltT, b, 8gt
• Data record (set b 16)
• Log record ltcommit Tgt

15
Access Privileges in SQL

• Access to insert, update, delete, select (query),
  execute triggers, execute PSMs etc.
• Insert, update, select may specify columns of a
  table also.

16
Example

• INSERT INTO Student (sNumber, sName)
• SELECT DISTINCT (pNumber, pName)
• FROM Professor
• WHERE (pNumber, pName) NOT IN
• (SELECT sNumber, sName
• FROM Student)
• Privileges needed
• INSERT Student (sNumber, sName)
• SELECT Professor (pNumber, pName)
• SELECT Student (sNumber, sName)

17
How to give privileges

• If you are owner of an object such as a table,
  you have all privileges to it.
• GRANT ltprivilegeListgt ON ltelementgt TO ltuserListgt
  WITH GRANT OPTION
• Element can be a table/view
• WITH GRANT OPTION the user(s) can grant
  privileges to others
• Eg GRANT INSERT (sNumber, sName) ON Student TO
  mmani WITH GRANT OPTION

18
Revoking privileges

• DB keeps track of who gave what privileges to
  whom.
• REVOKE GRANT OPTION FOR ltprivilegeListgt ON
  ltelementgt FROM ltuserListgt CASCADE RESTRICT
• eg REVOKE INSERT (sNumber, sName) ON Student
  FROM mmani CASCADE