Chapter Four

Database Security

Computer Networks and Information

Security
(CE 211)
Objectives
 Integrity for databases:
 record integrity,

 data correctness,

 update integrity

 Security for databases:

 Access control,

 inference and

 aggregation

 Multilevel secure databases:

 Partitioned

 Cryptography sealed
2  filtered
Introduction to Databases
 Database is a collection of related data and a set of rules that

organize the data by specifying certain relationships among the data.

 Through these rules, the user describes a logical format for the

data.
 The data items are stored in a file, but the precise physical format

of the file is no concern of the user.

 A database administrator is a person who defines the rules that

organize the data and also controls who should have access to
what part of the data.
 The user interacts with the database through a program called a
3
database manager or database management system (DBMS),
Concepts and Terminologies
1. Record:- the database file consists of records, each of which
contains one related group of data.
 Each record contains fields or elements, the elementary data

items themselves.
 The database can be viewed as a two-dimensional table, where a

record is a row and each filed of a record is an element of the

table.
 A database is easily represented as a single, compact table.

2. Schema:- is the logical structure of a database.

 A particular user may have access to only part of the database

called subschema.
4
 Schema and subschema can be used to present to users only those
Contd.
3. Attribute:- the rules of a database identify the columns with
names, these names of each column are called attributes of the
database.
4. Relation:- is a set of columns.
 The relation specifies clusters of related data values, in much the

same way that the relation “mother of” specifies a relationship

among pairs of humans.
5. Queries:- Users interact with database managers through
commands into the DBMS that retrieve, modify, add, or delete
fields and records of the database. This command is called query.
 DBMSs have precise rules of syntax for queries. Most query
5
languages use an English-like notation and many are based on a
 Relational Data Model

 Set of relation names: R

 Set of attribute names: A

 Relation schema: S=(r,{a1, …,an})

r relation name in R

{a1, …,an} subset of A

e.g., (student,{FName, ID, Department})

6
 Tuple (Record)

Tuple over a relation scheme S is a mapping:

t: {a1, …,an}  dom(a1  …  an)

e.g., t(FName)=Abebe Kebede

t(ID)= R/0101/04

t(Department)= BIS

7
 Query Languages

1. Relational Algebra 2. SQL(Structured Query Language)

 Select ()  Typical SQL query form:

 Project () SELECT A1, A2, ..., An

 Set difference (-) FROM r1, r2, ..., rm
 Union () WHERE C
 Rename (Px(r))  Ais represent attributes to be returned
 Set intersection ()
 ris represent relations
 Natural join ()
 C is a condition

8
Advantages of using databases
1. Shared Access:- Many users can share and use one common,
centralized set of data.

2. Minimal Redundancy:- individual users do not have to collect and

maintain their own sets of data.

3. Data Consistency:- change to a data value affects all users of the

data value.

4. Data Integrity:- Data values are protected against accidental or

malicious undesirable changes.

5. Controlled Access:- Only authorized users are allowed to view or

modify data values.
9
Database Security Requirements
1. Physical Database Integrity:- the data of a database are immune
to physical problems, such as power failures and someone can
reconstruct the database if it is destroyed through catastrophe.

2. Logical Database Integrity :- The structure of the database is

preserved.
 With logical integrity of a database, a modification to the value of

one filed does not affect the other fields.

3. Element Integrity :- the data contained in each element are

accurate.

4. Auditability:- It is possible to track who or what has accessed (or

10
modified) the elements in the database.
Contd.
5. Access Control:- A user is allowed to access only authorized data,

and different users can be restricted to different modes of access

( such as read and write) .

6. User Authentication:- Every user is positively identified, both for

the audit trail and for permission to access certain data.

7. Availability:- Users can access the database in general and all the

data for which they are authorized.

11
Integrity of the Database
 If a database is to serve as a central repository of data, users must

be able to trust the accuracy of the data values.

The database administrator must be assured that updates are

performed by authorized individuals.

The data must be protected from corruption, either by an outside

illegal program action or by an outside force such as fire or power

failure.
 Two situations can affect the integrity of database :

1. When the whole database is damaged (as happens, for

example, if its storage medium is damaged)
12
Contd.
 Integrity of the database as a whole is the responsibility of the

DBMS, the operating system and the computing system manager.

 From the perspective of the operating system and computing

system manager, database and DBMS are files and programs

respectively.
 Therefore, one way of protecting the database as a whole is to

regularly backup all files on the system.

 The periodic backups can be adequate controls against
catastrophic failure.
 Sometimes it is important to be able to reconstruct the database
13
at the point of failure.
Element Integrity
 The integrity of database elements is their correctness or accuracy.

 Authorized users are responsible for entering correct data into the

database.
Users and programs can make mistakes collecting data, computing

results and entering values.

The DBMS sometimes takes special action to help catch errors as

they are made and to correct errors after they are inserted.
The corrective action can be done in three ways:

1. The DBMS can apply field check, activities that test for
appropriate values in a position.
14
Contd.
3. Maintaining changing log files for the database:- A change log

lists every changes made to the database; it contains the original

