Protect Retain and Comply with your DB 2

Protect, Retain and Comply with your DB 2 Data David R. Schwartz dschwar@us. ibm. com 1 © IBM Corporation 2006 IDUG 2006 Tampa

Protect, Retain and Comply with your DB 2 Data Agenda • Regulatory compliance is critical to your business • Use encryption to protect your vital information • Generate test data while protecting your assets • Retaining data to comply with policies • Analyze unauthorized usage of your data 2 © IBM Corporation 2006 IDUG 2006 Tampa

Life is Not Easy… Regulation 3 Date Purpose Implications Basel II 2007 Improve measurement of total risk and strengthen ability to determine capital needed Devise and implement new methodologies and systems to measure and monitor credit and operational risk (only a select number of top institutions required to comply, but others choosing to do so voluntarily) Sarbanes-Oxley Enacted Strengthen financial reporting, internal controls by fixing responsibility within companies’ management CEO and CFO certifications of annual and quarterly SEC reports; evaluation of the effectiveness of internal controls; rapid disclosure of material changes in financial conditions or operations. Set up automatic controls repository to identify deficiencies HIPAA Enacted secure medical records (lifetime), prove how they have been used & who has used them Prohibit use or disclosure of protected information. Track authorizations by individual or explicitly authorized by the Regulations Patriot Act Enacted To prevent usage of the financial system to support illegal activities, particularly terrorism Customer Identification programs and anti-money laundering programs Various antimoney laundering (AML) Enacted Prevent the laundering of money derived from illegal activities Build or acquire automated risk management systems, CRM systems capable of identifying account holders’ activities Gramm-Leach. Enacted Protection of Bliley. IBM IBV analysis; AMI analysis personally Source: identifiable financial information Ensure the security and confidentiality of customer records; protect against anticipated threats and unauthorized access © IBM Corporation 2006 IDUG 2006 Tampa

…Nor Getting Easier • Japanese Protecting Personal Freedom Act • Department of Defense - 5015. 2 – requires certified application or technology to manage records (retention) • SEC Rule 17 a-4 – requires brokers to preserve communications with clients (6 years) • Corporate Information Security Accountability Act of 2003 – requires audit of IT security and reporting – security infrastructures meet minimum standards • California Bill 1386 – a bill that protects data concerning California Residents in all computers across the United States • European Union – various countries are working on proposed bills to protect data concerning EU residents 4 • . . . and more to come © IBM Corporation 2006 IDUG 2006 Tampa

Top Business Priorities • In the event of unforeseen budget constraints, compliance will take priority over many other strategic IT investments. • If spending for other IT initiatives can be tied to compliance, may increase likelihood of approval. Source: Financial Insights, “ 2004 IT Spending Survey Results: Diversity Among Banks? ” May 2004. 5 © IBM Corporation 2006 IDUG 2006 Tampa

IT Challenges in Meeting Regulatory Compliance • Ambiguity around scope of IT controls • Resource constraints • Business applications require consolidation and upgrades • Inadequate tools to automate compliance efforts • Myriad of technology silver bullets offered to solve compliance issues Forrester Forrtel Beyond SOX: Strategies For Easier Compliance And Better Internal Controls Air Date: May 19, 2005 6 © IBM Corporation 2006 IDUG 2006 Tampa

Sarbanes Oxley Act of 2002 7 • What is SOX? – Drafted to help increase public confidence in the reliability of financial results and auditor capability • Who is affected? – All US public companies – Global companies that do business in the U. S. – Similar legislation is being issued for government agencies • What it does – Criminal sanctions for breach of certain clauses – Enhanced disclosure based on rigorous internal controls reporting and certification by senior management – Imposes new auditor independence standards © IBM Corporation 2006 IDUG 2006 Tampa

