1/56
Week 9 Workshop – Database Security
2/56
Qing Wang
Website: http:// users.cecs.anu.edu.au/∼u5170295/
https:// graphlabanu.github.io/ website/
Zoom drop-in session: Tuesday 2pm-3pm (Week 10 to Week 12)
Research areas: Data management and analytics
Data mining
Deep learning on graphs
Graph algorithms.
http://users.cecs.anu.edu.au/~u5170295/
https://graphlabanu.github.io/website/
3/56
House Keeping
Lab 8 (Database programming) in Week 10 is optional – three options. A
sign-up page is available on Wattle.
Assignment 2 (Database Theory) is due at 23:59, Oct 12.
4/56
Week 9 Workshop – Database Security
5/56
“Hardware is easy to protect: lock it in a room, chain it to a desk, or
buy a spare. Information poses more of a problem. It can exist in
more than one place; be transported halfway across the planet in
seconds; and be stolen without your knowledge.”
– Bruce Schneier
6/56
Data Breaches
In 80% of cases, attackers are able to compromise an organization within
minutes. However, in almost 60% of cases, it takes years to learn that they
have been breached.1
The breakdown of Public breaches across patterns has
remained relatively constant for the last few years with
Cyber-Espionage, Privilege Misuse and Miscellaneous Errors
usually in the top three. Approximately 41% of the breaches
in this vertical were related to espionage, which should
come as no surprise, since it stands to reason that other
governments want to know what our government is thinking
regarding important issues, such as aliens, crop circles
and microwave surveillance. As is generally the case when
external espionage is conducted, the actors lean heavily
towards the state-affiliated side of the spectrum as shown in
Figure 26.
Figure 26: External actor varieties within Public breaches (n=113)
At this point the reader might wonder, “Am I reading the
Manufacturing section again”? No, you aren’t but there are
some very definite similarities between the two. Both deal in
secrets, and both appeal to a certain type of criminal who, by
the way, tends to use very similar tactics.
Speaking of similarities, let us now turn to an interesting
difference. For Manufacturing the actor was 93% external
and they went after trade secrets 91% of the time. Here,
we see a much greater number of internal actors making
up a sizeable 40%16, and the data variety was roughly
equal between trade secrets and personal information.
The insiders represented here in many instances fall into
scenarios such as a police officer who misuses his or her
ability to access criminal databases inappropriately. This
scenario helps to explain the 13% of breaches with fun/
curiosity as the motive.
Finding your inner breach
As a rule, the government is only in a hurry if you owe them
something. Otherwise their mills may grind fine, but they
grind very slowly. Certainly, it would appear that is the case
when it comes to breach discovery. In almost 60% of cases
in Figure 27 (when discovery is known) it takes them years to
learn that they have been breached. This may be due to the
high number of espionage-related attacks, which often aim to
cloak themselves in the victim network and remain hidden for
a lengthy period of time. Or, it may be more representative of
smaller government agencies that do not have the resources
to spot the problem sooner. Either way, it is bad news for us
humble citizens.
Figure 27: Time-to-discovery within Public breaches (n=66)
90
7
6
5
5
Organized
crime
Unaffiliated
Activist
Nation-state
State-
affiliated
Breaches
B
re
a
c
h
e
s
39
98
3 4
S
e
c
o
n
d
s
M
in
u
te
s
H
o
u
rs
D
a
ys
W
e
e
k
s
M
o
n
th
s
Y
e
a
rs
3
Things to consider:
Is that my data?—Know your own data, particularly the
more sensitive type. Know where it resides, who has
access to it, and who, in fact, does access it.
Exits are located above the wings—To prevent
your data from flying out of your organization, set up
controls to monitor data egress. If data leaves, you
need to know about it and where it is headed.
Know your enemy—The public sector includes
everything from organizations responsible for national
security to local zoning boards. Understand what
type of threat actor will be most interested in your
department.
16 The 40% representation of internal actors is not all malicious activity—
about half of insider representation stems from errors.
2017 Data Breach Investigations Report
29
Time-to-discovery within Public breaches (n=66)
1
Verizon 2016&2017 Data Breach Investigation Reports
7/56
Data Breaches
June 2019, ANU community was notified of a data breach.
“It’s shocking in its sophistication”
9 The Australian National University
Figure 2: Attack timeline
Spearphishing
against senior
officer
9 Nov 12−14 Nov 16 Nov 20−21 Nov 22 Nov 23 Nov 25−27 Nov
Webserver
compromised
& C2 via TOR
Creation of attack
station one
Exfiltration of
network data
Compromise of
legacy server
Creation of virtual
machines on
attack station one
27 Nov 29 Nov 21 Dec29 Nov−
13 Dec
21 Dec−
Mar ‘19
Access gained
to Enterprise
Services Domain
Clean-up operations
and loss of attack
station one
4th spearphishing
campaign and loss
of attack station two
2nd spearphishing
campaign
3rd spearphishing
campaign
Attack station
two created and
exfiltration resumes
C2 intrusion
and second
intrusion
attempt in
Feb 2019
13−20 Dec
Incident report on the breach of The Australian National University’s administrative systems
“While we cannot confirm exactly what data was taken, we know it was
much less than the 19 years’ worth we originally reported”
7/56
Data Breaches
June 2019, ANU community was notified of a data breach.
“It’s shocking in its sophistication”
9 The Australian National University
Figure 2: Attack timeline
Spearphishing
against senior
officer
9 Nov 12−14 Nov 16 Nov 20−21 Nov 22 Nov 23 Nov 25−27 Nov
Webserver
compromised
& C2 via TOR
Creation of attack
station one
Exfiltration of
network data
Compromise of
legacy server
Creation of virtual
machines on
attack station one
27 Nov 29 Nov 21 Dec29 Nov−
13 Dec
21 Dec−
Mar ‘19
Access gained
to Enterprise
Services Domain
Clean-up operations
and loss of attack
station one
4th spearphishing
campaign and loss
of attack station two
2nd spearphishing
campaign
3rd spearphishing
campaign
Attack station
two created and
exfiltration resumes
C2 intrusion
and second
intrusion
attempt in
Feb 2019
13−20 Dec
Incident report on the breach of The Australian National University’s administrative systems
“While we cannot confirm exactly what data was taken, we know it was
much less than the 19 years’ worth we originally reported”
8/56
Objectives of Database Security
Threats to Databases
Confidentiality Integrity
Database
security
Availability
Data should only be
shown to people who
are allowed to see it.
Data should only be
modified by people who
are allowed to modify it.
If someone is allowed to
see or modify data, they
should be able to do so.
9/56
Database Security – Examples
1 A health-care information system
– A patient’s medical information should not be improperly disclosed.
– A patient’s medical information should be correct.
– A patient’s medical information can be accessed when needed for
treatment.
2 A military system
– The target of a missile cannot be given to an unauthorized user.
– The target of a missile cannot be arbitrarily modified.
– The target of a missile can be accessed when needed.
9/56
Database Security – Examples
1 A health-care information system
– A patient’s medical information should not be improperly disclosed.
– A patient’s medical information should be correct.
– A patient’s medical information can be accessed when needed for
treatment.
2 A military system
– The target of a missile cannot be given to an unauthorized user.
– The target of a missile cannot be arbitrarily modified.
– The target of a missile can be accessed when needed.
9/56
Database Security – Examples
1 A health-care information system
– A patient’s medical information should not be improperly disclosed.
– A patient’s medical information should be correct.
– A patient’s medical information can be accessed when needed for
treatment.
2 A military system
– The target of a missile cannot be given to an unauthorized user.
– The target of a missile cannot be arbitrarily modified.
– The target of a missile can be accessed when needed.
9/56
Database Security – Examples
1 A health-care information system
– A patient’s medical information should not be improperly disclosed.
– A patient’s medical information should be correct.
– A patient’s medical information can be accessed when needed for
treatment.
2 A military system
– The target of a missile cannot be given to an unauthorized user.
– The target of a missile cannot be arbitrarily modified.
– The target of a missile can be accessed when needed.
9/56
Database Security – Examples
1 A health-care information system
– A patient’s medical information should not be improperly disclosed.
– A patient’s medical information should be correct.
– A patient’s medical information can be accessed when needed for
treatment.
2 A military system
– The target of a missile cannot be given to an unauthorized user.
– The target of a missile cannot be arbitrarily modified.
– The target of a missile can be accessed when needed.
9/56
Database Security – Examples
1 A health-care information system
– A patient’s medical information should not be improperly disclosed.
– A patient’s medical information should be correct.
– A patient’s medical information can be accessed when needed for
treatment.
2 A military system
– The target of a missile cannot be given to an unauthorized user.
– The target of a missile cannot be arbitrarily modified.
– The target of a missile can be accessed when needed.
9/56
Database Security – Examples
1 A health-care information system
– A patient’s medical information should not be improperly disclosed.
– A patient’s medical information should be correct.
– A patient’s medical information can be accessed when needed for
treatment.
2 A military system
– The target of a missile cannot be given to an unauthorized user.
– The target of a missile cannot be arbitrarily modified.
– The target of a missile can be accessed when needed.
9/56
Database Security – Examples
1 A health-care information system
– A patient’s medical information should not be improperly disclosed.
– A patient’s medical information should be correct.
– A patient’s medical information can be accessed when needed for
treatment.
2 A military system
– The target of a missile cannot be given to an unauthorized user.
– The target of a missile cannot be arbitrarily modified.
– The target of a missile can be accessed when needed.
10/56
Database Security – Core Services
Confidentiality
Enforced by access control mechanisms
Integrity
Enforced by access control mechanisms and integrity constraints
specified on schemas
Availability
Enforced by recovery and concurrency control mechanisms and
detection techniques for DoS attacks
Some further services
Encryption: to protect data when being transmitted across systems
and when being stored on secondary storage
Query authentication: to ensure query result is correct by using
signature mechanisms and data structures
…
11/56
Database Security – Core Services
Confidentiality
– E.g. enforced by access control mechanisms
Integrity
– E.g. enforced by access control mechanisms and integrity
constraints specified on schemas
Availability
– E.g. enforced by recovery and concurrency control mechanisms
Some further services
Encryption: to protect data when being transmitted across systems
and when being stored on secondary storage
Query authentication: to ensure query result is correct by using
signature mechanisms and data structures
…
11/56
Database Security – Core Services
Confidentiality
– E.g. enforced by access control mechanisms
Integrity
– E.g. enforced by access control mechanisms and integrity
constraints specified on schemas
Availability
– E.g. enforced by recovery and concurrency control mechanisms
Some further services
Encryption: to protect data when being transmitted across systems
and when being stored on secondary storage
Query authentication: to ensure query result is correct by using
signature mechanisms and data structures
…
11/56
Database Security – Core Services
Confidentiality
– E.g. enforced by access control mechanisms
Integrity
– E.g. enforced by access control mechanisms and integrity
constraints specified on schemas
Availability
– E.g. enforced by recovery and concurrency control mechanisms
Some further services
Encryption: to protect data when being transmitted across systems
and when being stored on secondary storage
Query authentication: to ensure query result is correct by using
signature mechanisms and data structures
…
12/56
Database Security – Core Services
Confidentiality
– E.g. enforced by access control mechanisms
Integrity
– E.g. enforced by access control mechanisms and integrity
constraints specified on schemas
Availability
– E.g. enforced by recovery and concurrency control mechanisms
Some further services
Encryption: to protect data when being transmitted across systems
and when being stored on secondary storage
Query authentication: to ensure a query result is correct by using
signature mechanisms and data structures
…
13/56
Access Control Mechanisms
14/56
Access Control Mechanisms
Three types:
1 Discretionary access control (DAC)
2 Mandatory access control (MAC)
3 Role-based access control (RBAC)
15/56
Database Security – DAC
Bob Alice
Your objects at your own discretion!
Grant privileges
Revoke privileges
Delegate privileges
Bob Alice
16/56
Granting/Revoking/Delegating Privileges
GRANT privileges ON object TO users [WITH GRANT OPTION]
REVOKE [GRANT OPTION FOR] privileges ON object FROM users
[RESTRICT|CASCADE]
Possible privileges:
SELECT
INSERT and INSERT(column)
UPDATE and UPDATE(column)
DELETE
REFERENCES(column)
…
16/56
Granting/Revoking/Delegating Privileges
GRANT privileges ON object TO users [WITH GRANT OPTION]
REVOKE [GRANT OPTION FOR] privileges ON object FROM users
[RESTRICT|CASCADE]
Possible privileges:
SELECT
INSERT and INSERT(column)
UPDATE and UPDATE(column)
DELETE
REFERENCES(column)
…
16/56
Granting/Revoking/Delegating Privileges
GRANT privileges ON object TO users [WITH GRANT OPTION]
REVOKE [GRANT OPTION FOR] privileges ON object FROM users
[RESTRICT|CASCADE]
Possible privileges:
SELECT
INSERT and INSERT(column)
UPDATE and UPDATE(column)
DELETE
REFERENCES(column)
…
17/56
Granting/Revoking/Delegating Privileges
The privileges of an object can be given to a user with or without the GRANT
OPTION
GRANT SELECT ON Supplier TO Bob;
GRANT SELECT ON Supplier TO Bob WITH GRANT OPTION;
The privileges of an object can be taken away from a user. It is also possible
to only revoke the GRANT OPTION on a privilege.
REVOKE SELECT ON Supplier FROM Bob;
REVOKE GRANT OPTION FOR SELECT ON Supplier FROM Bob;
17/56
Granting/Revoking/Delegating Privileges
The privileges of an object can be given to a user with or without the GRANT
OPTION
GRANT SELECT ON Supplier TO Bob;
GRANT SELECT ON Supplier TO Bob WITH GRANT OPTION;
The privileges of an object can be taken away from a user. It is also possible
to only revoke the GRANT OPTION on a privilege.
REVOKE SELECT ON Supplier FROM Bob;
REVOKE GRANT OPTION FOR SELECT ON Supplier FROM Bob;
18/56
Question
In which situations a user can grant a privilege on an object to other users?
(1) The user is the owner of the object.
(2) The user has the privilege on the object.
(3) The user is a superuser of the database.
(4) The user has received the privilege with the GRANT OPTION from the
owner of the object.
19/56
Example – Granting Privileges
Alice owns table EMPLOYEE:
(Alice): GRANT SELECT, INSERT ON Employee TO Bob WITH GRANT OPTION;
(Alice): GRANT SELECT ON Employee TO Jane WITH GRANT OPTION;
(Alice): GRANT INSERT ON Employee TO Jane;
(Bob): GRANT UPDATE ON Employee TO Tom WITH GRANT OPTION;
(Jane): GRANT SELECT, INSERT ON Employee TO Tom;
Questions:
1 What privilege(s) does Jane receive?
2 What privilege(s) does Tom receive?
19/56
Example – Granting Privileges
Alice owns table EMPLOYEE:
(Alice): GRANT SELECT, INSERT ON Employee TO Bob WITH GRANT OPTION;
(Alice): GRANT SELECT ON Employee TO Jane WITH GRANT OPTION;
(Alice): GRANT INSERT ON Employee TO Jane;
(Bob): GRANT UPDATE ON Employee TO Tom WITH GRANT OPTION;
(Jane): GRANT SELECT, INSERT ON Employee TO Tom;
Questions:
1 What privilege(s) does Jane receive?
2 What privilege(s) does Tom receive?
19/56
Example – Granting Privileges
Alice owns table EMPLOYEE:
(Alice): GRANT SELECT, INSERT ON Employee TO Bob WITH GRANT OPTION;
(Alice): GRANT SELECT ON Employee TO Jane WITH GRANT OPTION;
(Alice): GRANT INSERT ON Employee TO Jane;
(Bob): GRANT UPDATE ON Employee TO Tom WITH GRANT OPTION;
(Jane): GRANT SELECT, INSERT ON Employee TO Tom;
Questions:
1 What privilege(s) does Jane receive?
2 What privilege(s) does Tom receive?
20/56
Example – Granting Privileges
Alice owns table EMPLOYEE:
(Alice): GRANT SELECT, INSERT ON Employee TO Bob WITH GRANT OPTION;
(Alice): GRANT SELECT ON Employee TO Jane WITH GRANT OPTION;
(Alice): GRANT INSERT ON Employee TO Jane;
(Bob): GRANT UPDATE ON Employee TO Tom WITH GRANT OPTION;
(Jane): GRANT SELECT, INSERT ON Employee TO Tom;
Can these commands be executed?
21/56
Example – Granting Privileges
Alice owns table EMPLOYEE:
(Alice): GRANT SELECT, INSERT ON Employee TO Bob WITH GRANT OPTION;
(Alice): GRANT SELECT ON Employee TO Jane WITH GRANT OPTION;
(Alice): GRANT INSERT ON Employee TO Jane;
(Bob): GRANT UPDATE ON Employee TO Tom WITH GRANT OPTION;
(Jane): GRANT SELECT, INSERT ON Employee TO Tom;
Can these commands be executed?
– The first three are fully executed.
– The fourth one is not executed, because Bob does not have the
UPDATE privilege on the table.
– The fifth one is partially executed because Jane has the SELECT and
INSERT privileges but no GRANT OPTION for INSERT. Therefore, Tom
only receives the SELECT privilege.
21/56
Example – Granting Privileges
Alice owns table EMPLOYEE:
(Alice): GRANT SELECT, INSERT ON Employee TO Bob WITH GRANT OPTION;
(Alice): GRANT SELECT ON Employee TO Jane WITH GRANT OPTION;
(Alice): GRANT INSERT ON Employee TO Jane;
(Bob): GRANT UPDATE ON Employee TO Tom WITH GRANT OPTION;
(Jane): GRANT SELECT, INSERT ON Employee TO Tom;
Can these commands be executed?
– The first three are fully executed.
– The fourth one is not executed, because Bob does not have the
UPDATE privilege on the table.
– The fifth one is partially executed because Jane has the SELECT and
INSERT privileges but no GRANT OPTION for INSERT. Therefore, Tom
only receives the SELECT privilege.
21/56
Example – Granting Privileges
Alice owns table EMPLOYEE:
(Alice): GRANT SELECT, INSERT ON Employee TO Bob WITH GRANT OPTION;
(Alice): GRANT SELECT ON Employee TO Jane WITH GRANT OPTION;
(Alice): GRANT INSERT ON Employee TO Jane;
(Bob): GRANT UPDATE ON Employee TO Tom WITH GRANT OPTION;
(Jane): GRANT SELECT, INSERT ON Employee TO Tom;
Can these commands be executed?
– The first three are fully executed.
– The fourth one is not executed, because Bob does not have the
UPDATE privilege on the table.
– The fifth one is partially executed because Jane has the SELECT and
INSERT privileges but no GRANT OPTION for INSERT. Therefore, Tom
only receives the SELECT privilege.
21/56
Example – Granting Privileges
Alice owns table EMPLOYEE:
(Alice): GRANT SELECT, INSERT ON Employee TO Bob WITH GRANT OPTION;
(Alice): GRANT SELECT ON Employee TO Jane WITH GRANT OPTION;
(Alice): GRANT INSERT ON Employee TO Jane;
(Bob): GRANT UPDATE ON Employee TO Tom WITH GRANT OPTION;
(Jane): GRANT SELECT, INSERT ON Employee TO Tom;
Can these commands be executed?
– The first three are fully executed.
– The fourth one is not executed, because Bob does not have the
UPDATE privilege on the table.
– The fifth one is partially executed because Jane has the SELECT and
INSERT privileges but no GRANT OPTION for INSERT. Therefore, Tom
only receives the SELECT privilege.
22/56
Granting/Revoking/Delegating Privileges
A user can only revoke privileges that the user has granted earlier, with two
optional keywords in the REVOKE command:
CASCADE: revoking the privilege from a specified user also revokes the
privileges from all users who received the privilege from that user.
RESTRICT: revoking the privilege only from a specified user.
Possible implementations:
(1) Causing an error message in some DBMS if the revoked
privilege is still delegated;
(2) Revoking the privilege from the specified user in any case.
If a user receives a certain privilege from multiple sources, and the user
would lose the privilege only after all sources revoke this privilege.
23/56
Example – Revoking Privileges
Again, Alice owns table EMPLOYEE:
(Alice): GRANT SELECT ON Employee TO Bob WITH GRANT OPTION;
(Alice): GRANT SELECT ON Employee TO Jane WITH GRANT OPTION;
(Bob): GRANT SELECT ON Employee TO Tom;
(Jane): GRANT SELECT ON Employee TO Tom;
(Bob): REVOKE SELECT ON Employee FROM Tom;
Will Tom lose the SELECT privilege on EMPLOYEE?
– Tom will still hold the SELECT privilege on EMPLOYEE, since he has
independently obtained such privilege from Jane.
23/56
Example – Revoking Privileges
Again, Alice owns table EMPLOYEE:
(Alice): GRANT SELECT ON Employee TO Bob WITH GRANT OPTION;
(Alice): GRANT SELECT ON Employee TO Jane WITH GRANT OPTION;
(Bob): GRANT SELECT ON Employee TO Tom;
(Jane): GRANT SELECT ON Employee TO Tom;
(Bob): REVOKE SELECT ON Employee FROM Tom;
Will Tom lose the SELECT privilege on EMPLOYEE?
– Tom will still hold the SELECT privilege on EMPLOYEE, since he has
independently obtained such privilege from Jane.
24/56
Example – Revoking Privileges
Again, Alice owns table EMPLOYEE:
(Alice): GRANT SELECT ON Employee TO Bob WITH GRANT OPTION;
(Alice): GRANT SELECT ON Employee TO Jane WITH GRANT OPTION;
(Bob): GRANT SELECT ON Employee TO Tom;
(Jane): GRANT SELECT ON Employee TO Tom;
(Alice): REVOKE SELECT ON Employee FROM Bob CASCADE;
Will Tom lose the SELECT privilege on EMPLOYEE?
25/56
Example – Revoking Privileges
Again, Alice owns table EMPLOYEE:
(Alice): GRANT SELECT ON Employee TO Bob WITH GRANT OPTION;
(Alice): GRANT SELECT ON Employee TO Jane WITH GRANT OPTION;
(Bob): GRANT SELECT ON Employee TO Tom;
(Jane): GRANT SELECT ON Employee TO Tom;
(Alice): REVOKE SELECT ON Employee FROM Bob CASCADE;
Will Tom lose the SELECT privilege on EMPLOYEE?
– Tom will lose the SELECT privilege on EMPLOYEE.
25/56
Example – Revoking Privileges
Again, Alice owns table EMPLOYEE:
(Alice): GRANT SELECT ON Employee TO Bob WITH GRANT OPTION;
(Alice): GRANT SELECT ON Employee TO Jane WITH GRANT OPTION;
(Bob): GRANT SELECT ON Employee TO Tom;
(Jane): GRANT SELECT ON Employee TO Tom;
(Alice): REVOKE SELECT ON Employee FROM Bob CASCADE;
Will Tom lose the SELECT privilege on EMPLOYEE?
– Tom will lose the SELECT privilege on EMPLOYEE.
26/56
Delegating Privileges – Propagation
There are techniques to limit the propagation of privileges.
Limiting horizontal propagation: limits that an account given the
GRANT OPTION can grant the privilege to at most n other accounts;
Limiting vertical propagation: limits the depth of the granting
privileges.
How can we keep track of privilege propagation?
27/56
Privilege Propagation
tuna owns:
CITY(name, state, population)
STATE(name, abbreviation, capital, area, population)
The following commands are executed in order:
(tuna): GRANT SELECT, UPDATE ON City TO shark WITH GRANT OPTION;
(tuna): GRANT SELECT ON City TO minnow;
(tuna): GRANT SELECT ON State TO shark, minnow WITH GRANT OPTION;
(shark): GRANT SELECT ON State TO starfish WITH GRANT OPTION;
(shark): GRANT UPDATE (population) ON City TO starfish;
(starfish): GRANT SELECT ON State TO squid;
(shark): …
28/56
Privilege Propagation
A grant graph can be used to keep track of privilege propagation.
tuna
SELECT, CITY
(grant)
tuna
UPDATE, CITY
(grant)
tuna
SELECT, STATE
(grant)
tuna
UPDATE, STATE
(grant)
minnow
SELECT, CITY
shark
SELECT, STATE
(grant)
minnow
SELECT, STATE
(grant)
starfish
UPDATE, CITY(population)
shark
UPDATE, CITY
(grant)
shark
SELECT, CITY
(grant)
starfish
SELECT, STATE
(grant)
squid
SELECT, STATE
29/56
(tuna): GRANT SELECT, UPDATE ON City TO shark WITH GRANT OPTION;
(tuna): GRANT SELECT ON City TO minnow;
(tuna): GRANT SELECT ON State TO shark, minnow WITH GRANT OPTION;
(shark): GRANT SELECT ON State TO starfish WITH GRANT OPTION;
(shark): GRANT UPDATE (population) ON City TO starfish;
(starfish): GRANT SELECT ON State TO squid;
tuna
SELECT, CITY
(grant)
tuna
UPDATE, CITY
(grant)
tuna
SELECT, STATE
(grant)
tuna
UPDATE, STATE
(grant)
minnow
SELECT, CITY
shark
SELECT, STATE
(grant)
minnow
SELECT, STATE
(grant)
starfish
UPDATE, CITY(population)
shark
UPDATE, CITY
(grant)
shark
SELECT, CITY
(grant)
starfish
SELECT, STATE
(grant)
squid
SELECT, STATE
30/56
Using Views
CREATE VIEW view name AS
SELECT attribute list
FROM table list
…
Views can be used to create a “window” on a collection of data that is
appropriate for some users to access.
Some examples:
1 The owner A of a relation R wants to give a user B read
access to some columns of R. A can create a view V1 that
includes only those columns.
2 The owner A of a relation R wants to give a user B read
access to some rows of R. A can create view V2 that
selects only those rows from R.
31/56
Using Views
EXAMS(CourseID, StudtID, Grade, Date)
(Tom): CREATE VIEW HardCourses AS
SELECT CourseID, AVG(Grade) AS Difficulty FROM Exams
GROUP BY CourseID
Having AVG(Grade)≤50;
(Tom): CREATE VIEW AllCourses AS
SELECT CourseID, Grade FROM Exams;
32/56
Database Security – DAC
Bob Alice
Your objects at your own discretion!
Grant privileges
Revoke privileges
Delegate privileges
Bob Alice
– GRANT SELECT ON tableB TO Alice;
– REVOKE SELECT ON tableB FROM Alice;
– GARNT SELECT ON tableB TO Alice
WITH GRANT OPTION;
– REVOKE GRANT OPTION FOR SELECT
ON tableB FROM Alice;
– GRANT SELECT ON tableA TO Bob;
– REVOKE SELECT ON tableA FROM Bob;
– GRANT SELECT ON tableB TO Tom;
– REVOKE SELECT ON tableB FROM Tom;
32/56
Database Security – DAC
Bob Alice
Your objects at your own discretion!
Grant privileges
Revoke privileges
Delegate privileges
Bob Alice
– GRANT SELECT ON tableB TO Alice;
– REVOKE SELECT ON tableB FROM Alice;
– GARNT SELECT ON tableB TO Alice
WITH GRANT OPTION;
– REVOKE GRANT OPTION FOR SELECT
ON tableB FROM Alice;
– GRANT SELECT ON tableA TO Bob;
– REVOKE SELECT ON tableA FROM Bob;
– GRANT SELECT ON tableB TO Tom;
– REVOKE SELECT ON tableB FROM Tom;
32/56
Database Security – DAC
Bob Alice
Your objects at your own discretion!
Grant privileges
Revoke privileges
Delegate privileges
Bob Alice
– GRANT SELECT ON tableB TO Alice;
– REVOKE SELECT ON tableB FROM Alice;
– GARNT SELECT ON tableB TO Alice
WITH GRANT OPTION;
– REVOKE GRANT OPTION FOR SELECT
ON tableB FROM Alice;
– GRANT SELECT ON tableA TO Bob;
– REVOKE SELECT ON tableA FROM Bob;
– GRANT SELECT ON tableB TO Tom;
– REVOKE SELECT ON tableB FROM Tom;
32/56
Database Security – DAC
Bob Alice
Your objects at your own discretion!
Grant privileges
Revoke privileges
Delegate privileges
Bob Alice
– GRANT SELECT ON tableB TO Alice;
– REVOKE SELECT ON tableB FROM Alice;
– GARNT SELECT ON tableB TO Alice
WITH GRANT OPTION;
– REVOKE GRANT OPTION FOR SELECT
ON tableB FROM Alice;
– GRANT SELECT ON tableA TO Bob;
– REVOKE SELECT ON tableA FROM Bob;
– GRANT SELECT ON tableB TO Tom;
– REVOKE SELECT ON tableB FROM Tom;
32/56
Database Security – DAC
Bob Alice
Your objects at your own discretion!
Grant privileges
Revoke privileges
Delegate privileges
Bob Alice
– GRANT SELECT ON tableB TO Alice;
– REVOKE SELECT ON tableB FROM Alice;
– GARNT SELECT ON tableB TO Alice
WITH GRANT OPTION;
– REVOKE GRANT OPTION FOR SELECT
ON tableB FROM Alice;
– GRANT SELECT ON tableA TO Bob;
– REVOKE SELECT ON tableA FROM Bob;
– GRANT SELECT ON tableB TO Tom;
– REVOKE SELECT ON tableB FROM Tom;
32/56
Database Security – DAC
Bob Alice
Your objects at your own discretion!
Grant privileges
Revoke privileges
Delegate privileges
Bob Alice
– GRANT SELECT ON tableB TO Alice;
– REVOKE SELECT ON tableB FROM Alice;
– GARNT SELECT ON tableB TO Alice
WITH GRANT OPTION;
– REVOKE GRANT OPTION FOR SELECT
ON tableB FROM Alice;
– GRANT SELECT ON tableA TO Bob;
– REVOKE SELECT ON tableA FROM Bob;
– GRANT SELECT ON tableB TO Tom;
– REVOKE SELECT ON tableB FROM Tom;
32/56
Database Security – DAC
Bob Alice
Your objects at your own discretion!
Grant privileges
Revoke privileges
Delegate privileges
Bob Alice
– GRANT SELECT ON tableB TO Alice;
– REVOKE SELECT ON tableB FROM Alice;
– GARNT SELECT ON tableB TO Alice
WITH GRANT OPTION;
– REVOKE GRANT OPTION FOR SELECT
ON tableB FROM Alice;
– GRANT SELECT ON tableA TO Bob;
– REVOKE SELECT ON tableA FROM Bob;
– GRANT SELECT ON tableB TO Tom;
– REVOKE SELECT ON tableB FROM Tom;
32/56
Database Security – DAC
Bob Alice
Your objects at your own discretion!
Grant privileges
Revoke privileges
Delegate privileges
Bob Alice
– GRANT SELECT ON tableB TO Alice;
– REVOKE SELECT ON tableB FROM Alice;
– GARNT SELECT ON tableB TO Alice
WITH GRANT OPTION;
– REVOKE GRANT OPTION FOR SELECT
ON tableB FROM Alice;
– GRANT SELECT ON tableA TO Bob;
– REVOKE SELECT ON tableA FROM Bob;
– GRANT SELECT ON tableB TO Tom;
– REVOKE SELECT ON tableB FROM Tom;
32/56
Database Security – DAC
Bob Alice
Your objects at your own discretion!
Grant privileges
Revoke privileges
Delegate privileges
Bob Alice
– GRANT SELECT ON tableB TO Alice;
– REVOKE SELECT ON tableB FROM Alice;
– GARNT SELECT ON tableB TO Alice
WITH GRANT OPTION;
– REVOKE GRANT OPTION FOR SELECT
ON tableB FROM Alice;
– GRANT SELECT ON tableA TO Bob;
– REVOKE SELECT ON tableA FROM Bob;
– GRANT SELECT ON tableB TO Tom;
– REVOKE SELECT ON tableB FROM Tom;
33/56
Database Security – MAC
Bob Alice
Bob
(Top secret)
Alice
(Secret)
Tom
(Unclassified)
Jane
(Confidential)
Security
Clearance
Security
Class
(Top secret) (Secret) (Confidential) (Unclassified)
Bob Alice
Bob
(Top secret)
Alice
(Secret)
Tom
(Unclassified)
Jane
(Confidential)
Security
Clearance
Security
Class
(Top secret) (Secret) (Confidential) (Unclassified)
System-wide policies govern controlled access to classified information.
34/56
Mandatory Access Control
It is based the Bell-LaPadula model (originally developed for U.S.
Department of Defense multilevel security policy).
Subjects (e.g. users) are assigned security clearances;
Objects (e.g. rows, tables, views) are assigned security classes.
TS ≥ S ≥ C ≥ U
(TS: Top secret, S: Secret, C: Confidential, U: Unclassified)
Two rules are enforced by the model:
1 Subject X can read object Y only if clearance(X ) ≥ class(Y ).
↪→ Read down
2 Subject X can write object Y only if clearance(X ) ≤ class(Y ).
↪→Write up
The key idea is “preventing information in high level objects from
flowing to low level subjects”.
34/56
Mandatory Access Control
It is based the Bell-LaPadula model (originally developed for U.S.
Department of Defense multilevel security policy).
Subjects (e.g. users) are assigned security clearances;
Objects (e.g. rows, tables, views) are assigned security classes.
TS ≥ S ≥ C ≥ U
(TS: Top secret, S: Secret, C: Confidential, U: Unclassified)
Two rules are enforced by the model:
1 Subject X can read object Y only if clearance(X ) ≥ class(Y ).
↪→ Read down
2 Subject X can write object Y only if clearance(X ) ≤ class(Y ).
↪→Write up
The key idea is “preventing information in high level objects from
flowing to low level subjects”.
34/56
Mandatory Access Control
It is based the Bell-LaPadula model (originally developed for U.S.
Department of Defense multilevel security policy).
Subjects (e.g. users) are assigned security clearances;
Objects (e.g. rows, tables, views) are assigned security classes.
TS ≥ S ≥ C ≥ U
(TS: Top secret, S: Secret, C: Confidential, U: Unclassified)
Two rules are enforced by the model:
1 Subject X can read object Y only if clearance(X ) ≥ class(Y ).
↪→ Read down
2 Subject X can write object Y only if clearance(X ) ≤ class(Y ).
↪→Write up
The key idea is “preventing information in high level objects from
flowing to low level subjects”.
35/56
Mandatory Access Control
Multilevel relations: Assume that each row is assigned a security class.
Then users with different security clearances see a different collection of
rows when they access the same table.
city rating security class
Paris 4 secret (S)
Canberra 5 confidential (C)
Bob with C clearance can only access the second tuple.
Peter with S clearance can access both tuples.
Suppose that city is the primary key, and Bob with C clearance wishes to
add a row (Paris, 4, confidential(C)).
1 What would happen? The first record may be (partial) inferred.
2 How to solve the potential security issues? whiteTreating security
class as part of the primary key.
35/56
Mandatory Access Control
Multilevel relations: Assume that each row is assigned a security class.
Then users with different security clearances see a different collection of
rows when they access the same table.
city rating security class
Paris 4 secret (S)
Canberra 5 confidential (C)
Bob with C clearance can only access the second tuple.
Peter with S clearance can access both tuples.
Suppose that city is the primary key, and Bob with C clearance wishes to
add a row (Paris, 4, confidential(C)).
1 What would happen?
The first record may be (partial) inferred.
2 How to solve the potential security issues? whiteTreating security
class as part of the primary key.
35/56
Mandatory Access Control
Multilevel relations: Assume that each row is assigned a security class.
Then users with different security clearances see a different collection of
rows when they access the same table.
city rating security class
Paris 4 secret (S)
Canberra 5 confidential (C)
Bob with C clearance can only access the second tuple.
Peter with S clearance can access both tuples.
Suppose that city is the primary key, and Bob with C clearance wishes to
add a row (Paris, 4, confidential(C)).
1 What would happen? The first record may be (partial) inferred.
2 How to solve the potential security issues? whiteTreating security
class as part of the primary key.
35/56
Mandatory Access Control
Multilevel relations: Assume that each row is assigned a security class.
Then users with different security clearances see a different collection of
rows when they access the same table.
city rating security class
Paris 4 secret (S)
Canberra 5 confidential (C)
Bob with C clearance can only access the second tuple.
Peter with S clearance can access both tuples.
Suppose that city is the primary key, and Bob with C clearance wishes to
add a row (Paris, 4, confidential(C)).
1 What would happen? The first record may be (partial) inferred.
2 How to solve the potential security issues?
whiteTreating security
class as part of the primary key.
35/56
Mandatory Access Control
Multilevel relations: Assume that each row is assigned a security class.
Then users with different security clearances see a different collection of
rows when they access the same table.
city rating security class
Paris 4 secret (S)
Canberra 5 confidential (C)
Bob with C clearance can only access the second tuple.
Peter with S clearance can access both tuples.
Suppose that city is the primary key, and Bob with C clearance wishes to
add a row (Paris, 4, confidential(C)).
1 What would happen? The first record may be (partial) inferred.
2 How to solve the potential security issues? whiteTreating security
class as part of the primary key.
36/56
Database Security – MAC
Bob Alice
Bob
(Top secret)
Alice
(Secret)
Tom
(Unclassified)
Jane
(Confidential)
Security
Clearance
Security
Class
(Top secret) (Secret) (Confidential) (Unclassified)
Bob Alice
Bob
(Top secret)
Alice
(Secret)
Tom
(Unclassified)
Jane
(Confidential)
Security
Clearance
Security
Class
(Top secret) (Secret) (Confidential) (Unclassified)
Read down: Subject X can read object Y only if clearance(X) ≥ class(Y).
Write up: Subject X can write object Y only if clearance(X) ≤ class(Y).
37/56
DAC vs MAC
How do DAC and MAC differ from each other?
DAC is very flexible but complex.
Owners decide how their data is shared.
A user may have different privileges on different objects.
Different users may have different privileges on the same object.
…
MAC is comparatively rigid.
The system decides how data is shared.
Each object is given a security class, and each user is given a
security clearance.
An object can then be accessed by users with the appropriate
clearance.
…
37/56
DAC vs MAC
How do DAC and MAC differ from each other?
DAC is very flexible but complex.
Owners decide how their data is shared.
A user may have different privileges on different objects.
Different users may have different privileges on the same object.
…
MAC is comparatively rigid.
The system decides how data is shared.
Each object is given a security class, and each user is given a
security clearance.
An object can then be accessed by users with the appropriate
clearance.
…
37/56
DAC vs MAC
How do DAC and MAC differ from each other?
DAC is very flexible but complex.
Owners decide how their data is shared.
A user may have different privileges on different objects.
Different users may have different privileges on the same object.
…
MAC is comparatively rigid.
The system decides how data is shared.
Each object is given a security class, and each user is given a
security clearance.
An object can then be accessed by users with the appropriate
clearance.
…
38/56
DAC – Limitations
Suppose that Alice owns a table R.
Alice gives Bob the SELECT privilege to read it, but not Steve. However,
Steve may steal the information in R from Bob.
How?
Trojan Horse attacks.
38/56
DAC – Limitations
Suppose that Alice owns a table R.
Alice gives Bob the SELECT privilege to read it, but not Steve. However,
Steve may steal the information in R from Bob.
How? Trojan Horse attacks.
39/56
DAC – Limitations
Trojan Horse attacks: If Steve tricks Bob into copying data from table R
into table R′, then the access control on table R doesn’t apply to the copy of
the data in table R′.
Can this problem occur in MAC?
40/56
DAC – Limitations
DAC does not impose any restriction on the usage once data has been
obtained by a user, i.e., the dissemination of data is not controlled.
MAC prevents illegitimate flow of information by attaching security classes
to objects and security clearances to subjects.
41/56
SQL Injection Attacks
42/56
SQL Injection Attacks
SQL injection is one of the most basic and oldest tricks hackers use to get
into websites and their backend databases.
Web applications often access a database by
1 Connect to the database;
2 Send SQL statements to the database;←↩ SQL Injection!
3 Fetch the result and display data from the database;
4 Close the connection.
43/56
SQL Injection Attacks
Many web applications take user input from a form.
A user input is used in constructing a SQL query submitted to a database.
A SQL injection attack involves manipulating queries through the user input.
Send SQL statements
Fetch SQL results
Databases
Web forms
Hacker
Serializable
Repeatable read
Read committed
Phantom
read
Unrepeated
read
Dirty
read
Read uncommitted
Connect to the database
Close the connection
44/56
SQL Injection – Example
Consider a pizza-ordering application that allows users to review the orders
they have made in a given month.
8.1. Attack Scenario
In this section, we outline an example attack scenario for SQL injection. SQL (Structured
Query Language) is the language that most relational databases provide as the means for
applications to communicate with the database.1
Programs can use an SQL statement to specify what data they want the database to
retrieve or update. Given an SQL statement, the database determines how to efficiently obtain
or modify the relevant data, and returns the results to the program. An SQL injection attack is
possible if an application uses data that can be controlled by an attacker as part of an SQL
query. The attacker may be able to submit specially crafted input, such that the query that is
sent to the database is interpreted by the database differently from what the programmer
intended.
Suppose the pizza-ordering application from the previous chapter includes a feature that
allows users to review the orders they have made in a given month. The user is presented with
the form in Figure 8-1, which allows her to enter the month for which she would like to see
past orders.
Figure 8-1. The pizza order review form
When the form is submitted, it results in an HTTP request to the web application that
includes the month as a query parameter—for example, “10” for October.
https://www.deliver-me-pizza.com/show_orders?month=10
When receiving such a request, the application constructs an SQL query as follows:2
sql_query = “SELECT pizza, toppings, quantity, order_day ” +
“FROM orders ” +
“WHERE userid=” + session.getCurrentUserId() + ” ” +
“AND order_month=” + request.getParameter(“month”);
This query instructs the database to retrieve from the orders table the columns contain-
ing the name of the ordered pizza, its toppings, the order quantity, and the day of the month
the order was placed. Furthermore, only those rows are to be returned for which the user who
CHAPTER 8 ■ SQL INJECTION124
1. A relational database is one in which data is stored in tables with columns and rows.
2. In this chapter, we use examples written in Java. Note that this example is somewhat simplified; a real
application would also record and query for the year the order was placed, and use a normalized data-
base schema.
7842CH08.qxd 1/8/07 10:55 AM Page 124
The pizza order review form
45/56
SQL Injection – Example
When the form is submitted, it results in an HTTP request to the application:
https:// www.deliver-me-pizza.com/ show orders?month=10
When receiving such a request, the application constructs an SQL query:
sql query = “SELECT pizza, toppings, quantity, order day ”
+ “FROM orders ”
+ “WHERE userid=” + session.getCurrentUserId() + ” ”
+ “AND order month=” + request.getParameter(“month”);
Assuming that the current user’s userid is 1234, we have:
SELECT pizza, toppings, quantity, order day
FROM orders
WHERE userid=1234 AND order month=10
https://www.deliver-me-pizza.com/show_orders?month=10
46/56
SQL Injection – Example
The application then executes the query and retrieves the result set.
placed the order matches the currently logged-in user, and that correspond to an order made
in the requested month.
For example, the preceding HTTP request would result in the following string being
assigned to the variable sql_query (assuming the current user’s user-id is 4123):
SELECT pizza, toppings, quantity, order_day
FROM orders
WHERE userid=4123
AND order_month=10
The application then executes the query and retrieves the result set. It then inserts the
data returned into an HTML table to be returned to the user’s browser as part of the resulting
web page shown in Figure 8-2.
| Pizza | Toppings | Quantity | Order Day |
| Diavola | Tomato, Mozzarella, Pepperoni, … | 2 | 12 |
| Napoli | Tomato, Mozzarella, Anchovies, … | 1 | 17 |
Figure 8-2. Pizza order history
How can this feature in the application be attacked? We note that the application does not
perform any input validation on the query parameter month. In particular, we don’t verify or
enforce that the parameter is a string representing an integer; rather, we accept arbitrary
strings and insert them directly into the SQL query.
CHAPTER 8 ■ SQL INJECTION 125
7842CH08.qxd 1/8/07 10:55 AM Page 125
Pizza order history
How can this application be attacked?
47/56
SQL Injection – Example
What would happen if an attacker replaces ‘10’ with ‘0 OR 1=1’?
8.1. Attack Scenario
In this section, we outline an example attack scenario for SQL injection. SQL (Structured
Query Language) is the language that most relational databases provide as the means for
applications to communicate with the database.1
Programs can use an SQL statement to specify what data they want the database to
retrieve or update. Given an SQL statement, the database determines how to efficiently obtain
or modify the relevant data, and returns the results to the program. An SQL injection attack is
possible if an application uses data that can be controlled by an attacker as part of an SQL
query. The attacker may be able to submit specially crafted input, such that the query that is
sent to the database is interpreted by the database differently from what the programmer
intended.
Suppose the pizza-ordering application from the previous chapter includes a feature that
allows users to review the orders they have made in a given month. The user is presented with
the form in Figure 8-1, which allows her to enter the month for which she would like to see
past orders.
Figure 8-1. The pizza order review form
When the form is submitted, it results in an HTTP request to the web application that
includes the month as a query parameter—for example, “10” for October.
https://www.deliver-me-pizza.com/show_orders?month=10
When receiving such a request, the application constructs an SQL query as follows:2
sql_query = “SELECT pizza, toppings, quantity, order_day ” +
“FROM orders ” +
“WHERE userid=” + session.getCurrentUserId() + ” ” +
“AND order_month=” + request.getParameter(“month”);
This query instructs the database to retrieve from the orders table the columns contain-
ing the name of the ordered pizza, its toppings, the order quantity, and the day of the month
the order was placed. Furthermore, only those rows are to be returned for which the user who
CHAPTER 8 ■ SQL INJECTION124
1. A relational database is one in which data is stored in tables with columns and rows.
2. In this chapter, we use examples written in Java. Note that this example is somewhat simplified; a real
application would also record and query for the year the order was placed, and use a normalized data-
base schema.
7842CH08.qxd 1/8/07 10:55 AM Page 124
Alternatively, the attacker may modify the HTTP request, e.g.,
https:// www.deliver-me-pizza.com/ show orders?month=0% 20OR% 201% 3D1
Then request.getParameter(”month”) extracts ‘0%20OR%201%3D1’ and
returns the string ‘0 OR 1=1’.
https://www.deliver-me-pizza.com/show_orders?month=0%20OR%201%3D1
48/56
SQL Injection – Example
The SQL query that the application constructs and sends to the database
now becomes:
SELECT pizza, toppings, quantity, order day
FROM orders
WHERE userid=4123 AND order month=0 OR 1=1
Since the operator precedence of the AND operator is higher than that of OR,
the WHERE condition is equivalent to
WHERE (userid=4123 AND order month=0) OR 1=1
What happened?
The (malicious) user supplied a parameter that, once inserted into the SQL
query string, actually altered the meaning of the query!
49/56
SQL Injection – Example
However, the attacker might be able to do even more damage, e.g., making
a request such that the request parameter month evaluates to:
0 AND 1=0
UNION
SELECT cardholder, number, exp month, exp year
FROM creditcards
Then, the SQL query that the application constructs and sends to the
database becomes:
SELECT pizza, toppings, quantity, order day
FROM orders
WHERE userid=4123 AND order month=0 AND 1=0
UNION
SELECT cardholder, number, exp month, exp year
FROM creditcards
50/56
SQL Injection – Example
As a result, the attacker receives an HTML page that contains the entire
content of the creditcards table.
Figure 8-3. Pizza order history after SQL injection
Using the UNION syntax to combine an injected SELECT clause with the original query that
the programmer had intended to be executed, the attacker was able to retrieve data from an
entirely different database table than the one the original query referred to. The attacker can
potentially inflict even greater damage by using the ; statement separator to instruct the data-
base to execute a second separate statement, which in this case is not restricted to also be a
SELECT statement.3
For example, the attacker might arrange for the request parameter month to evaluate to
0;
DROP TABLE creditcards;
Then, the queries executed by the database will be
SELECT pizza, toppings, quantity, order_day
FROM orders
WHERE userid=4123
AND order_month=0;
DROP TABLE creditcards;
That is, after retrieving data from the orders table, the database is instructed to remove
the table creditcards from the schema. As such, this constitutes a DoS attack; after this state-
ment is executed, future orders might fail, or the application might initiate delivery of a pizza
without being able to charge users’ credit cards after the order is fulfilled.
CHAPTER 8 ■ SQL INJECTION128
3. The execution of multiple semicolon-separated statements may not be supported by some database
servers within queries made programmatically using the database’s API.
7842CH08.qxd 1/8/07 10:55 AM Page 128
Pizza order history after SQL injection
51/56
SQL Injection Attacks
How can we prevent SQL injection attacks?
Can SQL injection attacks be prevented by any of the following security
solutions?
1 Firewall
i.e., monitors and controls the incoming and outgoing network traffic
based on predetermined security rules
2 Intrusion detection system (IDS)
i.e., monitors a network or systems for malicious activity or policy
violations
3 Authentication
i.e., the process by which a system can identify users
52/56
SQL Injection Attacks – Protection Techniques
Several techniques of input validation:
Blacklisting?
i.e., blacklist quotes, semicolons, etc. from the input string. However, if
you forget to blacklist just one type of dangerous character, it could
give rise to a successful attack.
Whitelisting?
i.e., explicitly test whether a given input is within a well-defined set of
values that are known to be safe, e.g., the parameter month is a string
that represents a non-negative integer.
Escaping?
i.e., transform dangerous input characters to turn a potentially
dangerous input string into a sanitized one, e.g., escape(o‘connor)=
o“connor (the double quote is the escaped version of the single quote).
53/56
SQL Injection Attacks – Protection Techniques
The recommended solution: Parameterized Queries
Two steps:
1 The statement is prepared (parsed and compiled), in which ? is used
as place-holders for the actual parameters.
2 The actual parameters are passed to the prepared statement for
execution.
PreparedStatement stmt=conn.prepareStatement(
“SELECT pizza, toppings, quantity, order day ”
+ “FROM orders WHERE userid=? AND order month=?”);
stmt.setInt(1, session.getCurrentUserId());
stmt.setInt(2, Integer.parseInt(request.getParamenter(“month”)));
ResultSet res = ps.executeQuery();
The key idea is “separation between control and data”!
54/56
Research Topics
55/56
Research Topics
Differential privacy
What Apple’s differential privacy means for your data and the future machine
learning: https:// techcrunch.com/ 2016/ 06/ 14/ differential-privacy/
Learning with privacy at scale: https:
// machinelearning.apple.com/ research/ learning-with-privacy-at-scale
What Apple’s differential privacy means for your data and the future of machine learning
https://machinelearning.apple.com/research/learning-with-privacy-at-scale
https://machinelearning.apple.com/research/learning-with-privacy-at-scale
56/56
Research Topics
Data anonymization
k-anonymity: https:// en.wikipedia.org/ wiki/ K-anonymity
https://en.wikipedia.org/wiki/K-anonymity