University of Manitoba Asper School of Business 3500

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 2018

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

Data Administration ² Responsibility for managing data Data Administrator (DA) or Data Analyst Tactical Management Supervisory Mgt. Employees with no mgt. powers S Management Bu TPS Op si er nes at ion s s ² There are many databases and database systems in an organization. Strategic MI ² Data are used at many business levels S EI S Y S T E M S S/ ² Data make a valuable asset. DS D B ² 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 Database Administrator (DBA) ² DBA is focused on technology. S Y S T E M S ² 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 DBA’s Task 1 - System Planning & Design ² Estimation & Design (logical, physical) S Y S T E M S ª 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 technicalities of physical design (types of files, access structures, DBMS product, hardware) 6 of 21

D B DBA: System Development & Implementation ² Defining technology standards: S Y S T E M S ª 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 ² Determines need for change S Y S T E M S ª 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’s Task 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 columns (SQL 92+) ª Query GRANT INSERT ON Bicycle TO Order. Clerks REVOKE DELETE ON Customer FROM Assemblers ² Users include ª Name/Group ª PUBLIC 10 of 21

D B S Y S T E M S DBA: User Identification ² User identification ² Accounts ª Individual ² Alternative identification ª Finger & hand print readers ª Voice… ª Groups ² Passwords ² Disposable passwords 11 of 21

D B DBA’s Task 2. 2 - System Performance: Performance Monitors S Y S T E M S 12 of 22

D B SQL Server Query Analyzer S Y S T E M S 13 of 22

D B DBA’s Task 2. 3 - Database Backup Full ² Backups are crucial! S Y S T E M S ² 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 (e. g. , day); just new data are backed up; reduced risk but higher cost Operational Database (Op. DB) Backup Manager (part of DBMS) Copies Entire Op. DB Backup overwrites Database (Bkp DB) Partial t 1 t 2 new data Partial backup Operational grows Database new data (Op. DB) Partial backup 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 (the D-aspect of ACID principle)= getting databases to correct state (example of transferring $ from savings to checking account) ² Key facilities: ª Recovery Manager (part of DBMS) ª Transactions log (TL) file ª ROLLBACK procedure (delete incomplete transaction or part after Savepoint. Transaction Log (TL) (managed by Backup Manager) Transactions … copy to Savepoint Operational database (Op. DB) Transaction 2. roll back to Savepoint steps remain & roll forward System crash! 1. monitors Recovery Manager Transaction end; Copy to Op. DB 15 of 22

D B S Y S T E M S Transaction Log (TL) Transaction ID Pointer to previous Transaction TL record Log ID Pointer to next TL record Table Database task Key Attribute Old value New value Savepoint C*R*A*S*H Transaction 106 incomplete. Steps before Savepoint are saved. Those after it must be canceled in TL and run again until completion of Transaction 106. 16 of 22

D B S Y S T E M S DBA’s Task 2. 4 - Database Security ² Security Threats ² Physical security ª Protecting hardware ª Protecting software and data. ² Logical security (slide 21) ª Unauthorized actions ª ª ª Employees (!) Programmers Visitors Consultants Business partnerships © Strategic sharing © EDI (Electronic Data Interchange & other interorg. 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. ) ² Data and software ª Backups, Off-site backups (!) ² Disaster planning ª Plans, training & testing ª Telecommunication systems for backup ª Personal computers challenge (use file servers for backup) 19 of 21

D B Managerial Controls ² Insiders S Y S T E M S ª 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