1 / 25

Database Security Basics

Database Security Basics. Snapshot on DB2. Andrey Novikov, November 2004. What’s a relational DB?. Row = set of typed values. 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. Change info.

twila
Download Presentation

Database Security Basics

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

More Related