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 2015

D B S Y S T E M S Outline

 Data Administrator/Analyst (DA)  Database Administrator (DBA)  DBA’s duties  DB system backup & recovery  DB system security 2 of 22

D B S Y S T E M S Data Administration

 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 TPS=Transaction Processing Sys.

MIS=Management Info. Sys.

DSS=Decisions Support Sys.

EIS=Executive Info. Sys.

3 of 21

D B S Y S T E M S Data Administration/Administrator (DA)

 DA (sometimes called

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.

4 of 21

D B S Y S T E M S Database Administrator (DBA)

 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 5 of 21

D B S Y S T E M S DBA: (1) System Planning & Design

 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) 6 of 21

D B S Y S T E M S DBA: System Development & Implementation

 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.

7 of 21

D B DBA: System Upgrade S Y S T E M S

 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 8 of 21

D B S Y S T E M S DBA: (2.1) Users’ Access

 Control via:  1. Operating system  Access to directories  Access to files  Assigned to individuals or groups.

 2. DBMS functions (Read, write, modify… data; Administer system)  9 of 21

D B S Y S T E M S SQL Security Commands

   GRANT

privileges

REVOKE

privileges

Privileges include     SELECT DELETE INSERT UPDATE  Objects include    Table Table columns (SQL 92+) Query  Users include   Name/Group PUBLIC GRANT INSERT ON Bicycle TO OrderClerks REVOKE DELETE ON Customer FROM Assemblers 10 of 21

D B S Y S T E M S DBA: User Identification

 User identification  Accounts  Individual  Groups  Passwords  Alternative identification  Finger & hand print readers  Voice…  Disposable passwords 11 of 21

D B S Y S T E M S DBA: (2.2) System Performance: Performance Monitors

12 of 22

D B S Y S T E M S SQL Server Query Analyzer

13 of 22

D B DBA: (2.3) Database Backup S Y S T E M S

   Backups are crucial!

Offsite storage needed Types of backup   Full – in longer intervals (e.g., once a week); a copy of all tables made (part of DBMS) Partial (Differential) – in shorter intervals (e.g., day); just new data are backed up; reduced risk but higher cost

Backup Manager

Full Copies Entire OpDB Operational Database (Op DB) overwrites

Backup Database (Bkp DB)

Partial time 1: copies new data from Op DB

Partial backup

time 2: copies new data from Op DB grows

Partial backup

time 3: copies final partial backup to BkP DB

Bkp DB

14 of 21

D B S Y S T E M S DBA: 2.3 Database Recovery

 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)

Transaction Log (TL)

(managed by Backup Manager) Transactions … Savepoint copied to  Key facilities:    Recovery Manager (part of DBMS)

Transactions log (TL) file

ROLLBACK procedure Transaction unfinished

System crash!

uses Operational database recovers Recovery Manager  Alternative: User works with operational DB, and TL engaged only if former fails.

uses Backup databases 15 of 22

D B Transaction Log (TL) Transaction ID

Transaction Log ID Pointer to previous TL record Pointer to next TL record

Database task S Y S T E M S

Table Key Attribute Old value New value Checkpoint (Savepoint) is when results of all new transactions are stored permanently (e.g., in Op DB). COMMIT saves changes to TL. Just the first transaction saved permanently. Recovery Mgr rolls processing back to Checkpoint and runs steps/transactions that haven’t been saved after it.

16 of 22

D B S Y S T E M S DBA: (2.4) Database Security

 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) 17 of 21

D B S Y S T E M S 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) 18 of 22

D B S Y S T E M S Physical Security

 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 19 of 21

D B S Y S T E M S Managerial Controls

 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” 20 of 21

D B S Y S T E M S Logical Security

 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) 21 of 21