Securing & encrypting Data using Oracle Goldengate

-

 In this blog, we will discuss about security aspect of the GoldenGate. 

GoldenGate provides the following encryption options:

1) The data stored in extract and replicat trail files
2) Passwords used in the extract and replicat parameter files
3) Data send over TCP/IP networks

GoldenGate has built-in security features which enables us to secure and protect the data that is being processed as well as being transported over the network and the passwords.

GoldenGate can encrypt data before it sends it across the TCP/IP network to the target system. On the target system, GoldenGate decrypts the data before writing it to the GoldenGate trail files. By default, data sent across the network is not encrypted.

Steps for setting up the encryption with GoldenGate:

·       Generate Encryption Keys
·       Run the keygen command from the GoldenGate software installation home
·       Copy the GGSKEY1 file to the GoldenGate home directory on the target server
·       Encrypt database passwords

Generate Encryption Keys

Run the keygen command from the GoldenGate software installation home on source Side only.

KEYGEN (key length) (n)
Where: (key length) is the encryption key length, up to 128 bits.
(n) represents the number of keys to generate.
./keygen 128 4
0x0A0E5C624211E87040B50129726C0371
0x0D44A10F0A6A05101FCE1E2003F0B405
0x0F7AE63CD1C2222FFEE63B179373661A
0xBB5A266A0AFF58158771E5599E5AB84C

Create a text file called ENCKEYS and in this file for each key that has been generated and provide a logical name

vi ENCKEYS
ggskey1 0x0A0E5C624211E87040B50129726C0371
ggskey2 0x0D44A10F0A6A05101FCE1E2003F0B405
ggskey3 0x0F7AE63CD1C2222FFEE63B179373661A
ggskey4 0xBB5A266A0AFF58158771E5599E5AB84C

Copy the ENCKEYS file to the GoldenGate home directory on the target server

scp -rp ENCKEYS targethost:/u01/app/ogg/11.2.1.0_x

Encrypt database passwords on Source

Run ENCRYPT PASSWORD command along with the logical name of the secure key (from the GGSKEY1 file we have just created) to generate an encrypted password

GGSCI > encrypt password gg_schema_password encryptkey GGSKEY1

Encrypted password: AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC

Test this encrypted password

GGSCI > dblogin userid ggs_owner, password AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC, encryptkey GGSKEY1

Successfully logged into database.

Encrypt database passwords on Target

Run ENCRYPT PASSWORD command along with the logical name of the secure key (from the GGSKEY1 file we have just created) to generate an encrypted password

GGSCI > encrypt password gg_schema_password encryptkey GGSKEY1

Encrypted password: AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC

Test this encrypted password

GGSCI > dblogin userid ggs_owner, password AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC, encryptkey GGSKEY1

Successfully logged into database.

Add this line to Extract/Replicat(For eg):

userid Golgengate_schema_name,password AADAAAAAAAAAAAHAPEUFIGGBPBCBOBKCEEAEEAJCSEPIBHFGNDGJTBAIKJDAZCOBFBYFQFVEABSBJGPD, encryptkey ggskey1

Restart you Extract/Replicat process

Encrypt data sent over TCP/IP

In the pump parameter file add the ENCRYPT BLOWFISH along with the logical name of the secure encryption keyname generated from the previous step

RMTHOST remote hostname, MGRPORT 7809, ENCRYPT BLOWFISH, KEYNAME GGSECKEY1

Encrypt Trail and Extract files

In the extract parameter file to encrypt the data in the local or remote trail files, add the keyword ENCRYPTTRAIL before all trailfiles that needs to be encrypted.

Extract exedwa
userid ggs_owner, password AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC, encryptkey GGSECKEY1

ENCRYPTTRAIL EXTTRAIL ./dirdat/fin01/la

In the Replicat parameter file, include the parameter DECRYPTTRAIL so that Replicat decrypts the data for processing.

REPLICAT rpedwa
userid ggs_owner, password AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC, encryptkey GGSECKEY1
DECRYPTTRAIL

Location: Singapore

Comments

Post a Comment

Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !

Popular posts from this blog

Restart Innodb MySQL Cluster after Complete outage(All node Down)

-
Image
Restart Innodb MySQL Cluster after Complete outage(All node Down) In DBA World, Their could be activity(like DC Migration) where all node of an MySQL Innodb cluster need to brought down. In MySQL Innodb cluster, We need to follow below steps in order to bring up cluster. 1) Start all node in the MySQL Innodb cluster. systemctl start myql 2) Try to connect cluster using mysqlsh and check status. 3) Strat cluster using complete outage command. Restart Cluster using below command: var cluster = dba.rebootClusterFromCompleteOutage(); root@mysqlhost01:~# mysqlsh --uri clusteradmin@mysqlhost01:3306 Please provide the password for 'clusteradmin@mysqlhost01:3306': ************************ Save password for 'clusteradmin@mysqlhost01:3306'? [Y]es/[N]o/Ne[v]er (default No): MySQL Shell 8.0.17 Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of...
Read more

Oracle Block Corruption - Detection and Resolution

-
Oracle Block Corruption - Detection and Resolution Any kind of corruption in database can  lead to stand still operation of the organization and can cause sever impact to organisation. In this blog , We are going to discuss about Block Corruption of the oracle database. Block Corruption :  Oracle Block Corruption occurs due inconsistent block Header ,Footer ,Checksum or Structure. Depending on the kind of insistency ,We can divide corruption into Physical and Logical corruption. Physical Corruption is due to Bad header, header or footer mismatch ,Invalid Block checksum & misplaced block. Usually this kind of the block corruption are due to external reason and difficult to resolve by oracle utilities alone.  Logical Corruption is due to corrupt or invalid Block structure when block checksum is valid. This kind of the Corruption can be resolved by the Oracle Utilities. Detection of Block Corruption Whenever a corrupt block is read Oracle reports an error in the alert l...
Read more

Install & Configure MySQL Router - MySQL InnoDB Cluster

-
Image
Install & Configure MySQL Router -  MySQL InnoDB Cluster MySQL Router is one of the important component of MySQL InnoDB Cluster as it's used by applications to connect to the MySQL DBs running on cluster.  In this blog, We will show How we can install and configure MySQL Router either on application server or on dedicated server for MySQL Router. Below are the step to install and configure MySQL Router: 1) Install MySQL Router on the Server(In our case , We are restoring MySQL Router on the ubuntu server). Run command as root user : apt-get install MySQL-router 2) Bootstrap or Configure MySQL Router mysqlrouter --bootstrap clusteradmin@mysqlhost01:3306 --user=mysqlrouter In Case of any issue in configuring MySQL Router ,We can use -force option of the bootstrap. mysqlrouter --bootstrap clusteradmin@n4ldbdev5030:3306 --directory=/home/myrouter --user=mysqlrouter --force 3) Test Connectivity via MySQL Router. mysqlsh --uri clusteradmin@localhost:6446 you can learn more ...
Read more