Data and Database Administration

1. Data and Database Administration Chapter 12

2. Objectives • Define Key Terms • Understand major functions of Data Administration and Database Administration • Understand the Problem of Data Security and Security Enhancing Techniques • Understand Database Recovery: Importance and Recovery Procedures • Understand the importance of and ways to achieve Concurrency Control • Understand Data Quality Issues • Understand the Role of Data Dictionaries and Repositories

3. Definitions • Data Administration: • High-level function responsible for the overall management of data resources in an organization • Maintain corporate-wide definitions and standards. Database Administration: • Technical function that is responsible for physical database design • Dealing with technical issues such as security enforcement, database performance, and backup and recovery.

4. Common Problems Due toPoor Data Administration • Multiple definitions and inconsistent representation making integration a problem • Missing key data elements reducing data value • Poor data quality – detrimental impact on data reliability • Lack of awareness or knowledge of all available data • Poor and inconsistent query response time and/or excessive database downtime • Inadequate or stringent controls to ensure data privacy and security

5. Data Administration Functions • Data policies, procedures, standards. • Planning. • Data conflict (ownership) resolution. • Managing the information (data) repository. • Internal marketing of Data Administration concepts.

6. Database Administration Functions • Selection of hardware and software • Installing/upgrading DBMS • Tuning database performance • Improving query processing performance • Managing data security, privacy, and integrity • Data backup and recovery Fig. 12-1 is a list of DA and DBA functions in the context of the Systems Development Life Cycle.

7. Evolving Approaches toData Administration • Blend data and database administration into one role • Fast-track development – monitoring development process (analysis, design, implementation, maintenance) • New DBA Roles: • Procedural DBAs – managing quality of triggers and stored procedures • eDBA – managing Internet-enabled database applications • PDA DBA – data synchronization and personal database management • Data warehouse administration

8. Data Warehouse Administration • New role, coming with the growth in data warehouses • Similar to DA/DBA roles • Emphasis on integration and coordination of metadata/data across many data sources • Specific roles: • Support decision–support type applications • Manage data warehouse growth • Establish service level agreements regarding data warehouses and data marts

9. Open Source DBMSs • An alternative to proprietary packages such as Oracle, Microsoft SQL Server, or Microsoft Access • mySQL is an example of open-source DBMS • Less expensive than proprietary packages • Source code available, for modification

10. Database Security • Database Security: Protection of the data against accidental or intentional loss, destruction, or misuse • Increased difficulty due to Internet access and client/server technologies

11. Threats to Data Security • Accidental losses attributable to: • Human error. • Software failure. • Hardware failure. • Theft and fraud • Improper data access: • Loss of privacy (personal data). • Loss of confidentiality (corporate data). • Loss of data integrity • Loss of availability (through, e.g. sabotage, viruses and worms, DOS attacks, etc.).

12. Threats to Information Security

13. Contingency Planning Hierarchy

14. Figure 7-3 – Contingency Timeline

15. Summaries of Mitigation Plans

16. Data ManagementSoftware Security Features • Views or Sub-schemas – present subset of data • Dynamic result table - always based on current data in based tables • Select columns and restrict rows to display • Integrity Controls- Protect data from unauthorized use/ update • Domain – create a user defined type CREATEDOMAIN FRUITTYPE AS VARCHAR2(15) CHECK (IN (‘APPLE’,’PEAR’,’GRAPE’)); • DOMAIN vs. CONSTRAINT Fruit FRUITTYPE Fruit VARCHAR2(15) CHECK(IN (‘APPLE’,’PEAR’,’GRAPE’)) • You can change the domain in one place • With constraints you would have to update separately for all declarations

17. Data ManagementSoftware Security Features • Authorization Rules - restrict access to data and restrict the actions a person may take when they access data • GRANTALLON Major TO GAdkins; • GRANTSELECT, UPDATEON Student, Major TO Williams, Smith; • GRANTSELECTON Student (ID, Name) TO Jones; • REVOKESELECTON English101_VIEW FROM Rivers, Wright;

18. Data ManagementSoftware Security Features • User-defined procedures • E.g. trigger an authorization procedure which asks additional identification questions. • Encryption • Authentication schemes • How do you positively identify who is trying to gain access to a computer? • User Proves Identity by • Supplying user password/ pin • Inserting a smart card or token • Third-party authentication – using secret keys, digital certificates • Biometric input – fingerprint/eye-scan

19. Authentication Scheme: Passwords • Passwords: First line of defense • Should be at least 8 characters long • Should combine alphabetic and numeric data • Should not be complete words or personal information • Should be changed frequently • Passwords can be flawed: • Users share them with each other • They get written down, could be copied • Automatic logon scripts remove need to explicitly type them in • Unencrypted passwords travel the Internet

20. Security Policies and Procedures • Personnel controls • Hiring practices, employee monitoring, security training • Physical access controls • Equipment locking, check-out procedures, screen placement • Maintenance controls • Maintenance agreements, access to source code, quality and availability standards • Data privacy controls • Adherence to privacy legislation, access rules

