Chapter 2: Database Integrity, Security and Recovery

1
 Outline

Database integrity
Database recovery
Database security

2
 2.1. Database Integrity

• Database integrity – refers to correct processing of a database such as applying the appropriate business rules when
performing a database operations.
• It means that data stored in a database are accurate, reliable and consistent.
Data Integrity :
Integrity: It refers to accuracy or validation of the data.
• Several ways to ensure data integrity:
• Domain integrity
• Entity integrity constraint
• Referential integrity
• Business rules
• Database consistency
 Domain integrity
• Means entries in a field or column from the same domain
• Validation rules can be applied to trap errors
 Entity integrity constraint
• Each row in a relation must be unique
• Primary key shows the uniqueness of a rows, cannot be NULL (called entity integrity constraint)
 Referential integrity
• Means that if a table has a foreign key, then a rows of the key must be exist in the referenced table. 3
 Cont’d…
 Business rules
• Relationship between entities define the business rules.
 Database consistency
• Must be consistent before and after a transaction
• All database integrity constraints are satisfied
Ways data integrity can be lost
 Human error
 Natural disasters
 Worms and viruses
 Hardware malfunctions
 Malicious deletion or changing of data

4
 Data Security Requirements
The basic security standards which technologies can assure that are the security goals: confidentiality, integrity and availability.
1. Confidentiality: It consists of following aspects :
• Access control - Access to data is controlled by means of privileges, roles and user accounts.
• Authenticated users – Authentication is a way of implementing decisions of whom to trust. It can be employ passwords, finger prints etc.
• Secure storage of sensitive data – It is required to prevent data from hackers who could damage the sensitive data.
• Privacy of communication - The DBMS should be capable of controlling the spread of confidential personal information from unauthorized people
such as credit cards etc.
2. Integrity: Integrity contributes to maintaining a secure database by preventing the data from becoming invalid and giving misleading results. it’s
the requirement that information be protected without( improper) modification. It consists of following aspects :
• System and object privileges control access to applications tables and system commands so that only authorized users can change the data.
• Integrity constraints are applied to maintain the correctness and validity of the data in the database.
• Database must be protected from viruses so firewalls and anti-viruses should be used.
• Ensures that access to the network is controlled and data is not vulnerable to attacks during transmission across network.
3. Availability: Data should always be made available for the authorized user by the secure system without any delays.
• Availability is often thought of as a continuity of service assuring that database is available.
Denial of service attacks are attempts to block authorized users ability to access and use the system when needed. It has number of aspects.
• Ease of use – Resources managed by users for working with databases should be effectively managed so that it is available all the time to valid users.
• Flexibility – Administrators must have all the relevant tools for managing user population.
• Scalability - System performance should not get affected by the increase in number of users or processes which require services from system.
• Resistance – User profiles must be defined and the resource used by any user should be limited.
5
 Database Integrity Constraints
 Constraints :
• It can be defined in 3 ways of Constraints:
1) Business constraints– A value in one column may be constrained by value of some
another or by some calculation or formulae.
2) Entity constraints– Individual columns of a table may be constrained e.g. Not null.
3) Referential constraints – Sometimes referred to as key constraints. E.g. Table two
depends upon table one.

6
 2.1.1 Integrity Concept

• In Database Management Systems, integrity constraints are pre-defined set

of rules that are applied on the table fields(columns) or relations to ensure that
the overall validity, integrity, and consistency of the data present in the
database table is maintained.
• Evaluation of all the conditions or rules mentioned in the integrity constraint
is done every time a table insert, update, delete, or alter operation is
performed.
• The data can be inserted, updated, deleted, or altered only if the result of the
constraint comes out to be True.
• Thus, integrity constraints are useful in preventing any accidental damage to
the database by an authorized user.

7
 2.1.2. Integrity Constraints
• Integrity constraints are a set of rules. It is used to maintain the quality of information.
• Integrity constraints ensure that the data insertion, updating, and other processes have to be performed in such a way that
data integrity is not affected.
• Thus, integrity constraint is used to guard against
accidental damage to the database.
2.1.3 Types of Integrity Constraint
• There are four types of Integrity Constraints.

1. Domain constraints
• Domain constraints can be defined as the definition of a valid set of values for an attribute.
• The data type of domain includes string, character, integer, time, date, currency, etc.
• The value of the attribute must be available in the corresponding domain.
Example:

8
 Types of Integrity Constraint(cont’d…)
