Make QAD Bulletproof Mike Furgal Director DB and

Make QAD Bulletproof Mike Furgal Director – DB and Pro 2 Services March 20 th, 2017

Agenda § Why is Security Important § Securing Data in Motion § Securing Data at Rest • Encrypting data by the application • Transparent Data Encryption 2 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Why Security is Important § You have a laptop with a database on it § The database contains customer info with credit card numbers, maybe patient records § You forget the laptop in a taxi (or a pub) § Someone finds it, looks at what it contains, and sells the data to some bad people § The newspapers print yet another data-loss story which surprises no one § Your customers suffer losses and are upset § Your company suffers losses 3 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Security is becoming the number 1 IT Issue for 2017 4 © 2016 Progress

Where do you start § Authentication • System Level • Database Level § Password Management • Strength • Duration § Procedures and Policies • When should a password be shared § Roles and Responsibilities 5 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Next Step § Secure the network § Virtual Private Network § Encryption over the

HTTPS § SSL Encrypted Web Traffic § Required for all sensitive data over the web § Configured at Websever Level • Apache, Tomcat, IIS • http: //certbot. eff. org - A good starting place 7 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

HTTPS Shared Memory WS Agent Webserver https Database 8 © 2016 Progress Software Corporation

HTTPS Shared Memory WS Agent Webserver https Database Ap. Server https 9 © 2016

Secure Socket Layer § Encrypts data over the wire § Open. Edge configuration parameter • Simple to implement – use –ssl on the command line and connection string • Performance impact § Progress uses the Open. SSL libraries • Heartbleed vulnerability does not apply – Open. SSL 1. 01 – 1. 01 f at risk – Open. Edge uses Open. SSL 0. 9. 8 10 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

SSL Shared Memory ssl WS Agent Webserver https Database Ap. Server https 11 ©

Data In Motion Shared Memory ssl WS Agent Webserver https ssl Database Ap. Server https Db Server ssl 12 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

We have covered Authentication and Network transmission What about the Data? 13 © 2016

Date Encryption § ENCRYPT() / DECRYPT() functions § Application Level Encryption § Use this to encrypt data in stored fields • Credit Card • Bank Account Requires the Application Changes • County Identifiers (SSN, etc) 14 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Data Encryption § Transparent Data Encryption (TDE) § Encrypts all or portions of the database Requires NO Application Changes 15 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

What do we mean by "Transparent" ? § Data in database on disk are encrypted and decrypted automatically § Encryption key management is (mostly) automatic § No changes needed • 4 GL application’s code • SQL application's code • Application security infrastructure § Indexes work the same when encrypted and not § Low performance impact 16 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

What You Get § Transparent & configurable encryption for • Table data (by table) • Index data (by index) • Before Image data • After Image data • Audit data § Open. Edge Replication § Encrypted Backups § Encrypted Binary dump files § Restricted database utility access to encrypted data 17 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

