Why We need to Encrypt Data?
When replicating sensitive data with Oracle GoldenGate, it's important to encrypt the trail files. At any given time, organizations are gathering, storing, and transferring millions of records about their customers across multiple enterprise environments. There are various operational settings within Oracle GoldenGate (OGG) that should be set to ensure Personally Identifiable Information (PII), or Sensitive Personal Information (SPI) is not compromised, data consists of a broad range of information that can identify individuals, such as birth dates, addresses, driver’s license numbers, credit card numbers, bank account numbers, and much more.
To encrypt trail or extract files, Oracle GoldenGate uses 256-key byte substitution. All records going into those files are encrypted both across any data links and within the files themselves.
You can encrypt the data in any local or remote trail or file.
To encrypt trail or extract files
1. In the Extract parameter file, list the following parameter before all trails or files that you want to be encrypted. You can list multiple trails or files after one instance of this parameter.
ENCRYPTTRAIL
2. To disable encryption for any files or trails listed in the Extract parameter file, precede their entries with the following parameter:
NOENCRYPTTRAIL
3. In the Replicat parameter file, include the following parameter so that Replicat decrypts the data for processing.
DECRYPTTRAIL
You also can use DECRYPTTRAIL for an Extract data pump to decrypt the data for column mapping, filtering, transformation, and so forth. You can then leave it decrypted for downstream trails or files, or you can use ENCRYPTTRAIL to encrypt the data again before it is written to those files.
Starting from Oracle GoldenGate 12.1.2, Oracle Wallet is integrated into Oracle GoldenGate to manage encryption keys. The master key and wallet method then also became the recommended approach to encrypting 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.
ENCRYPTTRAIL AES192When replicating sensitive data with Oracle GoldenGate, it's important to encrypt the trail files. At any given time, organizations are gathering, storing, and transferring millions of records about their customers across multiple enterprise environments. There are various operational settings within Oracle GoldenGate (OGG) that should be set to ensure Personally Identifiable Information (PII), or Sensitive Personal Information (SPI) is not compromised, data consists of a broad range of information that can identify individuals, such as birth dates, addresses, driver’s license numbers, credit card numbers, bank account numbers, and much more.
To encrypt trail or extract files, Oracle GoldenGate uses 256-key byte substitution. All records going into those files are encrypted both across any data links and within the files themselves.
You can encrypt the data in any local or remote trail or file.
To encrypt trail or extract files
1. In the Extract parameter file, list the following parameter before all trails or files that you want to be encrypted. You can list multiple trails or files after one instance of this parameter.
ENCRYPTTRAIL
2. To disable encryption for any files or trails listed in the Extract parameter file, precede their entries with the following parameter:
NOENCRYPTTRAIL
3. In the Replicat parameter file, include the following parameter so that Replicat decrypts the data for processing.
DECRYPTTRAIL
You also can use DECRYPTTRAIL for an Extract data pump to decrypt the data for column mapping, filtering, transformation, and so forth. You can then leave it decrypted for downstream trails or files, or you can use ENCRYPTTRAIL to encrypt the data again before it is written to those files.
Starting from Oracle GoldenGate 12.1.2, Oracle Wallet is integrated into Oracle GoldenGate to manage encryption keys. The master key and wallet method then also became the recommended approach to encrypting 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.
Encrypting
Data with Master Key and Wallet Method.
1. Create
Master Key
To use this method of data encryption, you create a master-key wallet and add a master key to the wallet. This method works as follows, depending on whether the data is encrypted in the trails or across TCP/IP:
. Each time Oracle GoldenGate creates a trail file, it generates a new encryption key automatically. This encryption key encrypts the trail contents. The master key encrypts the encryption key. This process of encrypting encryption keys is known as key wrapand is described in standard ANS X9.102 from American Standards Committee.
. To encrypt data across the network, Oracle GoldenGate generates a session key using a cryptographic function based on the master key.
The wallet is created in a platform-independent format. The wallet can be stored on a shared file system that is accessible by all systems in the Oracle GoldenGate environment. Alternatively, you can use an identical wallet on each system in the Oracle GoldenGate environment. If you use a wallet on each system, you must create the wallet on one system, typically the source system, and then copy it to all of the other systems in the Oracle GoldenGate environment. This must also be done every time you add, change, or delete a master key.
Lets Create Wallet, master key and then update into GLOBAL parameter.
GGSCI (localhost.localdomain) 11> info masterkey version 1
To use this method of data encryption, you create a master-key wallet and add a master key to the wallet. This method works as follows, depending on whether the data is encrypted in the trails or across TCP/IP:
. Each time Oracle GoldenGate creates a trail file, it generates a new encryption key automatically. This encryption key encrypts the trail contents. The master key encrypts the encryption key. This process of encrypting encryption keys is known as key wrapand is described in standard ANS X9.102 from American Standards Committee.
. To encrypt data across the network, Oracle GoldenGate generates a session key using a cryptographic function based on the master key.
The wallet is created in a platform-independent format. The wallet can be stored on a shared file system that is accessible by all systems in the Oracle GoldenGate environment. Alternatively, you can use an identical wallet on each system in the Oracle GoldenGate environment. If you use a wallet on each system, you must create the wallet on one system, typically the source system, and then copy it to all of the other systems in the Oracle GoldenGate environment. This must also be done every time you add, change, or delete a master key.
Lets Create Wallet, master key and then update into GLOBAL parameter.
GGSCI (localhost.localdomain)
3> create wallet
Created wallet at location
'dirwlt'.
Opened wallet at location
'dirwlt'.
GGSCI (localhost.localdomain)
4> open wallet
Opened wallet at location
'dirwlt'.
GGSCI (localhost.localdomain)
5> add masterkey
Master key
'OGG_DEFAULT_MASTERKEY' added to wallet at location 'dirwlt'.
GGSCI (localhost.localdomain)
6> sh ls dirwlt
cwallet.sso
GGSCI (localhost.localdomain)
7> add masterkey ggcs
Master key 'ggcs' added to wallet
at location 'dirwlt'.
GGSCI (localhost.localdomain) 11> info masterkey version 1
Masterkey Name: OGG_DEFAULT_MASTERKEY
Creation Date: Wed Nov 8
14:44:39 2017
Version: 1
Renew Date: Wed Nov 8
14:44:39 2017
Status: Current
Key Hash (SHA1): 0x5F16E754E85E0551DE3E5834F4C59748497D6920
The
example creates a master key named ggcs. We 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
GGSCI (localhost.localdomain)
11> view params ./GLOBALS
MASTERKEYNAME ggcs
2. Using
the Encryption Key in an Extract.
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.
ENCRYPTTRAI
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}
Let add One new Extract and use Encryption
GGSCI (localhost.localdomain) 11> add trandata test.*,ALLCOLS
GGSCI (localhost.localdomain) 11> add extract test_gm, INTEGRATED tranlog, begin now
GGSCI (localhost.localdomain) 11> add exttrail /ogg_trail/gm, extract test_gm, megabytes 50 GGSCI (localhost.localdomain) 11> REGISTER EXTRACT test_gm DATABASE CONTAINER (psrs)
Viewing Parameter File configuration
GGSCI (localhost.localdomain) 11> view params test_gm
EXTRACT TEST_GM
SETENV (ORACLE_HOME='/u01/app/oracle/product/12.1.0/db_1')
SETENV (ORACLE_SID='CCDB')
USERID c##ogg@psrs, PASSWORD oracle
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 512)
ENCRYPTTRAIL
EXTTRAIL /ogg_home/ggs/trail_files/GM
TABLE TEST.DEPT_INFO;
Verify Trailfile Encrypted or not? Using logdump.
[oracle@localhost ~]/ogg_home/ggs/logdump
Logdump 1>open /ogg_trail/gm0000001
Logdump 2>ghdr on
Logdump 3> detail on
Logdump 4> data on
Logdump 5> n
3.Decrypting Trailfile at Target Side.
Use the DECRYPTTRAIL parameter for a data pump if you want trail data to be decrypted before it is written to the output trail. Otherwise, the data pump automatically decrypts it, if processing is required, and then reencrypts it before writing to the output trail. (Replicat decrypts the data automatically without any parameter input.)
