Question

Can someone help me explain database security and crash recovery in 2,000 words?

Answer 1

Answer:-

database security and crash recovery in 2000 words:-

In this project we will introduce two important issues relating to database management systems. A computer system is an electrochemical device subject to failures of various types. The reliability of the database system is linked to the reliability of the computer system on which it runs. In this unit we will discuss recovery of the data contained in a database system following failure of various types and present the different approaches to database recovery. The types of failures that the computer system is likely to be subjected to include failures of components or subsystems, software failures, power outages, accidents, unforeseen situations and natural or man-made disasters. Database recovery techniques are methods of making the database fault tolerant. The aim of recovery scheme is to allow database operations to be resumed after a failure with minimum loss of information at an economically justifiable cost.

"Database security" is protection of the information contained in the database against unauthorized access, modification or destruction.

"Database integrity" is the mechanism that is applied to ensure that the data in the database is correct and consistent.

Database security and integrity have been discussed in this unit.

OBJECTIVES

At the end of this unit, you should be able to:

• describe the term RECOVERY and INTEGRITY
• describe Recovery Techniques
• define Error and Error detection techniques
• describe types of Authorization

What is Recovery?

In practice several things might happen to prevent a transaction from completing. Recovery techniques are used to bring database, which does not satisfy consistency requirements, into a consistent state. The inconsistencies may arise due to dissatisfaction of the semantic integrity constraints specified in the schema or may be due to violation of certain implicit constraints that are expected to hold for a database. In other words, if a transaction completes normally then all the changes that it performs on the database are permanently committed. But, if transaction does not complete normally then none of its changes are committed. An abnormal termination may be due to several reasons including:

a) user may decide to abort his transaction
b) there might be a deadlock
c) there might be a system failure.

So the recovery mechanisms must make sure that a consistent state of database can be restored under all circumstances. In case of transaction abort or deadlock the system remains in control but incase of failure the system loses control because computer itself fails or some critical data are lost.

Kinds of Failures

When a transaction/program is made to be executed, a number of difficulties can arise, which leads to its abnormal termination. The failures are mainly of two types:

1. Soft failures: In such cases, a CPU or memory or software error abruptly stops the execution of the current transaction (or all transactions), thus lead to losing the state of program execution and the state/contents of the buffers. These can further be subdivided into two types:

   a) Statement failure
   b) Program failure

   A Statement of program may cause to abnormal termination if it does not execute completely. If during the execution of a statement, an integrity constraints get violated it leads to abnormal termination of program due to which any updates made already may not got reflected in the database leaving it in an inconsistent state.

   A failure of program can occur if some code in a program leads to its abnormal termination. E.g., a program which goes into an infinite loop. In such case the only way to break the loop is to abort the program. Thus part of program, which is executed before abortion from program may cause some updates in database, and hence the database is, updated only partially which leads to an inconsistent state of database. Also in case of deadlock i.e. if one program enters into a deadlock with some other program, then this program has to be restarted to get out of deadlock and thus the partial updates made by this program in the database makes the database in an inconsistent state.

   Thus soft failures can be occurred due to either of statement failure or failure of program.

2. Hard failure: Hard failures are those failures when some data on disk get damaged and cannot be read anymore. This may be due to many reasons e.g. a voltage fluctuation in the power supply to the computer makes it go off or some bad sectors may come on disk or there is a disk crash. In all these cases, the database gets into an inconsistent state.

In practice soft failures are more common than hard failures. Fortunately, recovery from soft failures is much quicker.

Failure Controlling Methods

Although failures can be controlled and removed / handled using different recovery techniques to be discussed later, but they are quite expensive both in case of time and in memory space. In such a case it is more beneficial to better avoid the failure by some checks instead of deploying recovery technique to make database consistent. Also recovery from failure involves manpower, which can be used in some other productive work, if failure can be avoided. It is therefore, important to find out ways and means by which failures could be controlled.