The IT Organization Is Key to SOX Compliance • Section 103 - Record Retention – Any records or transactions in the business systems of the Organization that impact assets or performance – Records must be retained for seven (7) years • Section 409 - Real-time Disclosure – System responsiveness – System Sensitivity to variation • Section 404 - Internal Controls – “Pervasive Control” – The internal controls that most closely relate to IT are those that assure the secure, stable and reliable performance of the hardware, software and personnel – If these general controls are not achieved, then the individual application controls cannot be relied upon – The application controls …that provide a controlled interface with the staff to provide the functionality, which culminates in the financial statements – Almost EVERY application directly or indirectly affect the financial statements 8 © IBM Corporation 2006 IDUG 2006 Tampa

Some Good News • In releasing the advice about IT controls, the SEC said compliance with Section 404 during its first year of implementation may have been costlier than needed, "due to excessive, duplicative or misfocused efforts. " • Washington, D. C. , May 16, 2005 -- "While the extent of documentation and testing requires the use of judgment, the (SEC) staff expects management to document and test relevant general IT controls in addition to appropriate application-level controls that are designed to ensure that financial information generated from a company's application systems can reasonably be relied upon, " the SEC said last month. "For purposes of the Section 404 assessment, the staff would not expect testing of general IT controls that do not pertain to financial reporting. " --- Commission Statement on Implementation of Internal Control Reporting Requirements --- US Securities and Exchange Commission 9 © IBM Corporation 2006 IDUG 2006 Tampa

Information Technology’s Significant Role 10 • But. . almost EVERY application directly or indirectly affect the • "It's hard to sign off on the validity of the data if the systems maintaining it aren't secure. " – "Security and Sarbanes-Oxley". Search. Security. com, by Edward Hurley, Sept. 25. 2003 • "It's a real challenge for CFO's to fully comply without some really good IT governance in place. " – "A More Perfect Union? " CFO. com, by Peter Krass, Aug. 27, 2003 • "If applications aren't coded securely and properly tested, how reliable are the internal controls of those systems? " – R. G. Scott & Associates, 2003 financial statements © IBM Corporation 2006 IDUG 2006 Tampa

BASEL II • • What is BASEL II? Developed by the Bank of International Settlements (BIS) in Basel Switzerland along with representatives from the G 10 nations • • Who is affected? It is directed at banks operating internationally, but its basic principles are intended to be applied to all banks • • What it does? Basel II aims to prevent widespread market fallout resulting from bank collapse due to misunderstood risk exposure Puts in place financial risk management structures that address today’s complex international banking relationships • • 11 Timeframes? Basel II simple and intermediate approaches optional from end-2006 Mandatory implementation end-2007 © IBM Corporation 2006 IDUG 2006 Tampa

Basel II is an Opportunity (Not Just A Mandate) Basel II Opportunities: Basel II Pillar 1: Pillar 2: Minimum Capital requirements Supervisory Review Process Calculation and Management of: § Credit Risk § Operational Risk § Market Risk § Ensure sound processes § Ensure adequate capital at all times § Ensure motivation improving risk management practices Pillar 3: Market Discipline Quantitative and qualitative disclosure of undertaken risks and risk methodologies • • • Improve shareholder value Economic capital savings Improve/maintain credit rating Improve risk management strategy Alignment of Risk & Finance Cost reduction through organizational process improvement • Identify potentially dangerous portfolio positions and other risk sources Data Foundation 12 © IBM Corporation 2006 IDUG 2006 Tampa

Key challenges – Data Management 13 © IBM Corporation 2006 IDUG 2006 Tampa

BASEL II Data Requirements • Data Capture – Must be fully operational by 2004, and financial institutions must have three years of data on file by 2007 – Certain Basel II approaches require up to 7 years of transactional data, leading to significant data volumes • Data Audit Trail – To satisfy regulatory inspections (and maintain minimum capital reserves) banks must be able to trace the origins of risk-bearing data and processes performed against it • Data Quality and Alignment – Under Basel II Pillars 2 and 3, which require reports to regulators and the market, data quality is critical • Data Management – A system that will store the historical view of the data, handle large volumes of data, scale as needed, and distribute data in a format that enables analysis and reporting functions; – Pillar 2 – “Ensure Sound Processes” 14 © IBM Corporation 2006 IDUG 2006 Tampa

