1 / 39

Implementing SQL Security in Database Systems

Explore SQL security implementation, discretionary access control, privileges, views, revokation, challenges in data security like SQL injection, data collusion, view leakage, and fine-grained access control considerations.

fralston
Download Presentation

Implementing SQL Security in Database Systems

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. Lecture 16: Data Storage Friday, November 5, 2004

  2. Outline SQL Security – 8.7 Part II: Database Implementation Today: Data Storage • The memory hierarchy – 11.2 • Disks – 11.3

  3. Discretionary Access Control in SQL GRANT privileges ON object TO users [WITH GRANT OPTIONS] privileges = SELECT | INSERT(column-name) | DELETE | REFERENCES(column-name)object = table | attribute

  4. Examples GRANT INSERT, DELETE ON Customers TO Yuppy WITH GRANT OPTIONS Queries allowed to Yuppy: INSERT INTO Customers(cid, name, address) VALUES(32940, ‘Joe Blow’, ‘Seattle’) DELETE Customers WHERE LastPurchaseDate < 1995 Queries denied to Yuppy: SELECT Customer.address FROM Customer WHERE name = ‘Joe Blow’

  5. Examples GRANT SELECT ON Customers TO Michael No Michael can SELECT, but not INSERT or DELETE

  6. Examples GRANT SELECT ON Customers TO Michael WITH GRANT OPTIONS Michael can say this: GRANT SELECT ON Customers TO Yuppi Now Yuppi can SELECT on Customers

  7. Examples GRANT UPDATE (price) ON Product TO Leah Leah can update, but only Product.price, but not (say) Product.name

  8. Examples Customer(cid, name, address, …..)Orders(. . ., cid, …) cid=foreign key Bill has INSERT/UPDATE rights to Orders. BUT HE CAN’T INSERT ! (why ?) GRANT REFERENCES (cid) ON Customer TO Bill Now Bill can INSERT tuples into Orders

  9. Views and Security • David has SELECT rights on table Customers • John is a debt collector: should see the delinquent customers only: David says: CREATE VIEW DelinquentCustomers SELECT * FROM Customers WHERE balance < -1000 GRANT SELECT ON DelinquentCustomers TO John Views are an important security mechanism

  10. Revokation REVOKE [GRANT OPTION FOR] privileges ON object FROM users { RESTRICT | CASCADE } Administrator says: REVOKE SELECT ON Customers FROM David CASCADE John loses SELECT privileges on DelinquentCustomers

  11. Revocation Same privilege,same object,GRANT OPTION Joe: GRANT [….] TO Art …Art: GRANT [….] TO Bob …Bob: GRANT [….] TO Art …Joe: GRANT [….] TO Cal …Cal: GRANT [….] TO Bob …Joe: REVOKE [….] FROM Art CASCADE What happens ??

  12. Revocation Admin Revoke 0 1 Joe Art 2 4 3 Cal Bob 5 According to SQL everyone keeps the privilege

  13. New Challenges in Data Security • SQL Injection • Data collusion • View Leakage

  14. Three Attacks • SQL injection Chris Anley, Advanced SQL Injection In SQL Server Applications, www.ngssoftware.com • Latanya Sweeney’s finding • Leakage in Views

  15. Search order by date: Search order by date: Search order by date: 9/15/04’; drop table user; -- 9/15/04 SQL Injection Go to your favorite shopping Website and login: Normal use: Now this:

  16. SQL Injection • The DBMS works perfectly. So why is SQL injection possible so often ?

  17. Latanya Sweeney’s Finding • In Massachusetts, the Group Insurance Commission (GIC) is responsible for purchasing health insurance for state employees • GIC has to publish the data: GIC(zip, dob, sex, diagnosis, procedure, ...)

  18. Latanya Sweeney’s Finding • Sweeney paid $20 and bought the voter registration list for Cambridge Massachusetts: GIC(zip, dob, sex, diagnosis, procedure, ...) VOTER(name, party, ..., zip, dob, sex)

  19. Latanya Sweeney’s Finding • William Weld (former governor) lives in Cambridge, hence is in VOTER • 6 people in VOTER share his dob • only 3 of them were man (same sex) • Weld was the only one in that zip • Sweeney learned Weld’s medical records ! zip, dob, sex

  20. Latanya Sweeney’s Finding • All systems worked as specified, yet an important data has leaked • How do we protect against that ? Some of today’s research in data security address breachesthat happen even if all systems work correctly

  21. Leakage in Views Employee(name, department, phone) CREATE VIEW Emp SELECT name, department FROM Employee CREATE VIEW Phons SELECT department, phone FROM Employee Want to keep secretthe employees’ phonenumbers Does this work ? Important leakage !

  22. New Trend:Fine-grained Access Control • SQL provides only coarse-grained control • Hence, implemented by the application. • BIG PROBLEMS: • Security policies checked at each user interface • Easy to get it wrong: SQL injection !

  23. Policy Specification Language (Too) many exists. The good ones re-use a declarative querylanguage, e.g. SQL, Xpath, XQuery CREATE AUTHORIZATION VIEW PatientsForDoctors AS SELECT Patient.* FROM Patient, Treats, Doctor WHERE Patient.pid = Treats.pid and Treats.did = Doctor.did and Doctor.uid = %userId and %accessMode in (‘local’, ‘ssh’) Contextparameters [Oracle] [Oracle 7i], [Rizvi et al.2004] Several policy languages for XML

  24. Enforcementby query analysis/modification SELECT Patient.name, Patient.age FROM Patient WHERE Patient.disease = ‘flu’ SELECT Patient.name, Patient.age FROM Patient, Treats, Doctor WHERE Patient.disease = ‘flu’ and Patient.pid = Treats.pid and Treats.did = Doctor.did and Doctor.userID = %currentUser e.g. Oracle

  25. Semantics • The Truman Model: transform reality • ACCEPT all queries • REWRITE queries • Sometimes misleading results • The non-Truman model: reject queries • ACCEPT or REJECT queries • Execute query UNCHANGED • Subtle semantics: instance dependent or independent SELECT count(*)FROM Patients [Rizvi et al. SIGMOD 2004]

  26. Part II of this Course:Database Implementation Outline: • Buffer manager • Transaction manager (recovery, concurrency) • Operator execution • Optimizer

  27. What Should a DBMS Do? • Store large amounts of data • Process queries efficiently • Allow multiple users to access the database concurrently and safely. • Provide durability of the data. • How will we do all this??

  28. Generic Architecture Query update User/ Application Query compiler/optimizer Query execution plan Transaction commands Record, index requests Execution engine Index/record mgr. • Transaction manager: • Concurrency control • Logging/recovery Page commands Buffer manager Read/write pages Storage manager storage

  29. The Memory Hierarchy Main MemoryDisk Tape • 10-40 MB/S • transmission rates • 100s GB storage • average time to • access a block: • 10-15 msecs. • Need to consider • seek, rotation, • transfer times. • Keep records “close” • to each other. • 1.5 MB/S transfer rate • 280 GB typical • capacity • Only sequential access • Not for operational • data • Volatile • limited address • spaces • expensive • average access • time: • 10-100 ns Cache: access time 10 nano’s

  30. Main Memory • Fastest, most expensive • Today: 2GB are common on PCs • Many databases could fit in memory • New industry trend: Main Memory Database • E.g TimesTen, DataBlitz • Main issue is volatility • Still need to store on disk

  31. Secondary Storage • Disks • Slower, cheaper than main memory • Persistent !!! • Used with a main memory buffer

  32. How Much Storage for $200

  33. DB Buffer Management in a DBMS Page Requests from Higher Levels BUFFER POOL • Data must be in RAM for DBMS to operate on it! • Table of <frame#, pageid> pairs is maintained. • LRU is not always good. disk page free frame MAIN MEMORY DISK choice of frame dictated by replacement policy

  34. Buffer Manager • Manages buffer pool: the pool provides space for a limited • number of pages from disk. • Needs to decide on page replacement policy • LRU • Clock algorithm • Enables the higher levels of the DBMS to assume that the • needed data is in main memory.

  35. Buffer Manager Why not use the Operating System for the task?? - DBMS may be able to anticipate access patterns - Hence, may also be able to perform prefetching - DBMS needs the ability to force pages to disk.

  36. Tertiary Storage • Tapes or optical disks • Extremely slow: used for long term archiving only

  37. Tracks Arm movement Arm assembly The Mechanics of Disk Cylinder Mechanical characteristics: • Rotation speed (5400RPM) • Number of platters (1-30) • Number of tracks (<=10000) • Number of bytes/track(105) Spindle Disk head Sector Platters

  38. Disk Access Characteristics • Disk latency = time between when command is issued and when data is in memory • Disk latency = seek time + rotational latency • Seek time = time for the head to reach cylinder • 10ms – 40ms • Rotational latency = time for the sector to rotate • Rotation time = 10ms • Average latency = 10ms/2 • Transfer time = typically 40MB/s • Disks read/write one block at a time (typically 4kB)

  39. Average Seek Time Suppose we have N tracks, what is the average seek time ? • Getting from cylinder x to y takes time |x-y|

More Related