• Home
    • 2018
    • 2017
  • Oracle GoldenGate
    • Core Platform
    • Cloud Service
    • For Oracle DB
    • For Big Data
    • For MySQL
    • For DB2 z/OS
    • For DB2 LUW
    • For DB2 i
    • For Informix
    • For SQL Server
    • For Teradata
    • For AWS Aurora
    • Foundation Suite
    • Use Cases
  • Data Management
    • General
    • Big Data
    • Oracle Database
    • MySQL
    • MS SQL Server
    • IBM DB2
    • IBM Informix
    • Teradata
    • SAP ASE
  • Data Science
  • IT
    • IT Management
    • IT Technology
  • Reading
  • About
Jinyu's Blog
About Data, Database, Integration and IT 

Oracle GoldenGate Security: Trail File Encryption

6/26/2017

0 Comments

 
When replicating sensitive data with Oracle GoldenGate, it's important to encrypt the trail files [2].  In this blog, I will discuss how to encrypt the trail data and manage the encryption keys using the two available methods supported by Oracle GoldenGate: the Master Key and Wallet  Method and the the Encryption Files (ENCKEYS)  Method. 

​1. Encrypting Data with the Master Key and Wallet Method

Let's begin with the recommended approach. Starting from Oracle GoldenGate 12.1.2, Oracle Wallet is integrated into Oracle GoldenGate to manage encryption keys [1] . The master key and wallet method then also became the recommended approach to encrypting â€‹trail files. 
What is Oracle Wallet?

Oracle Wallet is a password-protected container used to store authentication and signing credentials, including private keys, certificates, and trusted certificates needed by SSL. [3] You can use Oracle Wallet Manager to create wallets, generating certificate requests, open wallets to access the PKI-based (Public Key Infrastructure) services and upload wallets to LDAP directory etc.

Best Practices: Encrypting Oracle GoldenGate Trail Files

Oracle GoldenGate encryption with the master key and wallet is the best practice to encrypt Oracle GoldenGate trail files.

 The master key and wallet encryption process includes the following steps: 
  • Users have to create a master-key wallet and add a master key to the wallet.
  • Oracle GoldenGate automatically generates a new encryption key and use it to encrypt every new trail file. The encryption key is included in the trail header and is encrypted using the master key. 
  • Oracle GoldenGate on the target will decrypt the encryption key with the shared master key, and then use the encryption key to decrypt the trail file. 
The process is described in the diagram shown as follows: â€‹
Picture
The following is a step by step example of using the master key and wallet method. 

​1.1. Create the Master Key
First, you need to create a wallet once, add the master keys to the wallet and then copy the cwallet.sso file to all the systems (at
WALLETLOCATION  defined by GLOBALS with default to be dirwlt folder)  that run Oracle GoldenGate or put the cwallet.sso file to a shared storage accessible by all Oracle GoldenGate systems. 
Why do I got the "OGG-06114 Error retrieving masterkey" error?
Details:When I start the replicat, I got the following error on the replicat side:
2017-06-26 17:28:47  ERROR   OGG-06114  Error retrieving masterkey OGG_DEFAULT_M
ASTERKEY from wallet at location dirwlt.
What's the problem?

Answer: Please make sure you copy the cwallet.sso file to the wallet folder.

GGSCI> create wallet
Created wallet at location 'dirwlt'.
Opened wallet at location 'dirwlt'.

GGSCI> open wallet
Opened wallet at location 'dirwlt'.

GGSCI> add masterkey
Master key 'OGG_DEFAULT_MASTERKEY' added to wallet at location 'dirwlt'.

GGSCI> sh ls dirwlt 
cwallet.sso
The preceding  example doesn't specify a  master key name. GoldenGate will create the key under the default name,  OGG_DEFAULT_MASTERKEY. You can create a master key with a name shown as follows:
GGSCI> add masterkey ggcs
Master key 'ggcs' added to wallet at location 'dirwlt'.

GGSCI> info masterkey ggcs
Masterkey Name:                 ggcs
Creation Date:                  Sat Dec 17 16:23:21 2016
Version:        Creation Date:                  Status:
1               Sat Dec 17 16:23:21 2016        Current

The example creates a master key named ggcs. You tell Oracle GoldenGate to use the master key by configuring the the MASTERKEYNAME parameter. in GLOBALS file.  â€‹By default, Oracle GoldenGate will pick up the latest version. 
GLOBALS:
MASTERKEYNAME  [VERSION ]
MASTERKEYNAME ggcs
1.2. Managing the Keys
You can manage the master keys in GGSCI. The following example shows how you can check the masterkey details.
GGSCI (ip-172-30-3-169.ec2.internal) 2> open wallet
Opened wallet at location 'dirwlt'.

