1 / 22

Database Administration Part 2

CSCI260 Database Applications. Database Administration Part 2. Chapter Six. Chapter Objectives. Understand the need for and importance of database administration Learn different ways of processing a database Understand the need for concurrency control, security, and backup and recovery

kenley
Download Presentation

Database Administration Part 2

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. CSCI260 Database Applications Database AdministrationPart 2 Chapter Six

  2. Chapter Objectives • Understand the need for and importance of database administration • Learn different ways of processing a database • Understand the need for concurrency control, security, and backup and recovery • Learn typical problems that can occur when multiple users process a database concurrently • Understand the use of locking and the problem of deadlock Understand the use of locking and the problem of deadlock

  3. Chapter Objectives (continued) • Learn the difference between optimistic and pessimistic locking • Know the meaning of ACID transaction • Learn the four 1992 ANSI standard isolation levels • Understand the need for security and learn a generalized model of database security • Know the difference between DBMS and application security • Know the difference between recovery via reprocessing and recovery via rollback/rollforward

  4. Chapter Objectives (continued) • Understand the nature of the tasks required for recovery using rollback/rollforward • Know basic administrative and managerial DBA functions

  5. Database Security • Database Security strives to ensure: • Only authorized users • Perform authorized activities • At authorized times

  6. Admin Asst: Read, Insert and change data in all tables. ONLY delete from SEMINAR-CUSTOMER (un-enroll customer from seminar) and LINE-ITEM (take item off order). Management: Take all actions except delete customers. Never want to delete a customer. Sys Admin: Only define permissions. No other rights. Not a user, no need to change data.

  7. Database Security Guidelines • Run the DBMS behind a firewall • No access outside of organization • Problem with e-commerce applications • Still protect all non-e-commerce activities • Apply the latest operating system and DBMS service packs and patches • Spring 2003 Slammer worm exploited security hole in SQL Server • MS published patch eliminating hole, for those who applied it

  8. Database Security Guidelines • Limit DBMS functionality to needed features • Remove extra communication protocols • Remove pre-packaged stored procedures • Protect the computer that runs the DBMS • No one should use or access this computer • Keep behind locked and logged doors • Manage accounts and passwords

  9. Processing Rights and Responsibilities • Processing rights define who is permitted to do what, when • The individuals performing these activities have full responsibility for the implications of their actions • Individuals are identified by a username and a password

  10. DBMS Security(Granting Permissions) • Database users are known as an individual and as a member of one or more role • Granting access and processing rights/privileges may be granted to an individual and/or a role • Users possess the compilation of rights granted to the individual and all the roles for which they are members

  11. Application Security • Beyond providing generic access limitations to users, an application may introduce specific access rights for particular users.

  12. A Model of DBMS Security

  13. Database Backup and Recovery • Common causes of database failures… • Hardware failures • Programming bugs • Human errors/mistakes • Malicious actions • Since these issues are impossible to completely avoid, recovery procedures are essential

  14. Database Backup and Recovery • First – business functions must continue. • Customer orders, financial transactions, packing lists – all completed manually • Second – system must be restored to usable stage ASAP and as close as possible to what it was when it crashed • Third – users must be notified when system back online • Some data may need to be re-entered

  15. Recovery via Reprocessing • In reprocessing, all activities since the backup was performed are redone • This is a brut-force technique • This procedure is costly in the effort involved in re-entering the data • This procedure is risky in that human error is likely and in that paper record-keeping may not be accurate

  16. Recovery viaRollback and Rollforward • Most database management systems provide a mechanism to record activities into a log file

  17. Rollforward • Activities recorded in the log files may be replayed. In doing so, all activities are re-applied to the database • This procedure is used to resynchronize restored database data • This procedure is termed a Rollforward

  18. Rollback • Since log files save activities in sequence order, it is possible to undo activities in reverse order that they were originally executed • This is performed to correct/undo erroneous or malicious transaction(s) • This procedure is known as a Rollback

  19. Database Administration End of Presentation on Chapter Six

  20. Due Monday December 119am, in the classroomBe ready to demo your entire database! Final Project for CSCI260

More Related