and the modified values.

 Using this log a database administrator can undo any change that

were made in error.

 For example, a library fine might erroneously be posted against

Charles W. Robertson instead of Charles M. Robertson.

15
Auditability
 For some applications it may be desirable to generate an audit record

of all access (read or write) to a database.

 Such a record can help to:

1. Maintain the database’s integrity or at least to discover after the

fact who had affected what values and when.

2. Users can access protected data incrementally: - no single access

reveals protected data, but a set of sequential accesses viewed
together reveals the data, much like discovering the clues in a
detective novel.
 To be useful for maintaining integrity, database audit trails should
16
include accesses to the record, field and even element levels.
Access Control
 Databases are often separated logically by user access privileges.

 The database administrator specifies who should be allowed access

to which data, view relation, field, record or even element levels.

 The DBMS must enforce this policy, granting access to all

specified data or no access where prohibited.

 Although a user can not determine the contents of one file by reading

others, a user might be able to determine one data element by just

reading others.
 The problem of obtaining data values from others is called

inference.
17
 It is possible to access data by inference without needing direct
Contd.
 Restricting inference may mean prohibiting certain paths to prevent

possible inferences.
 Restricting access to control inference also limits queries from

users who do not intend unauthorized access to values.

 Size or granularity is different between operating system objects and

database objects.
 An access control list of several hundred files is much more easier to

implement than an access an access control list for database with

several hundred files of perhaps a hundred fields each.
Size affects the efficiency of processing
18
User Authentication
 The DBMS can enquire rigorous user authentication.

 For example, a DBMS might insist that a user pass both specific

password and time-of-days checks.

 This authentication supplements the authentication performed by the

operating system.
 The DBMS runs as an application program on top of the operating

system.
 This system design means there is no trusted path from the DBMS to

the OS so the DBMS must be suspicious of any data it receives,

including user authentication.
19

Integrity/Confidentiality/Availability
 The three aspects of computer security, integrity, confidentiality and

availability, are clearly related to DBMSs.

 Integrity applies to the individual elements of a database as well as to

the database as a whole.

 Integrity is major concern in the design of DBMSs

 Confidentiality is a key issue with databases because of inference

problems, where a user can access sensitive data indirectly.

 Availability is important because of the shared access motivation

underlying database development.

 Availability conflicts with confidentiality.
20
Reliability and Integrity
 Databases amalgamate data from many sources, and users expect a

DBMS to provide access to the data in a reliable way.

 Reliability (from software engineers perspective) means the that the

software runs for very long periods of time without failing.

 Users certainly expect a DBMS to be reliable.

 Database concerns about reliability and integrity can be viewed from

three dimensions:

1. Database Integrity:- concerns that the databases as a whole is

protected against damages.

 Are addressed by OS integrity controls and recovery procedures.
21
Contd.
2. Element Integrity:- concerns that the value of a specific data

element is written or changed only by authorized users.

 Proper access controls protect a database from corruption by

unauthorized users.

3. Element Accuracy:- concerns that only correct values are written

into the elements of a database.

 Checks on the values of elements can help to prevent insertion of

improper values
 Constraint conditions can also detect incorrect values.

22
Two-Phase Update
 A serious problem for a database manager is the failure of the computing

system in the middle of modifying data.

 If the data item to be modified was a long field, half of the field might

show the new value, while the other half would contain the old value.

Solution

 Using two-phase update:- Solving the problem using two-phase update.

1. The first phase, intent phase, the DBMS gathers the resources it needs

to perform the update.

 It does everything to prepare for the update, it may gather data, create
dummy records, open files, lock out other users and calculate final
23
answers, but it makes no changes.
Contd.
 If the system fails during execution of the first phase, no harm is done,

because all these steps can be restarted and repeated after the system
resumes processing.
 The last event of the first phase, called committing, involves the
writing of commit flag to the database.
 The commit flag means that the DBMS has passed the point of no

return.
 After committing, the DBMS begins making the permanent changes.

2. The second phase makes the permanent changes.

 No action from before the commit can be repeated , but the update
activities of phase two can also be repeated as often as needed.
24
 If the system fails during the second phase, the database may
Redundancy/Internal Consistency
 Many DBMSs maintain additional information to detect internal
inconsistency in data.

 The additional information ranges from a few check bits to duplicate

or shadow fields, depending on the importance of the data.

Error Detection and correction Codes

 One form of redundancy is error detection and correction codes, such as

parity bits, Hamming codes, and cyclic redundancy . [Reading

Assignment].

 These codes can be applied to single fields, records, or the entire

database.
25
 Each time a data item is placed in the database and/or retrieved from
Contd.
 Some of these codes point out the place of the errors, others show

precisely what the correct value should be.

 The more information provided the more space required to store the

codes

Shadow Fields

 Entire attributes or entire records can be duplicated in a database.

 If the data are irreproducible, this second copy can provide an

immediate replacement if an error is detected.

26
Recovery

 In addition to the error correction processes, a DBMS can

maintain a log of user access, particularly changes.

 In the event of a failure, the database is reloaded from a backup

