ENTERPRISE DATA WAREHOUSE ENCRYPTION PLAN & CHALLENGES Terry - - PowerPoint PPT Presentation

NCR TERADATA ENTERPRISE DATA WAREHOUSE ENCRYPTION PLAN & CHALLENGES

Terry Rankin Jay Irwin

2

Database Ops Terry Rankin

2

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.

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

3

Jay Irwin, JD

3

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

NCR ENABLES NEARLY 700 MILLION TRANSACTIONS EVERY SINGLE DAY

NCR POWERS WORLDWIDE, EVERY SECOND DOLLARS IN CASH GOES THROUGH NCR ATMs EACH YEAR NCR SERVICES ARE HERE TO SUPPORT OUR CUSTOMERS SPEAKING AND SPANNING FROM: THE WEST COAST OF THE US… …TO THE FAR EAST OF RUSSIA CONSUMERS USING FASTLANE SELFSERV CHECKOUTS SCAN EVERY YEAR – OVER 6 ITEMS FOR EVERY MAN, WOMAN AND CHILD ON EARTH. NCR CONNECTED PAYMENTS CURRENTLY PROTECTS MORE THAN AND MORE THAN ANNUALLY (SPECIALIZES IN CLOUD, MOBILE eWALLET PAYMENTS) NCR SERVICES

CENTERS RESPOND TO CUSTOMER INCIDENTS EACH YEAR

ATM’S + APTRA SOFTWARE SERVE OVER MORE THAN TWITTER AND IPHONE COMBINED NCR FACILITATES EVERY SECOND

CLOUD

APPLICATIONS

RUNNING IN RESTAURANTS WORLDWIDE

160,000

TOP 10

MOBILE BANKING APPS ARE POWERED BY NCR’S DIGITAL INSIGHT

8 OF THE

NCR IS THE GLOBAL LEADER IN OMNI-CHANNEL SOLUTIONS

NCR – Confidential - Use and Disclose Solely Pursuant to Company Instructions 5

FINANCIAL

PROVIDER OF MULTI- VENDOR ATM SOFTWARE ATM FAMILY (SelfServ) 15M DIGITAL BANKING USERS

5 OF THE TOP

AIRLINES USE NCR FOR PASSENGER ENABLEMENT SAAS / PAAS SOFTWARE COMPANY

TRAVEL SOFTWARE

10 15

TOP

#1

IN RETAIL SELF CHECKOUT (RBR) FOR THE 15th CONSECUTIVE YEAR POS SOFTWARE

RETAIL

#1

IN POS HARDWARE AND SOFTWARE IN NORTH AMERICA FOR FOOD SERVICE COMPANIES

HOSPITALITY

#1

Consent Management

Requests for consent must be simple to understand, clearly requested, and as easy to give as withdraw

Data Portability

Allow data subject to obtain and reuse their personal data for their own purposes by transferring it across different IT environments

Breach Notification

In the event of a breach that is likely to result in a risk to data subject’s rights or freedoms it must be reported within 72 hours to data controllers and if high risk to the individuals exposed

Privacy by Design

Brings a requirement that for the build and development of any new systems, orgs are required to setup appropriate technical and procedural measures to support GDPR

Right to Access & Challenge

Data subjects have the right to obtain confirmation of data use and a copy of personnel data held at no charge. They can also challenge the use of profiling & automated algorithms

Right to be Forgotten

If there is not a legitimate reason to retain personal data, data subjects have the right to request their data be erased

Data Protection Officers

Professionally qualified officers must be appointed for orgs larger than 250 employees

Organizations must comply with GDPR Requirements

Security of Processing

Encryption/tokenization, preserve confidentiality, CIA Triad, user logging and monitoring, DR/BCP, continuous control monitoring

DPIA

NCR Confidential 7

ACTIVE DATA WAREHOUSE 15.10 ENTERPRISE DATA WAREHOUSE 15.10 DEV/TEST/DR DATA WAREHOUSE 15.10

ERP D1/ES PSOFT NSC Misc Machine Logs Text Web and Social

SOURCES

MICRO FOCUS VOLTAGE ETL COMMON SCRIPTS Streaming Bulk Load

Basic NCR Teradata EDW Environment

Web Intelligence Crystal Reports Explorer Dashboards Live Office Mobile

SAP BO & TABLEAU

Orders AR Revenue AP CS Customer

UNIVERSES & WORKBOOKS

8

13 26 66 94 131 20 40 60 80 100 120 140 20,000 40,000 60,000 80,000 100,000 120,000 1998 2003 2008 2013 2018 Datasets Scripts

Year Datasets Scripts 1998 13 10,400 2003 26 20,800 2008 66 52,800 2013 94 75,200 2018 131 104,000

• Linux Shell Scripting – 1toM: Bteq (SQL), Fastload, Multiloads,

Tpump, TPT, Exports (Bteq exports, Fast exports)

NCR Teradata EDW ETL Complexity, Volume & History

9

Pros and Cons of Column Level vs. Transparent Data 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

Column-Level 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

Transparent Data Encryption

10

Plan of attack!

• PII discovery (3rd 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

11

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

EDW DB Encryption | EDER | 1.0

System Contextual Model

Application using API (Informatica) Key/API request and response

TD database

HPE secure data Appliance

Key Server Management console Web Services (SOAP/REST)

Application using UDF (BO, Tableau etc.) Key request and response

NCR Admin NCR Active Directory

Policy, configurations Read-only auditor BO/Tableau users 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 tier. 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.

1 2 3 4 5 6 3

13

Voltage Architecture – Load Balance, Redundancy and DR

14

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

15

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

Q&A

Terry Rankin Jay Irwin, JD