Oracle Golden Gate 系列十六 -- 配置 GG 安全說明與示例

創建時間：2024-10-22 17:31

由于GoldenGate所需的用戶權限較大，而每個GoldenGate進程配置文件中都需要設置該用戶和密碼用于數據庫登陸，出于安全性的考慮，建議將密碼進行加密。

官方文檔上介紹的加密有如下三種方法：

1. Encryption

Options are available for encrypting anddecrypting:

(1) data in an extract file ortrail

(2) database passwords

(3) data sent across TCP/IP

--這里的可以對trail文件加密或者對db 密碼進行加密。

2.Command security：

Sets user-levelpermissions for accessing Oracle GoldenGate commands through GGSCI.

3. Connection security

Allowsconnections to be established from the target system instead of the sourcesystem. For use when the target resides within a trusted network zone behind aninternal firewall.

這篇Blog 我們只看使用加密的方式，其他的方式自己查看官方文檔。

一.使用加密

This section contains instructions forencrypting and decrypting the following:

(1) The trail or extract file thatholds data being processed by Oracle GoldenGate

(2) A database password

(3) The data sent across TCP/IP

1.1 How data is encrypted

The following encryption methods are used:

(1) To encrypt trail or extractfiles, Oracle GoldenGate uses 256-key byte substitution. All records going intothose files are encrypted both across any data links and within the filesthemselves.

--對trail 和extractfile 進行加密，GG 使用256-key byte 替代，所有record記錄都以加密的形式寫入trail。

(2) To encrypt the databasepassword or data that is sent across TCP/IP, Oracle GoldenGate uses Blowfishencryption. Blowfish is a symmetric block cipher that can be used as a drop-inreplacement for DES or IDEA. Oracle GoldenGate’s implementation of Blowfish cantake a variable-length key from 32 bits to 128 bits. Blowfish encryption can becombined with Oracle GoldenGate trail encryption.

--對數據庫密碼進行加密或者TCP/IP 上發送data 時進行加密。 GG 使用Blowfish 加密算法。

1.2 Encrypting trail or extract files

You can encrypt the data in any local orremote trail or file.

NOTE:

(DB2 on z/OS)This feature cannot be used when FORMATASCII is used to write data to a file inASCII format. The trail or file must be written in the default canonical format.

1.2.1 To encrypt trail or extract files

1. In the Extract parameter file, list thefollowing 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 ortrails listed in the Extract parameter file, precede their entries with thefollowing parameter.

NOENCRYPTTRAIL

3. In the Replicat parameter file, includethe 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 downstreamtrails or files, or you can use ENCRYPTTRAIL to encrypt the data again before itis written to those files.

1.2.2 示例

我的測試環境使用的Extract+datapum+replicat，我們對這個環境下進行加密測試。

（1）Extract 參數添加加密參數

GGSCI (gg1) 46> view params ext1

extract ext1

ENCRYPTTRAIL

userid ggate@gg1, password ggate

--rmthost gg2,mgrport 7809

--rmttrail /u01/ggate/dirdat/lt

exttrail /u01/ggate/dirdat/lt

ddl include all objname dave.pdba;

table dave.pdba;

（2）Data Pump 我們不需要做處理，因為它只是將我們的trail 發送到Targetsystem。

（3）Replicat 添加解密參數

GGSCI (gg2) 16> view params rep1

replicat rep1

DECRYPTTRAIL

ASSUMETARGETDEFS

userid ggate@gg2,password ggate

discardfile/u01/ggate/dirdat/rep1_discard.txt, append, megabytes 10

--HANDLECOLLISIONS

ddl include all

ddlerror default ignore retryop

map dave.pdba, target dave.pdba;

（4）測試

在Source DB上做DML 操作，看可能同步到Target DB 上。

--Source DB

SQL> select count(*) from pdba;

COUNT(*)

----------

2678632

SQL> delete from pdba whererownum<100;

99 rows deleted.

SQL> commit;

Commit complete.

--Target DB

SQL> select count(*) from pdba;

COUNT(*)

----------

2678632

數據并沒有同步過來，確認了半天，GG進程是正常的，后來查看了一下dpump進程，找到了原因：

GGSCI (gg1) 31> info dpump

EXTRACT DPUMP Last Started 2011-11-1915:03 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:07 ago)

Log Read Checkpoint File /u01/ggate/dirdat/lt000000

First Record RBA 0

設置加密之后，dpump 的起始點又變成0了。

