1 / 21

Chapter 10 DB System Administration

University of Manitoba Asper School of Business 3500 DBMS Bob Travica. Chapter 10 DB System Administration. Based on G. Post, DBMS: Designing & Building Business Applications. Updated 2014. Outline. Data Administrator/Analyst (DA) Database Administrator (DBA) DBA’s duties

conroy
Download Presentation

Chapter 10 DB System Administration

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. University of ManitobaAsper School of Business3500 DBMSBob Travica Chapter 10 DB System Administration Based on G. Post, DBMS: Designing & Building Business Applications Updated 2014

  2. Outline • Data Administrator/Analyst (DA) • Database Administrator (DBA) • DBA’s duties • DB system backup & recovery • DB system security

  3. Data make a valuable asset. Data are used at many business levels There are many databases and database systems in an organization. Responsibility for managing data - Data Administrator (DA) or Data Analyst Data Administration EIS/ DSS Strategic Management MIS Tactical TPS Management Business Operations Supervisory Mgt. Employees with no mgt. powers TPS=Transaction Processing Sys. MIS=Management Info. Sys. DSS=Decisions Support Sys. EIS=Executive Info. Sys.

  4. DA (sometimescalled data architect or even business analyst) is a type of professional that resides in the IS function or in a unit interfacing with the IS function. Focus on informing in function of business, users (reports, output forms, queries) rather than IT Data definition and integration (e.g., Customer entity in CRM systems cutting across Sales, Marketing, R+D…). Decision support. Ideas for system design, involvement in system development. Data governance and security. Data Administration/Administrator (DA)

  5. DBA is focused on technology. 1. DBA actively participates in DB system life cycle (plan, develop, install, manage, upgrade…). 2. DBA manages DB system: 2.1 Users: Creating user accounts, assigning use privileges 2.2 System performance: Monitoring and tuning 2.3 Backup & recovery: Supervising backups & system restoration after crashes 2.4 Security: Monitoring Database Administrator (DBA)

  6. Estimation & Design (logical, physical) Data storage requirements, forms & reports needed (costs of development), hardware needs, matching organizational needs with DBMS products Time, labor & cost to develop Data modeling – coordinates with Data Analyst in the domain of logical design (e.g., class diagrams, user interface). Also DA and DBA cooperate on schemas. In charge of physical design (types of files, access structures, DBMS product, hardware) DBA: (1) System Planning & Design

  7. Defining technology standards: Programming standards. Layout and techniques. Variable & object definition. User interface. System testing techniques. Loading databases. Backup and recovery plans. User and operator training. DBA: System Development & Implementation

  8. Determines need for change Size and speed of the DB system Usage patterns System output: Additional reports & queries (coop. with DA and business analysts) Forecasting needs DBA: System Upgrade

  9. Controls based via: 1. Operating system Access to directories Access to files Assigned to individuals or groups. 2. DBMS functions (Read, write, modify… data; Administer system)  DBA: (2.1) Users’ Access

  10. GRANT privileges REVOKE privileges Privileges include SELECT DELETE INSERT UPDATE Objects include Table Table columns (SQL 92+) Query Users include Name/Group PUBLIC SQL Security Commands GRANT INSERT ON Bicycle TO OrderClerks REVOKE DELETE ON Customer FROM Assemblers

  11. User identification Accounts Individual Groups Passwords Alternative identification Finger & hand print readers Voice… Disposable passwords DBA: User Identification

  12. DBA: (2.2) System Performance: Performance Monitors

  13. SQL Server Query Analyzer

  14. Backups are crucial! Offsite storage needed Types of backup Full – in longer intervals (e.g., once a week); a copy of all tables made Partial (Differential) – in shorter intervals; just new data are copied; Alternative (not shown): No partial backups but changes made in Op DB after the last backup copied directly into Bkp DB. Operational Database (Op DB) Backup Database (Bkp DB) Copies Entire OpDB time 1: copies new data from Op DB Partial backup time 2: copies new data from Op DB Partial backup time 3: copies last partial backup to BkP DB DBA: (2.3) Database Backup Full Backup Manager (part of DBMS) Partial Bkp DB

  15. copied to uses Backup databases DBA: 2.3 Database Recovery Transaction Log (TL) (managed by Backup Manager) • Recovery needed if problems with software, hardware, incorrect user input, viruses, natural causes • Recovery = getting databases to correct state (previous example of transferring $ from savings to checking account) • Key facilities: • Recovery Manager (part of DBMS) • Transactions log file • ROLLBACK procedure • Alternative: User works with operational DB, and TL engaged only if former fails. Operational database Transactions … Savepoint Transaction unfinished System crash! recovers uses Recovery Manager

  16. Transaction Log (TL) Transaction ID Pointer to previous TL record Pointer to next TL record Transaction Log ID Database task Old value New value Table Key Attribute Checkpoint (Savepoint) is when results of all new transactions are stored permanently (e.g., in Op DB). COMMIT saves data in TL. Just the first transaction is saved permanently. Recovery Mgr rolls back to Checkpoint and runs steps/transactions that haven’t been saved after it.

  17. Physical security Protecting hardware Protecting software and data. Logical security Unauthorized disclosure Unauthorized modification Unauthorized withholding Security Threats Employees (!) Programmers Visitors Consultants Business partnerships Strategic sharing EDI (Electronic Data Interchange & other inter-org. networks) Hackers (Internet) DBA: (2.4) Database Security

  18. Data Privacy • A security issue • Who owns data? (a governance issue) • Customer rights • International issues (e.g., strict privacy regulations in West Europe; Canada stricter than the US)

  19. Hardware-related Preventing problems (fire, water…) Hardware backup facilities (“Hot sites” etc.) Telecommunication systems for backup Personal computers challenge (use file servers for backup) Data and software Backups, Off-site backups (!) Disaster planning Plans, training & testing Physical Security

  20. Insiders Employee selection & Job termination Monitoring suspicious behavior Job segmentation (who can do what with data*) Physical & Logical access limitations Outsiders Physical access limitations “Shadowing” Managerial Controls

  21. 1. Unauthorized disclosure (e.g., letting a competitor see the strategic marketing plans) 2. Unauthorized modification (e.g., letting employees change their salary figures) 3. Unauthorized withholding (e.g., preventing a finance officer from retrieving data needed to get a bank loan) Logical Security

More Related