Transactions and Security

1. Transactions and Security B term 2004: lecture 17 Murali Mani

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 Murali Mani

3. Solution • We need to consider <find an empty seat, reserve the seat> 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. Murali Mani

4. User Specifying Transactions • START TRANSACTION • Statements • COMMIT/ROLLBACK In Oracle SQLPlus, you can do the following SQL> set autocommit off; SQL> statements SQL> commit/rollback; Murali Mani

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. Murali Mani

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 <T, X, v>, which says the previous value for X in T was v. • Log also has <start T>, <commit T> to indicate start and commit of transaction. • Log record such as <T, X, v> are written to disk before the data is updated. • All updates are made to data, and then the log record <commit T> is written to disk. Murali Mani

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 <start T> • Log record <T, a, 4> • Data record (set a = 8) • Log record <T, b, 8> • Data record (set b = 16) • Log record <commit T> Murali Mani

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. Murali Mani

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) Murali Mani

10. How to give privileges • If you are owner of an “object” such as a table, you have all privileges to it. • GRANT <privilegeList> ON <element> TO <userList> [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 Murali Mani

11. Revoking privileges • DB keeps track of who gave what privileges to whom. • REVOKE [GRANT OPTION FOR] <privilegeList> ON <element> FROM <userList> [CASCADE | RESTRICT] • Eg: REVOKE INSERT (sNumber, sName) ON Studet FROM mmani CASCADE Murali Mani