copy and all later changes are then applied from the audit log.

27
Concurrency/Consistency
 Database systems are often multiuser systems.

 Accesses by two users sharing the same database must be controlled

so that neither interferes with the other.

 The DBMS uses a simple locking.

 If two users try attempt to read the same data item, there is no

conflict because both obtain the same value.

 But if both users try to modify the same data from the database,

this may lead to conflict, because the value written one user may
depend on the previous value of the data item.
Solution
 The DBMS treats the entire query-update cycle as a single atomic
28
operation.
Contd.
Read-Write problem

 Suppose one user is updating a value when a second user wishes to

read it.

 If the read is done while the write is in progress, the reader may

receive data that are only partly updated.

Solution

 The DBMS locks any read request until a write has been

completed.
29
Monitors
 The monitor is the unit of a DBMS responsible for the structural

integrity of the database.

 A monitor can check values being entered to ensure their

consistency with the rest of the database or with

characteristics of the particular field.

 For example, a monitor might reject alphabetic characteristics for

a numeric field.

30
Range Comparisons
 The range comparison monitor tests each new value to ensure that

the value is within an acceptable range.

 If the data value is outside the range, it is rejected and not entered

into the database.

 Range comparison can be used to ensure the internal consistency

of a database.

 When used in this manner, comparisons are between two database

31 elements
State Constraints
 State constraints describe the condition of the entire database.

 It describes the state of the correct database.

 The commit flag, which is set at the start of the commit phase

and cleared at the completion of the commit phase of the two-

phase updates, can be considered as a state constraint because

it is used at the end of every transaction for which the commit

flag set is not set.

32
Transaction Constraints
 Transaction constraint describe conditions necessary before the

changes can be applied to a database.

 For example, before a new employee is added to the database,

there must be a position number in the database with status

“vacant”

 Simple range checks can be implemented within most DBMS.

However, the more sophisticated state and transaction

constraints can require special procedures for testing.

 Such user written procedures are invoked by the DBMS each

33
Sensitive Data
 Sensitive Data are data that should not be made public.

 Determining which data items and fields are sensitive depends on

the individual database and the underlying meaning of the data.

 Some databases, such as public library catalogue contain no

sensitive data; others, such as defense related databases, are

totally sensitive.

 Total sensitive and nothing sensitive systems can be easily

handled using access control.

34  The most difficult problem is, the case in which some but not all
Factors which Make Data Sensitive
1. Inherently Sensitive:- The value itself may be so revealing that it is
sensitive.
2. From a Sensitive Source:- The source of a data may indicate a need
for confidentiality.
 Information from an informer whose identity would be compromised
if the information were disclosed.
3. Declared Sensitive:- the database administrator or the owner of the
data may have declared the data to be sensitive.
4. Part of a Sensitive attribute or a Sensitive record:- in a database,
an entire attribute or record may be classified as sensitive.
5. Sensitive in relation to previously disclosed information:- Some
35 data become sensitive in the presence of other data.
DBMS considers the following factors when dealing with Access Decision
1. Availability of Data
2. Acceptability of Access
3. Assurance of Authenticity

36
Types of disclosures

1. Exact data

2. Range of data

3. Negative results

4. Existence

5. Probable values

37
 Access control
Operating system
Database Management System
Granularity!

38
 Granularity
Database
Relation
Record
Attribute
Advantages vs. disadvantages
of supporting
Element
different granularity levels

39
 Relation-Level Granularity

(Works)= Secret
Person- Company- Salary
name name
Smith BB&C $43,982

Dell Bell $97,900

Black BB&C $35,652

40
 Tuple-level Granularity

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

Dell Bell $97,900 S

Black BB&C $35,652 P

41
 Attribute-Level Granularity

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

Dell Bell $97,900

Black BB&C $35,652

42
 Cell-Level Granularity

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

Dell S Bell S $97,900 S

Black P BB&C S $35,652 S

43
 Access Control
Security Mechanisms
through Views
Stored Procedures
Grant and Revoke
Query modification

44
 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.

45
 Security Through Views
Student relation

NAME COURSE GRADE SEMESTER

White CSCE 122 C+ Fall 2000
Black CSCE 313 A Fall 2000
Brown CSCE 580 A Spring 2000
Green CSCE 850 B+ Fall 2000
Blue CSCE 122 B Fall 2000

46
 Security Through Views
CREATE VIEW Outstanding-Student
AS SELECT NAME, COURSE, GRADE
FROM Student
WHERE GRADE > B

Outstanding-Student

NAME COURSE GRADE

Black CSCE 313 A
Brown CSCE 580 A
Green CSCE 850 B+

47
 Security Through Views
CREATE VIEW Fall-Student
AS SELECT NAME, COURSE
FROM Student
WHERE SEMESTER=“Fall 2000”

NAME COURSE
Fall-Student
White CSCE 122
Black CSCE 313
Green CSCE 850
Blue CSCE 122

48
 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.

49
 Grant and
GRANT Revoke
<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

50
 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

51
 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 580”

52