Transcript Basic Relational Concepts

Computer Science
CSC 405
Introduction to Computer Security
Topic 6. Database Security
CSC 405
Dr. Peng Ning
1
Agenda
• Discretionary access control in DBMS
• Mandatory access control and multi-level
databases
• Database inference control
Computer Science
CSC 405
Dr. Peng Ning
2
Computer Science
CSC 405
Dr. Peng Ning
3
Computer Science
Topic 6.1 DAC in DBMS
CSC 405
Dr. Peng Ning
4
Outline
•
•
•
•
Relational model
Grant and revoke
Extension to the basic model
Questions/comments in reviews
Computer Science
CSC 405
Dr. Peng Ning
5
Basic Relational Concepts
• Data is organized as a collection of tables, called
RELATIONS
– Example: two relations - EMP, DEPT
– EMP: name, title, department
– DEPT: department, location
•
•
•
•
Each row (or record) of a relation is called a TUPLE
Each relation has a unique name
Each attribute has a unique name within a relation
All values in a relation are atomic (indecomposable)
– As a consequence , we have two tuples for a user
Computer Science
CSC 405
Dr. Peng Ning
6
Examples
EMP
DEPT
Name
Title
Dept
Tom
Prof
ECE
Tom
Prof
CS
Adams
Prof
ECE
Smith
Inst
CS
Name
Location
CS
Wither Hall
ECE
Daniels Hall
Math
Harrelson Hall
Computer Science
CSC 405
Dr. Peng Ning
7
Relation Schemes
•
•
•
•
•
•
A relational database consists of 2 relation schemes:
EMP(Name, Title, Dept)
DEPT(Name, Location)
Schemes: structure of the database
Structured Query Language (SQL)
SQL "data definition" statements are used to create relations
CREATE TABLE EMP
CREATE TABLE DEPT
(Name CHAR(15) NOT NULL,
(Name CHAR(10) NOT NULL,
Title CHAR(4),
Location CHAR (15),
Dept CHAR(10),
PRIMARY KEY (Name))
PRIMARY KEY (Name))
Computer Science
CSC 405
Dr. Peng Ning
8
SQL
• The SELECT statement
SELECT Name
FROM
EMP
WHERE Dept = `ECE'
• Joins
SELECT
FROM
WHERE
AND
Tom
Adams
*
Tom
Prof
Smith
Inst
EMP, DEPT
EMP.Dept= DEPT.Name
Dept.Location = `Wither Hall'
Computer Science
CSC 405
CS
Wither Hall
CS
Wither Hall
Dr. Peng Ning
9
Views
CREATE VIEW EMP_LOCATION
AS SELECT
Name, Dept, Location
FROM
EMP, DEPT
WHERE
EMP.Dept = DEPT.Name
EMP_LOCATION
Name Dept
Tom ECE
Tom CS
Abrams
Smith CS
Location
Daniels Hall
Wither Hall
ECE Daniels Bldg
Wither Hall
• Views are "virtual" relations. They can be used to customize
relations and to provide security
Computer Science
CSC 405
Dr. Peng Ning
10
Discretionary Access Controls
• Decentralized administration
– Users can protect what they own
– The owner may grant access to others
– The owner may define the type of access
(read/write/execute) given to others
Computer Science
CSC 405
Dr. Peng Ning
11
Access Control Mechanisms
•
•
•
•
•
Identification and Authentication (I&A)
Security through Views
Stored Procedures
Grant and Revoke
Query Modification
Computer Science
CSC 405
Dr. Peng Ning
12
Identification and Authentication
• Identification provided by DBMS can be
distinct from that provided by the underlying
OS
– Example: MS SQL server
• Two options
– I&A through the OS
– Separate I&A
Computer Science
CSC 405
Dr. Peng Ning
13
Security Through Views
EMP
NAME
Smith
Jones
Baker
Adams
Harding
DEPT SALARY MANAGER
Toy
10,000
Jones
Toy
15,000
Baker
Admin
40,000
Harding
Candy
20,000
Harding
Admin
50,000
None
Users are allowed to access partial information (such as
the Toy dept data), but not the detailed information.
Computer Science
CSC 405
Dr. Peng Ning
14
Example
CREATE VIEW TOY_DEPT
AS SELECT NAME, SALARY, MANAGER
FROM
EMP
WHERE DEPT = 'Toy'
TOY_DEPT
NAME
SALARY
MANAGER
Smith
10,000
Jones
Jones
15,000
Baker
Computer Science
CSC 405
Dr. Peng Ning
15
Example
CREATE VIEW TOY_EMP_MGR
AS SELECT EMP, MANAGER
FROM
EMP
WHERE DEPT = 'Toy'
TOY_EMP_MGR
NAME
MANAGER
Smith
Jones
Jones
Baker
Computer Science
CSC 405
Dr. Peng Ning
16
Example
CREATE VIEW AVSAL(DEPT, AVG)
AS SELECT DEPT, AVG(SALARY)
FROM
EMP
GROUP BY DEPT
AVSAL
DEPT
AVG
TOY
12,500
CANDY 20,000
ADMIN
45,000
Computer Science
CSC 405
Dr. Peng Ning
17
Stored Procedures
• Right to execute compiled programs
• GRANT RUN ON program_A TO ADAMS
• Suppose program_A needs to access the relation EMP.
Adams can execute program_A even though he does
not have permission to access EMP
Computer Science
CSC 405
Dr. Peng Ning
18
Query Modification
• Adams:
GRANT SELECT ON EMP TO THOMAS WHERE SALARY < 15000
• THOMAS:
SELECT *
FROM EMP
• DBMS:
SELECT *
FROM EMP
WHERE SALARY < 15000
Computer Science
CSC 405
Dr. Peng Ning
19
The Grant Command
•
•
GRANT <privilege> ON <relation> TO <users>
[WITH GRANT OPTION]
–
GRANT SELECT ON EMP TO ADAMS
–
GRANT SELECT ON EMP TO ADAMS WITH GRANT
OPTION
–
GRANT SELECT, UPDATE(SALARY) ON EMP TO
JIM, JILL
Applied to base relations as well as views
Computer Science
CSC 405
Dr. Peng Ning
20
The Revoke Command
•
REVOKE <privileges> [ON <relations>]
FROM <users>
–
REVOKE SELECT ON EMP FROM TOM
–
REVOKE UPDATE ON EMP FROM SMITH
–
REVOKE RESOURCE FROM ABRAMS
–
REVOKE DBA FROM SMITH
Computer Science
CSC 405
Dr. Peng Ning
21
Semantics of Revoke
• A sequence of grant command follow by a
revoke operation
– G1, G2, …, Gn, Rh
• Semantics
– Equivalent to: G1, G2, …Gh-1, Gh+1, Gn
Computer Science
CSC 405
Dr. Peng Ning
22
Time-stamped Authorizations
B
E
30g
10g
40g
A
D
20g
60g
C
Computer Science
F
50g
CSC 405
Dr. Peng Ning
23
Cascading Revocation
Grant sequence:
A
B
C
20g
10g
D
30g
B revokes privilege from C :
A
B
10g
Computer Science
CSC 405
Dr. Peng Ning
24
Timestamps Make a Difference
B
E
30g
10g
40g
A
D
20g
60g
C
Computer Science
F
50g
CSC 405
Dr. Peng Ning
25
Timestamps Make a Difference
B
E
30g
10g
50g
A
D
20g
60g
C
Computer Science
F
40g
CSC 405
Dr. Peng Ning
26
Further Extension
• Make cascading optional
• Permit negative authorizations
Computer Science
CSC 405
Dr. Peng Ning
27
The Revoke Command
• REVOKE <privileges> [ON <relations>]
FROM <users> [CASCADE]
– REVOKE SELECT ON EMP FROM TOM
– REVOKE UPDATE ON EMP FROM SMITH
CASCADE
– REVOKE RESOURCE FROM ADAMS
– REVOKE DBA FROM SMITH CASCADE
Computer Science
CSC 405
Dr. Peng Ning
28
Non-cascading Revocation
A
B
20g
10g
A
C
D
30g
B
D
30g
10g
Computer Science
CSC 405
Dr. Peng Ning
29
Why Non-cascading Revoke
• Reasons for revoke
– Task is done. No need to have the privilege
anymore
– Task is still in progress. But a member left the
project (e.g., promoted)
Computer Science
CSC 405
Dr. Peng Ning
30
Example
20
B
40
50
E
80
G
D
A
30
C
70
60
Computer Science
CSC 405
F
Dr. Peng Ning
31
Example
20
B
40
50
E
80
G
D
A
30
20
C
70
60
F
After cascading
revocation
B
D
A
30
C
Computer Science
60
CSC 405
70
F
Dr. Peng Ning
32
Example
20
B
40
50
E
80
G
80
G
D
A
30
70
60
C
After non-cascading revocation
50
B
20
F
E
70
A
30
C
60
Computer Science
D
70
CSC 405
F
Dr. Peng Ning
33
Why Positive & Negative Authorization
• Closed world policy
– Cannot access unless explicitly granted the right
• Negative authorization
– User A should not be allowed to read table Emp
– Need explicit deny policies
Computer Science
CSC 405
Dr. Peng Ning
34
Positive & Negative Authorizations
40 —
B
E
10 + g
30 + g
A
D
20 —
C
Computer Science
CSC 405
Dr. Peng Ning
35
Complication
• It is possible to have two authorizations
– Grant A privilege p
– Deny A privilege p
• Negative authorizations override positive
authorizations
Computer Science
CSC 405
Dr. Peng Ning
36
Problem 1
User B gives D negative authorization at time 50 :
40 —
B
E
50 —
10 + g
30 + g
A
D
20 —
C
In our model, positive authorization granted by A to D
becomes blocked, but we do not delete the authorization.
Computer Science
CSC 405
Dr. Peng Ning
37
Problem 2
Suppose D receives negative authorization from B at time 60 :
40 —
B
E
60 —
10 + g
30 + g
50 +
A
D
F
20 —
C
What about the privilege given to F by D?
Under our approach, it becomes blocked, but we do not delete it.
Computer Science
CSC 405
Dr. Peng Ning
38
Revocation When Negative Authorizations
Are Present
Given :
40 —
B
E
60 —
10 + g
30 + g
50 +
A
D
F
20 —
C
Suppose A revokes B’ s privilege.
Computer Science
CSC 405
Dr. Peng Ning
39
Cascading Revocation When Negative Authorizations
Are Present
30 + g
50 +
A
D
20 —
F
C
Computer Science
CSC 405
Dr. Peng Ning
40
Non-cascading Revocation When Negative
Authorizations Are Present
E
40 —
60 —
50 +
A
D
F
30 + g
20 —
C
Computer Science
CSC 405
Dr. Peng Ning
41