2. Entity integrity constraints
• The entity integrity constraint states that primary key value can't be null.
• This is because the primary key value is used to identify individual rows in
relation and if the primary key has a null value, then we can't identify those rows.
• A table can contain a null value other than the primary key field.
Example:

3. Referential Integrity Constraints

• A referential integrity constraint is specified between two tables.
• In the Referential integrity constraints, if a foreign key in Table 1 refers to the
Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be
null or be available in Table 2. Example:
9
 Types of Integrity Constraint(cont’d…)

4. Key constraints
• Keys are the entity set that is used to identify an entity within its entity set uniquely.
• An entity set can have multiple keys, but out of which one key will be the primary key.
• A primary key can contain a unique and null value in the relational table.
• Example:

10
 Benefits Of Using Constraints

Guaranteed integrity and consistency

Defined as a part of table definition
Applies across all applications
Cannot be circumvented
Application development and productivity
Requires no special programming
Easy to specify and maintain
Defined once only

11
 Database Recovery
• Several approaches to recover from system failures
• Backup failure – makes a copies of the database
• Journalizing facilities – used to store the audit trails of transactions and database changes
• Checkpoint facilities – will refuse to accept any new transaction
• Recovery manager – restore the database correctly after a failure has occurred.
• Types of database failure
• Aborted transaction – to correct the errors, the system must roll back by undoing the steps for the
transaction
• Incorrect data – updating a database correctly but with incorrect data
• System failure – power failure, disk crashed
• Database destruction – part of database may be destroyed
Basic Recovery Concepts
• Backup mechanism – it makes periodic backup copies of the database.
• Logging concept – that keeps the track of current state of transaction and the changes made in the database.
• Check pointing mechanism – that enables update to be made permanent

12
 2.2. Database Security
• It measures used to ensure that data has integrity.
• It protects data from unauthorized access.
• Security: it is protecting the database from unauthorized access, alteration or deletion.
• Database security is the mechanisms that protect the database against intentional or accidental threats. 
Database Security Issues
Database security is abroad area that addresses many issues. Include the
following :
•Legal and ethical issues
• Right of access
• Privacy laws
•Policy issues
• Government, institutional or corporate policies
•System-related issues
• Where should security be handled: HW, OS or DBMS
•Multiple security levels 
• Categorization of data and users based on these classifications
• Example: top secret, secret, confidential, unclassified 13
 2.2.1 Database threats or Threats to Databases security
All data must be protected from all types of threats
• Loss of integrity
• If the data should not be corrupted, through intentional or accidental acts.
• Improper modification of information
• Loss of availability
• If the data should remain accessible to those who have legitimate access rights.
• Legitimate user cannot access data objects
• Loss of confidentiality
• If the data should not be accessible to those who do not have legitimate access rights.
• Unauthorized disclosure of confidential information
• Loss of privacy
Threat to a database may be intentional or accidental.
• Accidental threats
• It caused by accidents such as operator carelessness, power failure, disk crashes and fire.
• Intentional threats
• It caused by human, to exploit weaknesses in the system for personal gain. Such as unauthorized access to
database.

14
 2.2.2 Identification and Authentication
• Authorization is a process of permitting users to perform certain operations on certain data objects in a
shared database.
• Authorization is the granting of a right or privilege that enables a subject to have legitimate access to a system
or a system‘s object. The process of authorization involves authentication of subjects (i.e. a user or program)
requesting access to objects (i.e. a database table, view, procedure, trigger, or any other object that can be
created within the system).
• Authorization controls, also known as access controls can be built into the software, and govern not only what
system or object a specified user can access, but also what the user may do with it.
• There are different forms of user authorization on the resource of the database. These forms are privileges on
what operations are allowed on a specific data object.
• User authorization on the data/extension:
1. Read Authorization: the user with this privilege is allowed only to read the content of the data object.
2. Insert Authorization: the user with this privilege is allowed only to insert new records or items to the data object.
3. Update Authorization: users with this privilege are allowed to modify content of attributes but are not authorized to
delete the records.
4. Delete Authorization: users with this privilege are only allowed to delete a record and not anything else.
• Different users, depending on the power of the user, can have one or the combination of the above forms of authorization
on different data objects.
15
 2.2.3 Types of Database Counter measures
