Database administration part 2
1 / 22

Database Administration Part 2 - PowerPoint PPT Presentation

  • Uploaded on

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' Database Administration Part 2' - kenley

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Database administration part 2

CSCI260 Database Applications

Database AdministrationPart 2

Chapter Six

Chapter objectives
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
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 continued1
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

  • 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
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 guidelines1
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 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
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
Application Security

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

Database backup and recovery
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 recovery1
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
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 via rollback and rollforward
Recovery viaRollback and Rollforward

  • Most database management systems provide a mechanism to record activities into a log file


  • 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


  • 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

Database Administration

End of Presentation on Chapter Six

Due monday december 11 9am in the classroom be ready to demo your entire database

Due Monday December 119am, in the classroomBe ready to demo your entire database!

Final Project for CSCI260