TDE Concepts • Block-level encryption • Database key store (. ks file) • Passphrases • Key store user accounts • Key store service • Ciphers • Manual mode vs. Auto mode • Encryption policies 18 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Block-Level Encryption clear text Crypt Service cipher text security context: - cipher-algorithm - encryption key data blocks in shared memory clear text Crypt Service on-disk data cipher text encrypted data blocks on disk 19 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Database Key Store • One for each encrypted database • Not part of the database Database Master Key (DMK) • Each TDE-enabled database has one unique DMK • Limits risk -- If compromised only that db is accessible • Managed by a DBA Data object encryption keys • Unique key(s) for EACH db object • If key cracked, intruder only has access to that db object 20 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Keystore Passphrase A sequence of text used to control access to a program or data such as an encryption key Similar to a password in usage but … May include whitespace and punctuation Generally longer than a password for added security 21 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Database Key Store Built-in Accounts Admin Account User Account § Must be used to change any § Use for daily non admin-tasks key store value § For example use to start § Used to administer off-line database servers and to • Encryption configuration access data • Key store access • Manual/autostart mode There is no ability to allow a key store file to be opened if the key store admin account passphrase is lost Recommendation: Use the admin account exclusively for administration 22 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Key Store Service Passphrase Delivery Autostart mode Manual start mode • Less secure • Alllows the database to be • Default mode • More secure • Requires a passphrase every started without a passphrase • Can be set to either key store account time the database is started • Can impact automated database tools • Options: • Type in passphrase • Write ‘secure’ scripts to automate delivery of passphrase (very hard to do) 23 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Encryption Policies Encryption attributes of database objects are managed through encryption policies Policies are stored in the Encryption Policy Area To administer policies you must be a DBA and have access to the key store ADMIN account Built-in to TDE security protects policy records Access requires command be run locally 24 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Setting Up TDE 1 2 Add encryption policy storage area to the database Enable the database for encryption 3 Configure encryption policies 4 Encrypt existing unencrypted data (optional) 25 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Encryption Policy Storage Area § Create a data area for encryption policies • Type II area added to the database • Name is "Encryption Policy Area" Create structure definition file with policy area e “Encryption Policy Area”: 120, 64; 8. Add the encryption policy area using PROSTRCT Add PROSTRCT ADD mydb encrypt_policy_area. st Policy area will normally not have much data in it. One or two records per encrypted object 26 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Enabling TDE proutil db-name -C enableencryption [-Cipher cipher-number] [-Autostart {user | admin}] [-biencryption enable|disable] [-aiencryption enable|disable] [-Passphrase] [[-userid][-password]] § Enables the database for TDE • Must be run on a command line § Does not encrypt any data § Creates the key store file proutil tde. Sport -C enableencryption 27 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Encryptable Database Objects Open. Edge Database Type I data area Type II data area Entire area encrypted Selected objects encrypted Tables Indexes LOBs Table Index LOB Table Index Table LOB Index LOB Table Cannot be encrypted • Encryption Policy Area • Schema Area 28 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Creating an Encryption Policy Database object type Action is encrypt Database area name proenv> proutil tde. Sport -C epolicy manage area encrypt "Data. Area 100" Open. Edge Release 11. 6 as of Fri Oct 16 18: 22: 20 EDT 2015 Encryption policy setting for Area Data. Area 100 in Area 100 Cipher specification setting to AES_CBC_128 completed. Policy uses default cipher The policy for this data base object is created and placed in the encryption policy storage area of the database 29 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Encryption Setup § Added encryption policy storage area § Enabled encryption for the database § Created an encryption policy 30 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Options for Encrypting Existing Data 1 2 3 Data are encrypted, when updated, by the normal course of database updates each time a block is written to the database Dump and load data objects, encrypting data during the load operation Run EPOLICY MANAGE UPDATE command to encrypt all data in a database object 31 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Temporary Files Both ABL and Open. Edge SQL clients create temporary storage files when needed – example Temp Tables -t startup parameter (save temp files) • You cannot connect when an ABL client uses the -t parameter • Using Open. Edge SQL client the -t startup parameter is ignored 32 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Other things that ARE encrypted Data automatically encrypted • PROBKUP • After image hot-standby databases • Open. Edge Replication targets Data optionally encrypted • Binary dump and load • Audit archive and load Recommendation: Backup the database and the key store to different media 33 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Things NOT encrypted § § § § EXPORT BUFFER-COPY DISPLAY MESSAGE OUTPUT TO OUTPUT THROUGH RAW-TRANSFER 34 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Bringing it all together TDE Shared Memory ssl WS Agent Backup Webserver https TDE ssl Database Ap. Server TDE https Binary Dump Db Server 35 ssl © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Documentation Open. Edge Getting Started: Core Business Services Open. Edge Data Management: Database Administration 36 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Summary § Open. Edge has all the tools and security features availabe to protect your database • Authentication • Encryption Over the Wire • Encryption of Data at Rest § Procedures and Policies are required • Train the staff § Progress Services are here to help • Security Audit • Security Implementation 37 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.