Data Base Management System 2021

Chapter 05-Database Security & Transaction Processing

5.1 Database Security

➢ All organizations public, governmental or private, small or large depend on computerized information systems for
carrying out their daily activity.

➢ At the heart of each such information system, there is a database.

➢ At a very general level, we can define a database as a persistent collection of related data, where data are facts that have an
implicit meaning.

➢ For instance, an employee's name, social security number, or dates of birth are all facts that can be recorded in a database.

➢ Typically, a database is built to store logically interrelated data representing some aspects of the real world, which must be
collected, processed, and made accessible to a given user population.

➢ The database is constructed according to a data model which defines the way in which data and interrelationships between
them can be represented.

➢ The collection of software programs that provide the functionalities for defining, maintaining, and accessing data stored in
a database is called a database management system (DBMS).

➢ Database security refers to the collective measures used to protect and secure a database or database management software
from illegitimate use and malicious threats and attacks.

➢ It is a broad term that includes a multitude of processes, tools and methodologies that ensure security within a database
environment.

➢ In other word database security is Protecting the database from unauthorized access, alteration or deletion.

➢ The data stored in the database needs to be protected from unauthorized access and spiteful destruction or modification.

➢ Security within the dbms protects the integrity of the data, records and databases.

➢ It can provide encryption protection at the data level

➢ Major elements of DBMS Security include user authentication, user authorization, encryption of data and/or userid and
password and the auditing user actions.

➢ Security of the database involves the protection of the database against:

1. Unauthorized access of data

2. Alteration i. e. Modification of data
3. Destruction of data
Database Security can be given by following ways-
1) Physical Considerations
Actual Physical location of computer system must be secured against unauthorized access.
2) Human Considerations
Users of databases must be authorized & Authenticated users should be given full access to databases.
3) Operating System issues
Weakness in part of OS security may lead to unauthorized access to databases, so proper Antiviruses &
must be installed & Firewall must be configured.

4) Network level issues

Prepared By: Ms. Y. U. Kadam, Lecturer, KKWP, Nashik Page 1
Data Base Management System 2021
A data can be accessed remotely via network links, so private data which is valuable to organizations must be protected. It
can be secured by following some protocols (rules & regulations).
5) Database System issues
A database system must ensure that there are no violations against security. It also must satisfy that data is in integrated
form & certain consistency constraints are applied on it.

5.2 Data Security Requirements

We should use technology to ensure a secure computing environment for the organization. Although it is not possible to find a
technological solution for all problems, most of the security issues could be resolved using appropriate technology. The basic
security standards which technology can ensure are confidentiality, integrity and availability.

Confidentiality
A secure system ensures the confidentiality of data. This means that it allows individuals to see only the data they are supposed
to see.

Privacy of Communications
The DBMS should be capable of controlling the spread of confidential personal information such as health, employment, and
credit records.

Secure Storage of Sensitive Data

Once confidential data has been entered, its integrity and privacy must be protected on the databases and servers wherein it
Resides.

Authentication
One of the most basic concepts in database security is authentication, which is quite simply the process by which it system
verifies a user's identity, A user can respond to a request to authenticate by providing a proof of identity, or an authentication
token.

Authorization
Authorization is the process through which system obtains information about the authenticated user, including which database
operations that user may perform and which data objects that user may access.
A user may have several forms of authorization on parts of the database. There are the following authorization rights.
• Read authorization allows reading, but not modification, of data.
• Insert authorization allows insertion of new data, but not modification of existing data.
• Update authorization allows modification, but not deletion of data.
• Delete authorization allows deletion of data.
A user may be assigned all, none, or a combination of these types of authorization. In addition to these forms of authorization
for access to data, a user may be granted authorization to modify the database schema:
• Index authorization allows the creation and deletion of indexes.
• Resource authorization allows the creation of new relations.
• Alteration authorization allows the addition or deletion of attributes in a relation.
• Drop authorization allows the deletion of relations.

Integrity
A secure system ensures that the data it contains is valid. Data integrity means that data is protected from deletion and
corruption, both while it resides within the data-case, and while it is being transmitted over the network.

Availability
A secure system makes data available to authorized users, without delay.

Prepared By: Ms. Y. U. Kadam, Lecturer, KKWP, Nashik Page 2

Data Base Management System 2021

5.3 Creating, Altering, Deleting User:

a) Creating External Database Users:

The following example creates an external user, who must be identified by an external source before accessing the database:

Syntax- Create user <User Name> identified by < DBA>;

Create user abc identified by system;

b) Altering User

If we have create users and need to be able to do things like change their password, and change various attributes for the user.
We use the alter user command for these tasks. Here are some examples of the uses of the alter user command in action:

1. alter user myuser identified by new_password;

2. alter user myuser account lock;
3. alter user myuser account unlock;
4. alter user myuser password expire;

c) Deleting User
If we are going to create users, we better be able to remove them. The drop user command does just that. The command is as
simple as drop user followed by the user name.
Syntax- Drop User <User Name>;
Example: drop user myuser ;
5.4 Types of database users

1) Naive users: Are unsophisticated users who interact with the system by invoking one of the application programs that have
been written previously. Consider a user who wishes to find her account balance over the World Wide Web. Such a user may
access a form, where she enters her account number. An application program at the Web server then retrieves the account balance,
using the given account number, and passes this information back to the user.
2) Application programmers: Are computer professionals who write application programs. Application programmers can
choose from many tools to develop user interfaces. Rapid application development (RAD) tools are tools that enable an
application programmer to construct forms and reports with minimal programming effort.
3) Sophisticated user: Interact with the system without writing programs. Instead, they form their requests in a database query
language. They submit each such query to a query processor, whose function is to break down DML statements into instructions
that the storage manager understands. Analysts who submit queries to explore data in the database fall in this category.
4) Specialized users: Are sophisticated users who write specialized database applications that do not fit into the traditional
data-processing framework Among these applications are computer-aided design systems, knowledge base and expert systems,
systems that store data with complex data types (for example, graphics data and audio data), and environment - modeling
systems.
5) Database Administrator (DBA): Coordinates all the activities of the database system; the database administrator has a
good understanding of the enterprise’s information resources and needs.

5.5 Database Privileges-System Privilege, Object Privilege

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

Prepared By: Ms. Y. U. Kadam, Lecturer, KKWP, Nashik Page 3

Data Base Management System 2021
(create a session); select rows from someone else's table; or execute someone else's stored procedure.

System privileges
A system privilege is the right to perform a particular action, on a particular type of object. For example, the privileges to
create tables. The most important system privileges are:
1. create session.
2. create table
3. create view
4. create procedure
5. sysdba
6. sysoper

SYSOPER privilege: allows operations such as: Startup a database, Shutdown a database, Backup a database, Recover a database
and create a database. This privilege allows the user to perform basic operational tasks without the ability to look at user data.

SYSDBA privilege: includes all SYSOPER privileges plus full system privileges (with the ADMIN option), plus 'CREATE
DATABASE' etc.

Object privileges
• An object privilege is a privilege or right to perform a particular action on a specific table, view, sequence, procedure,
function, or package.
• For example, the privilege to delete rows from the table DEPT is an object privilege.
• Object privileges granted for a table, view, sequence, procedure, function, or package apply whether referencing the
base object by name or using a synonym.

5.6 Granting Privilege

1. Granting System Privileges
2. Granting Object Privileges
3. Granting Privileges on Columns
1] Granting System Privileges
We can grant system privileges users using the GRANT statement.The following statement grants the system privilege
CREATE SESSION and to the user ajay
Syntax:
SQL> Grant <system privilages>to <user list>;
E.G.SQL> Grant CREATE SESSION to ajay;
Grant succeeded.

2] Granting Object Privileges

We Can also use the GRANT statement to grant object privileges to roles and users. To grant an object privilege, we must fulfill
one of the following conditions: we can own the object specified. We should possess the GRANT ANY OBJECT PRIVILEGE
system privilege that enables us to grant and revoke privileges on behalf of the object owner. The WITH GRANT OPTION
clause was specified when we were granted the object privilege by its owner.
The following statement grants the SELECT, INSERT, and DELETE object privileges for all
columns of the emp table to the users ajay.
Syntax:
SQL> Grant <object privilages> on <table_name> to <user list>;
e.g. SQL> GRANT SELECT, INSERT, DELETE ON EMP TO ajay;

Prepared By: Ms. Y. U. Kadam, Lecturer, KKWP, Nashik Page 4

