1 / 42

Enterprise Database Systems Recovery Security and Authorization

Enterprise Database Systems Recovery Security and Authorization. Technological Educational Institution of Larissa in collaboration with Staffordshire University Larissa 2006. Dr. Georgia Garani garani@teilar.gr Dr. Theodoros Mitakos teo_ms@yahoo.com. Agenda. Recovery techniques

jmorrissey
Download Presentation

Enterprise Database Systems Recovery Security and Authorization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Enterprise Database SystemsRecovery Security and Authorization Technological Educational Institution of Larissa in collaboration with Staffordshire University Larissa 2006 Dr. Georgia Garani garani@teilar.gr Dr. Theodoros Mitakosteo_ms@yahoo.com

  2. Agenda Recovery techniques Database security Database authorization

  3. Recovery concepts • Recovery from transaction failures means that database is restored to the most recent consistent state • In case of extensive damage the recovery method restores a past copy of the database that was backed up to archival storage • If there is not a physical damage but there is some inconsistency the changes are reversed either by • Undoing some operations or • Redoing some operations

  4. NO-UNDO/REDO Algorithm • Deferred update techniques do not update the database on disk until after a transaction reaches its commit point. • If a transaction fails before reaching its commit point it will not have changed the database in any way so UNDO is not need. It may be necessary to REDO the effect of the operations of a committed transaction from the log.

  5. UNDO/REDO Algorithm • Immediate update techniques: The database may be updated by some operations of a transaction before the transaction reaches its commit point. • If a transaction fails after recording some changes in the database but before reaching its commit point the effect of its operations on the database must be undone; the transaction must be rolled back. In the general case both undo and redo may be required during recovery.

  6. UNDO/NON-REDO Algorithm • A variation of the previous algorithm where all updates are recorded in the database before a transaction commits requires undo only. This called UNDO/NO-REDO algorithm.

  7. Disk blocks • Cashed blocks • A directory for the cache is used to keep track of which database items are in the buffers. • Page replacement strategy is used to select buffers for replacement (LRU, FIFO) • Flush-replace cache buffers to make space available. • Dirty bit: Indicates whether or not a buffer has been modified • Pin-unpinned bit: If (or not) a page in the cach can be written back to disk. • In-place updating writes the buffer back to the same original disk location overwritting the old value of any changed data items on disk. • Shadowing writes an updated buffer at different disk location so multiple versions of data items can be maintained.

  8. Write-Ahead logging • Write ahead logging: The BFIM of the data item is recorded in the appropriate log entry and the log entry is flushed to disk before the BFIM is overwritten with the AFIM in the database on disk. • REDO type log entry includes new value (AFIM) of the item • UNDO type log entry includes the old value (BFIM) of the item • Write ahead logging: The log blocks that contain the associated log records for a particular data block update must first be written to disk before the data block itself can be written back to disk.

  9. Steal/no-steal • No-Steal: If a cache page updated by a transaction cannot be written to disk before the transaction commits. • Steal: Writing a an updated buffer before a transaction commits. (the cache manager needs a buffer frame for another transaction and replaces an existing page.)

  10. Force/no-force • Force approach: All pages updated by a transaction are immediately written to disk when the transaction commits. (otherwise is called no-force) • Deferred update recovery scheme follows a no-steal approach. (steal/no-force avoids the need for very large buffer space to store all updated pages in memory)

  11. Advantages Steal/No-force • (steal: avoids the need for very large buffer space to store all updated pages in memory) • (no-force: an updated page of a committed transaction may still be in buffer when another transaction needs to update it, thus eliminating the I/O cost to read the page again from disk)

  12. Example WAL protocol • The before image of an item cannot be overwritten by its after image in the database on disk until all UNDO-type log records for the transaction have been force-written to disk. • The commit operation of a transaction cannot be completed until all the REDO-type and UNDO-type log records for that transaction have been force written to disk. • Active transactions: Transactions that have started but not committed yet. • Committed, aborted transactions

  13. Checkpoints • A checkpoint record into a log marks where the system writes out to the database on disk all DBMS buffers that have been modified. • Taking a checkpoint consists of the following actions • Suspend execution of transactions temporarily • Force-write all main memory buffers that have been modified to disk • Write a checkpoint record to the log, and force-write the log to disk. • Resume executing transactions

  14. Transaction Rollback • If transaction fails for whatever reason after updating the database it may be necessary to roll back. If any data item values have been changed by the transaction and written to the database, they must be restored to their previous values (BFIM). • Cascading rollback: If a transaction T is rolled back, any transaction S that has, in the interim, read the value of some data item X written by T must also be rolled back. Similarly, once S is rolled back any transaction R that has read the value of some data item Y written by S must also rolled back and so on. (recoverable but not strict or cascadeless schedules).

  15. T1 Read_item(A) Read_item(D) Write_item(D) T2 Read_item(B) Write_item(B) Read_item(D) Write_item(D) Example 1/2 • T3 • Read_item(C) • Write_item(B) • Read_item(A) • Write_item(A)

  16. Example 2/2 A B C D 30 15 40 20 [start_transaction,T3] [Read_item(),T3,C] * [Write_item,T3,B,15,12] 12 [Read_item(),T2,B] ** [Write_item,T2,B,12,18] 18 [start_transaction,T1] [Read_item(),T1,A] [Read_item(),T1,D] ** [Write_item,T1,D,20,25] 25 [Read_item(),T2,D] [Write_item,T2,D,25,26] 26 [Read_item(),T3,A] SYSTEM CRASH

  17. Operations before crash WRITE(B) READ(C) READ(A) T3 BEGIN READ(B) WRITE(B) READ(D) WRITE(D) T2 BEGIN WRITE(D) READ(D) READ(A) T1 BEGIN SYSTEM CRASH TIME

  18. A NO-UNDO/REDO algorithm • A transaction cannot change the database on disk until it reaches its commit point • A transaction does not reach its commit point until all its update operations are recorded in the log and the log is force-written to disk. (restatement of WAL) • The database is never updated on disk until after the transaction commits so there is no need to UNDO any operation. REDO is needed in case the system fails after a transaction commits but before all its changes are recorded in the database on disk.

  19. Recovery in S-user Environment Deferred update • PROCEDURE RDU_S: Use two lists of transactions: the committed transactions since the last checkpoint, and the active transactions. Apply the REDO operation to all the WRITE_ITEM operations of the committed transactions from the log in the order in which they were written to the log. Restart active transactions. • REDO(WRITE_OP): Redoing a write_item operation WRITE_OP consists of examining its log entry [write_item,T,X,new_value] and setting the value of item X in the database to new_value, which is the after image (AFIM). (redo is idempotent) • An operation is idempotent if executing it over and over is equivalent to executing it just once. • The result of recovery from a system crash during recovery should be the same as the result of recovering when there is no cash during recovery.

  20. Recovery in M-user EnvironmentDeferred update • We consider a system where concurrency control uses strict two-phase locking, so the locks on items remain in effect until the transaction reaches its commit point. This ensures strict and serializable schedules. • PROCEDURE RDU_M: Use two lists of transactions: the committed transactions T since the last checkpoint, and the active transactions T’. REDO all WRITE operations of the committed transactions from the log, in the order which the were written into the log. Transactions that are active and did not commit are effectively canceled and must be resubmitted. • Note that if a data item X has been updated more than once by committed transactions it is only necessary to REDO the last update of X from the log during recovery.

  21. Pros and Cons of the method • It limits the concurrent execution of transactions because all items remain locked until the transaction reaches its commit point. • Transaction operations never need to be undone • A transaction does not record any changes n the database on disk until after it reaches its commit point (never rolled back during transaction execution) • A transaction will never read the value of an item that is written by an uncommitted transaction because items remain locked until a transaction reaches its commit point.

  22. Transaction actions that do not affect the database • E.g. generating reports, printing messages • If a transaction fails before completion erroneous reports may be produced. • Such reports should be produced only after the report reaches its commit point. • Do not generate reports immediately but keep them as batch jobs which are executed only after the transaction reaches its commit point.

  23. Recovery based on Immediate Update • When a transaction issues an update command the database can be updated immediately without any need to wait for the transaction to reach its commit point. However an update operation must still be recorded in the log before it is applied to the database so we can recover in case of failure.

  24. Categories of immediate update algorithms • If the recovery technique ensures that all updates of a transaction are recorded in the database on disk before the transaction commits, there is never a need to REDO any operation of committed transactions (UNDO/NON_REDO algorithm) • If the transaction is allowed to commit before all its changes are written to the database we have the most general case known as the UNDO/REDO recovery aalgorithm

  25. Recovery in S-user EnvironmentImmediate update (UNDO/REDO) • PROCEDURE RIU_S • Use two lists of transactions maintained by the system: the committed transactions since the last checkpoint and the active transactions. • Undo all the write_item operations of the active transaction from the log, using the UNDO procedure described below • Redo the write_item operations of the committed transactions from the log, in the order in which they were written in the log, using the REDO procedure described earlier. UNDO(WRITE_OP): Undoing a write_item operation write_op consists of examining its log entry[write_item, T,X,,old_value, new_value] and setting the value of item X in the database to old_vale which is te before image (BFIM). Undoing a number of write_item operations from one or more transacions from the log must proceed in the reverse order from the order in which the operations were written in the log.

  26. Recovery in M-user EnvironmentImmediate update (UNDO/REDO) • The log uses checkpoints and the concurrency control protocol produces strict schedules. • PROCEDURE RIU_M • Use two lists of transactions maintained by the system: the committed transactions since the last checkpoint and the active transactions. • Undo all the write_item operations of the active transaction from the log, using the UNDO procedure. The operations should be undone in the reverse order from the order in which the operations were written in the log. • Redo the write_item operations of the committed transactions from the log, in the order in which they were written in the log.

  27. Shadow Paging • This recovery scheme does not require the use of a log in a single user environment. In multiuser environment logs and checkpoints must be incorporated in the shadow paging technique. • Current directory – shadow directory of database pages on disk • During execution the shadow directory is never modified. Only the current directory entries are modified. • To recover from a failure during transaction execution it is sufficient to free the modified database pages and to discard the current directory. • It is considered as NO-REDO/NO-UNDO technique

  28. Disadvantages • Difficult to keep related pages together on disk • The overhead of writing shadow directories to disk as transactions commit is significant • Handling of garbage collection is difficult

  29. Recovery in Multidatabase systems • Need a global recovery manager • Two phase protocol • Either all participating databases commit the effect of transaction or one of them do.

  30. Catastrophic failures • Backup both the database and the log. • Restore both the database and the log.

  31. Example SQL server • Exercise: Backup a database using Transact SQL commands

  32. Legal ad ethical issues regarding the right to access information Policy issues at enterprise level System-related issues The need to categorize the data and the users identifying multiple security levels. Types of security

  33. Threats to databases • Loss of integrity: The information must be protected from improper modification. • Loss of availability: It refers to making objects available to users. • Loss of confidentiality: It refers to the protection of data from unauthorized disclosure.

  34. Database security mechanisms for the users • Discretionary security mechanisms: These are used to grant privileges to users, including the capability to access specific data files, records, or fields in a specific mode. • Mandatory security mechanisms: These are used to enforce multilevel security by classifying the data and users into various security classes and then implementing the appropriate security policy of the enterprise.

  35. Access control • Preventing unauthorized persons from accessing the system itself either to obtain information or to make malicious changes in a portion of the database.

  36. Statistical databases • Statistical database users are allowed to access the database to retrieve statistical information about a population but not to access the detailed confidential information on specific individuals. Security for statistical databases must ensure that information on individuals cannot be accessed. The countermeasures for this security problem is called inference control problem.

  37. Flow Control Data encryption • It prevents information from flowing in such a way that it reaches unauthorized users. • Data encryption is used to protect sensitive data that is being transmitted via some type of communications network. The data is encoded using some coding algorithm. An unauthorized user who access encoded data will have difficulty deciphering it.

  38. The DBA • The DBA performs the following actions • Account creation • Privilege granting • Privilege revocation • Security level assignment

  39. Database audit • User account: Login and password • Login session: Time from log in to log out • The system log keeps track of the operations a user applies to the database • If any tampering is suspected a database audit is performed which consists of reviewing the log to examine all accesses and operations applied to the database during a certain period of time. • A database log that is used mainly for security purposes is sometimes called audit trail.

  40. SQL types of privileges • SELECT privilege on R: This gives the account the privilege to use the select statement to retrieve tuples from R. • MODIFY privilege on R: This gives the account the capability to modify tuples of R. • REFERENCES privilege on R: This gives the account the capability to reference relation R when specifying integrity constraints.

  41. EXAMPLES • GRANT CREATETAB TO A1; • The CREATETAB privilege gives account A1 the capability to create new database tables • CREATE SCHEMA EXAMPLE AUTHORIZATION A1; • User account A1 can create tables under the schema called EXAMPLE. It has all the relation privileges. • GRANT INSERT, DELETE ON EMPLOYEE, DEPARTMENT TO A2 • GRANT INSERT, DELETE ON EMPLOYEE, DEPARTMENT TO A3 WITH GRANT OPTION • GRANT SELECT ON EMPLOYEE TO A4 • REVOVE SELECT ON EMPLOYEE TO A4

  42. EXAMPLES cont • CREATE VIEW A3EMPLOYEE AS • SELECT NAME, BDATE, ADDRESS • FROM EMPLOYEE • WHERE DNO=5 • GRANT SELECT ON A3EMPLOYEE TO A3 WITH GRANT OPTION • GRANT UPDATE ON EMPLOYEE(SALARY) TO A4

More Related