Unit-V:

DATABASE SECURITY AND TRANSACTION

PROCCESSING
Marks:10

Database Management System (DMS) Mr. S. Y. Divekar

Database Management System (DMS) Mr. S. Y. Divekar
Topic:- 5.1 : Outlines
Database Security :
Introduction to Database Security,
Data Security Requirements.
Types of Database User:
Creating altering and deleting Users.

Database Management System (DMS) Mr. S. Y. Divekar

Database Security:
Database security refers to the use of the DBMS features and
other related measures to comply with the security requirements
of the organization. From the DBA’s point of view, security
measures should be implemented to protect the DBMS against
service degradation and the database against loss, corruption, or
mishandling.
To protect the DBMS against service degradation there are certain
minimum recommended security safeguards.
For example:
• Change default system passwords.
• Change default installation paths.
• Apply the latest patches.
• Secure installation folders with proper access rights.
• Make sure only required services are running.
• Set up auditing logs.
• Set up session logging.
• Require session encryption.
Database Management System (DMS) Mr. S. Y. Divekar
Data Security Requirements:
Physical database integrity
Logical database integrity
Element integrity
Auditability
Access control
User authentication
Availability

Database Management System (DMS) Mr. S. Y. Divekar

Data Security Requirements:
Physical database integrity
◦ immunity to physical catastrophe, such as power failures, media failure
◦ physical securing hardware, UPS
◦ regular backups

Logical database integrity

◦ reconstruction Ability
◦ maintain a log of transactions
◦ replay log to restore the systems to a stable point

Element integrity
◦ integrity of specific database elements is their correctness or accuracy
◦ field checks
– allow only acceptable values
◦ access controls
– allow only authorized users to update elements
◦ change log
– used to undo changes made in error
◦ referential Integrity (key integrity concerns)
◦ two phase locking process
Database Management System (DMS) Mr. S. Y. Divekar
Data Security Requirements:
Auditability
◦ log read/write to database

Access Control (similar to OS)

◦ logical separation by user access privileges
◦ more complicated than OS due to complexity of DB
(granularity/inference/aggregation)

User Authentication
◦ may be separate from OS
◦ can be rigorous

Availability
◦ concurrent users
◦ granularity of locking
◦ reliability

Database Management System (DMS) Mr. S. Y. Divekar

Types of Database User:
Database users are categorized based up on their interaction with
the data base.
These are seven types of data base users in DBMS.
Database Administrator (DBA) :
Database Administrator (DBA) is a person/team who defines the
schema and also controls the 3 levels of database.
The DBA will then create a new account id and password for the
user if he/she need to access the data base.
DBA is also responsible for providing security to the data base and
he allows only the authorized users to access/modify the data
base.
◦ DBA also monitors the recovery and back up and provide technical
support.
◦ The DBA has a DBA account in the DBMS which called a system or
superuser account.
◦ DBA repairs damage caused due to hardware and/or software
failures.
Database Management System (DMS) Mr. S. Y. Divekar
Types of Database User:
Naive / Parametric End Users :
Parametric End Users are the unsophisticated who don’t have any
DBMS knowledge but they frequently use the data base
applications in their daily life to get the desired results.
For examples, Railway’s ticket booking users are naive users.
Clerks in any bank is a naive user because they don’t have any
DBMS knowledge but they still use the database and perform
their given task.
System Analyst :
System Analyst is a user who analyzes the requirements of
parametric end users. They check whether all the requirements of
end users are satisfied.

Database Management System (DMS) Mr. S. Y. Divekar

Types of Database User:
Sophisticated Users :
Sophisticated users can be engineers, scientists, business analyst,
who are familiar with the database. They can develop their own
data base applications according to their requirement. They don’t
write the program code but they interact the data base by writing
SQL queries directly through the query processor.
Data Base Designers :
Data Base Designers are the users who design the structure of
data base which includes tables, indexes, views, constraints,
triggers, stored procedures. He/she controls what data must be
stored and how the data items to be related.

Database Management System (DMS) Mr. S. Y. Divekar

