Unit V
Database Security and Transaction Processing
10 Marks
Contents
5.1 Database Security-Introduction to database security, Data security requirements, Types of
Database users-Creating, altering and deleting the users
5.2 Protecting the data within database-Database privileges: System privileges and object
privileges, granting and revoking privileges-grant and revoke command
5.3 Transaction-concept, properties and states of transaction
5.4 Database backup: Types of failures, causes of failures, database backup introduction, types
of database backup-physical and logical
5.5 Database Recovery: Recovery concept, Recovery techniques-Roll forward, Rollback
5.1 Database Security
5.1.1 Introduction to database Security
Q. Explain the database security with its requirements. (4M)
Database security Database security refers to the collective measures used to protect and secure a
database or database management software from illegal use and malicious threats and attacks.
Requirements of Database Security:
1. For prevention of data theft such as bank account numbers, credit card information,
passwords, work related documents or sheets, etc.
2. To make data remain safe and confidential.
3. To provide confidentiality which ensures that only those individuals should ever be able to
view data they are not entitled to.
4. To provide integrity which ensures that only authorized individuals should ever be able
change or modify information.
5. To provide availability which ensure that the data or system itself is available for use when
authorized user wants it.
6. To provide authentication which deals with the desire to ensure that an authorized individual.
7. To provide non-repudiation which deals with the ability to verify that message has been sent
and received by an authorized user.
OR
1. Confidentiality: The principle of confidentiality specifies that only sender and intended
recipients should be able to access the contents of a message. Confidentiality gets compromised
if an unauthorized person is able to access the contents of a message
2. Integrity: when the contents of the message are changed after the sender sends it, but before it
reaches the intended recipient, we say that the integrity of the message is lost.
3. Authentication: Authentication helps to establish proof of identities. The Authentication
process ensures that the origin of a message is correctly identified.
4. Availability: The goal of availability s to ensure that the data, or the system itself, is available
for use when the authorized user wants it.
5.1.3 Types of Database Users
Q. List and explain types of DBMS users.(4M)
1. Naive Users: -
• Naive means Lacking Experience (untrained) these are the users who need not be
aware of the presence of the Data Base System.
• Example of these type of users is the user of an ATM machine. Because these
users only responds to the instructions displayed on the screen (enter your pin
number, click here, enter the required money etc.). Obviously operations
performed by these users are very limited.
2. Application Programmers: -
• Professional / Application programmers are those who are responsible for
developing application programs or user interface. The application programs could
be written in a general-purpose programming language or the commands available
to manipulate a database.
• For example: Writing a C program to generate the report of employees who are
working in particular department, will involve a query to fetch the data from
database. It will include a embedded SQL query in the C Program.
3. Sophisticated Users:-
• Simply we can say that these are the EXPERIENCED users. These people interact
with the system without writing programs. They form requests by writing queries
in database query language.
• Sophisticated users are the users who are familiar with the structure of database &
facilities of DBMS.
4. Specialized Users:-
• These are the sophisticated users who write specialized database applications.
• For example computer aided design (CAD) systems, knowledge-based and
expert systems.
5. Database Administrator:-
• It is responsible for managing the whole database system.
• He/She designs, creates & maintains the database.
• He/She manages the users who can access this database & controls integrity issues.
6. Database Designers:-
• Database designers are the computer professionals which responsible for
developing database orientedapplication /software's.
5.1.3 Creating, Altering and Deleting Users
Q. How to create, alter and delete users?
CREATE USER
The CREATE USER statement allows you to create a new database user which you can use to
log in to the database.
Syntax:
CREATE USER username IDENTIFIED BY password;
CREATE USER username: Specify the name of the user to be created.
IDENTIFIED BY password: Specify a password for the local user to use to log on to the
database.
ALTER USER
The ALTER USER statement allows you to change the authentication or database resource
characteristics of a database user.
Generally speaking, to execute the ALTER USER statement, your account needs to have
the ALTER USER system privilege. However, you can change your own password using
the ALTER USER statement without having the ALTER USER system privilege.
Syntax:
ALTER USER username IDENTIFIED BY password;
DROP USER
The DROP USER statement allows you to delete a user from the Oracle Database. If the user has
schema objects, the DROP USER statement also can remove all the user’s schema objects along
with the user.
Syntax:
DROP USER username [CASCADE];
5.2 Protecting Data within Database
Q. How to protect data within database privilege?
Authorization is permission given to user, program, or process to access an object or set of
objects. The type of data access granted to a user can be read-only, or read and write. Privileges
specify the type of Data Manipulation Language (DML) operations like SELECT, INSERT,
UPDATE, DELETE, etc., which the user can perform upon data.
Privileges
A privilege is permission to access a named object in a prescribed manner; for example,
permission to query a table. Privileges can be granted enable a particular user to connect to the
database (create a session); select rows from someone else's table; or execute someone else's
stored procedure.
Database privileges
A privilege is a right to execute a particular type of SQL statement or to access another user's
object. Some examples of privileges include:
• The right to connect to the database (create a session)
• The right to create a table
• The right to select rows from another user's table
• The right to execute another user's stored procedure
Privileges are granted to users so that these can accomplish tasks required for their job. You
should grant a privilege only to user who absolutely requires the privilege to accomplish
necessary work. Excessive granting of unnecessary privileges can lead to compromised security.
5.2.1 System privileges
Q. Write note on System Privileges. (2M)
A system privilege allows a user to perform administrative actions in database.
These include privileges such as:
1. Create database
2. Create procedure
3. Create view
4. Create table
5. Create trigger
6. Backup database
7. Execute
5.2.2 Object privileges
Q. Write note on Object Privileges. (2M)
Object privileges are privileges given to users as rights and restrictions to change contents of
database object – where database objects are like tables, stored procedures, indexes, etc. Ex.
Select, insert, delete, update, execute, references etc.
5.2.3 Granting and Revoking Privileges
Q. What is the use of Grant and Revoke?
Q. Describe Grant and Revoke commands.
Q. Give the use of grant and revoke command with syntax and example.
Procedure for granting privileges Grant:
This command is used to give permission to user to do operations on the other user’s object.
Syntax: GRANT <object_privileges> ON <object_name> TO <username>[with grant option];
Example: Grant slect,update on emp to user1;
Examples
• Grant the SELECT authority on the EMP table to all users.
GRANT SELECT ON EMP TO PUBLIC;
• Grant the SELECT, DELETE and UPDATE authority on DEPT table to user 'AJAY'.
GRANT SELECT, DELETE, UPDATE ON DEPT TO AJAY;
• Grant the SELECT.' DELETE and UPDATE authority with the capability to grant those
privileges to others users on DEPT table to user 'AJAY'.
GRANT SELECT, UPDATE ON DEPT TO AJAY WITH GRANT OPTION;
• Grant ALL privileges on EMP table to user 'DEEP'.
GRANT ALL ON EMP TO DEEP;
• Give the system privileges for creating tables and views to 'AJAY'.
GRANT CREATE TABLE, CREATE VIEW TO AJAY
• Grant the UPDATE authority on the SAL column of the EMP to user 'AJAY'.
GRANT UPDATE (SAL) ON EMP TO AJAY;
Procedure for revoking privileges Revoke:
This command is used to withdraw the privilege that has been granted to a user.
Syntax: REVOKE <object_privileges> ON <object_name> FROM <username>;
Example: Revoke slect,update on emp from user1;
Examples
• Revoke the system privileges for creating table from 'AJAY'.
REVOKE CREATE TABLE FROM AJAY;
• Revoke the SELECT privileges on EMP table from ‘AJAY’.
REVOKE SELECT ON EMP FROM AJAY;
• Revoke the UPDATE privileges on EMP table from all users.
REVOKE UPDATE ON EMP FROM PUBLIC;
• Remove ALL privileges on EMP table from user 'AJAY'.
REVOKE ALL ON EMP FROM AJAY;
• Remove DELETE and UPDATE authority on the SAL and JOB columns of the EMP table
from user 'AJAY'.
REVOKE DELETE, UPDATE (SAL, JOB) ON EMP FROM AJAY;
Q. Write SQL command for following
i)Create user ii) Grant privileges to user. Iii) Remove privileges from user.
Ans:
i)Create user
Syntax: CREATE USER <user_name> IDENTIFIED BY <password>;
Example: CREATE USER RAJ IDENTIFIED BY RAJ123;
ii) Grant privileges to user.
Syntax: GRANT <privilege option> ON <object_name> TO <user_name);
(assuming table Employee for granting permissions to user ‘RAJ’ for select, insert, update and
delete privilege)
GRANT SELECT, INSERT, UPDATE, DELETE ON EMPLOYEE TO RAJ;
Iii) Remove privileges from user.
REVOKE <privilege option> ON <object_name> FROM <user_name);
(assuming table Employee for revoking permissions to user ‘RAJ)
REVOKE SELECT, INSERT,UPDATE,DELETE ON EMPLOYEE FROM RAJ;
5.3 Transaction
Definition: “Transaction is a set of operations performed as a sigle logical unit of work.”
Operations in Transaction-
The main operations in a transaction are-
1. Read Operation
2. Write Operation
1. Read Operation-
Read operation reads the data from the database and then stores it in the buffer in main memory.
For example- Read(A) instruction will read the value of A from the database and will store it
in the buffer in main memory.
2. Write Operation-
Write operation writes the updated data value back to the database from the buffer.
For example- Write(A) will write the updated value of A from the buffer to the database.
5.3.1 Properties of Transaction-
Q. Explain ACID properties of Transaction.
It is important to ensure that the database remains consistent before and after the transaction.
To ensure the consistency of database, certain properties are followed by all the transactions
occurring in the system.
These properties are called as ACID Properties of a transaction.
1. Atomicity-
This property ensures that either the transaction occurs completely or it does not occur at all.
In other words, it ensures that no transaction occurs partially.
That is why, it is also referred to as “All or nothing rule“.
It is the responsibility of Transaction Control Manager to ensure atomicity of the transactions.
2. Consistency-
This property ensures that integrity constraints are maintained.
In other words, it ensures that the database remains consistent before and after the transaction.
It is the responsibility of DBMS and application programmer to ensure consistency of the
database.
3. Isolation-
This property ensures that multiple transactions can occur simultaneously without causing any
inconsistency.
During execution, each transaction feels as if it is getting executed alone in the system.
A transaction does not realize that there are other transactions as well getting executed
parallely.
Changes made by a transaction becomes visible to other transactions only after they are
written in the memory.
The resultant state of the system after executing all the transactions is same as the state that
would be achieved if the transactions were executed serially one after the other.
It is the responsibility of concurrency control manager to ensure isolation for all the
transactions.
4. Durability-
This property ensures that all the changes made by a transaction after its successful execution
are written successfully to the disk.
It also ensures that these changes exist permanently and are never lost even if there occurs a
failure of any kind.
It is the responsibility of recovery manager to ensure durability in the database.
5.3.2 Transaction States
There are five states of transaction which tell about the current state of the transaction and also
tell how we will further do the processing in the transactions.
These are different types of Transaction States:
1. Active State-
This is the first state in the life cycle of a transaction.
A transaction is called in an active state as long as its instructions are getting executed.
All the changes made by the transaction now are stored in the buffer in main memory.
If all the ‘read and write’ operations are performed without any error then it goes to the
“partially committed state”; if any instruction fails, it goes to the “failed state”.
2. Partially Committed State-
After the last instruction of transaction has executed, it enters into a partially committed state.
After entering this state, the transaction is considered to be partially committed.
It is not considered fully committed because all the changes made by the transaction are still
stored in the buffer in main memory.
3. Committed State-
After all the changes made by the transaction have been successfully stored into the database, it
enters into a committed state.
Now, the transaction is considered to be fully committed.
4. Failed State-
When a transaction is getting executed in the active state or partially committed state and some
failure occurs due to which it becomes impossible to continue the execution, it enters into
a failed state.
5. Aborted State-
After the transaction has failed and entered into a failed state, all the changes made by it have
to be undone.
To undo the changes made by the transaction, it becomes necessary to roll back the
transaction.
After the transaction has rolled back completely, it enters into an aborted state.
5.4 Database Backup:
Q. Write note on Database Backup with type of failures. (4M)
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
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.
Methods of Backup
The different methods of backup in a database are:
Full Backup - This method takes a lot of time as the full copy of the database is made
including the data and the transaction records.
Transaction Log - Only the transaction logs are saved as the backup in this method.
To keep the backup file as small as possible, the previous transaction log details are
deleted once a new backup record is made.
Differential Backup - This is similar to full backup in that it stores both the data and
the transaction records. However only that information is saved in the backup that has
changed since the last full backup. Because of this, differential backup leads to
smaller files.
Types of Failure
There can be multiple reasons of failure in a database because of which a database backup
and recovery plan is required. Some of these reasons are:
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.
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.
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.
6. User Error
Normally, user error is the biggest reason of data destruction or corruption in a database. To
rectify the error, the database needs to be restored to the point in time before the error
occured.
5.5. Database Recovery
Q. Explain recovery techniques with example.
Recovery is the process of restoring a database to the correct state in the event of a failure.
It ensures that the database is reliable and remains in consistent state in case of a failure.
Database recovery can be classified into two parts;
1. Rolling Forward applies redo records to the corresponding data blocks.
2. Rolling Back applies rollback segments to the datafiles. It is stored in transaction tables.
Backward Recovery – The term “Rollback ” and “UNDO” can also refer to backward
recovery. When a backup of the data is not available and previous modifications need to be
undone, this technique can be helpful. With the backward recovery method, unused
modifications are removed and the database is returned to its prior condition. All adjustments
made during the previous traction are reversed during the backward recovery. In another word,
it reprocesses valid transactions and undoes the erroneous database updates.
Forward Recovery – “Roll forward “and “REDO” refers to forwarding recovery. When a
database needs to be updated with all changes verified, this forward recovery technique is
helpful.
Some failed transactions in this database are applied to the database to roll those modifications
forward. In another word, the database is restored using preserved data and valid transactions
counted by their past saves.
