Lecture 16: Data Storage Friday, November 5, 2004 Outline sql security 7

Lecture 16: Data Storage

• Friday, November 5, 2004

Outline

• SQL Security – 8.7

• Part II: Database Implementation

• Today: Data Storage

• The memory hierarchy – 11.2
• Disks – 11.3

Discretionary Access Control in SQL

Examples

Examples

Examples

Examples

Examples

Views and Security

• David has SELECT rights on table Customers

• John is a debt collector: should see the delinquent customers only:

Revokation

Revocation

Revocation

New Challenges in Data Security

• SQL Injection

• Data collusion

• View Leakage

Three Attacks

• SQL injection

• Chris Anley, Advanced SQL Injection In SQL Server Applications, www.ngssoftware.com

• Latanya Sweeney’s finding

• Leakage in Views

SQL Injection

SQL Injection

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

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:

Latanya Sweeney’s Finding

• Sweeney paid $20 and bought the voter registration list for Cambridge Massachusetts:

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 !

Latanya Sweeney’s Finding

• All systems worked as specified, yet an important data has leaked

• How do we protect against that ?

Leakage in Views

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 !

Policy Specification Language

Enforcement by query analysis/modification

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

Part II of this Course: Database Implementation

• Outline:

• Buffer manager

• Transaction manager (recovery, concurrency)

• Operator execution

• Optimizer

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

Generic Architecture

The Memory Hierarchy

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

Secondary Storage

• Disks

• Slower, cheaper than main memory

• Persistent !!!

• Used with a main memory buffer

How Much Storage for $200

Buffer Management in a DBMS

• Data must be in RAM for DBMS to operate on it!

• Table of pairs is maintained.

• LRU is not always good.

Buffer Manager

Buffer Manager

Tertiary Storage

• Tapes or optical disks

• Extremely slow: used for long term archiving only

The Mechanics of Disk

• Mechanical characteristics:

• Rotation speed (5400RPM)

• Number of platters (1-30)

• Number of tracks (<=10000)

• Number of bytes/track(105)

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)

Average Seek Time

• Suppose we have N tracks, what is the average seek time ?

• Getting from cylinder x to y takes time |x-y|