Transcript 8. integrity and security

Chapter 8
Integrity and Security
Integrity

Data Integrity.
The data is consistent with all the stated constraints
that apply to it, and hence considered to be valid.



If security controls exist without any integrity
controls, the reliability and validity of data rely
entirely on the authorized users’ correct use of
the system.
It does not absolutely guarantee that the data
is correct.
We need constraints on the manipulation of
data , in particular on insert, update and delete.
Entity Integrity

Entity Integrity
 In a base relation, no attribute of a primary key can be null.

In the “SP”- relation of the sample database the
primary key is the combination S#, P#
 if some values of P# are null, we are unable to determine the
proper quantity of a particular part, additionally, the entity
shipment becomes incorrectly defined ;
 if the value of both P# and S# are null , the remaining
information is totally meaningless


The same condition should also hold for the alternate
keys
Most DBMSs provide this rule automatically
Referential Integrity

Referential Integrity
 If a foreign key exists in a relation, either the foreign key value must
match a candidate key value of some tuple in its home relation, or the
foreign key value must be wholly null.


In other words, if an attribute refers to an entity, the entity must be
known in the database. (e.g. the ZIP-code in an address should
refer to an existing city) ;
A null value may be considered a “not yet known” entity
 consider using a default value instead;



Key modification in a home relation should be propagated;
Propagation for deletion might have severe consequences;
The referential integrity implementation can save a lot of
programming work.
Referential Integrity
application

If a primary key is to be updated or the entire tuple is to
be deleted :
 If the primary key value does not occur anywhere as foreign
key
• then allow the operation
 If the primary key value occurs as a foreign key
• do not allow the operation (error message)
• or allow the operation and also set the foreign occurrences to null
or to a specified default value
• or, allow the operation and also
– in case of update, propagate the changed value to the foreign key
occurrences , and where the foreign key forms part of the key of this
relation, propagate the changes where this primary key is also a
foreign key in another relation
– in case of delete, propagate the deletion , eventually in several levels
• enter into dialog with the user
Integrity in Create Table
CREATE TABLE table-name
( {column_name data-type [NOT NULL] [UNIQUE]
[DEFAULT default_option] [CHECK (search_condition) ] [, … ] }
[PRIMARY KEY ( column-commalist ),]
{ [UNIQUE ( column-commalist ), ] [, … ] }
{ [FOREIGN KEY ( column-commalist )
REFERENCES parent-base-table [ ( list_of_candidate_key_columns ) ],
[MATCH {PARTIAL½FULL }
[ ON DELETE referential_action ]
[ ON UPDATE referential_action ] [ , … ] }
{ [CHECK ( search-condition) ] [, … ] } )
referential_actions : NO ACTION , CASCADE, SET DEFAULT, SET NULL
Database Security

Database Security
 The protection of the database against threats using both
technical and administrative controls


Security considerations do not only apply to the data
but encompasses hardware, software, people .
Database security must be considered in relation to:
 theft and fraud
 loss of confidentiality (secrecy), usually critical to the
organisation
 loss of privacy ( critical to the individual )
 loss of integrity
 loss of availability
Threats

Threat
Any situation or event, whether intentional or
unintentional, that will adversely affect a system and
consequently an organization .



represented by a person, action or
circumstance that is likely to bring harm to the
organization
harm can be tangible (loss of hardware,
software or data )
or intangible (loss of credibility
or client confidence )
Examples of Threats
8
Threats: Loss of
Confidentiality









Wire tapping
Illegal entry by hacker
Acquiring and using another person's identifier and
password to gain access
Blackmail
Inadequate or ill-thought-out procedures that allow
confidential output to be mixed with normal output
Software failure giving greater access than normal
Staff shortage or strikes
Inadequate staff training
Viewing unauthorized data and disclosing it
Threats: Loss of Privacy

Wire tapping

Acquiring and using another person's identifier and
password to gain access

Blackmail

Software failure giving greater access than normal

Inadequate staff training

Viewing unauthorized data and disclosing it
Threats: Loss of Integrity

Electronic interference and radiation

Data corruption due to power loss or surge

Fire (electrical fault/lightning strike)

Inadequate staff training

Physical damage to equipment

Breaking cables or disconnection of cables

Introduction of viruses
Threats: Loss of availability










Electronic interference and radiation
Arson
Bomb attack
Fire (electrical fault/lightning strike)
Flood
Debris from aircraft
Hardware failures such as disk head crash
Physical damage to equipment
Breaking cables or disconnection of cables
Introduction of viruses
Summary of Threats to
Computer Systems
Typical Multi-user Computer
Environment
Countermeasures – Computer-Based
Controls

View


Is the dynamic result of one or more relational
operations operating on the base relations to produce
another relation.
A view is a virtual relation that does not actually
exist in the database, but is produced upon
request by a particular user, at the time of request.
Countermeasures – Computer-Based Controls

Backup


Process of periodically taking a copy of the database
and log file (and possibly programs) to offline storage
media.
Journaling

Process of keeping and maintaining a log file (or
journal) of all changes made to database to enable
effective recovery in event of failure.
Countermeasures – Computer-Based Controls

Checkpointing


Point of synchronization between the database and the
transaction log file. All buffers are force-written to
secondary storage.
Integrity

Prevents data from becoming invalid, hence giving
misleading or incorrect results.
Countermeasures – Computer-Based Controls

Encryption

The encoding of the data by a special algorithm that
renders the data unreadable by any program without
the decryption key.
Countermeasures – Computer-Based Controls







Associated Procedures
Authorization and Authentication
Backup
Recovery
Audit
Installation of new application software
Installation/upgrading of system software
Authorization / Authentication

Subject : User or Program
Object :Database table, view, application, procedure
or another system object

Authorization

 The granting of a right or privilege which enables a subject to
legitimately have access to a system or object.

Authentication
 A mechanism by which a subject is determined to be the
genuine subject that they claim to be.
Authentication - User and Group Identifiers
19
Authentication - Access Control Matrix
20
Privileges

Most DBMSs provide the facility to create a named database
within which a user can create tables, views, indexes, …

Different types of privileges that can be granted to a subject:
 use of a specific named database;
 selection or retrieval of data;
 creation of tables and other objects;
 update of data;
 deletion of data;
( eventually restricted to some columns)
 insertion of data;
 execution of specific procedures;
 creation of databases;
 creation of DBMS user identifiers;
 group privileges eventually with inheritance.
Associated procedures





Checkpointing
Encryption
Authorization and authentication
Recovery
Audit controls for :
 ensuring accuracy of input data;
 ensuring accuracy of processing;
 prevention and detection of errors during program execution;
 testing and documenting program development and
maintenance;
 avoiding unauthorized program alteration;
 granting and monitoring access to data;
 ensuring documentation is up to date.
Data protection and privacy laws

Privacy
Concerns the right of an individual not to have
personal information collected, stored and
disclosed either willfully or indiscriminately.

Data Protection
The protection of personal data from unlawful
acquisition, storage and disclosure, and the
provision of necessary safeguards to avoid the
destruction or corruption of the legitimate data held.