查看ext1的report，確定當前正在使用的trails 文件：

GGSCI (gg1) 33> view report ext1

….

2011-11-19 15:03:32 INFO OGG-01026 Rolling over remotefile /u01/ggate/dirdat/lt000012.

2011-11-19 15:03:33 INFO OGG-01053 Recovery completed fortarget file /u01/ggate/dirdat/lt000013, at RBA 1009.

2011-11-19 15:03:33 INFO OGG-01057 Recovery completed forall targets.

…..

我們重置一下DataPump的序列號：

GGSCI (gg1) 36> stop dpump

Sending STOP request to EXTRACT DPUMP ...

Recovery is not complete. This normal stop will wait and checkpointrecovery's work when recovery has finished. To force Extract to stop now, usethe SEND EXTRACT DPUMP, FORCESTOP command.

GGSCI (gg1) 37> send dpump forcestop

Sending FORCESTOP request to EXTRACT DPUMP...

STOP request will be executed immediately(recovery aborted).

GGSCI (gg1) 38> alter extractdpump,extseqno 13,extrba 0

EXTRACT altered.

GGSCI (gg1) 39> start dpump

Sending START request to MANAGER ...

EXTRACT DPUMP starting

GGSCI (gg1) 40> info dpump

EXTRACT DPUMP Last Started 2011-11-1915:22 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:07 ago)

Log Read Checkpoint File /u01/ggate/dirdat/lt000013

First Record RBA 0

在到Target DB 確認一下：

SQL> select count(*) from pdba;

COUNT(*)

----------

2678533

同步正常。所以經過測試，我們在對trail 進行加密時，要注意一下dpump 的trail 起始點。

1.3 Encrypting the password of a database user

You can encryptany of the following database passwords through Oracle GoldenGate:

(1) The database password that isused by the Extract and Replicat processes and other processes to log into thesource and target databases. (Not all database types require a database loginfor Oracle GoldenGate processes.)

(2) The database password for anOracle ASM user.

--可以使用GG 加密Extract，Replicat，和ASM 用戶的密碼。

To encrypt a database user password

1.3.1. Run GGSCI and issue the ENCRYPTPASSWORD command to generate an encrypted password.

--在GGSCI 里運行encrypt password 生成加密后的密碼

The command provides the following options.

(1) The default ENCRYPT PASSWORD command,without any options, generates an encrypted password using a default key thatis randomly generated by Oracle GoldenGate.

ENCRYPT PASSWORD <password>

--默認情況下encrypt 命令使用隨即生成的key來進行加密。

(2) ENCRYPT PASSWORD with the ENCRYPTKEY<keyname> option generates an encrypted password using a user-defined keycontained in the ENCKEYS lookup file.

ENCRYPT PASSWORD <password> ENCRYPTKEY<keyname>

--可以通過指定key來進行加密

For <keyname>,specify the logical name for the key you want to use, as it appears in thelocal ENCKEYS file. To use this option, you must first generate a key, createan ENCKEYS file on the local system, and create an entry in the file for thegenerated key.

--注意這里的keyname，必須要通過命令生成，在local system 上生成一個enckeys的文件，而不是隨便指定，關于生成key的命令，下節里單獨說明。

The encrypted password is output to the screen when you run the ENCRYPT PASSWORD command.

1.3.2. Copy the encrypted password andpaste it into the appropriate Oracle GoldenGate parameter statement as shown inTable 5.

Where:

(1) <user> is the database user name for theOracle GoldenGate process or (Oracle only) a host string. For Oracle ASM, the user must be SYS.

--如果是ASM，則必須是SYS用戶，由此可以看見加密的重要性了。

(2) <encrypted_password> isthe encrypted password that is copied from the ENCRYPT PASSWORD commandresults.

(3) ENCRYPTKEY DEFAULT is requiredif the password was encrypted using ENCRYPT PASSWORD without the ENCRYPTKEY option.

(4) ENCRYPTKEY <keyname> isrequired if the password was encrypted using ENCRYPT PASSWORD with the ENCRYPTKEY<keyname> option. Specify the logical name of the key as it appears inthe ENCKEYS lookup file.

1.3.3 示例

這里我們對Extract 進程的參數中的密碼進行默認的加密，在生成加密時不指定key。

（1）之前的參數配置，使用的是明碼：

GGSCI (gg1) 41> view params ext1

extract ext1

ENCRYPTTRAIL

userid ggate@gg1, password ggate