21. Backup and RecoveryBasic Recovery Facilities • Backup Facilities • Journalizing Facilities • Transaction Log - time, records, input values. • Database Change Log - before & after images. • Checkpoint Facility • Restart point after a failure. • Recovery Manager

22. Backup Facilities • Automatic dump facility that produces backup copy of the entire database • Periodic backup (e.g. nightly, weekly) • Cold backup – database is shut down during backup • Hot backup – selected portion is shut down and backed up at a given time • Incremental backup • Backups stored in secure, off-site location

23. Journalizing Facilities • Audit trail of transactions and database updates • Transaction log – record of essential data for each transaction processed against the database • Database change log – images of updated data • Before-image – copy before modification • After-image – copy after modification Produces an audit trail

24. Database Audit Trail From the backup and logs, databases can be restored in case of damage or loss

25. Checkpoint Facilities • DBMS periodically refuses to accept new transactions  system is in a quiet state • Database and transaction logs are synchronized This allows recovery manager to resume processing from short period, instead of repeating entire day

26. Recovery and Restart Procedures • Switch - Mirrored databases • Restore/Rerun - Reprocess transactions against the backup • Transaction Integrity - Commit or abort complete transaction and all related changes

27. Recovery and Restart Procedures • Rollback • Backward Recovery • Apply before images • Roll Forward • Forward Recovery • Apply after images (preferable to restore/rerun)

28. Commit and Rollback in Oracle • Updates to your table are temporary – you can reverse them during your current work session • Command: COMMIT • Saves changes immediately • called when you Exit SQL/PLUS • Command: ROLLBACK • Any changes you have made since the most recent COMMIT command will be revered.

29. Types of Database Failure • Aborted transactions • Preferred recovery: rollback • Alternative: rollforward to state just prior to abort • Incorrect data • Preferred recovery: rollback • Alternative 1: rerun transactions not including inaccurate data updates • Alternative 2: compensating transactions • System failure (database intact) • Preferred recovery: switch to duplicate database • Alternative 1: rollback • Alternative 2: restart from checkpoint • Database destruction • Preferred recovery: switch to duplicate database • Alternative 1: rollforward • Alternative 2: reprocess transactions Table 12.1

30. Concurrency Control • In a multi-user environment, simultaneous access to data can result in interference and data loss • Concurrency Control • The process of managing simultaneous operations against a database so that data integrity is maintained and the operations do not interfere with each other in a multi-user environment.

31. LOST UPDATE

32. Concurrency Control Techniques • Serializability – • Finish one transaction before starting another • Locking Mechanisms • The most common way of achieving serialization • Data that is retrieved for the purpose of updating is locked for the updater • No other user can perform update until unlocked

33. Locking Mechanisms Locking level (Granularity): Database used during database updates Table used for bulk updates Block or page very commonly used Record only requested row; fairly commonly used Field requires significant overhead; impractical

34. Locking Mechanisms • Types of locks: • Shared lock • Read but no update permitted. • Used when just reading to prevent another user from placing an exclusive lock on the record • Exclusive lock • No additional access permitted • Used when preparing to update (write)

35. Deadlock • An impasse that results when two or more transactions have locked common resources, and each waits for the other to unlock their resources A deadlock situation UserA and UserB will wait forever for each other to release their locked resources!

36. Managing Deadlock • Deadlock prevention: • Lock all records required at the beginning of a transaction • Two-phase locking protocol • Growing phase – all necessary locks are acquired • Shrinking phase –all locks are released • May be difficult to determine all needed resources in advance • Deadlock Resolution: • Allow deadlocks to occur • Mechanisms for detecting and breaking them • Resource usage matrix –indicates what users are using which resources

37. Versioning • Optimistic approach to concurrency control • Instead of locking: • Assumption is that simultaneous updates will be infrequent • Each transaction can attempt an update as it wishes • The system will reject an update when it senses a conflict • Use of rollback and commit for this

38. Use of Versioning Two users try to change the balance. Tell the other user to restart transaction Allow the user that was given earliest access to change the balance..

39. Managing Data Quality • Develop liaisons between IT and business units • Five Data Quality Issues: • Security policy and disaster recovery • Personnel controls • Physical access controls • Maintenance controls (hardware & software) • Data protection and privacy

40. Data Dictionaries and Repositories • Data dictionary • Documents data elements of a database • System catalog • System-created database that describes all database objects • Information Repository • Stores metadata describing data and data processing resources • Information Repository Dictionary System (IRDS) • Software tool managing/controlling access to information repository

41. Viewing Oracle’s System Catalog • DBA_TABLES – information about tables SELECTTABLE_NAMEFROMDBA_TABLES WHEREOWNER = ‘JSMITH’; • DBA_TAB_COLUMNS – information about columns in tables SELECTTABLE_NAME, COLUMN_NAME FROMDBA_TAB_COLUMNS WHEREOWNER = ‘JSMITH’; • DBA_VIEWS – information about views that have been created SELECTVIEW_NAMEFROMDBA_VIEWS WHEREOWNER = ‘JSMITH’; * Use all caps for login name in this class