Different methods/techniques can be adopted to control different types of failures. For e.g. consider a hard failure i.e. system crashing. The cause of system shutdown could be a failure in power supply unit or loss of power, due to which information stored on the storage medium can be lost. One method to avoid loss of data stored on disk due to power failure is to provide an uninterruptable power source by using voltage stabilizers or batteries or transformers. Also since recovery from soft failures is quicker, so it is hard failure, which, as far as possible, should be controlled by taking some preventive measures. In case of failure of system software, it can be controlled by ensuring that all the functions as well as statements used in the program have been placed in right positions and debugging is done prior to its execution so that appropriate solution can be applied thus avoiding inconsistency in database. Soft failure can also be controlled by checking the integrity constraints used in program prior to its execution or by checking the preconditions to be satisfied by a statement so that program won't go into an infinite loop thus causing abnormal termination and hence leaving database in a corrupt state. If all such precautions are taken in advance then no extra effort has to be done in recovering erroneous data on the database.

Recovery Techniques

Several recovery techniques have been proposed for database systems. As we have seen that two types of failures are there, so now we will discuss about how to recover from those two types of failures. Soft failure or Media failure recovery can be done using/restoring the last backup copy or by doing forward recovery if the system logs is intact. While Hard failure or system failure recovery using log include backward recovery as well as forward recovery. So there are two main strategies for performing recovery:

1) Backward Recovery (UNDO)

In this scheme the uncommitted changes made by a transaction to a database are undone. Instead the system is reset to some previous consistent state of database that is free from any errors.

DATABASE with changes Undo DATABASE without changes → Before mages

Forward Recovery (Redo)

In this scheme the committed changes made by a transaction are reapplied to an earlier copy of the database.

DATABASE without changes DATABASE with changes Redo After images

In simpler words, when a particular error in system is detected, the recovery system makes an accurate assessment of the state of the system and then makes appropriate adjustment based on the anticipated results had the system been error free. One thing to be noted that the Redo operation must be idempotent i.e. executing it several times must be equivalent to executing it once. This characteristic is required to guarantee correct behaviour of database even if a failure occurs during the recovery process.

Depending on the above discussed recovery scheme several types of recovery methods have been used. Some of them are discussed below:

Shadow Pages

This mechanism provides recovery from a system failure that causes the contents of main storage to be lost. One first assumes that a mapping vector V keeps tract of where (i.e. in which slot) the physical page corresponding to a particular logical page number can be found.

Pages: MappingV Slots

Figure 1 : Shadow Page Mapping

As shown in fig 1, the ith element in V contains the address of the slot used to store the contents of the page i or a null for a not yet defined page. A bit map M is used to indicate which slots are used or free.

(1 Corresponds to used slot & O for free slot).

A checkpoint operation will make sure that a stable state of the database is on disk: such a state is represented by V, M, and the contents of the slots.

The idea of shadow page mechanism is to maintain a dual state at all times. Out of the two states or stages, the first stage is the stable one, & is permanently stored in the database. The second state takes into account the changes made to the database. The second state is developed progressively while changes are being made. Some of the portions reside in the main memory buffer, while some others may be swapped onto disk; & portions not affected by the changes are shared by both states.