Other Relevant Recent Events… • These incidents were all committed by "insiders": individuals who were, or previously had been, authorized to use the information systems they eventually employed to perpetrate harm – Two credit union employees worked together to alter credit reports in exchange for financial payment…the fraud loss exceeded $215, 000 – Bank’s star performer … loses $600 Million for the company – “Logic bomb" deleted 10 billion files in the computer systems of an international financial services company…loses at $3 Million Source: "Insider Threat Study: Illicit Cyber Activity in the Banking and Finance Sector”, June 2005, United States Secret Service, CERT® - Coordination Center, Software Engineering Institute • …U. S. Sen. Charles Schumer, D-N. Y. , said he was told the data backup tapes were likely stolen off of a commercial plane by baggage handlers in December… Source: The Associated Press, March 1, 2005 15 © IBM Corporation 2006 IDUG 2006 Tampa

The Bottom Line – Improving Internal Control Regulators have multiple goals. . . ü Security of the national and international services infrastructure ü Improved risk management across the enterprise ü Integrity of financial reporting processes and related business practices ü Customer information security . . . which drive investment in several areas • People: Professionals with regulatory experience will be hired to enable firms to meet and anticipate new regulatory requirements • Process: More robust processes and procedures will enable top management to monitor and enhance regulatory compliance • Technology: Significant investment will be made to do the following: – Encrypt sensitive data – Protect sensitive production data – Save data for future audits and to comply with retention rules – Auditiability - discover who did what, where and when • Real time • Historically – Engage in real-time monitoring of operations 16 © IBM Corporation 2006 IDUG 2006 Tampa

Encrypting Your Data • DB 2 provides security mechanisms such as native DB 2 authorization (grant/revoke), DB 2 Secondary authorizations, and DB 2/RACF interface • Applications which can access the data through various attach mechanisms are also protected, usually via RACF signon authentication • Access to the “raw” underlying linear VSAM datasets or Image Copy data is typically controlled by RACF dataset access control • But what about…… 17 © IBM Corporation 2006 IDUG 2006 Tampa

Encrypting Your Data (Continued) • …People who can circumvent the OS to look at the data on DASD – Personnel (storage vendor) who support your DASD subsystem hardware (e. g. IBM, EMC or Hitachi) – What about expired removable media such as CTAPE? – What about when old DASD hardware is updated and replaced? – Any time you send data offsite for archival or backup/recovery purposes, you are at risk… 18 © IBM Corporation 2006 IDUG 2006 Tampa

Encrypting Sensitive Data IBM Data Encryption for IMS and DB 2 Databases http: //www. ibm. com/software/data/db 2 imstools/db 2 tools/ibmencrypt. html 19 © IBM Corporation 2006 IDUG 2006 Tampa

Data Encryption – What is it? During encryption IMS or DB 2 application data (″paul″) is converted to database data that is unintelligible (″x@vg″) except to the person who has the encryption key label and can, therefore, decrypt the data. The encryption key label is assigned by your security administrator 20 © IBM Corporation 2006 IDUG 2006 Tampa

IBM Data Encryption Tool for IMS and DB 2 Databases - Decryption 21 © IBM Corporation 2006 IDUG 2006 Tampa

DB 2 V 8 Encryption § Column Level Encryption § Requires DB 2 Version 8 § DB 2 requires ICSF, but does not require ICSF’s clear key encryption support § Requires application changes § The application needs to manage the encryption key(s) § Does not support encryption of numeric columns § You must add 24 bytes to encrypted columns, and round to an 8 -byte boundary § Not supported by the DB 2 Load/Unload utilities § Encryption for DRDA on the wire 22 © IBM Corporation 2006 IDUG 2006 Tampa

