COURSE NAME: DBMS

COURSE CODE:23AD2102A
TOPIC:
DATA SECURITY USING ACCESS
CONTROL

Session - 13

1
 AIM OF THE SESSION

To familiarize students with the advance and complex Subqueries in PostgreSQL.

INSTRUCTIONAL OBJECTIVES

This Session is designed to:

1. Discuss the subqueries.
2. Various guidelines and types of subqueries.

LEARNING OUTCOMES
At the end of this session, you should be able to understand the basic concepts of
Subqueries and learn how to write complex subqueries with PostgreSQL commands.

2
SECURITY OBJECTIVES
Prevent/detect/deter improper
Disclosure of information

Prevent/detect/deter Secrecy
Improper modification
of information

Integrity Availability

Prevent/detect/deter improper
Denial of access to services

3
DATABASES

• Collection of
• interrelated data and
• set of programs to access the data
• Convenient and efficient processing of data
• Database Application Software

4
DATABASE SECURITY
• Protect Sensitive Data from
• Unauthorized disclosure
• Unauthorized modification
• Denial of service attacks
• Security Controls
• Security Policy
• Access control models
• Integrity protection
• Privacy problems
• Fault tolerance and recovery
• Auditing and intrusion detection

5
 Protection of Data Confidentiality

 Access control – which data users can

access
 Information flow control – what users can

do with the accessed data

 Data Mining

6
 Access Control

 Ensures that all direct accesses to object are

authorized
 Protects against accidental and malicious
threats by regulating the read, write and
execution of data and programs

7
ACCESS CONTROL

Requires:

- Proper user identification

- Information specifying the access rights
is protected form modification

8
 Access Control

Access control components:

- Access control policy: specifies the
authorized accesses of a system
- Access control mechanism: implements
and enforces the policy

9
ACCESS CONTROL


Subject: active entity that requests access to an object
- e.g., user or program
 Object: passive entity accessed by a subject
- e.g., record, relation, file
 Access right (privileges): how a subject is allowed to access an object
- e.g., subject s can read object o

10
 Protection Object

 Database
 Relation Advantages vs. disadvantages
 Record of supporting
different granularity levels
 Attribute
 Element

11
 Relation-Level
Granularity
Confidential relation
Person- Company- Salary
name name
Smith BB&C $43,982

Dell Bell $97,900

Black BB&C $35,652

12
 Tuple-level Granularity

Works
Person-name Company- Salary
name
Smith BB&C $43,982 Public

Dell Bell $97,900 Conf.

Black BB&C $35,652 Public

13
 Attribute-Level
Granularity
Works
Person- Company- Salary
name Publ. name Publ. Conf.
Smith BB&C $43,982

Dell Bell $97,900

Black BB&C $35,652

14
 Cell-Level Granularity

Works
Person- Company- Salary
name name
Smith P BB&C P $43,982 C

Dell C Bell C $97,900 C

Black P BB&C C $35,652 C

15
 Access Control Policies

 Discretionary Access Control (DAC)

 Mandatory Access Control (MAC)

 Role-Based Access Control (RBAC)

16
 Discretionary Access Control (DAC)
 For each subject access right to the objects are
defined
 (subject, object, +/- access mode)
 (Black, Employee-relation, read)
 User based
 Grant and Revoke
 Problems:
- Propagation of access rights
- Revocation of propagated access rights

17
DAC BY GRANT AND REVOKE

GRANT SELECT ON Employee GRANT SELECT ON Employee

TO Black TO Red
Black Red
WITH GRANT OPTION
?
Brown revokes grant
given to Black
?
Brown does not want
Brown (owner) Red to access the
Employee relation
GRANT UPDATE(Salary) ON
Employee TO White

White
18
 Implementation
File 1 File 2
Access Control List (column) Joe:Read Joe:Read
(ACL) Joe:Write Sam:Read
Joe:Own Sam:Write
Capability List (row) Sam:Own
Joe: File 1/Read, File 1/Write, File 1/Own, File 2/Read
Sam: File 2/Read, File 2/Write, File 2/Own
Subject Access Object
Joe
Access Control Triples Read File 1
Joe Write File 1
Joe Own File 1
Joe Read File 2
Sam Read File 2
Sam Write File 2
Sam Own File 2

19
 Access Control Mechanisms

 Security through Views

 Stored Procedures
 Grant and Revoke
 Query modification

20
 Security Through Views
 Assign rights to access predefined views
CREATE VIEW Outstanding-Student
AS SELECT NAME, COURSE, GRADE
FROM Student
WHERE GRADE > B

Problem:
Difficult to maintain updates.

21
 Stored Procedures
 Assign rights to execute compiled programs
 GRANT RUN ON <program> TO <user>

Problem:
Programs may access resources for which the user
who runs the program does not have permission.

22
 Grant and Revoke
GRANT <privilege> ON <relation>
To <user>
[WITH GRANT OPTION]
------------------------------------------------------------------------------------------------------------------------------------
 GRANT SELECT * ON Student TO Matthews
 GRANT SELECT *, UPDATE(GRADE) ON Student TO
FARKAS
 GRANT SELECT(NAME) ON Student TO Brown

GRANT command applies to base relations as well

as views