Types of Database User:
Application Program :
Application Program are the back end programmers who writes
the code for the application programs. They are the computer
professionals. These programs could be written in Programming
languages such as Visual Basic, Developer, C, FORTRAN, COBOL
etc.
Casual Users / Temporary Users :
Casual Users are the users who occasionally use/access the data
base but each time when they access the data base they require
the new information, for example, Middle or higher level
manager

Database Management System (DMS) Mr. S. Y. Divekar

Creating Users:
You create a database user with the CREATE USER statement.
To create a user, you must have the CREATE USER system
privilege.
Because it is a powerful privilege, a DBA or security administrator
is normally the only user who has the CREATE USER system
privilege.
A newly created user cannot connect to the database until
granted the CREATE SESSION system privilege.
CREATE USER bela
IDENTIFIED BY bela99
DEFAULT TABLESPACE DBS_space
QUOTA 10M ON DBS_space
TEMPORARY TABLESPACE temp_space
PROFILE STAFF
PASSWORD EXPIRE
;

Database Management System (DMS) Mr. S. Y. Divekar

Alter Users:
Use the ALTER USER statement:
To change the authentication or database resource characteristics
of a database user
To permit a proxy server to connect as a client without
authentication
ALTER USER role_name IDENTIFIED
BY password REPLACE prev_password;
Drop Users:
The DROP USER statement is used to remove a user from the
Oracle database and remove all objects owned by that user.
DROP USER user_name [ CASCADE ];
DROP USER SYS CASCADE;

Database Management System (DMS) Mr. S. Y. Divekar

Topic:- 5.2 : Outlines
Protecting the data within database –
Database Privileges: System Privileges and Object
Privileges.
Granting and Revoking Privileges:
Grant and Revoke Command.

Database Management System (DMS) Mr. S. Y. Divekar

Protecting the data within database :
Data Control Language(DCL) is used to control privileges in
Database. To perform any operation in the database, such as for
creating tables, sequences or views, a user needs privileges.
Database Privileges: System Privileges and Object Privileges.
System: This includes permissions for creating session, table, etc
and all types of other system privileges.
Object: This includes permissions for any command or query to
perform any operation on the database tables.
DCL have two commands,
GRANT: Used to provide any user access privileges or other
priviliges for the database.
REVOKE: Used to take back permissions from any user.

Database Management System (DMS) Mr. S. Y. Divekar

GRANT :
Grants a privilege to a user It means that giving authority to other
user by administrator If you are administrator then only you have
authority for grating the other authority to other user Can grant
privilege only if you have been granted that privilege.
Syntax:
GRANT < Object Privileges > ON <ObjectName> TO <UserName>
[WITH GRANT OPTION];

Database Management System (DMS) Mr. S. Y. Divekar

OBJECT PRIVILEGES:
Each object privilege that is granted authorizes the grantee to
perform some operation on the object. A user can grant all the
privileges or grant only specific object privileges.
The list of object privileges is as follows:
ALTER : Allows the grantee to change the table definition with the
ALTER TABLE command
DELETE : Allows the grantee to remove the records from the table
with the DELETE command
INDEX : Allows the grantee to create an index on the table with the
CREATE INDEX command
INSERT : Allows the grantee to add records to the table with the
INSERT command
SELECT : Allows the grantee to query the table with the SELECT
command
UPDATE : Allows the grantee to modify the records in the tables
with the UPDATE command
Database Management System (DMS) Mr. S. Y. Divekar
GRANT :
Example:
Give the user POLY permission to only view and modify records in
the table Student.

GRANT SELECT, UPDATE ON Student TO POLY;

Grant succeeded.

Database Management System (DMS) Mr. S. Y. Divekar

REVOKE:
The REVOKE statement is used to deny the grant given on an
object. Revokes a privilege from a user It is use to taking off or
remove of authority or say getting back authority from user.
Syntax:
REVOKE < Object Privileges > ON <Object Name> FROM
<Username>;
Example:
All privileges on the table Student have been granted to POLY.
Take back the Delete privilege on the table.
REVOKE DELETE ON Student FROM POLY;
Revoke succeeded.