Four main control measures are used to provide security of data in databases:
•Access control
• Handled by creating user accounts and passwords identify database users and to control login process
by the DBMS.
• To provisions for restricting access to the database as a whole.
•Inference control
• Statistical or summary data may allow users to infer or deduce information. Such inference must not
allow inference of data that user is not authorized to access.
• It used to provide statistical information or summaries of values based on various criteria.
• Must ensure information about individuals cannot be accessed

•Flow control
• It prevents information from flowing in such a way that it reaches unauthorized users.
• Covert channels, which allow data to flow in manners violating security must be blocked.
•Data Encryption
• Used to protect sensitive transmitted data
• Encryption protects sensitive data during storage and transmission
• A final security issue is data encryption
• Passwords, SSNs, credit card information …
• It used to protect sensitive data (such as credit card numbers) that is being transmitted via some type
communication network.
16
 Database Security Mechanisms
Two types of database security mechanisms
1.Discretionary security mechanisms
• Privilege grants and revokes allow specific users to perform specific
operations on specific data.
• Initial grants start with DBA
• Grants may be passed on between users
• Used to grant privileges to users
2.Mandatory security mechanisms
• Enforce multi-level security
• Classify data and users into various security classes
• Implement security policy
• Typically, user can only see data which has a lower (or same) classification
as themselves
• Role-based security is similar
17
 Database Security and the DBA
• The database administrator (DBA) is the central authority for managing(administering) a database system thus
responsible for overall security.
• The database administrator is responsible to make the database to be as secure as possible. For this the DBA
should have the most powerful privilege than every other user.
• The DBA provides capability for database users while accessing the content of the database.

• The DBA is responsible for the overall security of the database system. The DBA’s security responsibilities
include
• granting privileges to users who need to use the system

• classifying users and data in accordance with the policy of the organization
• DBA-privileged commands
• Account creation
• Privilege granting
• Privilege revocation
• Security level assignment
18
 Cont’d...
• The major responsibilities of DBA in relation to authorization of users are:
1. Account Creation: involves creating different accounts for different USERS as well as USER GROUPS.
2. Security Level Assignment: involves in assigning different users at different categories of access levels.
3. Privilege Grant: involves giving different levels of privileges for different users and user groups.
4. Privilege Revocation: involves denying or canceling previously granted privileges for users due to various
reasons.
5. Account Deletion: involves in deleting an existing account of users or user groups. It is similar with
denying all privileges of users on the database.
• The DBA has a DBA account in the DBMS System / root /super user account sometimes these are called a
system or super user account.
• These accounts provide powerful capabilities that allows such as:
1. Account creation – access control
2. Privilege granting – discretionary
3. Privilege revocation – discretionary
4. Security level assignment – mandatory

Action 1 is access control, whereas 2 and 3 are discretionary and 4 is used to control mandatory
authorization.
19
 Access Protection and Audits
• Login Session: user logs in with account/password
• DBMS tracks all operations applied by a user throughout each login session.
• Can be tracked in system log, which records all operations for recovery from a transaction failure or
system crash.
• A log used primarily for security purposes is an audit trial.
• A database audit is performed when tampering is suspected.
• Logs are reviewed to try to identify what happened and who did it.

• If any tampering with the database is suspected, a database audit is performed.

• A database audit consists of reviewing the log to examine all accesses and operations applied to the
database during a certain time period.

• A database log that is used mainly for security purposes is sometimes called an audit trail.

20
 Discretionary Access Control
• The typical method of enforcing discretionary access control in a database
system is based on the granting and revoking privileges.
Types of Discretionary Privileges
1. The account level:
At this level, the DBA specifies the particular privileges that each account
holds independently of the relations in the database.
2. The relation level (or table level):
At this level, the DBA can control the privilege to access each individual
relation or view in the database.

21
 Account Level Privileges
The privileges at the account level apply to the capabilities provided to the
account itself and can include as follows:

•CREATE SCHEMA or CREATE TABLE privilege

•CREATE VIEW privilege
•ALTER privilege
•DROP privilege
•MODIFY privilege execute insert, delete, or update, SELECT privilege
•Privilege names are based on corresponding SQL commands.
•Account level privileges are not specified by SQL standard, left to DBMS
to define.

22
 Relation Level Privileges
The second level of privileges applies to the relation level. This includes base relations
and virtual (view) relations.
• These privileges are specified by SQL standard
• SELECT privilege on R:
• privilege to use the SELECT statement to retrieve tuples from R.
• UPDATE, DELETE and INSERT privileges on R:
• Capability to modify tuples of R
• Both the INSERT and UPDATE privileges can specify that only certain attributes can
be modified
• REFERENCES privilege on R:
• Capability to reference relation R when specifying integrity constraints.
• Can also be restricted to specific attributes of R
 The granting and revoking of privileges generally follow an authorization model for
