1 / 18

Transactions, Logging and Security

Transactions, Logging and Security. 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.

akiva
Download Presentation

Transactions, Logging and Security

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Transactions, Logging and Security 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 • 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. Murali Mani

  6. READ UNCOMMITTED Session 2 -------BEGIN TRANSACTION----- select color from cust where id=500; color ------ red select color from cust where id=500; color ----- blue select color from cust where id=500; color ----- blue -----------COMMIT------------ Session 1 -------BEGIN TRANSACTION----- update cust set color='blue' where id=500; -----------COMMIT------------ | | | | V Time Murali Mani

  7. READ COMMITTED Session 2 -------BEGIN TRANSACTION----- select color from cust where id=500; color ------ red select color from cust where id=500; color ----- red select color from cust where id=500; color ----- blue -----------COMMIT------------ Session 1 -------BEGIN TRANSACTION----- update cust set color='blue' where id=500; -----------COMMIT------------ | | | | V Time Murali Mani

  8. READ COMMITTED Session 2 -------BEGIN TRANSACTION----- select color from cust where id=500; color ------ red select color from cust where id=500; color ----- red select color from cust where id=500; color ----- -----------COMMIT------------ Session 1 -------BEGIN TRANSACTION----- delete cust where id=500; -----------COMMIT------------ | | | | V Time Murali Mani

  9. REPEATABLE READ Session 2 -------BEGIN TRANSACTION----- select color from cust where id=500; color ------ red select color from cust where id=500; color ----- red select color from cust where id=500; color ----- red -----------COMMIT------------ Session 1 -------BEGIN TRANSACTION----- delete cust where id=500; -----------COMMIT------------ | | | | V Time Murali Mani

  10. REPEATABLE READ Session 2 -------BEGIN TRANSACTION----- select id from cust where color=‘blue’; id -- select id from cust where color=‘blue’; id -- select id from cust where color=‘blue’; id -- 500 -----------COMMIT------------ Session 1 -------BEGIN TRANSACTION----- update cust set color=‘blue’ where id=500; -----------COMMIT------------ | | | | V Time Murali Mani

  11. SERIALIZABLE Session 2 -------BEGIN TRANSACTION----- select id from cust where color=‘blue’; id -- select id from cust where color=‘blue’; id -- select id from cust where color=‘blue’; id -- -----------COMMIT------------ Session 1 -------BEGIN TRANSACTION----- update cust set color=‘blue’ where id=500; -----------COMMIT------------ | | | | V Time Murali Mani

  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 color=‘blue’ where id > 0 and id <= 2000; -----------COMMIT------------ -------BEGIN TRANSACTION----- update cust set color=‘blue’ where id > 2000 and id <= 4000; -----------COMMIT------------ -------BEGIN TRANSACTION----- update cust set color=‘blue’ where id > 4000 and id <= 6000; -----------COMMIT------------ | | | | V Time Murali Mani

  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 <start T> to log • If T changes the value of X from v to a new value, write to the log <T, X, v>, this says previous value for X in T was v. • 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

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

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

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

  17. 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

  18. 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 Student FROM mmani CASCADE Murali Mani

More Related