Database Management System (DMS) Mr. S. Y. Divekar

Topic:- 5.3 : Outlines
Transaction Concepts,
 Properties of Transaction [ACID Properties].
States of Transaction.

Database Management System (DMS) Mr. S. Y. Divekar

Transactions :
A Database Transaction is a logical unit of processing in a DBMS
which entails one or more database access operation.
A transaction is a single logical unit of work which accesses and
possibly modifies the contents of a database. Transactions access
data using read and write operations.
All types of database access operation which are held between
the beginning and end transaction statements are considered as a
single logical transaction in DBMS.
During the transaction the database is inconsistent. Only once the
database is committed the state is changed from one consistent
state to another.

Database Management System (DMS) Mr. S. Y. Divekar

Transactions :

Database Management System (DMS) Mr. S. Y. Divekar

ACID Properties:
A transaction is a very small unit of a program and it may contain
several low level tasks. A transaction in a database system must
maintain Atomicity, Consistency, Isolation, and Durability −
commonly known as ACID properties − in order to ensure
accuracy, completeness, and data integrity.

Database Management System (DMS) Mr. S. Y. Divekar

Atomicity :
A transaction is a single unit of operation. You either execute it
entirely or do not execute it at all. There cannot be partial
execution.
By this, we mean that either the entire transaction takes place at
once or doesn’t happen at all. There is no midway i.e.
transactions do not occur partially.
Each transaction is considered as one unit and either runs to
completion or is not executed at all. It involves the following two
operations.
—Abort: If a transaction aborts, changes made to database are
not visible.
—Commit: If a transaction commits, changes made are visible.
Atomicity is also known as the ‘All or nothing rule’.

Database Management System (DMS) Mr. S. Y. Divekar

Atomicity :
Consider the following transaction T consisting of T1 and T2:
Transfer of 100 from account X to account Y.

If the transaction fails after completion of T1 but before

completion of T2.( say, after write(X) but before write(Y)), then
amount has been deducted from X but not added to Y. This
results in an inconsistent database state. Therefore, the
transaction must be executed in entirety in order to ensure
correctness of database state.

Database Management System (DMS) Mr. S. Y. Divekar

Consistency :
Once the transaction is executed, it should move from one
consistent state to another.
This means that integrity constraints must be maintained so that
the database is consistent before and after the transaction. It
refers to the correctness of a database. Referring to the example
above,
The total amount before and after the transaction must be
maintained.
Total before T occurs = 500 + 200 = 700.
Total after T occurs = 400 + 300 = 700.
Therefore, database is consistent. Inconsistency occurs in
case T1 completes but T2 fails. As a result T is incomplete.

Database Management System (DMS) Mr. S. Y. Divekar

Isolation :
Transaction should be executed in isolation from other
transactions (no Locks). During concurrent transaction execution,
intermediate transaction results from simultaneously executed
transactions should not be made available to each other. (Level
0,1,2,3).
This property ensures that multiple transactions can occur
concurrently without leading to the inconsistency of database
state.
Transactions occur independently without interference. Changes
occurring in a particular transaction will not be visible to any
other transaction until that particular change in that transaction is
written to memory or has been committed.
This property ensures that the execution of transactions
concurrently will result in a state that is equivalent to a state

Database Management System (DMS) Mr. S. Y. Divekar

Isolation :
achieved these were executed serially in some order.
Let X= 500, Y = 500.
Consider two transactions T and T”.

Suppose T has been executed till Read (Y) and then T’’ starts. As a
result , interleaving of operations takes place due to
which T’’ reads correct value of X but incorrect value of Y and
sum computed by
T’’: (X+Y = 50, 000+500=50, 500)
is thus not consistent with the sum at end of transaction:
T: (X+Y = 50, 000 + 450 = 50, 450).
This results in database inconsistency, due to a loss of 50 units.
Hence, transactions must take place in isolation and changes
should be visible only after they have been made to the main
memory.
Database Management System (DMS) Mr. S. Y. Divekar
Durability :
After successful completion of a transaction, the changes in the
database should persist. Even in the case of system failures.