GGSCI (ip-172-30-3-169.ec2.internal) 3> info masterkey
Masterkey Name:                 OGG_DEFAULT_MASTERKEY
Creation Date:                  Fri Dec 16 22:20:14 2016
Version:        Creation Date:                  Status:
1               Fri Dec 16 22:20:14 2016        Current

GGSCI (ip-172-30-3-169.ec2.internal) 4> info masterkey version 1
Masterkey Name:                 OGG_DEFAULT_MASTERKEY
Creation Date:                  Fri Dec 16 22:20:14 2016
Version:                        1
Renew Date:                     Fri Dec 16 22:20:14 2016
Status:                         Current
Key Hash (SHA1):                0x44C06B01B64265A5502D4D7DE0AFBEE541EB96C7
You can also renew masterkeys to create a new encryption key with a different bit order.
GGSCI> renew masterkey ggcs
Master key 'ggcs' renewed to version 2 in wallet at location 'dirwlt'.

GGSCI> renew masterkey
Master key 'OGG_DEFAULT_MASTERKEY' renewed to version 2 in wallet at location 'dirwlt'.
GGSCI (ip-172-30-3-169.ec2.internal) 11> ifno masterkey
ERROR: Invalid argument 'ifno'.

GGSCI> info masterkey
Masterkey Name:                 OGG_DEFAULT_MASTERKEY
Creation Date:                  Fri Dec 16 22:20:14 2016
Version:        Creation Date:                  Status:
1               Fri Dec 16 22:20:14 2016        Available
2               Sat Dec 17 16:25:58 2016        Current
1.3. Using the Encryption Key
You can use the ENCRYPTTRAIL parameter in Extract (including the Pump) parameter file to encrypt the trail files. If you don't specify the MASTERKEYNAME in the GLOBALS, Oracle GoldenGate will use the default master key named OGG_DEFAULT_MASTERKEY. If you don't specify the AES cipher name, AES128 is used. 
ENCRYPTTRAIL AES192
ENCRYPTTRAIL 
When extract directly write to the remote host, the encryption using REMOTEHOSTOPTIONS syntax is shown as follows:
RMTHOSTOPTIONS host, MGRPORT port, ENCRYPT {AES128 | AES192 | AES256 | BLOWFISH}
The decryption is mostly automatic, which means you don't need to use ​DECRYPTTRAIL unless your want to create a decrypted trail file in the pump. â€‹
When shall I use DECRYPTTRAIL parameter if the wallet-based encryption is used?

Answer: When using the wallet-based encryption, you only need to use the DECRYPTTRAIL parameter in PUMP, and only when the PUMP process is a NOPASSTHROGH, and you need to send decrypted trail files to the targets.

2. Encryption with the Encryption Files (ENCKEYS) Method

Before Oracle GoldenGate 12.1.2, or for database including DB2 for Series, DB2 for z/OS, and NonStop, you can use the encryption file method. You need to create encryption key files with a list of key names and the associated key content shown as follows:
encyptkey1 0x74E8701BD5DFB21F559ECB34594ED437 
encyptkey2 0x4E62863FE5C8AA70DA9B4A3D80250C34 
encyptkey3 0x9900ED62CC0FEB77D3841D52E28C957D
When you encrypt the trail file, you need to specify the key name along with the encryption algorithm used. 
ENCRYPTTRAIL [Algorithm] KEYNAME [keyname]
DECRYPTTRAIL [Algorithm] KEYNAME [keyname]
The encryption files (ENCKEYS) process includes the following steps: 
  • Users need to create the ENCKEYS  and copy the file to all of the related Oracle GoldenGate systems.
  • Oracle GoldenGate use the defined encryption key encrypt the trail files.
  • Oracle GoldenGate on the target will decrypt the encryption key. 
The following diagram describe the process:
Picture
2.1 Generate the Encryption Key 
Oracle GoldenGate provides the keygen utility to generated encryption keys. The following example creates a AES256 key and create a new ENCKEYS file.
>./keygen 256 1 >ENCKEYS
2.2 Store the Encryption Key in a ENCKEYS Lookup File 
You can use any text editor to add the key names to the ENCKEYS file. The example defines the key name to be keyaes2561. 
> more ENCKEYS
0x75EBF271E0588D443B8B3259200AB23BBF41E92EC5BAF83E6FE3B83153AA6844
> vi ENCKEYS
> more ENCKEYS
keyaes2561 0x75EBF271E0588D443B8B3259200AB23BBF41E92EC5BAF83E6FE3B83153AA6844
2.3 Copy ENCKEYS File to Every Oracle GoldenGate System
You have to copy the ENCKEYS  file to every system where the encryption and decryption are performed. 

