Transcript Data Security

Lecture 16: Data Storage
Friday, November 5, 2004
1
Outline
SQL Security – 8.7
Part II: Database Implementation
Today: Data Storage
– The memory hierarchy – 11.2
– Disks – 11.3
2
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
3
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’
4
Examples
GRANT SELECT ON Customers TO Michael
No Michael can SELECT, but not INSERT or DELETE
5
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
6
Examples
GRANT UPDATE (price) ON Product TO Leah
Leah can update, but only Product.price, but not (say) Product.name
7
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
8
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
9
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
10
Revocation
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
Same privilege,
same object,
GRANT OPTION
What happens ??
11
Revocation
Admin
Revoke
0
Joe
1
4
Art
2
3
Cal
5
Bob
According to SQL everyone keeps the privilege
12
New Challenges in Data Security
• SQL Injection
• Data collusion
• View Leakage
13
Three Attacks
• SQL injection
Chris Anley, Advanced SQL Injection In SQL
Server Applications, www.ngssoftware.com
• Latanya Sweeney’s finding
• Leakage in Views
14
SQL Injection
Go to your favorite shopping Website and login:
Search order by date:
Normal use:
Search order by date:
9/15/04
Now this:
Search order by date:
9/15/04’; drop table user; -15
SQL Injection
• The DBMS works perfectly. So why is
SQL injection possible so often ?
16
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, ...)
17
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)
18
Latanya Sweeney’s Finding
zip, dob, sex
• 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 !
19
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 breaches
that happen even if all systems work correctly
20
Leakage in Views
Employee(name, department, phone)
CREATE VIEW Emp
SELECT name, department
FROM Employee
CREATE VIEW Phons
SELECT department, phone
FROM Employee
Important leakage !
Want to keep secret
the employees’ phone
numbers
Does this work ?
21
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 !
22
Policy Specification Language
(Too) many exists. The good ones re-use a declarative query
language, 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’)
Context
parameters
[Oracle 7i], [Rizvi et al.2004]
[Oracle]
23
Several policy languages for XML
Enforcement
by 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
24
Semantics
• The Truman Model: transform reality
– ACCEPT all queries
– REWRITE queries
– Sometimes misleading results
SELECT count(*)
FROM Patients
• The non-Truman model: reject queries
– ACCEPT or REJECT queries
– Execute query UNCHANGED
– Subtle semantics: instance dependent or independent
[Rizvi et al. SIGMOD 2004]
25
Part II of this Course:
Database Implementation
Outline:
• Buffer manager
• Transaction manager (recovery,
concurrency)
• Operator execution
• Optimizer
26
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??
27
User/
Application
Transaction
commands
Query
update
Generic Architecture
Query compiler/optimizer
Record,
index
requests
Transaction manager:
•Concurrency control
•Logging/recovery
Read/write
pages
Execution engine
Query execution
plan
Index/record mgr.
Page
commands
Buffer manager
Storage manager
storage
28
The Memory Hierarchy
Main Memory
Disk
Tape
• 10-40 MB/S
• 1.5 MB/S transfer rate
transmission rates • 280 GB typical
• 100s GB storage
capacity
• average time to
• Only sequential access
access a block:
• Not for operational
10-15 msecs.
data
• Need to consider
seek, rotation,
transfer times.
Cache:
• Keep records “close”
access time 10 nano’s
to each other.
29
•Volatile
•limited address
spaces
• expensive
• average access
time:
10-100 ns
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
30
Secondary Storage
•
•
•
•
Disks
Slower, cheaper than main memory
Persistent !!!
Used with a main memory buffer
31
How Much Storage for $200
32
Buffer Management in a DBMS
Page Requests from Higher Levels
BUFFER POOL
disk page
free frame
MAIN MEMORY
DISK
DB
choice of frame dictated
by replacement policy
• Data must be in RAM for DBMS to operate on it!
• Table of <frame#, pageid> pairs is maintained.
• LRU is not always good.
33
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.
34
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.
35
Tertiary Storage
• Tapes or optical disks
• Extremely slow: used for long term
archiving only
36
The Mechanics of Disk
Cylinder
Mechanical characteristics:
• Rotation speed (5400RPM)
• Number of platters (1-30)
• Number of tracks (<=10000)
• Number of bytes/track(105)
Disk head
Spindle
Tracks
Sector
Arm movement
Arm assembly
Platters
37
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)
38
Average Seek Time
Suppose we have N tracks, what is the
average seek time ?
• Getting from cylinder x to y takes time |x-y|
1 N N
| x  y | dydx 
2 0 0
N
N
1 N x
( ( x  y )dy   ( y  x)dy )dx 
x
N 2 0 0
1 N x 2 ( N  x) 2
( 
)dx 
2 0
N
2
2
1 N3 N3
N
(

)

N2 6
6
3
39