This property ensures that once the transaction has completed

execution, the updates and modifications to the database are
stored in and written to disk and they persist even if a system
failure occurs.
These updates now become permanent and are stored in non-
volatile memory. The effects of the transaction, thus, are never
lost.

Database Management System (DMS) Mr. S. Y. Divekar

States of Transactions:
States through which a transaction goes during its lifetime.
These are the states which tell about the current state of the
Transaction and also tell how we will further do processing we
will do on the transactions.
These states govern the rules which decide the fate of the
transaction whether it will commit or abort.

Database Management System (DMS) Mr. S. Y. Divekar

States of Transactions:
These are different types of Transaction States :
Active State –
When the instructions of the transaction is running then the
transaction is in active state. If all the read and write operations
are performed without any error then it goes to “partially
committed state”, if any instruction fails it goes to “failed state”.

Partially Committed –
After completion of all the read and write operation the changes
are made in main memory or local buffer. If the changes are
made permanent on the Data Base then state will change to
“committed state” and in case of failure it will go to “failed state”.

Database Management System (DMS) Mr. S. Y. Divekar

States of Transactions:
Failed State –
When any instruction of the transaction fails it goes to “failed
state” or if failure occurs in making permanent change of data on
Data Base.

Aborted State –
After having any type of failure the transaction goes from “failed
state” to “aborted state” and in before states the changes are
only made to local buffer or main memory and hence these
changes are deleted or rollback.

Database Management System (DMS) Mr. S. Y. Divekar

States of Transactions:
Committed Stage –
It is the stage when the changes are made permanent on the Data
Base and transaction is complete and therefore terminated in
“terminated state”.

Terminated State –
If there is any roll back or the transaction come from “committed
state” then the system is consistent and ready for new transaction
and the old transaction is terminated.

Database Management System (DMS) Mr. S. Y. Divekar

Topic:- 5.4 : Outlines
Database backup Introduction.
 Types of Database Backups
- Physical
- Logical.
Database Failures-,
Types of Failures.
Causes of Failures.

Database Management System (DMS) Mr. S. Y. Divekar

Database Backup:
Database Backup is storage of data that means the copy of the
data.
It is a safeguard against unexpected data loss and application
errors.
It protects the database against data loss.
If the original data is lost, then using the backup it can
reconstructed.
The backups are divided into two types:
1. Physical Backup
2. Logical Backup

Database Management System (DMS) Mr. S. Y. Divekar

Database Backup:
1. Physical backups:- Physical Backups are the backups of the
physical files used in storing and recovering your database, such as data
files, control files and archived redo logs, log files.
It is a copy of files storing database information to some other location,
such as disk, some offline storage like magnetic tape.
Physical backups are the foundation of the recovery mechanism in the
database.
Physical backup provides the minute details about the transaction and
modification to the database.
2. Logical backup:- Logical Backup contains logical data which is
extracted from a database.
It includes backup of logical data like views, procedures, functions,
tables, etc.
It is a useful supplement to physical backups in many circumstances
but not a sufficient protection against data loss without physical
backups, because logical backup provides only structural information.

Database Management System (DMS) Mr. S. Y. Divekar

Database Backup:
Importance of Backups
Planning and testing backup helps against failure of media,
operating system, software and any other kind of failures that
cause a serious data crash.
It determines the speed and success of the recovery.
Physical backup extracts data from physical storage (usually from
disk to tape). Operating system is an example of physical backup.
Logical backup extracts data using SQL from the database and
store it in a binary file.
Logical backup is used to restore the database objects into the
database. So the logical backup utilities allow DBA (Database
Administrator) to back up and recover selected objects within the
database.

Database Management System (DMS) Mr. S. Y. Divekar

