Oracle GoldenGate: Security Best Practices

Introduction

Securing platforms, applications, and data from unauthorized access is of great importance to IT organizations. In this article we shall detail the product features and best practices for securing your Oracle GoldenGate environment.

The concepts presented in this article are for educational purposes only. Before applying any changes presented in this article to your environment, you should thoroughly test to assess functionality and performance implications.

Main Article

Security best practices for Oracle GoldenGate center around (1) data storage, (2) data transmission, and (3) data access. By default all security features are disabled, and it is up to each customer to determine what features to enable to meet their requirements. Best practice is to thoroughly secure the Oracle GoldenGate instance; especially when PII data is being replicated.

Credential Store

Oracle GoldenGate credential store manages the user ids and passwords used by the product to interact with databases. This eliminates the need to specify usernames and passwords in Oracle GoldenGate parameter files, or when accessing the database via GGSCI commands. The credential store is implemented as an auto-login wallet which provides automated restart of Oracle GoldenGate processes without requiring human intervention.

A credential store may be shared between multiple instances of Oracle GoldenGate. Best practice is to create a credential store location on a file system that is shared between all Oracle GoldenGate instances within the data center.

To enable Oracle GoldenGate credential store:

1) Create the shared credential store location.

In my sandbox environment, I create a mount point /u01/app/oggCredentialStore for shared folder contained within a NAS device.

[oracle@oggmidtier app]$ ls -l
total 12
drwxrwxr-x. 28 oracle oracle 4096 Oct  9 10:48 ogg122
drwxrw—-.  3 oracle oracle    0 Oct 19 11:02 oggCredentialStore
drwxr-xr-x. 10 oracle oracle 4096 Mar 24  2017 oracle
drwxrwx—.  6 oracle oracle   97 Oct  9 09:44 oraInventory

2) Set the credential store location in the Oracle GoldenGate GLOBALS file.

[oracle@oggmidtier ogg122]$ cat GLOBALS
CREDENTIALSTORELOCATION /u01/app/oggCredentialStore

3) Start GGSCI and create the credential store.

GGSCI (oggmidtier) 1> add credentialstore

Credential store created in /u01/app/oggCredentialStore/

4) Use the alter credentialstore command to manage user ids and passwords. Best practice is to define an alias for each set of user credentials.

In my test environment the Oracle GoldenGate database user and password is the same across all databases, so I am setting an alias that is shared across all of my Oracle GoldenGate instances.

GGSCI (oggmidtier) 3> alter credentialstore add user c##ggadmin password Oracle1 alias oggcapture

Credential store in /u01/app/oggCredentialStore/ altered.

GGSCI (oggmidtier) 4> alter credentialstore add user ggadmin password Oracle1 alias oggapply

Credential store in /u01/app/oggCredentialStore/ altered.

5) Test the credential store.

GGSCI (oggmidtier) 6> dblogin useridalias oggcapture
Successfully logged into database CDB$ROOT.

6) Modify Extract and Replicat parameter files to use the user id alias for database access.

Master Key and Wallet

The Oracle GoldenGate wallet is used to store the master key Oracle GoldenGate processes use for data file and transmission encryption. The wallet is created as an auto-login wallet which provides automated restart of Oracle GoldenGate processes without requiring human intervention.

The wallet is in a platform-independent format. It must either be stored on a shared file system that is accessible by all systems in the Oracle GoldenGate environment, or it must be copied to all of those systems initially and every time the master key changes. Best practice is to create a wallet location on a file system that is shared between all Oracle GoldenGate instances within the data center.

To enable Oracle GoldenGate master key and wallet:

1) Create the shared wallet location.

In my sandbox environment, I create a mount point /u01/app/oggWallet for shared folder contained within a NAS device.

[oracle@oggmidtier app]$ ls -l
total 12
drwxrwxr-x. 28 oracle oracle 4096 Oct 19 12:22 ogg122
drwxrw—-.  3 oracle oracle    0 Oct 19 12:31 oggCredentialStore
drwxrw—-.  2 oracle oracle    6 Oct 19 12:49 oggWallet
drwxr-xr-x. 10 oracle oracle 4096 Mar 24  2017 oracle
drwxrwx—.  6 oracle oracle   97 Oct  9 09:44 oraInventory

2) Set the wallet location in the Oracle GoldenGate GLOBALS file.

[oracle@oggmidtier ogg122]$ cat GLOBALS
CREDENTIALSTORELOCATION /u01/app/oggCredentialStore
WALLETLOCATION /u01/app/oggWallet

3) Start GGSCI and create the wallet.

GGSCI (oggmidtier) 1> create wallet

Created wallet at location '/u01/app/oggWallet'.

Opened wallet at location '/u01/app/oggWallet'.

4) Execute the command add masterkey to add the OGG Default Masterkey to the wallet.

GGSCI (oggmidtier) 2> add masterkey