IBM Data Encryption for IMS and DB 2 Databases – Additional Info § Row level encryption § Supports all levels of DB 2 § No application changes needed § Applications need no awareness of keys § Supports both secure key and clear key encryption § Index access is unaffected by encryption § Compatible with DB 2 Load/Unload utilities and DB 2 Tools § Performance is similar to row level data compression 23 © IBM Corporation 2006 IDUG 2006 Tampa

IBM Data Encryption Tool for DB 2 and IMS – ISPF 24 © IBM Corporation 2006 IDUG 2006 Tampa

Implementation Steps § Set up and validate the Integrated Cryptographic Service Facility (ICSF). Setup consists of installing the hardware configuration data and setting the system master key § Generate and then store (in the CKDS) a triple DES encryption key for use with this product § Build the IMS or DB 2 user exit using this product. The user exit must specify the generated encryption key label § Back up your data § Unload your data § Create/install the exit § Reload the data, during which process the data is encrypted § Validate your output 25 © IBM Corporation 2006 IDUG 2006 Tampa

IBM Encryption Facility for z/OS, 1. 1 Licensed Program Product MSU-based pricing* Runs on servers: System z 9 109 (z 9 -109), z. Series z 900 or z 990, z. Series z 800 or z 890, or equivalent Requires: z/OS 1. 4 or higher z/OS. e 1. 4 or higher Feature: Encryption Services Encryption Facility Client Optional Priced Feature* Planned availability: Oct. 28, 2005 • Supports encrypting and decrypting of data at rest (tapes, disk) • Supports either Public Key/Private keys or passwords to create highly secure exchange between partners Web download Planned availability: October 28, 2005 • Java™ technology-based code that allows client systems to decrypt and encrypt data for exchange with z/OS systems Feature: DFSMSdss Encryption Optional Priced Feature* Planned availability: December 2, 2005 • Allows encryption and compression of DUMP data sets created by DFSMSdss™ • Supports decryption and decompression during RESTORE * Variable Workload License Charges (VWLC), Entry Workload License Charges (EWLC), z. Series Entry License Charges™ (z. ELC), Parallel Sysplex MFE_160 License Charges (PSLC) 26 © IBM Corporation 2006 IDUG 2006 Tampa

Protecting Your Production Data For Use In Test § Need representative data for application testing § Need to make sure that personal data is not easily identified § Data still needs to be “valid” 27 © IBM Corporation 2006 IDUG 2006 Tampa

Protecting Sensitive Production Data IBM DB 2 Test Database Generator for z/OS and Multi-platforms http: //www-306. ibm. com/software/data/db 2 imstools/db 2 tdbg/ 28 © IBM Corporation 2006 IDUG 2006 Tampa

DB 2 Test Database Generator • A powerful tool that provides several methods of generating test data for DB 2 easily – From scratch or from existing data sources • Maintains referential integrity while extracting data sets from source databases • Create test data in new or existing tables • Copy a slice of data instead of all of the data • Create a restructured database for testing • Useful for regulatory compliance and data protection one-to-one 1. Start with data that exists somewhere in your enterprise 2. Leverage knowledge of data relationships 3. Apply transformation rules 4. Create test data 29 many-to-one one-to-many © IBM Corporation 2006 IDUG 2006 Tampa

DB 2 Grouper § A common component of some IBM DB 2 Tools § The Problem § Many Relationships between DB 2 objects, such as tables, in a business application -Some relationships can be discovered easily, while others cannot. § The Objective § Enable the location, augmentation, and management of this information as the basis for consistent data management activities § The Solution § Grouper is a component for discovering, recording, and managing groups of related objects (tables) that comprise a business application 30 © IBM Corporation 2006 IDUG 2006 Tampa

Would You Like To Include Related Tables? § Related tables are detected § RI defined § Application related § You can choose to include any number of related sources § TDBG discovers, via Grouper, related tables § You can also define related sets of tables and edit the discovered relationships 31 © IBM Corporation 2006 IDUG 2006 Tampa

