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

Download 462 b.
Hajmi462 b.

Lecture 16: Data Storage

  • Friday, November 5, 2004


  • SQL Security – 8.7

  • Part II: Database Implementation

  • Today: Data Storage

    • The memory hierarchy – 11.2
    • Disks – 11.3

Discretionary Access Control in SQL






Views and Security

  • David has SELECT rights on table Customers

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




New Challenges in Data Security

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

Leakage in Views

New Trend: Fine-grained Access Control

  • SQL provides only coarse-grained control

  • Hence, implemented by the application.


    • Security policies checked at each user interface
    • Easy to get it wrong: SQL injection !

Policy Specification Language

Enforcement by query analysis/modification


  • 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

Secondary Storage

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

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|

Do'stlaringiz bilan baham:

Ma'lumotlar bazasi mualliflik huquqi bilan himoyalangan ©fayllar.org 2017
ma'muriyatiga murojaat qiling