Types of Database Backup:
Regular backups are required to protect database and ensure its
restoration in case of failure. Various backup types provide
different protection to our database. Backing up and restoring data is
one of the most important responsibilities of IT professionals.
Three common types of database backups can be run on a desired
system: normal (full), incremental and differential.
i) Normal or Full Backups:
When a normal or full backup runs on a selected drive, all the files on
that drive are backed up. This, of course, includes system files,
application files, user data — everything. Those files are then copied
to the selected destination (backup tapes, a secondary drive or the
cloud), and all the archive bits are then cleared.
Normal backups are the fastest source to restore lost data because all
the data on a drive is saved in one location.

Database Management System (DMS) Mr. S. Y. Divekar

ii) Incremental Backups:
A common way to deal with the long-running times required for
full backups is to run them only on weekends. Many businesses then
run incremental backups throughout the week since they take far less
time. An incremental backup will grab only the files that have been
updated since the last normal backup. Once the incremental
backup has run, that file will not be backed up again unless it changes
or during the next full backup.
iii) Differential Backups:
An alternative to incremental database backups that have a less
complicated restore process is a differential backup. Differential
backups and recovery are similar to incremental in that these backups
grab only files that have been updated since the last normal backup.
However, differential backups do not clear the archive bit. So a file that
is updated after a normal backup will be archived every time a
differential backup is run until the next normal backup runs and clears
the archive bit.

Database Management System (DMS) Mr. S. Y. Divekar

Type of failure :
A database management system is susceptible to a number
of failures.
Soft Failure:
Soft failure is the type of failure that causes the loss in volatile
memory of the computer and not in the persistent storage. Here,
the information stored in the non-persistent storage like main
memory, buffers, caches or registers, is lost. They are also known
as system crash.
The various types of soft failures are as follows −
Operating system failure.
Main memory crash.
Transaction failure or abortion.
System generated error like integer overflow or divide-by-zero
error.
Failure of supporting software.
Power failure.
Database Management System (DMS) Mr. S. Y. Divekar
Type of failure :
Hard Failure
A hard failure is the type of failure that causes loss of data in the
persistent or non-volatile storage like disk. Disk failure may cause
corruption of data in some disk blocks or failure of the total disk.
The causes of a hard failure are −
Power failure.
Faults in media.
Read-write malfunction.
Corruption of information on the disk.
Read/write head crash of disk.
Recovery from disk failures can be short, if there is a new,
formatted, and ready-to-use disk on reserve. Otherwise, duration
includes the time it takes to get a purchase order, buy the disk,
and prepare it.

Database Management System (DMS) Mr. S. Y. Divekar

Type of failure :
Network Failure
Network failures are prevalent in distributed or network
databases. These comprises of the errors induced in the database
system due to the distributed nature of the data and transferring
data over the network.
The causes of network failure are as follows −
Communication link failure.
Network congestion.
Information corruption during transfer.
Site failures.
Network partitioning.

Database Management System (DMS) Mr. S. Y. Divekar

Causes of Database Failures:
A database includes a huge amount of data and transaction.
If the system crashes or failure occurs, then it is very difficult to
recover the database.

There are some common causes of failures such as:

1. System Crash
2. Transaction Failure
3. Network Failure
4. Disk Failure
5. Media Failure

Each transaction has ACID property. If we fail to maintain the ACID

properties, it is the failure of the database system.

Database Management System (DMS) Mr. S. Y. Divekar

Causes of Database Failures:
1. System Crash: System crash occurs when there is a hardware or
software failure or external factors like a power failure.
The data in the secondary memory is not affected when system
crashes because the database has lots of integrity. Checkpoint
prevents the loss of data from secondary memory.
2. Transaction Failure: The transaction failure is affected on only
few tables or processes because of logical errors in the code.
This failure occurs when there are system errors like deadlock or
unavailability of system resources to execute the transaction.
3. Network Failure: A network failure occurs when a client –
server configuration or distributed database system are
connected by communication networks.

Database Management System (DMS) Mr. S. Y. Divekar

Causes of Database Failures:
4. Disk Failure: Disk Failure occurs when there are issues with
hard disks like formation of bad sectors, disk head crash,
unavailability of disk etc.
5. Media Failure: Media failure is the most dangerous failure
because, it takes more time to recover than any other kind of
failures.
A disk controller or disk head crash is a typical example of media
failure.
Natural disasters like floods, earthquakes, power failures, etc.
damage the data.