Data Base Management System 2021
3] Granting Privileges on Columns
Granting Privileges on Columns can grant INSERT, UPDATE, or REFERENCES privileges on individual columns in a table.
The following statement grants INSERT privilege on the acct_no column of the accounts table to ajay.
Syntax:
SQL> Grant <object privilages> (column_name) on <table_name> to <user list>;
e.g. SQL> GRANT INSERT (acct_no) ON accounts TO ajay;

5.7 Revoking Privileges

1. Revoking System Privileges
2. Revoking Object Privileges
1] Revoking System privileges
Any user with the ADMIN OPTION for a system privilege or role can revoke the privilege or role from any other database user
or role. The revoker does not have to be the user that originally granted the privilege or role. Users with GRANT ANY ROLE
can revoke any role. The following statement revokes the CREATE TABLE system privilege and the accts_rec role from ajay
Syntax:
SQL> Revoke <system privilages> from <user list>;
e.g.SQL> REVOKE CREATE TABLE FROM ajay;
2] Revoking Object Privileges
This command is used to revoke an existing privilege from a user. It can revoke a system privilege, object privilege or a role
from a user. Only DBA or a user with ADMIN OPTION can revoke system privilege.
Syntax:
SQL> REVOKE [privilege ] ON [object] FROM {USER |PUBLIC | ROLE} ;

Examples:
1. Revoke all Privileges on Emp table FROM user Sachin.
SQL > REVOKE ALL ON EMP FROM Sachin;

2. REVOKE INSERT,SELECT Privileges on Emp table FROM user Sachin.

SQL > Revoke INSERT,SELECT ON EMP FROM Sachin;

5.8 Transaction Processing: The Concept of Transaction

A transaction is a unit of a program execution that accesses and possibly modifies various data objects (tuples, relations).

The collection of operation that forms single logical unit of work is called as Transaction. This is a program whose execution
accesses & updates contents of database. After every transaction database should be in a Consistent state.

Ex.

Transaction has its name with End statement.

Initially, A=500, B=400. Before & after transaction values of A & B must be in Consistent state.

T1:

Read (A);

A=A-50;

Write (A);

Read (B);

B=B+50;

Prepared By: Ms. Y. U. Kadam, Lecturer, KKWP, Nashik Page 5

Data Base Management System 2021
Write (B);

End;

a] Properties of Transaction[ACID properties]

There are a set of properties that guarantee that database transactions are processed reliably, referred to as ACID (Atomicity,
Consistency, Isolation, Durability).
Atomicity: Atomicity refers to the ability of the database to guarantee that either all of the tasks of a transaction are performed
or none of them are. Database modifications must follow an all or nothing rule. Each transaction is said to be atomic if when
one part of the transaction fails, the entire transaction fails.
Consistency: The consistency property ensures that the database remains in a consistent state before the start of the transaction
and after the transaction is over (whether successful or not). Execution of a transaction in isolation (that is, with no other
transaction executing concurrently) preserves the consistency of the database.

Isolation: Even though multiple transactions may execute concurrently, the system guarantees that, for every pair of transactions
Ti and Tj , it appears to Ti that either Tj finished execution before Ti started, or Tj started execution after Ti finished. Thus, each
transaction is unaware of other transactions executing concurrently in the system.

Durability:Durability refers After a transaction completes successfully, the changes it has made to the database persist, even if
there are system failures.

b]States of Transaction

• Active : This is the initial state; the transaction stays in this state while it is executing

• Partially committed:after the final statement has been executed transaction goes to partially Committed State.

• Failed:after the discovery that normal execution can no longer proceed transaction goes to failed state

• Aborted:after the transaction has been rolled back and the database has been restored to its state prior to the start of the
transaction

• Committed:After successful completion transaction goes to Commited State

Prepared By: Ms. Y. U. Kadam, Lecturer, KKWP, Nashik Page 6

Data Base Management System 2021

5.9 Database backup:

➢ A data backup is the result of copying or archiving files and folders for the purpose of being able to restore them in case of
data loss.

➢ Data loss can be caused by many things ranging from computer viruses to hardware failures to file corruption to fire, flood, or
theft (etc).

➢ Database backup is the process of backing up the operational state, architecture and stored data of database software.

➢ It enables the creation of a duplicate instance or copy of a database in case the primary database crashes, is corrupted or is lost.

5.10 Types of Failure

