IS 325 Notes for Wednesday September 30, 2013

1. IS 325 Notes for Wednesday September 30, 2013

2. Data as a Resource Proper delivery of information not only depends on the capabilities of the computer hardware and software but also on the organization’s ability to manage data as an important organizational resource.

3. Topics • DBMS and DB Applications • Database, Data, and System Administration • DBA Tasks • Types of DBAs • Test and Production Environments

4. DB Applications • Data is the lifeblood of computerized applications • In many ways, business today is data • Using DBMS is the efficient way for data persistence and manipulation • DBA is at the center of the development lifecycle

5. Database vs. DBMS • Database • An organized store of data • Data can be accessed by names • DBMS • Software that enable users or programmers to share and manage data

6. Enterprise IT Infrastructure– a big picture

7. Application Development Lifecycle

8. Data Administration • Concentrate on the business aspects • Business lexicon  logical data model • Requirements gathering, analysis, and design • Typical tasks include • Identifying and cataloging business data • Producing conceptual and logical data models • Creating enterprise data model • Setting data policies and standards • Concerns more about metadata

9. Metadata • Metadata is often described as data of data • Definition • Business name • Abbreviation • Type and length/accuracy • Domain, or range of valid values

10. Data Models – three levels • Conceptual model • Outlines data requirements at a very high level • Describes data mostly in business context • Logical model • Provides in-depth details of data types, lengths, relationships, and cardinality • Physical model • Defines the way data is organized in physical medium

11. DBA vs. DA DBAs have to take care the first two levels if no DA roles are implemented in an organization

12. System Administration • SAs are more concerned about the installation and setup of DBMS • Typical SA tasks include • Underlying OS systems • DBMS Installation, modification, and support • System configurations enabling DBMS to work with other software systems

13. DBA Tasks • Ensuring data and databases are useful, usable, available, and correct • Typical DBA tasks include • DB design and implementation • Performance monitoring and tuning • Availability • DB security and authorization • Backup and recovery • Data integrity • DBMS release migration

14. DB Design & Implementation • Understand and adhere to sound relational design principles • Relational theories and ER diagrams • DBMS specifics • Understanding conceptual/logical models and being able to transform to physical DB implementation • Poor design can result in poor performance

15. Performance Monitoring & Tuning • Performance = the rate at which the DBMS supplies info to its users • Influenced by five factors • Workload • Throughput • Resources • Optimization • Contention

16. Availability • Multifaceted process • Keep the DBMS up and running • Minimize the downtime required for admin tasks • Technologies and up-front planning can help

17. DB Security & Authorization • Ensure data is available only to authorized users by granting privileges to different (groups of) users • Actions need to be controlled • Creating/altering DB objects and/or their structures • Reading/modifying data from tables • Starting/stopping DB and/or associated objects • Running stored procedures or DB utilities

18. Backup & Recovery • Be prepared to recover DB in the event of • Improper shutdown of DB applications, due to • Software error • Human error • Hardware failure • Types of recovery • Recover to current • Point-in-time recovery • Transaction recovery

19. Data Integrity • Store the correct data in the correct way • Physical integrity • domains and data types • Semantic integrity • quality data with no redundancy • Internal integrity • internal structures and code

20. Desired Skill Set • SQL + PL/SQL • System specific operations and practices • Data modeling methodologies and tools • Networking (client/server) • O/S • Programming (conventional and web-oriented) • Transactional/messaging systems

21. Types of DBAs • System DBA • DB architect • DB analyst • Data modeler • Application DBA • Task-oriented DBA • Data warehouse administrator

22. Test & Production • At least two separate environments must be created for quality DB implementation • Testing (aka development) • QA (aka staging) • Production • Differences • They should share the same configuration • They don’t need to be identical • Testing DB may have only a subset of data

23. Multiple DB Environments

24. Traditional Administration Definitions • Data Administration: A high-level function that is responsible for the overall management of data resources in an organization, including maintaining corporate-wide definitions and standards • Database Administration: A technical function that is responsible for physical database design and for dealing with technical issues such as security enforcement, database performance, and backup and recovery

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

26. Traditional Database Administration Functions • Selection of DBMS and software tools • Installing/upgrading DBMS • Tuning database performance • Improving query processing performance • Managing data security, privacy, and integrity • Data backup and recovery

27. Evolving Approaches to Data Administration • Blend data and database administration into one role • Fast-track development – monitoring development process (analysis, design, implementation, maintenance) • 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

28. 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 DSS applications • Manage data warehouse growth • Establish service level agreements regarding data warehouses and data marts

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

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

31. Locations of data security threats

32. 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)

33. Internet security

34. Web Security • Static HTML files are easy to secure • Standard database access controls • Place Web files in protected directories on server • Dynamic pages are harder • Control of CGI scripts • User authentication • Session security • SSL for encryption • Restrict number of users and open ports • Remove unnecessary programs

35. Database Software Security Features • Views or subschemas • Integrity controls • Authorization rules • User-defined procedures • Encryption • Authentication schemes • Backup, journalizing, and checkpointing

36. Views and Integrity Controls • Views • Subset of the database that is presented to one or more users • User can be given access privilege to view without allowing access privilege to underlying tables • Integrity Controls • Protect data from unauthorized use • Domains–set allowable values • Assertions–enforce database conditions

37. Authorization Rules • Controls incorporated in the data management system • Restrict: • access to data • actions that people can take on data • Authorization matrix for: • Subjects • Objects • Actions • Constraints

38. Authorization matrix

39. Implementing authorization rules Authorization table for subjects (salespeople) Authorization table for objects (orders)

40. Oracle privileges

41. Authentication Schemes • Goal – obtain a positive identification of the user • 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

42. Strong authentication • Passwords are 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

43. Strong authentication • Possible solutions: • Two factor–e.g. smart card plus PIN • Three factor–e.g. smart card, biometric, PIN • Biometric devices–use of fingerprints, retinal scans, etc. for positive ID • Third-party mediated authentication–using secret keys, digital certificates

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

45. Database Recovery • Mechanism for restoring a database quickly and accurately after loss or damage • Recovery facilities: • Backup Facilities • Journalizing Facilities • Checkpoint Facility • Recovery Manager

46. Back-up 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 • Backups stored in secure, off-site location

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

48. Audit trails From the backup and logs, databases can be restored in case of damage or loss

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

50. Recovery and Restart Procedures • Disk Mirroring–switch between identical copies of databases • Restore/Rerun–reprocess transactions against the backup • Transaction Integrity–commit or abort all transaction changes • Backward Recovery (Rollback)–apply before images • Forward Recovery (Roll Forward)–apply after images (preferable to restore/rerun)