Database Management System (DMS) Mr. S. Y. Divekar

Topic:- 5.5 : Outlines
Database Recovery- Recovery Concept.
Recovery Techniques-Roll Forward, Rollback.

Database Management System (DMS) Mr. S. Y. Divekar

Database Recovery :
It is the method of restoring the database to its correct state in
the event of a failure at the time of the transaction or after the
end of a process.
Earlier, you have been given the concept of database recovery as
a service that should be provided by all the DBMS for ensuring
that the database is dependable and remains in a consistent state
in the presence of failures.
In this context, dependability refers to both the flexibility of the
DBMS to various kinds of failure and its ability to recover from
those failures.

Database Management System (DMS) Mr. S. Y. Divekar

Database Recovery :
Need for Database Recovery:
Due to hardware or software errors, the system crashes, which
ultimately resulting in loss of main memory.
Failures of media, such as head crashes or unreadable media that
results in the loss of portions of secondary storage.
There can be application software errors, such as logical errors
that are accessing the database that can cause one or more
transactions to abort or fail.
Natural physical disasters can also occur, such as fires, floods,
earthquakes, or power failures.
Carelessness or unintentional destruction of data or directories
by operators or users.
Damage or intentional corruption or hampering of data (using
malicious software or files) hardware or software facilities.

Database Management System (DMS) Mr. S. Y. Divekar

Database Recovery :
Recovery facilities:
Every DBMS should offer the following facilities to help out with
the recovery mechanism:
Backup mechanism makes backup copies at a specific interval for
the database.
Logging facilities keep tracing the current state of transactions
and any changes made to the database.
Checkpoint facility allows updates to the database for getting the
latest patches to be made permanent and keep secure from
vulnerability.
Recovery manager allows the database system for restoring the
database to a reliable and steady-state after any failure occurs.

Database Management System (DMS) Mr. S. Y. Divekar

Database Recovery Techniques :
Rollback: The Rollback transaction is a transaction which rolls back
the transaction to the beginning of the transaction.
The transaction can be rolled back completely by specifying the
transaction name in the Rollback statement or to cancel any
changes to a database during current transaction.
It is permissible to use before Commit transaction.
Rollforward: Recovering a database by applying different
transactions that recorded in the database log files.
It is nothing but re-doing the changes made by a transaction i.e.
after the committed transaction and to over write the changed
value again to ensure consistency.

Database Management System (DMS) Mr. S. Y. Divekar

Database Recovery Techniques :
The Redo Log and Rolling Forward (REDO operation): The redo log
is a set of operating system files that record all changes made to any
database buffer, including data, index, and rollback segments,
whether the changes are committed or uncommitted. The redo log
protects changes made to database buffers in memory that have not
been written to the data files.
The first step of recovery from an instance or disk failure is to roll
forward or reapply all of the changes recorded in the redo log to the
data files. Because rollback data is also recorded in the redo log,
rolling forward also regenerates the corresponding rollback
segments.
Rolling forward proceeds through as many redo log files as necessary
to bring the database forward in time. Rolling forward usually
includes online redo log files and may include archived redo log files.
After roll forward, the data blocks contain all committed changes as
well as any uncommitted changes that were recorded in the redo log.

Database Management System (DMS) Mr. S. Y. Divekar

Database Recovery Techniques :
Rollback Segments and Rolling Back (UNDO operation):
Rollback segments record database actions that should be undone
during certain database operations. In database recovery, rollback
segments undo the effects of uncommitted transactions previously
applied by the rolling forward phase. After the roll forward, any
changes that were not committed must be undone. After redo log
files have reapplied all changes made to the database, then the
corresponding rollback segments are used. Rollback segments are
used to identify and undo transactions that were never committed,
yet were recorded in the redo log and applied to the database during
roll forward. This process is called rolling back.

Database Management System (DMS) Mr. S. Y. Divekar

Thanks

Database Management System (DMS) Mr. S. Y. Divekar