There are many types of failures that can affect database processing. Some failures affect the main memory only, while others
involve secondary storage. Following are the types of failure:

• Hardware failures: Hardware failures may include memory errors, disk crashes, bad disk sectors, disk full errors and so
on. Hardware failures can also be attributed to design errors, inadequate (poor) quality control during fabrication,
overloading (use of under-capacity components) and wear out of mechanical parts.

• Software failures: Software failures may include failures related to software’s such as, operating system, DBMS software,
application programs and so on.

• System crashes: System crashes are due to hardware or software

• Statement Failure :It is referred as the inability of database system to execute given SQL statement

• Media Failure: It occurs when proper data backup not taken on media such as CD, HDD etc. Ex. Disk head crash.

• Application Software Errors: It occurs when any application software fails to take input, resource limitation problem etc.

5.11Causes of Failure
There are many different types of failure that can affect database processing, each of which has to be dealt with in a different
manner. Some failures affect main memory only, while others involve non-volatile (secondary) storage. Among the causes of
failure are:

• System Crashes

• User Error
• Carelessness
• Sabotage (intentional corruption of data)
• Statement Failure
• Application software errors
• Network Failure
• Media Failure
• Natural Physical Disasters

5.12 Types of Database Backup-Physical & Logical

1. Physical Backup:

Prepared By: Ms. Y. U. Kadam, Lecturer, KKWP, Nashik Page 7

Data Base Management System 2021
• Physical backups are copies of physical database files. For example, a physical backup might copy database content
from a local disk drive to another secure location.

• A physical backup can be hot or cold:

a) Hot backup: Users can modify the database during a hot backup. Physical Backup Log files of changes made during
the backup are saved, and the logged changes are applied to synchronize the database and the backup copy. A hot
backup is used when a full backup is needed and the service level does not allow system downtime for a cold backup

b) Cold backup: Users cannot modify the database during a cold backup, so the database and the backup copy are
always synchronized. Cold backup is used only when the service level allows for the required system downtime.

We can perform a full or incremental Physical backup:

Full—Creates a copy of data that can include parts of a database such as the control file, transaction files
(redo logs), archive files, and data files.
Incremental—Captures only changes made after the last full physical backup.

2. Logical Backup
• A logical backup copies data, but not physical files, from one location to another.

• A logical backup is used to move or archive a database, tables, or schemas and to verify database structures.

• A full logical backup enables you to copy these items across environments that use different components, such as
operating systems and rule files

• A logical export backup generates necessary Structured Query Language (SQL) statements to obtain all table data that
is written to a binary file.

• A logical export backup does not contain database instance-related information, such as the physical disk location, so
you can restore the same data on another database machine.

• Periodic logical export backups (at least weekly) are recommended in case physical backups fail or the database
machine becomes unavailable.

5.13 Database Recovery

• Data recovery is the process of restoring data that has been lost, accidentally deleted, corrupted or made
inaccessible.
• In enterprise data recovery typically refers to the restoration of data to a desktop, laptop, server or
external storage system from a backup.
• The System Recovery Options menu contains several tools, such as Startup Repair, that can helps us to
recover Windows from a serious error.

5.14 Recovery Techniques- Roll Forward, Rollback

1. Roll Forward (Forward Recovery)

Prepared By: Ms. Y. U. Kadam, Lecturer, KKWP, Nashik Page 8

Data Base Management System 2021
• Roll-forward recovery, used together with after-imaging and backup, lets you recover from mediafailures.

• When a database disk fails, you can restore the most recent backup, then use roll-forward recovery to restore the
database to the condition it was in before you lost the disk.

• The Roll forward redoes the changes made by a transaction, after the committed transaction and over writes the
changed value once again to ensure consistency.

• To restore a database by using roll-forward recovery, you must already have a backup copy of the database.

• Roll-forward recovery recovers the entire database. You cannot use roll-forward recovery to restore individual tables.

2. Rollback (Backward Recovery)

• Rollback segments are used to identify and undo transactions that were never committed, yet were either saved to the
data files before the failure, or were applied to the database during the roll forward.
• This process is called rolling back or transaction recovery
• The Rollback transaction is a transaction which rolls back the transaction to the beginning of the transaction . It is
possible to use before Commit transaction.

Prepared By: Ms. Y. U. Kadam, Lecturer, KKWP, Nashik Page 9