When a page is modified, the corresponding entry in the mapping V is changed to point to a new slot and flagged to indicate the fact that it is a new slot. The new bit map is also updated to keep track of newly allocated slots (the already allocated slots corresponding to old pages cannot be released before checkpoints.

A checkpoint operation therefore consists in flushing onto disk the V, M and pages which are in the buffers and writing the master record. The master is a single physical record, which indicates which of the states, corresponds to the new stable version of the database. It is the writing of the master record, which actually flip-flops the database state from one state to next one.

If the writing operation doesn't succeed the old state remain available.

The Log

The checkpoint mechanism just described can be used as way of committing data changed by a transaction it there is only one transaction active at the time. But if several transactions are running concurrently then the end of the one transaction needs to commit only the pages changed by the transaction that are not yet committing. It may also be the case that transaction can update data on the same page which may further complicates the issue. Another issue in this case is that one may not want to force the data pages back to disk at each end of transaction if many transactions update the same page in a rather short time. So a log is used to remember which transaction did which change to page. Thus the system knows exactly how to separate the changes made by transactions that has already committed from the other changes made by transaction that did not yet committee. Any operation such as begin transaction, insert, delete, update and end transaction (commit), adds a record to the log containing the transaction identifier and enough information to undo or redo the changes.

Let us consider several transactions with their respective start & end times shown in fig. 2.

T1 T2 T3 T4 t1 t2time Checkpoint Crash

Figure 2

A checkpoint is taken at time t1 & a crash occurs at time t2. At restart time the stable check pointed state is restored. The transaction T1 has all of its changes in the restored state; therefore nothing needs to be done. Also T4 starts after the checkpoint; therefore nothing needs to be done for it also as the restoring to the last checkpoint got rid of all its changes. For T2, which committed before crash, some changes are already in committed before crash, some changes are already in stable state but the changed made to database after the checkpoint must be redone to have a consistent state of database. Lastly for T3, which did not commit, all changes made before the checkpoint must be undone.

The restart algorithm is described as follows. First note that the two extra operations are done at checkpoint times besides what has been described in the section on shadow pages, both operations have to do with the log. First on entry (of type checkpoint) is made in the log & then secondly its address in the log is stored in the master. At restart time the last stable check pointed state is restored. Then the checkpoint record in the log is located. By reading the log & locating all start & end transaction records the process can partition the transaction in classes of Types T2, T3, T4 (clearly no T1 is ever found). The process then reads the log backwards from the checkpoint undoing all log records of transaction of type 3. Then restart reads the log forwards from the checkpoint, redoing the changes of type 2 transactions. When this is done a new checkpoint is taken to make these changes stable.

Error Reporting and Detection Schemes

An error is said to have occurred if the execution of a command to manipulate the database cannot be successfully completed either due to inconsistent data or due to state of program. For e.g.: - There may be a command in program to store data in database. On the execution of command without any problem, it is found that there is no space/place in database to accommodate that additional data. Then if can be soul that an error has occurred. This error is due to physical state of database.

Broadly errors are classified into following categories :-

1. User error : This includes errors in the program (e.g. Logical errors) as well as errors made by online users of database. These types of errors can be avoided by applying some check conditions in programs or by limiting the access rights of online users e.g. read only. So only updation or insertion operation require that appropriate check routines perform appropriate checks on the data entered. In case of error, some prompts can be passed to user to enable him to correct those errors.
2. Consistency error : These errors occur due to inconsistent state of database caused may be due to wrong execution of commands or in case of abortion of a transaction. To overcome these errors the database system should include routines that check for the consistency of data entered in the database.
3. System error : These include errors in database system or the OS for e.g. deadlocks, (discussed earlier in Concurrency Control Unit). Such errors are fairly hard to detect and require reprogramming the erroneous components of the system software.

Security & Integrity

Information security is the protection of information against unauthorized disclosure, alteration or destruction. Database security is the protection of information that is maintained in a database. It deals with ensuring only the "right people" get the rights access to the "right data". By right people are mean to those people who have the right to access or interact with the database. This ensured that the confidentiality of the data is maintained. For e.g.: - In an educational instruction, information about student's grade, & university's personal information accessible only to authorities concern & not to everyone. Another example can be in case of medical records of patients in a hospital, these could accessible only to health care officials. In computer definition, specification of access rules about who has what type of access to what information is known as problem of authorization. These access rules are defined at the time database is defined. The person who writes access rules is called on authorizer. The process of ensuring that information & other protected object are accessed only in authorized ways is called access control. The term integrity is also applied to data & to the mechanism that help to ensure its correctness. Integrity refers to the avoidance of accidental loss of consistency. Protection of database contents from unauthorized access includes legal & ethical issues, organization policies as well as database management policies. To protect database several levels of security measures are maintained: -

1. Physical : The site or sites containing the computer system must be physically secured against illegal entry of unauthorized person.
2. Human : A template authorization is given to user to reduce chance of any other user giving access to outsides in exchange of some favors.
3. O.S. : Even though a fool proof security measures are taken to secure database System, weakness in O.S. security may serve as a means of unauthorized access to the database.
4. Network : Since databases allow distributed or remote access through terminals or network, software level security within the network software is an important issue to be taken under consideration.
5. Database system : In database also according to user needs authorization is distributed or done. That is to say user may bee allowed to read data & issue queries but would not be allowed to deliberately modify the data. Only some upper level users may be allowed to do so giving them authorized access rights with database itself. It is the responsibility of database system to ensure that these authorization restrictions are not violated.

To ensure database security scarcity at all these above levels must be maintained.

It is the DBA who is responsible for database security, creation & cancellation of user right/accounts, assigning appropriate security level to user accounts or granting and revoking certain user privileges.

Relationship between security & integrity

Database security usually refers to access, where as database integrity refers to avoidance of accidental loss of consistency. But generally, the turning point or the dividing line between security & integrity is not always clear. Fig. Below shows relationship between data security & integrity.

USER Information modification Security Violation Unauthorized modification) No Security Violation (Authorized mo dification) No Correct Modification(Usually InadvertentlyMaliciou sly orrect Incorrect Correct Possible doesn't exist) Integrity Violation Integrity Violation

Difference between OS & Database Security

Through security within the OS can be implemented at several levels ranging from passwords for access to system to the isolation of concurrent processes running with the system, but there is a difference between security measures taken at OS level as compared to database security. More objects must be protected in a database since the lifetime of data is normally longer in a database. Also database security is concerned with different levels of granularly such as file, record or field. While OS project only real resources, in database systems the objects can be complex logical structures, a number of which can map to same physical data objects. Moreover different architectural levels internal conceptual & external have different security requirements while database security is concerned with the semantics of data, as well as with its physical representation. OS can provide security by not allowing any operation to be performed on the database unless the user is authorized for the operation concerned. More on the authorization will be discussed later. Given below is a diagram showing architecture of a database security subsystem.

USERS Thrusted F ont End OS Security Security Kernel Physical Reference monitor Logical Untrusted DBMS Object (files) Tuples (pages)

Authorization

Authorization is the culmination of the administrative policies of the organization. As name specifies, authorization is a set of rules that can be used to determine which user has what type of access of which portion of the database. The person who writes access rules is called an authorizer.

An authorizer may set several forms of authorization on parts of the database. Among them are the following:

1. Read Authorization: allows reading, but not modification of data.
2. Insert Authorization: allows insertion of new data, but not the modification of existing data, e.g. insertion of tuple in a relation.
3. Update authorization: allows modification of data, but not its deletion. But data items like primary-key attributes may not be modified.
4. Delete authorization: allows deletion of data only.

A user may be assigned all, none or combination of these types of authorization, which are broadly called access authorizations.

In addition to these manipulation operations, a user may be granted control operations like

1. Add: Allow adding new object types such as new relations (in case of RDB), records and set types (in case of network model) or record types and hierarchies (in hierarchical model of DB).
2. Drop: Allows the deletion of relations in DB.
3. Alter: Allows addition of new attributes in a relations (data-items) or deletion of existing data items from the database.
4. Propagate Access Control: This is an additional right that allows to propagate the access control or access right which one already has to some other i.e. if user A has access right R over a relation S, then if having propagate access control, he can propagate his access right R over relation S to another user B either fully or part of it.

The ultimate form of authority is given to the database administrator. He is the one who may authorize new users, restructure the database and so on. The process of authorization involves supplying information known only to the person the user has claimed to be in the identification procedure.

A basic model of Database Access Control

Models of database access control have grown out of earlier work on protection in operating systems.

Security problem

Consider,
Table: Employee (Emp #, Name, Address, dept #, salary, Assessment)

So, the problem is which level of access to grant?

Unconstrained - access > Strictly limited access

One of the most influential protection models was developed by Lampson and extended by Graham and Denning. This model has 3 components:

1. A set of objects: where objects are those entities known to the operating system, to which access must be controlled.
2. A set of subjects: where subjects are entities that request access to objects.
3. A set of all access rules: which can be thought of as forming an access (often referred to as authorization) matrix A, where columns O₁, O₂ …….., represent objects and rows S₁, S₂, ……., S_m represent subjects. The entry A[S_i, O_j] contains a list of access types t₁, t₂, …….., specifying the access privileges held by subject S_i for object O_j.

Object Subject	Emp-Name	Pers-no	Address	Tel-no	Salary
Personnel Manager	All	All	All	All	All
Admin. Clerk	Read	Read	Read	Read	-

As the above matrix shows, Personnel manager and Admin Clerk are the two subjects. Objects of database are Emp-name, Pers-no, Address, Tel-no, Salary. Let access rights be Read, Update, Delete, Insert. As per the access matrix, Personnel manager can perform any operation on the database of an employee while the Admin Clerk can only read the data but cannot update, delete or insert the data into the database. So in order to get to know what a given subject can do, the corresponding row to this subject should be looked at. Similarly, in order to know what all operations can be performed on an object, the column of the matrix should be followed.

In summary, it can be said that the basic access matrix is the representation of a tuple <S, O, a> where s is subject, O is an object and a is an access type.

SUMMARY

In this unit we discussed the recovery of the data contained in a database system after failures of various types. The types of failures that the computer system is likely to be subject to include that of components or subsystems, software failures, power outages, accidents, unforeseen situations, and natural or man-made disasters. Database recovery techniques are methods of making the database fault tolerant. The aim of the recovery scheme is to allow database operations to be resumed after a failure with a minimum loss of information and at an economically justifiable cost.

A database recovery system is designed to recover from the following types of failures: failure without loss of data; failure with loss of volatile storage; failure with loss of nonvolatile storage; and failure with a loss of stable storage.

The basic technique to implement database recovery is by using data redundancy in the form of logs, checkpoints, and archival copies of the database.

Security and integrity concepts are crucial since modifications in a database require the replacement of the old values. The DBMS security mechanism restricts users to only those pieces of data that are required for the functions they perform. Security mechanisms restrict the type of actions that these users can perform on the data that is accessible to them. The data must be protected from accidental or intentional (malicious) corruption or destruction. In addition, there is a privacy dimension to data security and integrity.

Security constraints guard against accidental or malicious tampering with data; integrity constraints ensure that any properly authorized access, alteration, deletion, or insertion of the data in the database does not change the consistency and validity of the data. Database integrity involves the correctness of data and this correctness has to be preserved in the presence of concurrent operations, error in the user’s operation and application programs, and failures in hardware and software.

In a nutshell

Database Security

• Protect the database contents from unauthorized access
  • Legal and ethical issues
  • Organization policies
  • Database management policies
• Database security techniques
  • Control access
  • Encrypt data
• The DBA can/is
  • Responsible for database security
  • Create and cancel user accounts
  • Assign appropriate security level to user accounts
  • Grant and revoke certain user privileges

MODEL ANSWERS

Check Your Progress

1. The following properties should be taken into consideration:
   • Loss of data should be minimal
   • Recovery should be quick
   • Recovery should be automatic
   • Affect small portion of database.
     
2. Data manipulation operations are:< >ReadInsertDeleteUpdate
   
   Add
3. Drop
4. Alter
5. Propagate access control
   
6. Data security is the protection of information that is maintained in database against unauthorized access, modification or destruction.

   Data integrity is the mechanism that is applied to ensure that data in the database is correct and consistent.