JSPM’s

RAJARSHI SHAHU COLLEGE OF ENGINEERING,

POLYTECHNIC
Department of Computer Engineering
Academic Year: 2024-25

UNIT-5 ( 10Marks)
MSBTE Question bank

Sr.No. Question Year Marks

1 Database security Database security refers to the collective measures W-18 4
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. 2. To make data remain safe and confidential.

3. To provide confidentiality which ensures that only those

individuals should ever 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:
96
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 Authentication helps to establish proof of identities. The

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:

4. For prevention of data theft such as bank account numbers, credit

card information, passwords, work related documents or sheets, etc.

5. 2. To make data remain safe and confidential.

6. To provide confidentiality which ensures that only those

individuals should ever entitled to. 4. To provide integrity which
ensures that only authorized individuals should ever be able change
or modify information.

8. To provide availability which ensure that the data or system itself

is available for use when authorized user wants it.

9. To provide authentication which deals with the desire to ensure

that an authorized individual.

10. To provide non-repudiation which deals with the ability to

verify that message has been sent and received by an authorized user.

OR

3. 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

4. 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.

2 Explain transaction ACID properties. W-18 4

W-19

W-22

W-23

97
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 1. Atomicity: When one transaction takes place, many operations For each
occur under one transaction. Atomicity means either all operations
will take place property and reflect in the database or none of them property - 1
will be reflected. 2. Consistency: Consistency keeps the database Mark
consistent. Execution of a transactionneeds to take place in isolation. It
helps in reducing complications of executing multiple transactions at
a time and preserves the consistency of the database.3. Isolation: It is
necessary to maintain isolation for the transactions. This means one
transaction should not be aware of another transaction getting
executed. Also their intermediate result should be kept hidden. 4.
Durability: When a transaction gets completed successfully, it is
important that the changes made by the transaction should be
preserved in database in spite of system failures

3 Explain any four roles of database administrator W-18 4

1. Schema Definition The Database Administrator creates the

database schema by executing DDL statements. Schema includes the
logical structure of database table (Relation) like data types of
attributes, length of attributes, integrity constraints etc.

2. Storage structure and access method definition The DBA creates

appropriate

storage structures and access methods by writing a set of definitions

which is translated by data storage and DDL compiler.

3. Schema and physical organization modification DBA writes set of

definitions to modify the database schema or description of physical
storage organization

4. Granting authorization for data access The DBA provides different

access rights to the users according to their level. Ordinary users

98
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 might have highly restricted access to data, while you go up in the
hierarchy to the administrator, you will get more access

rights. Integrity constraints specifications: Integrity constraints are

written by DBA and they are stored in a special file which is accessed
by database manager while updating data.

5. Routine Maintenance some of the routine maintenance activities

of a DBA is given below.

(i) Taking backup of database periodically

(ii) Ensuring enough disk space is available all the time.

(iii) Monitoring jobs running on the database.

(iv) Ensure that performance is not degraded by some expensive task

submitted by some users.

6. Integrity- constraint specification: Integrity constraints are written

by DBA and they are stored in a special file, which is accessed by
database manager, while updating thedata.

1 Explain state of transaction with the help of diagram S-19 4

S-22

Active –the initial state; the transaction stays in this state while it is

executingPartially committed –after the final statement has been

executed.Failed - after the discovery that normal execution can no
longer proceed.Aborted – after the transaction has been rolled back
and the database restored to its state prior to the start of the
transaction. Two options after it has been aborted: restart the
transaction - can be done only if no internal logical error kill the
transaction Committed –after successful completion.

2 Explain recovery techniques with example. S-19 4

99
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 S-24

When recovering the database, it is must redo the effects of the

previous transactions. This is called Rolling Forward or simple
Forward Recovery. Not all but some active transaction that didn’t
complete successfully needs to rollback, when the disk drive crashed.
Such kind of rollback is called Backward Recovery.

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 aswell as any uncommitted changes that were recorded in
the redo log.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.

100
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 1 Describe database backups with it’s types. W-19 4

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 professionalsThree
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.

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:

101
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 An alternative to incremental database backups that has 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