TDBG Examples § Create a target column ACCT_BALANCE which is a random number that falls within a specified range § Create a target column that generates random values for positions 1 -6 of a Social Security Number § Create a target column that is exactly the PIN column with the 3 rd and 5 th positions replaced (masked) with the letter X § A person’s last name in a table has to be transformed into another name, but has to be a recognizable name. § Generate a random number § Use the generated number as a key to a “names” table § Replace the original name with the one from the lookup table for the target § The order number in a table has to be changed to another number. This should also be applied to any dependent tables § Generate a random number. This is the key, so duplicate numbers are not allowed § Make sure the dependent table has the same order number 32 © IBM Corporation 2006 IDUG 2006 Tampa

Defining the Sources 33 © IBM Corporation 2006 IDUG 2006 Tampa

Sources 34 © IBM Corporation 2006 IDUG 2006 Tampa

Target 35 © IBM Corporation 2006 IDUG 2006 Tampa

Target Rules 36 © IBM Corporation 2006 IDUG 2006 Tampa

Result 37 © IBM Corporation 2006 IDUG 2006 Tampa

Saving Data For Future Audits And To Comply With Retention Rules IBM DB 2 Data Archive Expert for z/OS http: //www-306. ibm. com/software/data/db 2 imstools/db 2 archiveexpert. html 38 © IBM Corporation 2006 IDUG 2006 Tampa

How is Data Being Archived Today? § Mostly by home-grown application code § Tailored to each application § Costly to maintain § Some folks aren’t archiving at all(!) § Purge and hope they don’t need it later § Run afoul of the auditors § Strict data retention regulations require a robust solution 39 © IBM Corporation 2006 IDUG 2006 Tampa

Inactive (dormant) Data §Inactive data exists everywhere § Transactional histories § Data Warehouses § e-mail, . . . §Inactive data is not unusable; --it just has a lower probability of access § Data that is unusable should be eliminated §Why keep inactive data? § Legislated by government § Business need § Trend analysis, § Previous customer history § As databases grow in size, the percentage of inactive data grows 10 GB 10% inactive 100 GB 50% inactive 2 TB 80% inactive 50 TB 95% inactive *Reference: Inmon 40 © IBM Corporation 2006 IDUG 2006 Tampa

Inactive Data - COSTS YOU §IT executives are concerned how to manage complex enterprise databases that keep growing larger §Expanding capacity is not always the most effective way to deal with growth §Allowing databases to grow without bounds can affect bottom line by increasing costs in the following ways: §Performance §Manageability §Hardware and storage costs 41 © IBM Corporation 2006 IDUG 2006 Tampa

DB 2 DATA ARCHIVE EXPERT ARCHIVE AGED DATA Selective archival and retrieval Archive at the row level Supports application and system defined RI Supports hardware data compression Archive to file, table or both 42 © IBM Corporation 2006 IDUG 2006 Tampa

DB 2 Data Archive Expert Components § An ISPF or ISC user interface § Specifications for archiving archive target archive unit - starting point table - connection key mapping between archive unit & archive target - archived table space - archived table or - archived data set - related table - row uniqueness - archived data version - archived row filter criteria § DB 2 Data Archive Expert automatically stores metadata § A callable Application Programming Interface (API) § DB 2 Grouper component 43 © IBM Corporation 2006 IDUG 2006 Tampa

Steps in Archiving 1. 2. • “find related tables function” 3. Identify connection keys • Connection keys are columns in the tables of the archive unit that provide relationships between the tables 4. Specify junction tables • A junction table is a table that contains connection keys to other tables, but whose data is not to be archived 5. Specify row uniqueness • In most cases DAE requires row uniqueness 6. Specify table archive targets • 44 Select a starting point table Find related tables Specify one or more table spaces in which to place archived tables, or choose to use the DAE default archive tables 7. Specify a row filter • A row filter is the criteria applied to the starting point table that selects the rows sets to be archived 8. Running an archive specification • Each time that an archive specification is run, a new set of archive data is created PROJECT PROJNO PROJNAME DEPTN O IF 2000 USER EDUCATION C 01 2/1/1983 MA 2110 W L PROGRAMMING D 11 2/1/1983 MA 2111 W L PROGRAM DESIGN D 11 12/1/1982 MA 2112 W L ROBOT DESIGN D 11 12/1/1982 MA 2113 W L PROD CONT PROGS D 11 12/1/1982 OP 1010 OPERATION E 11 2/1/1983 Project PRENDAT E Department Employee © IBM Corporation 2006 IDUG 2006 Tampa