23
 Grant and Revoke
REVOKE <privileges> [ON <relation>]
FROM <user>
-------------------------------------------------------------------------------------------------------------------------
 REVOKE SELECT* ON Student FROM Blue
 REVOKE UPDATE ON Student FROM Black
 REVOKE SELECT(NAME) ON Student FROM Brown

24
 Non-cascading Revoke
B E

A D

C F
A revokes D’s privileges
E
B
A

C F

25
 Cascading Revoke
B E

A D

C F
A revokes D’s privileges
B
A

26
Positive and Negative
Authorization
B
- E
+
+ D
A

- Problem:
Contradictory authorizations
C • GRANT <privilege> ON X TO <user>
• DENY <privilege> ON X TO <user>

27
Negative Authorization
B
- E
+ -
+ D
A
+
-
C F

What should happen with the privilege given by D

To F?

28
 Query Modification
 GRANT SELECT(NAME) ON Student TO Blue
WHERE COURSE=“CSCE 590”

 Blue’s query:
SELECT *
FROM Student

 Modified query:
SELECT NAME
FROM Student
WHERE COURSE=“CSCE 590”

29
 DAC Overview
 Advantages:
 Intuitive
 Easy to implement

 Disadvantages:
 Inherent vulnerability (look TH example)
 Maintenance of ACL or Capability lists
 Maintenance of Grant/Revoke
 Limited power of negative authorization

30
 Mandatory Access Control (MAC)
 Security label
- Top-Secret, Secret, Public
 Objects: security classification
- File 1 is Secret, File 2 is Public
 Subjects: security clearances
- Brown is cleared to Secret, Black is cleared to Public
 Dominance ()
- Top-Secret  Secret  Public

31
 MAC
 Access rights: defined by comparing the security
classification of the requested objects with the
security clearance of the subject
 If access control rules are satisfied, access is
permitted
 Otherwise access is rejected
 Granularity of access rights!

32
MAC – BELL-LAPADULA (BLP)
MODEL
 Singlesecurity property: a subject S is allowed a
read access to an object O only if label(S)
dominates label(O)
 Star-property:a subject S is allowed a write access
to an object O only if label(O) dominates label(S)
No direct flow of information from
high security objects to low security objects!
33
MULTILEVEL SECURITY

• Multilevel security  users at different security level, see different

versions of the database
• Problem: different versions need to be kept consistent and coherent
without downward signaling channel (covert channel)

34
MULTILEVEL RELATION

• Schema R(A1,C1,…,An,Cn,Tc)
• R: relation name
• Ai: attribute name
• Ci: security classes
• Tc: Tuple security classes
• Instantiation of relation: sets of tuples of the form <a1,c1,
…,an,cn,tc>
• ai: attribute value
• ci: attribute classification label
• tc: tuple classification label

35
 Multilevel Relation
Example
SSN (SSN) Course (Course) Grade (Grade)
111-22-3333 S CSCE 786 S A TS
444-55-6666 S CSCE 567 S C TS

Top-secret user sees all data

Secret user sees Secret-View:
SSN (SSN) Course (Course) Grade (Grade)
111-22-3333 S CSCE 786 S null S
444-55-6666 S CSCE 567 S null S

36
36
 SUMMARY

An aggregate function in SQL performs a calculation on multiple

values and returns a single value. SQL provides many aggregate
functions that include avg, count, sum, min, max, etc. An
aggregate function ignores NULL values when it performs the
calculation, except for the count function

37
 SELF-ASSESSMENT QUESTIONS

1. Which of the following is true about sub-queries?

a) They execute after the main query executes.

b) They execute in parallel to the main query.
c) The user can execute the main query and then, if wanted, execute the
sub-query.
d) They execute before the main query executes.

2. Which of the following clause is mandatorily used in a sub-query?

(a) SELECT
(b)WHERE
(c) ORDER BY
(d) GROUP BY

38
 SELF-ASSESSMENT QUESTIONS
3. Which of the following multi-row operators can be used with a sub-
query?

(a) IN
(b) ANY
(c) ALL
(d) ALL OF THE ABOVE

4. Which of the following is true about the result of a sub-query?

a) The result of a sub-query is generally ignored when executed.

b) The result of a sub-query doesn't give a result, it is just helpful in speeding up the
main query execution.
c) The result of a sub-query is used by the main query.
d) The result of a sub-query is always NULL.

39
 TERMINAL QUESTIONS

1. Describe various types of SQL complex subqueries.

2. List out the guidelines for creating the SQL subqueries.

3. Analyze the use of ALL,IN, or ANY operator while using subqueries in

PostgreSQL.

40
 REFERENCES FOR FURTHER LEARNING OF THE
SESSION

Reference Books:
1. Database System Concepts, Sixth Edition, Abraham Silberschatz, Yale University Henry, F. Korth
Lehigh University, S. Sudarshan Indian Institute of Technology, Bombay.
2. An Introduction to Database Systems by Bipin C. Desai
3. Fundamentals of Database Systems, 7th Edition, RamezElmasri, University of Texas at Arlington,
Shamkant B. Navathe, University of Texasat Arlington.

Sites and Web links:

1. [Link]
2. [Link]

41
THANK YOU

Team – DBMS

42