Master key 'OGG_DEFAULT_MASTERKEY' added to wallet at location '/u01/app/oggWallet'.

The master key is a random sequence of 256 bit data that is used to encrypt the encryption keys Oracle GoldenGate uses for secure data transmission and file encryption. This eliminates the need to use wallet storage for the data encryption keys used by Extract, Extract Data Pump, and Replicat.

GoldenGate Trail Encryption

Use the parameter encrypttrail in the Change Data Capture Extract, specifying either AES128, AES192, or AES256 encryption to encrypt the data blocks of the GoldenGate Trails. When used in conjunction with Master Key and Wallet encryption, downstream Extract Data Pumps will automatically encrypt the data written to the Remote Trails, and Replicats will automatically decrypt the trail as data is processed.

An Extract parameter file with Trail encryption active would look similar to this:

extract etpc
useridalias oggcapture
encrypttrail aes256
exttrail ./dirdat/et
logallsupcols
updaterecordformat compact
reportcount every 60 seconds, rate
table pdborcl.tpc.*;

Data Transmission Encryption

Use the rmthost parameter option encrypt, specifying either AES128, AES192, or AES256 in the Extract Data Pump to encrypt the transmission data stream. When used in conjunction with master key and wallet a session key is generated that is based upon the master encryption key and the specified algorithm.

An Extract Data Pump parameter file with transmission encryption enabled would look similar to this:

extract rtpc
rmthost 192.168.120.42, mgrport 7809, encrypt aes256
rmttrail ./dirdat/rc
table pdborcl.tpc.*;

GGSCI Command Security

GGSCI command security may be used to grant or deny users to specific commands within the utility. The CMDSEC file contains space or tab delimited data rows that specifies what commands users and groups may execute. The format of each data rows is:

command_name command_object OS_group OS_user {YES | NO}

Below is an example of a functional CMDSEC file:

#command_name	command_object	OS_group	OS_user	authorized
# 
# Oracle user can do antyhing
* 		* 		oracle 		oracle	YES
# Oracle DBA group can execute all commands except start
start 		* 		oradba 		* 	NO
* 		* 		oradba 		* 	YES
# SYSOPS group can status and view report files
status 		* 		sysops 		* 	YES
view  		report 		sysops 		* 	YES
# Jane is the SYSOPS group can stop groups
stop 		* 		sysops 		jane 	YES
# No one else can execute commands
* 		* 		* 		* 	NO

When setting up CMDSEC, important things to consider:

1) The oracle user requires full access to all files and directories.

2) Only the oracle user must be allowed to start processes. Processes are owned by the user running GGSCI, and no other users; including oracle, can stop them. For example, if the user jane in the sysops group were allowed to start Extracts or Replicats, she would be the only person (other than root) allowed to stop them.

3) The dba group and other users monitoring the instance require write access to the file ggserr.log.

4) The dba group and other users monitoring the instance require execute access to the ggsci object.

5) The dba group and other users monitoring the instance require full access to the dirchk directory in order to execute the status command.

6) The dba group and other users monitoring the instance require read and execute access to the dirrpt directory in order to execute the view report command.

Manager Access Rules

The Oracle GoldenGate Manager parameter ACCESSRULE is used to control connection access to the Manager process and the processes under its control. The following is a sample mgr.prm file with access rules set:

PORT 7809
purgeoldextracts ./dirdat/*, usecheckpoints, minkeepdays 4

— Only allow connections from Extract Data Pumps on the
— OGG instance at 192.168.120.42

accessrule, prog server, ipaddr 192.168.120.42, pri 1, allow

— Commands to replicats may only be issued on the local server

accessrule, prog replicat, ipaddr localhost, pri 1, allow

— Commands that require Manager to do something (start, stop, etc)
— may only be issued on the local server

accessrule, prog mgr, ipaddr localhost, pri 1, allow

Disk File Security

The security of Oracle GoldenGate objects and data files is controlled by the security controls of the operating system.

Best practice is to have all files that comprise the Oracle GoldenGate application owned by the operating system user oracle; who will have full read, write, execute, and delete privileges. Group read and execute privileges to the files should be granted to the DBA group. Everyone else should be granted no access privileges; with the following exceptions:

1) Privileges required for CMDSEC implementation.

2) Limit access to the logdump object to the oracle user only.

3) Limit access to the server, extract, replicat, and mgr objects to the oracle user only.

4) Limit access to the dirdat directory to the oracle user only.

Oracle Key Vault

Oracle GoldenGate is fully integrated with Oracle Key Vault for storage and management of the Credential Store and Master Key and Wallet files. Oracle Key Vault provides a centralized platform to securely store and manage encryption keys, credential files, Oracle wallets, and Java keystores.

The implementation of Oracle Key Vault is beyond the scope of this article.

Summary

In this article we presented Oracle GoldenGate security features that should be enabled in every production instance.

For more information on what other articles are available for Oracle GoldenGate please view our index page.

Add Your Comment