--rmthost gg2,mgrport 7809

--rmttrail /u01/ggate/dirdat/lt

exttrail /u01/ggate/dirdat/lt

ddl include all objname dave.pdba;

table dave.pdba;

（2）生成加密之后的密碼：

GGSCI (gg1) 42> encrypt password ggate

No key specified, using default key...

--這里defaultkey 是隨即生成的。

Encrypted password: AACAAAAAAAAAAAFAPHODADQGAJVDSHPG

（3）修改參數中的密碼：

--先測試一下連接：

GGSCI (gg1) 45> dblogin useridggate@gg1,password AACAAAAAAAAAAAFAPHODADQGAJVDSHPG,encryptkey default

Successfully logged into database.

--這里成功連接。

當然，直接使用明碼還是可以登陸的：

GGSCI (gg1) 46> dblogin useridggate@gg1,password ggate

Successfully logged into database.

修改之后的參數：

GGSCI (gg1) 53> view params ext1

extract ext1

ENCRYPTTRAIL

userid ggate@gg1,passwordAACAAAAAAAAAAAFAPHODADQGAJVDSHPG,encryptkey default

--rmthost gg2,mgrport 7809

--rmttrail /u01/ggate/dirdat/lt

exttrail /u01/ggate/dirdat/lt

ddl include all objname dave.pdba;

table dave.pdba;

使用default key 加密的示例就到這。

1.4 Encrypting data sent across TCP/IP

You can encryptcaptured data before Oracle GoldenGate sends it across the TCP/IP network tothe target system. On the target system, Oracle GoldenGate decrypts the data beforewriting it to the Oracle GoldenGate trails (unless trail encryption also isspecified).

By default, datasent across a network is not encrypted.

在前面的幾小節，將的都是對文件進行加密或者是對DB 密碼進行加密。在網絡上發送trail內容時也可以進行加密，先加密傳到Target ，然后在Target進行解密。

默認情況下，在網絡上傳輸時，是不進行加密的。

To encrypt data sent across TCP/IP

1. On the source system, generate one ormore encryption keys and create an ENCKEYS file.

--在Source 生成密鑰。

2. Copy the finished ENCKEYS file to theOracle GoldenGate installation directory on all target systems. The key namesand values in the source ENCKEYS file must match those of the target ENCKEYS file,or else the data exchange will fail and Extract and Collector will abort withthe following message:

GGS error 118 – TCP/IP Server with invalid data.

--將Source 端的密鑰copy到Target 的GG 安裝目錄下。 Source 和Target 上密鑰的名稱必須一致。否在在使用時會報TCP/IP Server withinvalid data.的錯誤。

3. Depending on whether this is a regularExtract group or a passive Extract group ，use the ENCRYPT option of either the RMTHOST or RMTHOSTOPTIONS parameterto specify the type of encryption and the logical key name as shown:

ENCRYPTBLOWFISH, KEYNAME <keyname>

然后修改RMTHOST參數，如果采用Data Pump 就修改Data pump的參數。

Where:

（1） BLOWFISH specifies Blowfishencryption.

（2） <keyname> is the logicalname for the encryption key you want to use, as it appears in the ENCKEYS file.

示例：

RMTHOST sys1, MGRPORT 7840, ENCRYPTBLOWFISH, KEYNAME superkey

RMTHOSTOPTIONS ENCRYPT BLOWFISH, KEYNAMEsuperkey

4. If using a static Collector and Blowfishencryption, append the following additional parameters in the Collector startupstring:

-KEYNAME <name>

-ENCRYPT BLOWFISH

Where:

（1） KEYNAME <name> specifies thename of the key.

（2） ENCRYPT BLOWFISH specifiesBlowfish encryption.

Collectormatches these parameters to those specified with the KEYNAME and ENCRYPT optionsof RMTHOST.

二.使用密鑰加密

You must createat least one encryption key and two ENCKEYS lookup files, one on the source andone on the target, if you want to:

（1）Encrypt data sent across TCP/IP

（2）Use a user-defined key to encryptthe database password

--如果在網絡上加密或者使用密鑰來對密碼進行加密時就必須生成密鑰。

This procedure is not required if:

(1) you are using a default keygenerated by Oracle GoldenGate to encrypt the database password

(2) you are encrypting a trail orextract file.

在以上兩種情況下，不需要生成密鑰：一是使用GG 默認的密鑰來對密碼加密，二是對trail 文件進行加密時。