discretionary privileges known as the access matrix model where rows and columns
represents.
23
 Access Matrix Model
The Access Matrix Model is a way of keeping track of discretionary
privileges.
•Rows represents subjects
• (users, accounts, programs)
•Columns represent objects
• (relations, records, columns, views, operations)
•Each position M(i, j) represents the types of privileges (read, write, update)
that subject i holds on object j

24
 Revoking Privileges
• Revoking privileges takes them away
• Sometimes it is desirable to grant a privilege to a user temporarily, then revoke it.
• Example: The owner of a relation may want to grant the SELECT privilege to a user for a specific task and then
revoke that privilege once the task is completed.
Hence, a mechanism for revoking privileges is needed.
• In SQL a REVOKE command is included for the purpose of canceling privileges .

Revoke select ON Employee from user;

Examples
• Suppose that the DBA creates four accounts:
A1, A2,A3,A4.
• A1 should be able to create base relations. DBA must issue the following GRANT command in
SQL:
GRANT CREATETAB TO A1;
• Same effect can be accomplished by having the DBA issue a CREATE SCHEMA command, as follows:

CREATE SCHEMA CUSTOMER AUTHORIZATION A1;

A1 can now create tables under the schema called CUSTOMER.
25
 Cont...

• A1 creates the two base relations:

EMPLOYEE and DEPARTMENT
• A1 is then owner of these two relations and has all relation privileges on
each of them
• A1 grants A2 the privilege to insert and delete tuples in both of these
relations, but
A2 cannot propagate these privileges to others:

grant insert, delete on employee , department to A2;

26
 Cont...
• A1 allows A3 to retrieve information from both tables and also allows A3
to propagate the privilege to other accounts:
GRANT SELECT ON EMPLOYEE, DEPARTMENT TO A3
WITH GRANT OPTION;

• A3 can grant the SELECT privilege on the EMPLOYEE relation to A4:

GRANT SELECT ON EMPLOYEE TO A4;

A4 can’t propagate the SELECT privilege since GRANT OPTION was not given to A4

27
 Cont...

• A1 decides to revoke the SELECT privilege on the EMPLOYEE relation

from A3:

REVOKE SELECT ON EMPLOYEE FROM A3;

• DBMS must now automatically revoke the SELECT privilege on

EMPLOYEE from A4.

28
 Cont‘d...
• A1 wants to give back to A3 a limited capability to SELECT from the
EMPLOYEE relation with ability to propagate the privilege.
• limited to retrieve only the NAME, BDATE, and ADDRESS attributes and only for
the tuples with DNO=5.
A1 then can create the following view:
create view A3employee as select name, bdate, address
from employee
where Dno = 5;
• After the view is created,
A1 can grant SELECT on the view A3EMPLOYEE to A3 as follows:
grant select on A3employee to A3
with grant option;

29
 Example

tuna owns relations:

Cities(name, state, population)
States(name, abbreviation, capital, area, population)

tuna: GRANT SELECT,UPDATE ON Cities TO shark

WITH GRANT OPTION;
 
tuna: GRANT SELECT ON Cities TO minnow;
 
tuna: GRANT SELECT ON States TO shark, minnow
WITH GRANT OPTION;
 
shark: GRANT SELECT ON Cities TO starfish
WITH GRANT OPTION;
shark: GRANT UPDATE(area,population)ON Cities To starfish;

31
 Multilevel Security
• Typical security classes/levels:
Top secret (TS), secret (S), confidential (C), unclassified (U)
TS ≥ S ≥ C ≥ U.
• Bell-LaPadula model classifies each Subject (user, account, program) and
Object (relation, tuple, column, view, operation) into one of the security classifications,
T, S, C, or U.
We will refer to
• class(S)  clearance (classification) of a subject (S)
A subject S is not allowed read access to an object O unless class(S)≥class(O).
This is known as the simple security property.
• class(O)  classification of an object(O)
A subject S is not allowed to write an object O unless class(S) ≤ class(O).
This is known as the star property (or *-property).

34
 Comparing DAC and MAC
