Database Management System Contents Database Security issues Threats

Database Management System Contents Database Security issues Threats to database Role of DBA in database security Authentication Authorization Data Encryption Database Integrity Database Failure Database Recovery

Database Management System Database Security Database security is a crucial issue in the DBMS. Here the term security refers to the protection of database against unauthorized access intentional or accidental. The database security involves security of all part of the system i. e. DBMS, Network, operating system, physical location of the database system. Security Issues are : ◦ ◦ Privacy of database. Database availability Database integrity. Lack of accountability Threats to Database

Database Management System Database Security Role of DBA: DBA is the central authority to manage the database. Security responsibilities are: Managing User Accounts: It includes creation and deletion of user accounts as well as granting and revoking privileges to/from the users. Performing Database Audit: To access the database every user must have an authentic account identification and password. When user enters id √ of all the user activities along with and password, DBA keeps record terminal numbers. In case of data security issue these records are inspected to find the source of tampering or threat. The database log that is used for database security is generally called an Audit Trail. Authentication: it is a process by which database identifies the users before allowing the access. Mainly two elements are required for authentication process: ◦ ◦ User ID Authentication token

Database Management System Database Security Authorization: after authentication next step is authorization. It is a process of granting privileges to user which enables them to access certain portion of database and perform operations on it. To keep track of each user’s authorization An Access Matrix is maintained. Forms of Authorization: Read, Insert, Update, Delete (data in database) Index, Resources, Alteration, Drop (database schema) Objects V W X Y A x √ B √ √ x x C x x √ √ Subjects

Database Management System Database Security Data Encryption: It is a technique to protect sensitive data such as account passwords, Credit Card details etc that is required to be submitted over network. It is usually an additional security mechanism along with normal security techniques. In this technique data is encoded using some coding algorithm. Only an authorized user knows the coding algorithm to encode and decode the data. Here the original data is called “Plaintext” after performing some coding algorithm on Plaintext becomes “Cipher text”. This Cipher text is transmitted over the network. This process in called encryption. On the receiver side the received cipher text is converted into plaintext using same or related coding algorithm. This process is call decryption. Encryption is performed at source side before data transmission. And encryption is done at receiver side after receiving the data.

Database Management System Database Security

Database Management System Database Security Encryption Algorithm: There are two types of encryption techniques: Symmetric Algorithm: where same coding algorithm also known as encryption key is used on both sides to encrypt or decrypt the data is called Symmetric Encryption Algorithm. Asymmetric Algorithm: where send and receiver use different coding algorithms for encoding and decoding of the data is called Asymmetric Encryption Algorithm.

Database Management System Database Security Database Integrity: Database integrity means completeness, correctness and consistency of database. Data in database must be accurate, valid and consistent. It is another technique for database security. Integrity is maintained through integrity constraints. Integrity means set of integrity rules which are: Domain Integrity Rules: it concerns with the correctness of attribute values within relations. It checks the validity of the entries for a given column. Domain integrity can be maintained by restricting data types, check or range constraints, Foreign key, Default or Non Null Values. Entity Integrity Rules: Entity integration states every data must be uniquely identifiable. For this a primary key is used in a table. Referential Integrity Rules: This rule states the correctness and consistency between relations. It can be implemented using Foreign Key constraint on relations.

Database Management System Database Security Database Failure: database failure means due to some hardware/software problem the database stop the normal flow of execution. This failure can be lossy or lossless. Following are the types of database failures: Logical Error: if a transaction stops due to wrong input, integer overflow, division by zero, it is called logical error. For this certain exceptions are to programmed into the database that states what to do after certain logical error. System Error: any undesirable state because of deadlock, incorrect synchronization etc is called system error. Disk/Media Failure: loss of data because of disk crash, power disruption or error in data transfer operation. To overcome this failure a backup copy of database must be kept. Natural problems or disasters: problems like fire, earthquakes, floods, theft, accidental overwriting comes under this type of failure.

Database Management System Database Security Database Recovery: whenever a failure occurs it the responsibility of DBMS to recover the database with as little damage as possible. The database system must maintain the atomicity and durability of the database. So the recovery scheme of DBMS is responsible for database recovery after the failure. Usually a backup of database is kept for most previous consistent state. So backup and recovery go hand in hand for protecting database against loss and failure. Recovery Techniques: there are basic 7 recover techniques: Backward Recovery Forward Recovery ◦ Log-based Recovery Deferred Update Immediate Update ◦ ◦ Shadow-Paging Checkpoints

Database Management System Database Security ◦ ◦ ◦ Backward Recovery (UNDO): in this case uncommitted changes made by any transaction that suffered some error or failure are rolled back to the previous consistent state. Forward Recovery (REDO): whenever an error occurs in database, the change is reapplied to the previously backed up copy of the database. This recovery method can be categorized into three types: Log-Based Recovery Shadow- Paging Checkpoints Log-Based Recovery: a transaction log is maintained to track all updates operations of the transactions. This method is used when multiple transactions are executed simultaneously. A log contains following information: Transaction id Operation performed on transaction Data items being affected by the transaction operation i. e. table name, row and column number.

Database Management System Database Security Log-Based Recovery: this recovery technique is implemented using any of the following method: Deferred update: here changes are implemented on the database only when the transaction ends successfully. Changes are recorded in the log file and the log file is applied to the database on commit. On rollback, any changes to data in the log file are cleared. If a traction fails before commit point there is no need to change database. In case of any failure after commit point once the database is recovered the log file changes can be implemented on the database without losing any data. Immediate update: here the changes are inserted into the database at the time of the execution of the transaction without waiting for it to commit. Both original and changed values are stored in the log file. On commit all the changes are made permanent and the data in the log file is discarded. In case of rollback all the changes are discarded. If system crashes changes after the commit are restored and uncommitted changes are discarded.

Database Management System Database Security Shadow Paging: when only one transaction is being executed then shadow paging can be used as a recovery technique. Here copies of the database are maintained. Here database is divided into fixed length blocks called Pages. These pages are mapped into the physical blocks of storage with the help of page table in any order. During the execution of a transaction two pages of tables are maintained: one for current page table one as shadow page table. The current page table points to the most recent database page. When a transaction starts this current page is copied to the shadow page table. Shadow page is saved on the disk and current page is used for the transaction execution. All the operations are performed on the current page. When a transaction commits all the changes of the current page is copied to the disk page called. In case of failure database can be recovered from the shadow page.

Database Management System Database Security Shadow Paging:

Database Management System Database Security Checkpoints: the database systems usually keep a log for each transactions and in case of failures it performs one operation: Replays the log and restore the transactions. Returns the database to the previous consistent state To restore the database, the database server generates a consistency points called “Checkpoints”. A Checkpoint is a copy of the database at a particular moment. It also involves some information so that at the time of recover database can restart at that established point. If there is no checkpoint then in case of failure database server will process all the transaction that were recorded in the logical song since the system restarted. A checkpoint can occur in one of the following situations: When a specific event occurred for example backup of the database is created. Events that block transaction processing trigger checkpoints. When resource limitation occurs i. e. log file is 75% full.

Database Management System Database Security Checkpoints:

Database Management System Database Security Checkpoints: A checkpoint operation is performed periodically. The information and operation performed at each check point consists of the following: A start of checkpoint record giving the identification i. e. date and time of the checkpoint. All log information from the buffers is copied to the log in stable storage. All database updates from the buffers in the volatile storage are written to the disk.

Chapter End