Understanding Database Administration: Security, Backup, and Concurrency Management
This chapter outlines essential concepts in database administration, including the importance of administration, various processing methods, and concurrency control. It addresses the critical need for database security, exploring both application and database security measures. The chapter also discusses backup and recovery strategies and explains recovery methods such as rollback and rollforward. Participants will also learn about ACID transactions, locking mechanisms, and administrative duties. Finally, the text emphasizes managing permissions and roles within a database context, ensuring both security and operational efficiency.
Understanding Database Administration: Security, Backup, and Concurrency Management
E N D
Presentation Transcript
CSCI260 Database Applications Database AdministrationPart 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 • 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
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
Chapter Objectives (continued) • Understand the nature of the tasks required for recovery using rollback/rollforward • Know basic administrative and managerial DBA functions
Database Security • Database Security strives to ensure: • Only authorized users • Perform authorized activities • At authorized times
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.
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
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
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
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
Application Security • Beyond providing generic access limitations to users, an application may introduce specific access rights for particular users.
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
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
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
Recovery viaRollback and Rollforward • Most database management systems provide a mechanism to record activities into a log file
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
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
Database Administration End of Presentation on Chapter Six
Due Monday December 119am, in the classroomBe ready to demo your entire database! Final Project for CSCI260
