Transcript CST221: Database Systems (II) - WCU Computer Science

CST221: Database Systems
Dr. Zhen Jiang
Computer Science Department
West Chester University
West Chester, PA 19383
Outline

Overview
◦ Non-relational DB system
◦ NonSQL DB system
Injection
 Inference

◦ Role access control (UML)
◦ Perturbation

Design
◦ Models
◦ Encryption
Database System Overview
Integration
Administration
Security &
encryption
 Privacy & inference
 Transaction &
injection
 Sketching & hashing



Application
Programming
Interface (API)
integration
Traditional Database
The relation of key vs. non-key
 The relation between key and foreign key

◦ Intra-table relation
◦ Inter-table relation

E-R diagram
◦ http://www.cs.wcupa.edu/~zjiang/ER.pdf
◦ Any regularity?
 Arbitrary & Abrupt
◦ Ambiguity
 Sample of such ambiguity in normalization process
caused by the lack of background
Non-Relational Database

Data does not relate in the true sense
◦ e.g., Mongo, which handles document stores
or other content and/or metadata stores
NonSQL Database

A more clear structure
 e.g., Kobo, Playtika (mobile service)
 Distributed database system
 No need and not possible for a “join” operator






Fast third-party data aggregation
Fast caching for application objects
Globally distributed data repository
E-commerce and internet burstness
Game (data intensive applications)
Ad targeting (social networks)
Injection

Direct DB injection
◦ http://www.youtube.com/watch?v=v6bphRHH
4sM

Indirect DB injection
◦ http://www.irongeek.com/i.php?page=videos/
webgoat-sql-injection
You need a tool
for the trace of
transactions
interrupt each
transaction as you
debug and trace the
record of each
transaction
•
•
•
Internet is not safe
https://www.youtube.com/watch?v=SJgYdTc
kMBY
Even worse ( 2
min)https://www.youtube.com/watch?v=hcSs
s9BHPFo
Information Assurance (in product lifecycle)
• Definition (3 min)
https://www.youtube.com/watch?v=Wde_38_xNc
• Our solution, e.g., (5
min)https://www.youtube.com/watch?v=97Tnjr72
IoQ

Authorization
◦ Restrict access to data and restrict the actions that
people may take (when they access data).

Encryption
◦ Scramble data so that the data cannot be read.

Authentication
◦ Password check
◦ Key protection, not to protect everything!
◦ https://www.youtube.com/watch?v=3QnD2c4Xovk

Role based access control
Inference (aggregation)
Basically, inference occurs when users are
able to piece together (aggregate)
information to determine a fact that
should be protected.
 Role cheating

Flight ID
Cargo Hold
Contents
Classification
1254
A
Boots
Unclassified
1254
B
Atomic bomb
Top secret
1254
C
Butter
Unclassified
General Jones (who has a top security
clearance) requests information and
would see all three.
 Civilian Smith (who has no security
clearance) requests the data and would
see the following data:

Flight ID
Cargo Hold
Contents
Classification
1254
A
Boots
Unclassified
1254
C
Butter
Unclassified



When Smith sees that nothing is scheduled
for hold B on flight 1254, he might attempt
to insert the record, and his insertion will
fail due to the unique constraint on cargo
space availability.
He has all the data he needs to infer that
there is a secret shipment on flight.
He could then cross-reference the flight
information table to find out the source and
destination of the secret shipment and
various other information.
Poly-instantiation: allows different records
(hold B) to exist in the same table.
 Overbooking!


Other causes such as:
◦ Count of highly preferred customers
◦ Average salary

Problem is difficult
◦ Information?
 Content: what is critical?
◦ Path?
 Hold A-C, Hold B? Total space? Probing!

Existing solutions
◦ Limit access
 Role access control
 Too many restriction could seriously hinder the
functionality
22
23
◦ Perturbation
 Alter the data so that individual details are
accurate but overall generalization are
inaccurate.
 Include dummy data in the results returned by
the query unauthorized.
 Protect sensitive data, but also achieve
