Database Transaction and Security

Are you preparing for IT certification? With practice questions, study notes, interactive quizzes, tips and technical articles, uCertify PrepKits ensure that you get a solid grasp of core technical concepts to ace your certification exam in first attempt.

Database Transaction and Security

Rating:

First of all, we need to understand what is a transaction in a database? and how does it affect the database?

A transaction is a group of actions such as insert, update, and delete performed on the database to change the state of a database. After a transaction is performed successfully, the database changes to a new state from its existing state.

The databases that support transaction are known as transactional databases. Most of the modern day relational databases support transaction.

A single transaction may require many SQL queries to execute to change the current state of the database. It is also important that all the queries be executed. For example, if money is transferred from one account to another, then in this scenario, the account from which the money is taken out should be debited and the account to which the money is deposited should be credited.

A transaction is issued to the database using SQL (structured query language) and has the following stages:

• A transaction begins.
• Execution of queries to change the current state of the database.
• After the successful execution of all the queries, a transaction is committed, i.e. the changes are saved permanently.

A database transaction has the following properties:

• Atomicity
• Consistency
• Isolation
• Durability

Each of these properties is discussed below:

Atomicity: This property suggests that either all the changes are made in the database or no change has been made. It ensures that either a transaction is committed (i.e. all the changes made to the database are permanently saved) or rolled back (i.e. no change has been made and the database is recovered to its original state).

Consistency: This property suggests that the database remains in a valid state before and after the transaction is committed. This means that a transaction cannot violate the business rules or integrity constraints of the database. If an integrity constraint states that an account cannot be debited if the balance is less than $500, then any transaction violating this rule will be aborted.

Isolation: This property ensures that a transaction is isolated from any outside operation. This means that data in a transaction cannot be viewed in its intermediate state.

Durability: This property refers that once the user has been notified about the success of the transaction, the transaction will continue and not revert back. This implies that the transaction is being checked for all data integrity constraints and no constraint has been found violated.

Transaction Logs:

A majority of relational databases that we use provide support for transaction logging. Transaction logging is a process in which each transaction committed against the database is logged and the original value of the data is stored. When a SQL query gets executed to change the values in the database, then the original data values are stored in the transaction log. These values are stored so that the database can be recreated using these values in case a transaction fails. A transaction log keeps on storing the original data values until a transaction is committed or rolled back.

Another important aspect of transaction processing is locking. For example, if a user begins a transaction and performs an action that causes to lock a certain portion of the database, then no other user is allowed to access the locked data until the transaction is completed.

Database Security:

The database security is of great importance and a major concern for any organization. Securing the data from people of malicious intent, accidental damage, catastrophic damage is a great challenge for a Database Administrator. The Database Administrator decides the level of security keeping in view the sensitivity of data. There are different levels of security.

At the lowest level of security, there is no restriction, i.e. everybody has full access to the database. This level of security is implemented where there is already network security through which access can be restricted. In such type of scenario, there is no need to duplicate the security scheme.

The next level of security is share level security. At this level, the Database Administrator assigns a password to the whole database and the users who know the password can access the full database. This level of security is easy to implement and administer because it requires to change only one password periodically. This type of security is adequate in situations where the organization wants to provide full access to the database to its employees. For example, a database that stores annual holiday list or database regarding news, which the organization publish from time to time.

After this level comes user level security. At this level, each user is assigned some particular privileges by the Database Administrator. For example, it is the prerogative of the Database Administrator to assign different rights to different users so that each user can be restricted to use the database. He can assign various rights such as some users are entitled to only view the data, some can modify by adding or deleting entries from the database, etc. This level of security is a bit difficult to implement and administer, but it provides a higher degree of security to the data. This level of security can be implemented on individual users as well as a group of users. To implement on a group of users, each group should be identified first such as system administrators, order-entry clerks, salespeople, and so forth-and then determine the security privileges that each role has for each object in the system.

Auditing:

In addition, in order to restrict the access of the database, the Administrator might want to track the details about what the users have done on the database or who logged on to the database, etc. The requirement of keeping such audit trails varies from organization to organization. Suppose the Database Administrator might want to keep the details about who logged and logged off, then a single entity with the attributes UserName , LogOn , and LogOff will probably suffice the purpose.

Keeping track of deletions can be more complex. The Database Administrator has a couple of options here. He can prevent users from actually deleting records at all and set a Deleted flag instead. This is a useful technique if he wants to copy the records to an archive file before removing them from the database.

After modeling the audit requirements, the Database Administrator needs to restrict access to the audit tables.

Rating: