1. Database Security Basics Snapshot on DB2 Andrey Novikov, November 2004

2. What’s a relational DB? Row = set of typed values

3. Retrieve info SELECT o.num, s.name, c.name FROM Orders o, Customers c, Salespeople S WHERE o.snum = s.num AND o.cnum = c.num

4. Change info INSERT INTO Orders VALUES (25, 12, 23, 2) UPDATE Customers SET Name = “Petrov” WHERE Num = 23

5. Problem-solving features • Data consistency • Access rights • Transactions • Concurrency

6. Data consistency • Foreign keys • Value limits • User defined types

7. Access rights • Select, Insert, Update, Delete • Create/drop objects • Execute scripts • Execute stored procedures

8. Transactions $ ACCOUNT 1 ACCOUNT 2 Change sum at account 1 + Change sum at account 2 = Single transaction Transaction: compound atomic operation. Single operation – transaction of one step.

9. Concurrency USER 1 USER 2

10. When do changes take effect? • Generally, on transaction commitment. • May be earlier, depends on isolation levels

11. Situation: a hotel reserving system Room ID Category Booked for CLIENT 1 CLIENT 2 ADMIN

12. Concurrency question If USER1 makes uncommited changes, and USER2 connects, what does USER2 see?

13. Phenomena of multi-user environment • Lost update (LU) One update of two fired • Dirty Read (DR) Read of uncommited data • Non-repeatable Read (NR) Rows change unpredictably • Phantoms (P) Sudden rows in result of repeated SELECT

14. Answer: Isolation Levels • Repeatable Read • Read Stability • Cursor Stability • Uncommited Read

15. Repeatable Read • All scanned rows are locked for duration of transaction • Others may not insert, delete or update • LU- DR- NR- P-

16. RR: Example • You lock all the room entries resulting from your SELECT. • No one can book or release same numbers, or change category. • You get same list each time

17. Read Stability • All retrieved and modified rows are locked for duration of transaction • Others may not delete or update rows that we read. Changes to other rows are unseen. • LU- DR- NR- P+

18. RS: Example • You lock only the room entries that you read and modify (i.e. book) • Anyone can book/release all rooms except those you read about • List changes sometimes (you possibly cannot book room that seems to be free)

19. Cursor stability • The current row is locked (last read) • Others may delete or update rows that we read, except the current row • LU- DR- NR+ P+ • Default for DB2

20. RS: Example • You lock only the current room entry (last you read/modified) • Anyone can book/release all rooms except the current • You can be sure only on the room you’re reading about at the moment

21. Uncommited Read • Table is locked: no one can drop/alter the table you’re reading from • Everyone does what he wants • We see dirt, but no uncommited new tables • Exception: Cursor Stability on updateable cursor

22. Uncommited Read (continued) • LU- DR+ NR+ P+ • Used for read-only tables and SELECTs with non-important results (ex.: search machines)

23. RS: Example • Anyone can book/release all rooms except that you booked • You see rooms with uncommited cancellation

24. Conclusion on ILs • Security vs Speed • Program developer is to choose IL, Admin is to let him do it. • Place to think!

25. Source • IBM 700 and 703 exam preparation guides http://www-106.ibm.com/developerworks/db2/library/tutorials/db2cert/db2cert_V8_tut.html