preservation of the properties of the dataset.
 Sketching with a probability of p.
 With probability p to use the original data
 With probability (1-p) to use a replacement
 Preservation
 Given each query f in the original table T with n
rows, build a re-constructible query f’ in the
revised table T’ (with n rows), so that the result
difference can be controlled in a limited range
with a probability of p.
 In other words, the expected number of rows
that get perturbation is n(1-p). For a domain
∆C, n(1-p)k rows will be expected to lie within
the available value range (k ∆C), k[1, 0].
 Among total nr rows observed from T’ in the value
range (k ∆C), subtracting the n(1-p)k rows, we have
the estimation for the number of unperturbed rows.
Scaled up by 1/p, we get the total number of original
rows (n0), as only a p fraction of rows were retained.
Security and Privacy





f’ = n0/n
[n-n0, n0]A = [n-nr, nr]
a=Pr(row T) vs. b=Pr(row in perturbed table T’)
Privacy breach, security threshold 
>a/b
b  b’ (sketch does not help to distinguish the cases)
Server Storage (with a) vs. Client retaining (with b)
OO Design for DB Systems

Injection, inference
◦ RBAC (role based access control)
◦ Use case
 http://www.cs.wcupa.edu/~zjiang/intro_uc.ppt
◦ Class design is needed for better maintaining the data
ownership
 http://www.cs.wcupa.edu/~zjiang/DB_OO_design.htm

Non-relational DB
◦ Activity pattern – prediction of future relation, e.g.,
credit card security

NonSQL DB
◦ Relations in structure for the use.
Models
Database role based
 Application role based
 Application function based
 Application role and function based
 Application table based

28
Model Based on Database Roles
Application authenticates application
users: maintain all users in a table
 Each user is assigned a role; roles have
privileges assigned to them
 A proxy user is needed to activate
assigned roles; all roles are assigned to the
proxy user
 Model and privileges are database
dependent

29
30

Implementation in SQL Server:
◦ Use application roles:
 Special roles you that are activated at the time of
authorization
 Require a password and cannot contain members
◦ Connect a user to the application role:
overrules user’s privileges
31

Implementation in SQL Server (continued):
◦
◦
◦
◦

Connect to database as the proxy user
Validate the user name and password
Retrieve the application role name
Activate the application role
Great article on app roles:
◦ SQL Server Security: Pros and Cons of Application Roles
By Brian Kelley
◦ http://www.sqlservercentral.com/articles/Security/sqlserve
rsecurityprosandconsofapplicationroles/1116/
32
Model Based on Application Roles
Application roles are mapped to real
business roles
 Application authenticates users
 Each user is assigned to an application
role; application roles are provided with
application privileges (read and write)

33
34

Implementation in SQL Server
◦ Create a database user
◦ Connect the application to the database using
this user
◦ Create stored procedures to perform all
database operations
35
Model Based on Application Functions
Application authenticates users
 Application is divided into functions
 Considerations:

◦
◦
◦
◦
Isolates application security from database
Passwords must be securely encrypted
Must use a real database user
Granular privileges require more effort during
implementation
36
37
Model Based on Application Roles
and Functions
Combination of models
 Application authenticates users
 Application is divided into functions
 Highly flexible model

38
39
Model Based on Application Tables
Depends on the application to
authenticate users
 Application provides privileges to the
user based on tables; not on a role or a
function
 User is assigned access privilege to each
table owned by the application owner

40

Privileges:
◦
◦
◦
◦
◦
◦
0 -no access
1 –read only
2 – read and add
3 –read, add, and modify
4 – read, add, modify, and delete
5 – read, add, modify, delete, and admin
41
42

Implementation in SQL Server:
◦ Grant authorization on application functions
to the end user
◦ Alter authorization table from the security
model based on database roles; incorporate
the table and access columns required to
support model
43
Data Encryption
Passwords should be kept confidential
and preferably encrypted
 Passwords should be compared
encrypted:

◦ Never decrypt the data
◦ Hash the passwords and compare the hashes
44