• Discretionary Access Control (DAC) policies:
• + high degree of flexibility
• + suitable for a large variety of application domains
• - vulnerable to malicious attacks, such as Trojan horses embedded in application
programs.
• Mandatory Access Control (MAC) policies:
• + ensure a high degree of protection
• + prevent illegal flow of information
• - too rigid applicable in limited environments
• In many practical situations, DAC is preferred why?
• because they offer a better trade-off between security and applicability.

36
 Role-Based Access Control
• Role-based access control (RBAC)
• emerged rapidly in the 1990s
• suitable for managing and enforcing security in large-scale enterprise-wide systems
• Permissions are associated with roles, and users are assigned to appropriate roles.
• avoid overhead of managing each individual’s privileges
• The Commands to use
Roles are created using CREATE ROLE and DESTROY ROLE commands.
• GRANT and REVOKE commands can then be used to assign and revoke privileges from
roles
• RBAC ensures that only authorized users are given access to certain data or
resources.
• Many DBMSs support roles
• A role hierarchy is a natural way of organizing roles to reflect the organization’s
lines of authority and responsibility
• RBAC systems may allow temporal constraints on roles
• time and duration of role activations
• timed triggering of a role by an activation of another role
37
 EXAMPLE
create role bigfish;
grant select any table to bigfish;
grant bigfish to tuna, flounder;

Tuna and Flounder now have all privileges available to the Bigfish role.

38
 E-Commerce Access Control
• E-Commerce environments (and similar web environments) required
elaborate policies
• beyond traditional DBMS access control
• e-commerce environment resources include not only traditional data, but also
knowledge and experience.
• Access control mechanism should be flexible enough to support a wide spectrum of
heterogeneous objects.
• Role-based models have promise for addressing the key security requirements
of Web-based applications.

• In contrast, DAC and MAC models lack capabilities needed to support

security requirements of emerging enterprise and Web-based applications.

39
 Statistical Database Security
• Statistical databases are used mainly to produce statistics on various populations.
• Database may contain confidential data on individuals, which should be protected
from unauthorized access.
• General users are only permitted to retrieve statistical information on the
populations,
such as averages, sums, counts, maximums, minimums, and standard deviations.
• Statistical database security techniques must prohibit the retrieval of individual data.
• Allowed:
• retrieve the number of individuals in a population
• retrieve the average income of the population
• Not Allowed:
• retrieve individual data, such as the income of a specific person
• This can be achieved by prohibiting queries that retrieve attribute values and
allowing only queries using statistical aggregate functions

41
 cont’d…
• In some cases it is possible to infer the values of individual tuples from a sequence
statistical queries
• particularly true when the conditions result in a population consisting of a small
number of objects
• Example:
• Following are allowable queries:
select count(*) from person where <condition>;
select avg(income) from person where <condition>;

• Suppose condition on both queries is

last_degree=‘ph.d.’ and sex=‘f’ and city=‘Jimma’ and state=‘tx’;

• If first query returns 1, then we have an individual’s income.

• If we can match the condition to that one actual person, we have gained prohibited
information about that person

42
 Covert Channels
• A covert channel allows a transfer of information that violates the security or
the policy.
• allows information to pass from a higher classification level to a lower classification
level through improper means.
• Two broad categories:
• Storage channels
The information is conveyed by accessing system information or information otherwise
inaccessible to the user.
• Timing channel
allow the information to be conveyed by the timing of events or processes.
• One way to avoid covert channels:
• programmers to not actually gain access to sensitive data that a program is supposed to
process after the program has been put into operation.

44
 Data Encryption
• Encryption is the process of encoding the data by a special algorithm that extracts the data
unreadable by any program without the decryption key.
• Encryption is a means of maintaining secure data in an insecure environment.
• It consists of applying an encryption algorithm to data using some pre-specified
encryption key.
• The resulting data has to be decrypted using a decryption key to recover the original
data.
• If a database system holds particularly sensitive data, it may be deemed necessary to
encode it as a precaution against possible external threats or attempts to access it.
• The DBMS can access data after decoding it, although there is a degradation in
performance because of the time taken to decode it
• Encryption also protects data transmitted over communication lines. To transmit data
securely over insecure networks requires the use of a Cryptosystem, which includes:

45
 Ch2-Assignment
Group1
1. Write the types of privileges and explain each and give example ?
2. Write the difference between Grant and Revoke privileges in the SQL
within examples?
Group2:
3. Write the difference and similarity, advantage and disadvantage DAC,MAC
and RBAC for each?
4. Write the SQL injection common to a database system that attacks on
databases?

46