You can defineyour own key or run Oracle GoldenGate’s KEYGEN utility to create a key randomly.

2.1 To define your ownkey

(1) The key name can be a string of1 to 24 alphanumeric characters without spaces or quotes.

--keyname 由1到24個字母或數字組成，不能包含空格和引號。

(2) The key value can be up to 128bits (16 bytes) as a quoted alphanumeric string (for example “Dailykey”) or ahex string with the prefix 0x (for example 0x420E61BE7002D63560929CCA17A4E1FB).

--key value 最多由16個bytes或者128bits 組成。如果是字節，會被引號括起來，如果是十六進制的，則沒有。

2.2 To use KEYGEN to generate a key

Changedirectories to the Oracle GoldenGate home directory on the source system, and issuethe following shell command. You can create multiple keys, if needed. The keyvalues are returned to your screen.

進入GG的安裝目錄，運行keygen命令生成key：

KEYGEN <key length> <n>

Where:

（1）<key length> is theencryption key length, up to 128 bits.

（2） <n> represents the number ofkeys to generate.

--n表示生成幾個keys

Example:

KEYGEN 128 4

2.3 To store the keys for use by Oracle GoldenGate

1. On the source system, open a new ASCIItext file.

2. For each key that you generated, enter alogical name followed by the key value itself. Place multiple key definitionson separate lines. Do not enclose a key name or value within quotes; otherwiseit will be interpreted as text. Use the following sample ENCKEYS file as aguide.

將生成的key name 存放的一個ASCII 文件里，每行一對keyname 和 key value，不要用引號括起來。

3. Save the file as ENCKEYS withoutan extension in the Oracle GoldenGate installation directory. The name must bein upper case.

--在GG 安裝目錄下保存創建的ASCII 文件為ENCKEYS，注意必須大寫，沒有擴展名。

4. Copy the ENCKEYS file to the targetOracle GoldenGate installation directory. The key names and values in thesource ENCKEYS file must match those of the target ENCKEYS file, or else thedata exchange will fail and Extract and Collector will abort with the followingmessage:

GGS error 118 –TCP/IP Server with invalid data.

--Copy 這個ENCKEYS文件到target system的根目錄下，名稱必須和source 一致。

2.4 示例

2.4.1 生成key

gg1:/u01/ggate> keygen 128 4

0x1C4E2A6B3F005D495987CA43FFEBDE54

0xEF90654D6C22F846A3307673AB22897D

0xC2D3A02F99449344EED9212358593326

0x9516DC11C6662E423883CD520490DD4E

注意這里只生成了keyvalue，并沒有生成key name，在我們保存的時候，就可以隨便指定我們的keyname了。

2.4.2 保存密鑰

在GG根目錄下創建ENCKEYS 文件，并保存我們的密鑰

gg1:/u01/ggate> touch ENCKEYS

gg1:/u01/ggate> vi ENCKEYS

davekey1 0x1C4E2A6B3F005D495987CA43FFEBDE54

davekey2 0xEF90654D6C22F846A3307673AB22897D

davekey3 0xC2D3A02F99449344EED9212358593326

davekey4 0x9516DC11C6662E423883CD520490DD4E

2.4.3 用密鑰對我們的DB密碼進行加密

GGSCI (gg1) 54> encrypt password ggateencryptkey davekey1

Encrypted password: AACAAAAAAAAAAAFAJDEIOHGHKCHBSAYA

2.4.4 測試加密后的密碼：

GGSCI (gg1) 55> dblogin useridggate,password AACAAAAAAAAAAAFAJDEIOHGHKCHBSAYA,encryptkey davekey1

Successfully logged into database.

連接成功，如果要修改Extract和Replicat 的話，只需要把這部分更新到參數里就ok了。

小結：

GG的加密方式還是比較靈活，可以對trail 加密，在網絡傳輸時進行加密，也可以對密碼進行加密。

當事務量較大的時候，對trail 和傳輸時加密都會增加系統的壓力。所以最好的加密方式就是對密碼進行加密。即起到了安全控制，也不影響GG的性能。

-----------------------------------

Oracle Golden Gate 系列十六 -- 配置 GG 安全說明與示例

https://blog.51cto.com/u_15462270/4867393

ꄴ前一個：無

ꄲ后一個：無

Oracle Golden Gate 系列十六 -- 配置 GG 安全 說明 與 示例

Oracle Golden Gate 系列十六 -- 配置 GG 安全說明與示例