2 Describe database privileges. Write down the procedure for granting W-19 6
& revoking privileges in database objects to the users.

Database privileges: Procedu

When multiple users can access database objects, authorization can re for
be controlled to these objects with privileges. Every object has an
owner. Privileges control if a user can modify an object owned by granting
another user. Privileges are granted or revoked either by the instance privilege
administrator, a user with the ADMIN privilege or, for privileges to
a certain object, by the owner of the object. s

1) System Privileges: 2M

System privileges are privileges given to users to allow them to

perform certain functions that deal with managing the database and
the servere.gCreate user, Create table, Drop table etc.

2) Object Privileges:

Object privileges are privileges given to users as rights and

restrictions to change contents of database object – where database
objects are things like tables, stored procedures, indexes, etc.Ex.
Select,insert,delete,update,execute,references etcProcdure 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] ;

102
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 Example: Grant select, update on emp to user1;Procedure for
revoking privileges

Revoke: This command is used to withdraw the privileges that

hasbeen granted to a user.

Syntax: Revoke <object privileges>on<object name>from

<username> ;

Example: Revoke select, update on emp from user1;

1 State the concept of database recovery. S-22 2

When recovering the database, it is must redo the effects of the

previous transactions. This is called Rolling Forward or simple
Forward Recovery. Not all but some active transaction that didn’t
complete successfully needs to rollback, when the disk drive crashed.
Such kind of rollback is called Backward Recovery

2 State types of database user S-22 2

1. Database Administrator (DBA)

2. Naive / Parametric End Users

3.Sophisticated Users

4.A System Analyst

2 Describe any four responsibilities of Database Administrator. W-22 4

Responsibilities of Database Administrator (DBA): Any 4: 1 M

each
1. Schema Definition:Database or schema can be designed or defined
by DBA.
2. Creating storage structure:
DBA allocate or decide the space to store the database.
3. Create grant access methods:
Different access methods to access the database can be granted by
DBA to the users.
4. Schema modification:

103
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 The database or schema which is already defined can be modified by
DBA as per the requirements.
5. Granting authorization:
To access the different databases, DBA can grant the authorization
to authorized users only.
6. Performance tuning:
The problems/errors arise in database accessing; can be resolved by
DBA to increase the performance.

7. Regular maintenance:
DBA can monitor the transactions in database and maintain the
database error free by doing the regular maintenance

3 Explain Database Recovery techniques in detail. W-22 4

Database Recovery Techniques:Database recovery techniques are

used to restore the original data in system from backup. Backward
and forward recovery is two types of database recovery.Recovery
Techniques:
1. Log based recovery.
2. Shadow paging recovery
3. Checkpoints

1. Log based recovery:

It records sequence of log records, which includes all activities done
by database users.It records the activities when user changes the
database.In case of database failure, by referring the log records users
can easily recover the data.

2. Shadow paging recovery:

This technique is the alternative for log based recovery.In this
technique, database is divided into pages that can be stored on the
disk.The page table is used to maintain the record of location of
pages.In case of database failure, page table is used to recover the
parts of database.

3. Checkpoints:
Checkpoint records all committed transactions into logs.When
system fails, it check log to determine recovery action.

1 Explain the most common types of database failures S-23 4

104
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 Define failure. Enlist types of failure W-23 2

Transaction Failures: Transactions can fail for a number of reasons. Any 2

Failure can be due to an error in the transaction caused by incorrect
input data as well as the detection of a present or potential deadlock. types of
Furthermore, some concurrency control algorithms do not permit a failure,
transaction to proceed or even to wait if the
2M each
data that they attempt to access are currently being accessed by
another transaction. This might also be considered a failure.

1. System Failures( or Hardware/ Software failure): The reasons for

system failure can be traced back to a hardware or to a software
failure.

2. Media Failures: Media failure refers to the failures of the

secondary storage devices that store the database. Such failures may
be due to operating system errors, as well as to hardware faults such
as head crashes or controller failures.

3. Communication/Network Failures: There are a number of types of

communication failures. The most common ones are the errors in
themessages, improperly ordered messages, lost messages, and
communication line failures mainly arising due to errors in network.