Specification Summary 45 © IBM Corporation 2006 IDUG 2006 Tampa

Results of an Archive Run 46 © IBM Corporation 2006 IDUG 2006 Tampa

DB 2 Data Archive Expert For z/OS Benefits § By reducing operational costs § By freeing up developers from writing customized archiving software § By discovering related tables using the DB 2 Grouper component § By allowing the data to be removed/deleted from the source independently from the copy to the archive § By working with data hardware compression § By capturing all pertinent information about the archive § By providing a choice of archiving strategies § To table § To file § To both (multi-tier) 47 © IBM Corporation 2006 IDUG 2006 Tampa

Who did what, where and when? • DB 2 Log Analysis Tool • DB 2 Query Monitor • DB 2 Performance Expert for z/OS and Multi-Platforms • The future http: //www-306. ibm. com/software/data/db 2 imstools/ 48 © IBM Corporation 2006 IDUG 2006 Tampa

Who Did What, Where and When? Use the DB 2 Log Analysis Tool to. . • Monitor data changes by automatically building reports • Isolates accidental or undesired changes to databases • Audit mode allows load of detail level report data into a single audit table Use DB 2 Query Monitor to. . • Provide current and historical views of query activity • Set exceptional SQL-related events • Provide proactive event notification • Provide monitoring across the enterprise Use the OMEGAMON for DB 2 PE Audit Reports to. . • Monitor usage of sensitive data, like employee salary records • Monitor grants of critical privileges • Monitor unsuccessful access attempts • The Audit Summary Reports present aggregated DB 2 data. • The Audit Detail Reports and the Audit Trace show a detailed listing including Utility access to auditables 49 © IBM Corporation 2006 IDUG 2006 Tampa

Query Monitor: A Summary Report That Answers the Question, “What activity hit my tables? ” 50 © IBM Corporation 2006 IDUG 2006 Tampa

The Windows version of QMF offers the extra dimension of a drilldown report 51 © IBM Corporation 2006 IDUG 2006 Tampa

OMEGAMON PE: Audit Summary Report 8 52 © IBM Corporation 2006 IDUG 2006 Tampa

OMEGAMON PE: Audit Report – DML access and Authorization failure 8 53 © IBM Corporation 2006 IDUG 2006 Tampa

OMEGAMON PE: Audit Report - Detail 54 © IBM Corporation 2006 IDUG 2006 Tampa

DB 2 Auditing Requirements §An auditing tool focused on compliance §Useful for both DBAs and Corporate Auditors ƒ Monitoring and reporting for both Access and Change ƒ "After the fact" (log access) reporting ƒ Various filtering available – object name, user name, program, etc. ƒ Maintain history or access directly ƒ Uses DB 2 trace records 55 © IBM Corporation 2006 IDUG 2006 Tampa

How IBM DB 2 Tools Can Help § Companies today have many challenges § Face a bewildering array of government regulations and privacy acts § Data Security § Compliance is often difficult § No silver bullets § Ambiguity of compliance § Un-ambiguity of non-compliance § Compliance should be based upon a sound method for internal controls § Overall compliance architecture § IBM DB 2 Tools can help § § 56 Encrypt sensitive data Protect sensitive production data Save data for future audits and to comply with retention rules The 4 “W’s” – Discover who did what, where and when © IBM Corporation 2006 IDUG 2006 Tampa