NCR TERADATA ENTERPRISE DATA WAREHOUSE ENCRYPTION PLAN & CHALLENGES Terry Rankin Jay Irwin
Terry Rankin – IT Director NCR About me: • IT Director of Database Operations and Architecture at NCR Corporation, a leading technology and omni-channel solutions company. He is responsible for all on-premises databases, including Oracle (EBS/Non-EBS), Microsoft SQL Server, Teradata and Progress DBAs/Ops/Architecture. Database Ops Terry Rankin • Working at NCR and with Teradata products for close to 20 years with experience in Teradata environments encompassing database administration, load utilities, upgrades, BAR/DR and security. He is currently implementing Micro Focus Voltage on Teradata environments to address GDPR data privacy requirements. 2 2
Jay Irwin – Director, Teradata Center for Enterprise Security Jay Irwin is Director of Teradata Center for Enterprise Security. He has a BA and JD from Drake. Before Teradata, Jay worked 25+ years in law enforcement, investigations, litigation practice, and security consulting for large companies. He writes and lectures on cyber security, information assurance and international privacy regulation. He developed a security risk assessment a program for 47 state agencies, created an information Jay Irwin, JD assurance compliance program for a key defense contractor, and managed the audit logging and monitoring compliance effort for a top 5 financial institution. Jay speaks at TAU, TIFs, PACs, The Data Warehouse Institute, and other data security forums. 3 3
NCR ENABLES NEARLY 700 MILLION TRANSACTIONS EVERY SINGLE DAY NCR SERVICES NCR SERVICES ARE HERE 8 OF THE CENTERS RESPOND TO NCR POWERS TO SUPPORT OUR CUSTOMERS TOP 10 DOLLARS IN CASH MOBILE WORLDWIDE, GOES THROUGH NCR BANKING APPS EVERY SECOND CUSTOMER INCIDENTS ATMs EACH YEAR SPEAKING ARE POWERED EACH YEAR BY NCR’S DIGITAL INSIGHT CONSUMERS USING FASTLANE AND SPANNING FROM: SELFSERV CHECKOUTS SCAN 160,000 CLOUD …TO THE FAR THE WEST COAST OF EAST OF EVERY YEAR – OVER 6 ITEMS FOR APPLICATIONS THE US… RUSSIA EVERY MAN, WOMAN AND CHILD ON EARTH. RUNNING IN RESTAURANTS WORLDWIDE NCR CONNECTED PAYMENTS CURRENTLY ATM’S + APTRA SOFTWARE SERVE OVER NCR FACILITATES PROTECTS MORE THAN AND MORE THAN MORE THAN TWITTER ANNUALLY (SPECIALIZES IN CLOUD, MOBILE eWALLET AND IPHONE COMBINED EVERY SECOND PAYMENTS)
NCR IS THE GLOBAL LEADER IN OMNI-CHANNEL SOLUTIONS SOFTWARE FINANCIAL RETAIL HOSPITALITY TRAVEL 5 OF THE TOP TOP #1 #1 #1 10 15 PROVIDER OF MULTI- IN RETAIL SELF IN POS HARDWARE AIRLINES USE SAAS / PAAS VENDOR ATM SOFTWARE CHECKOUT AND SOFTWARE IN NCR FOR SOFTWARE COMPANY (RBR) FOR THE 15th NORTH AMERICA FOR PASSENGER ATM FAMILY (SelfServ) CONSECUTIVE YEAR FOOD SERVICE ENABLEMENT COMPANIES 15M DIGITAL POS SOFTWARE BANKING USERS 5 NCR – Confidential - Use and Disclose Solely Pursuant to Company Instructions
Organizations must comply with GDPR Requirements Data Protection Officers Security of Processing Encryption/tokenization, preserve confidentiality, CIA Professionally qualified officers must be appointed Triad, user logging and monitoring, DR/BCP, for orgs larger than 250 employees continuous control monitoring Privacy by Design Consent Management Brings a requirement that for the build and Requests for consent must be simple to development of any new systems, orgs are understand, clearly requested, and as required to setup appropriate technical and easy to give as withdraw procedural measures to support GDPR Data Portability Right to Access & Challenge Data subjects have the right to obtain Allow data subject to obtain and reuse DPIA confirmation of data use and a copy of personnel their personal data for their own purposes data held at no charge. They can also challenge by transferring it across different IT the use of profiling & automated algorithms environments Breach Notification Right to be Forgotten In the event of a breach that is likely to result in a If there is not a legitimate reason to retain risk to data subject’s rights or freedoms it must personal data, data subjects have the be reported within 72 hours to data controllers right to request their data be erased and if high risk to the individuals exposed
Basic NCR Teradata EDW Environment ERP Customer Streaming D1/ES ACTIVE DATA WAREHOUSE ETL 15.10 Web AP Intelligence Bulk Load COMMON SCRIPTS PSOFT Crystal Revenue ENTERPRISE DATA Reports NSC WAREHOUSE 15.10 Explorer Misc AR DEV/TEST/DR DATA WAREHOUSE MICRO FOCUS Dashboards 15.10 VOLTAGE Machine Logs Orders Live Office Text CS Mobile Web and Social SAP BO & UNIVERSES & SOURCES NCR Confidential TABLEAU WORKBOOKS 7
NCR Teradata EDW ETL Complexity, Volume & History 120,000 140 131 120 100,000 94 100 80,000 80 66 60,000 60 40,000 40 26 20,000 20 13 0 0 1998 2003 2008 2013 2018 Datasets Scripts Year Datasets Scripts 1998 13 10,400 Linux Shell Scripting – 1toM: Bteq (SQL), Fastload, Multiloads, 2003 26 20,800 Tpump, TPT, Exports (Bteq exports, Fast exports) 2008 66 52,800 2013 94 75,200 2018 131 104,000 8
Pros and Cons of Column Level vs. Transparent Data Encryption Column-Level Encryption Pros Cons Highest performance per record Application View DDL changes needed to decrypt ■ Smallest data footprint Some user, coder, DBA training required ■ Format preservation Requires add-on solution ■ Keys generated on demand ETL scripts containing PII data need to be modified to encrypt ■ Sensitive data stays encrypted more of the time Perform analytics on most data without decryption Transparent Data Encryption Pros Cons No application changes needed to decrypt Slower performance per record ■ Built-in to some databases Largest data footprint (most exposed clear-text records) ■ Lack of format preservation increases storage ■ Sensitive data decryption grows with usage (increases risk) ■ Performing analytics requires decryption ■ 9
Plan of attack! PII discovery (3 rd party tool and/or documentation (Metadata) ER diagrams, “Old School” documentations, Informatica MDX Prioritize your datasets or applications (P1-P5), by risk/exposure. Start small, set standards, document and have everything in place prior to mass adoption. Kerberos/AD implementation For easy role access management Voltage environment setup Architecture, Performance, DR/HA 10
Plan of attack! ETL implementation (Protect in scripts & Informatica) Staff augmentation (Pre-Informatica) BI (unprotect in views) This way it doesn’t matter which BI/reporting tool is used. Only hits against views (Not tables!) BI/Reporting tools 3 layers of protection when it comes to what is viewable: 1) Universe/Workbook 2) Security views 3) Column encryption 11
System Contextual Model BO/Tableau users Application using API HPE secure data Appliance (Informatica) 3 6 Key Server Key/API request and response EDW DB Encryption | EDER | 1.0 Read-only auditor 1 Management 5 console NCR Admin Application using UDF (BO, 4 3 Tableau etc.) Policy, configurations Web Services 2 Key request and response (SOAP/REST) NCR Active Directory TD database 1) Users login to BO/Tableau 2) Query passed to TD DB with user ID using query banding. 3) TD using UDF make a call to voltage appliance to get key to encrypt/decrypt data. In case of some application like informatica can call appliance API to get the encryption/decryption key. In case of API encryption/decryption happens on application tier while for UDF it’s DB tie r. 4) Before key is returned to UDF call, user is being authenticated and authorized using AD. 5) NCR admin are responsible to configure policy and standards. 6) Read only auditor is responsible for security audits.
Voltage Architecture – Load Balance, Redundancy and DR 13
Performance Numbers - Is it fast enough? UDF 3.00 (Unprotected) Performance Highlights: Total Elapsed time to protect 4 columns of a 77,247,720 raw table: 36 seconds = ~6 times speed increase Protections Per Elapsed Second: 8,583,080 = ~5.7 times speed increase Protections Per AMP CPU Second: 39,271,895 = ~3 times speed increase UDF 2.20 Performance Highlights: Total Elapsed time to protect 4 columns of a 77,247,720 raw table: ~215 seconds Protections Per Elapsed Second: ~ 1,500,000 Protections Per AMP CPU Second: ~ 11,400,000 Following is the query with “VSPROTECTUN” that just took 36 seconds. INSERT INTO TEDW.POC_ENCRYPT_ALL_PEOPLE_WK_HT_10x Select instance_id , person_id , yr_week_nbr , as_of_date_time , vsProtectUn(first_name,'AUTO','poc@daytonoh.ncr.com', NULL ) , vsProtectUn(full_name,'AUTO','poc@daytonoh.ncr.com', NULL ) , vsProtectUn(last_name,'AUTO','poc@daytonoh.ncr.com', NULL ) , vsProtectUn(quick_look_id,'AUTO','poc@daytonoh.ncr.com', NULL ) , update_date_time , week_period_end_date from TEDW.POC_ALL_PEOPLE_WK_HT ------ took 36secs for 77,443,635 rows 14
Lessons Learned Latest version for better performance Kerberos/AD, not a prerequisite, but makes life easier Don’t try to “Boil the Ocean” Dataguise vs Documentation 15
Q&A Terry Rankin Jay Irwin, JD
Recommend
More recommend