2.4 Using the Encryption Key
​In the extract and replicat parameter files,  you can use the ENCRYPTTRAIL and DECRYPTTRAIL parameters with the KEYNAME to configure the encryption and decryption. The following is an example extract parameter file:
extract extgdrds
useridalias awsuser
encrypttrail AES256 keyname keyaes2561
EXTTRAIL ./dirdat/rd
ddl include all
ddloptions addtrandata, report
TABLE awsuser.member;
The following is an example replicat parameter file: 
replicat repgdrds

MACRO #exception_handler
BEGIN
, TARGET ggadm.exception_tbl
, COLMAP ( rep_name = "REPGDRDS"
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG")
, optype = @GETENV ("LASTERR", "OPTYPE")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP"))
, INSERTALLRECORDS
, EXCEPTIONSONLY;
END;

reperror(1413, exception)
useridalias ggadmin_tgt
DDLERROR 1918 IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 10
DDLERROR 1435 IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 10
dboptions integratedparams(parallelism 2)
decrypttrail aes256 keyname keyaes2561
discardfile ./dirrpt/repgdrds.dsc,append megabytes 50
ddl include all
map awsuser.member, TARGET pdb1.pmdemo.member;
What should I do if I got the "OGG-02268 Decryption key found in source trail after switching trail sequence." error?

Answer: The error message shows that the previous sequence trail had no trail file decryption key, but the new trail file has the trail file decryption key. You just need to restart the pump process to reset the table mapping.

When extract directly write to the remote host, the encryption using REMOTEHOSTOPTIONS syntax is shown as follows:
RMTHOSTOPTIONS host, MGRPORT port, ENCRYPT {AES128 | AES192 | AES256 | BLOWFISH} KEYNAME [keyname]
In most of the GoldenGate extracts, the data are decrypted when reading from database transaction logs. Therefore, encrypting them before delivering to the target databases helps protect your data.
Resources
  1. Oracle GoldenGate 12.1.2 Documentation: 11.2 Encrypting Data with the Master Key and Wallet Method 
  2. Loren Penton, Masking Sensitive Data with Oracle GoldenGate, Oracle A-Team Blog
  3. Oracle Documentation, Database Advanced Security Administrator's Guide, 9 Using Oracle Wallet Manager
Created: 12/19/2016, Last Updated: 6/26/2017
0 Comments

GoldenGate 12.1 New Feature: Credential Store

11/3/2016

0 Comments

 
Oracle GoldenGate 12c introduces the Credential Store, which manages the user IDs and passwords to login to the source and target databases.  With the credential store, Oracle GoldenGate can define useridalias for database logins. This simplifies the setup and hides the login details.  For GoldenGate configurations, it's now recommended to use the credential store.

To configure a credential store, you can use the following commands: 
GGSCI> add credentialstore
Credential store created in ./dircrd/.

GGSCI> alter credentialstore delete user ggadmin
Credential store in ./dircrd/ altered.

GGSCI> alter credentialstore add user c##ggadmin@target alias ggadmin
Password:
Credential store in ./dircrd/ altered.

GGSCI> dblogin useridalias ggadmin
Successfully logged into database PDB1.
Notes: The @target SQL*Net connection is configured by default in GGCS. The c##ggadmin is the user created by default on the DBaaS instance to enable Oracle GoldenGate replication. 
0 Comments

    Oracle GoldenGate

    This blog has updates, tips and tricks for Oracle GoldenGate.

    Categories

    All
    12.1 New Feature
    12.2 New Feature
    12.3 New Feature
    Azure
    Basic Concepts
    Best Practices
    Cloud
    Customer Story
    DB2 For ISeries
    DB2 For LUW
    DB2 For Z/OS
    Disaster Recovery
    Globalization
    GoldenGate Fundamentals
    GoldenGate Security
    How To
    HP NonStop
    Informix IDS
    Installation
    JD_Edward
    Manager
    MySQL
    Network
    News
    Oracle DB
    Release
    SQL Server
    Sybase
    Teradata
    Top
    Troubleshooting
    Tutorial
    Use Case
    User Story

    Archives

    April 2018
    March 2018
    February 2018
    January 2018
    December 2017
    November 2017
    October 2017
    September 2017
    August 2017
    July 2017
    June 2017
    May 2017
    April 2017
    March 2017
    February 2017
    January 2017
    December 2016
    November 2016
    October 2016
    September 2016
    May 2016
    April 2016
    March 2016
    February 2016
    December 2015
    November 2015
    October 2015
    July 2015
    June 2015
    May 2015
    March 2015
    February 2015
    December 2014
    November 2014
    October 2014
    September 2014
    August 2014
    October 2013

    RSS Feed

Copyright © 2010-2027 Jinyu Wang.