2 Explain properties of transaction S-23 4

A transaction can be defined as a group of tasks. A single task is the

minimum processing unit which cannot be divided further.

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 mustmaintain Atomicity, Consistency, Isolation, and
Durability −commonly known as

ACID properties − in order to ensure accuracy, completeness, and

data integrity.

on must be treated
as an atomic unit, that is, either all of its operations are executed or

105
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 none. There must be no state in a database where a transaction is left
partially completed. States should be defined either before the
execution of the transaction or after theexecution/abortion/failure of
the transaction.

n a consistent state after

any transaction. No transaction should have any adverse effect on the
data residing in the database. If the database was in a consistent state
before the execution of a transaction, it must remain consistent after
the execution of the transaction as well.

han one transactions

are being executed simultaneously and in parallel, the property of
isolation states that all the transactions will be carried out and
executed as if it is the only transaction

in the system. No transaction will affect the existence of any other

transaction.

ility: The database should be durable enough to hold all its

latest updates even if the system fails or restarts. If a transaction
updates a chunk of data in a database and commits, then the database
will hold the modified data. If a transaction commits but the system
fails before the data could be written on to the disk, then that data
will be updated once the system springs back into action

3 Describe database privileges. Write down the command for granting W-23 6
and revoking privileges on database object to users

A user privilege is the right to run a particular type of SQL statement,

or the right to access an object belonging to another user, run a
PL/SQL package, and so on.

There are two types of privileges defined by Oracle Database

1. System Privileges

2. Object Privileges

106
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 1. System Privileges

• There are over 100 distinct system privileges.

• Each system privilege allows a user to perform a particular database

operation. System privileges allow a user to perform administrative
actions in a database.

• System privileges can be very powerful, and should be granted only

when necessary to roles and trusted users of the database.

• For e.g.: create session, create table, create view, create sequence,
create procedure, create role

2. Object Privileges

• Each type of object (for e.g.: table, view, procedure etc.) has
different privileges associated with it.

• It allows for the use of certain operations on database objects as

authorized by another user

• All DML are comes under Object privileges.

• You can specify ALL [PRIVILEGES] to grant or revoke all

available object privileges for an object.

Grant Privileges

GRANT is used for giving privileges on various database objects in

OracleWe can grant users various privileges to tables.

These privileges can be any combination of SELECT, INSERT,

UPDATE, DELETE,

REFERENCES, ALTER, INDEX, or ALL.

Syntax

GRANT privilege-type ON [TABLE] {table-Name | view-Name}

TO grantees

Example:

grant create synonym to scott;

Revoke

Use the REVOKE statement to remove privileges from a specific

user or role, or from all users, to perform actions on database objects

107
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 Syntax:

REVOKE privilege-type ON [ TABLE] {table-Name | view-Name}

FROM grantees

revoke create synonym from scott;

1 List Privileges and object privileges S-24 2

There are two types of privileges defined by Oracle Database

1. System Privileges

2. Object Privileges

1. System Privileges

All DML are comes under Object privileges.

• You can specify ALL [PRIVILEGES] to grant or revoke all

available object

privileges for an object.Grant Privileges

GRANT is used for giving privileges on various database objects in

OracleWe can grant users various privileges to tables.

These privileges can be any combination of SELECT, INSERT,

UPDATE, DELETE,

REFERENCES, ALTER, INDEX, or ALL.

Syntax

GRANT privilege-type ON [TABLE] {table-Name | view-Name}

TO grantees

Example:

grant create synonym to scott;

Revoke

Use the REVOKE statement to remove privileges from a specific

user or role, or from all users, to perform actions on database objects

Syntax:

108
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 REVOKE privilege-type ON [ TABLE] {table-Name | view-Name}
FROM grantees

revoke create synonym from scott;

2 State the cause of database failure S-24 2

What Are The Causes of Database Loss? The database recovery

process is not entirely different from data recovery .....Power Failure.
Power failures can lead to hardware failure. ...

Disk Failure. ...

Human Error. ...

Software Corruption. ...

Virus Infection. ...

Natural Disasters. ...

Disgruntled Employees.