Transactions and Security

1
Transactions and Security

• B term 2004 lecture 17

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
• SERIALIZABLE this is default.
• 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.

6
Short note on logging

• We need the DB to withstand crashes etc, in the
  middle of a transaction. This is done by logging.
• Undo logging a simple scheme.
• For any update made, we write to log ltT, X, vgt,
  which says the previous value for X in T was v.
• Log also has ltstart Tgt, ltcommit Tgt to indicate
  start and commit of transaction.
• 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.

7
Undo Logging Example

• Consider a transaction that sets a 2 a, b 2
  b let previous 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

8
Access Privileges in SQL

• Access to insert, update, delete, select (query),
  references (foreign keys and other constraints),
  trigger, execute (to execute PSMs) etc.
• Insert, update, select may specify columns of a
  table also.

9
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)

10
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 OPTIONgt
• 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

11
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 Studet FROM
  mmani CASCADE