Share via:

Post Views: 0

Dear Readers,

In this article we will see Oracle GoldenGate 12c Security (Useridalias & Credential Store).

We care for your speed. Below is an easy way to perform Oracle GoldenGate 12c Security (Useridalias & Credential Store)

ADDCREDENTIALSTORE is a new command in Oracle GoldenGate 12c

The credential store eliminates the need to specify user names and clear text passwords in the oracle goldengate parameter file. It is implemented as an auto login wallet within the Oracle Credential Store Framework (CSF)

We can use USERIDALIAS in an EXTRACT or REPLICAT parameter files to map a user specified alias to a userid-password alias which is stored in the credential store.

Note :

Before doing practical example make sure GoldenGate setup was done on Source and Target

On Source Database

On Source database we have DEPT and EMP table to be replicated under AJAY schema.

Let’s check the tables


SQL> show user
USER is "AJAY"
SQL> select * from dept;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	55 SALES	  INDIA
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	40 OPERATIONS	  BOSTON
	50 MARKETING	  CANADA

6 rows selected.

SQL> select * from EMP;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 17-DEC-80	    800 		   20
      7499 ALLEN      SALESMAN	      7698 20-FEB-81	   1600        300	   30
      7521 WARD       SALESMAN	      7698 22-FEB-81	   1250        500	   30
      7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20
      7654 MARTIN     SALESMAN	      7698 28-SEP-81	   1250       1400	   30
      7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		   30
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10
      7788 SCOTT      ANALYST	      7566 09-DEC-82	   3000 		   20
      7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10
      7844 TURNER     SALESMAN	      7698 08-SEP-81	   1500 	 0	   30
      7876 ADAMS      CLERK	      7788 12-JAN-83	   1100 		   20
      7900 JAMES      CLERK	      7698 03-DEC-81	    950 		   30
      7902 FORD       ANALYST	      7566 03-DEC-81	   3000 		   20
      7934 MILLER     CLERK	      7782 23-JAN-82	   1300 		   10

14 rows selected.

SQL> show user

USER is "AJAY"

SQL> select * from dept;

DEPTNO DNAME LOC

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

55 SALES INDIA

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

50 MARKETING CANADA

6 rows selected.

SQL> select * from EMP;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 09-DEC-82 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 12-JAN-83 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

Connect GGSCI and login with authentication details

[oracle@gg ogg_src]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.0 OGGCORE_19.1.0.0.0_PLATFORMS_190508.1447_FBO
Linux, x64, 64bit (optimized), Oracle 12c on May  9 2019 06:21:59
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.

GGSCI (gg.orcl.com) 1> dblogin USERID ggadmin@orcl_1,PASSWORD ggadmin
Successfully logged into database

[oracle@gg ogg_src]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 19.1.0.0.0 OGGCORE_19.1.0.0.0_PLATFORMS_190508.1447_FBO

Linux, x64, 64bit (optimized), Oracle 12c on May 9 2019 06:21:59

Operating system character set identified as UTF-8.

GGSCI (gg.orcl.com) 1> dblogin USERID ggadmin@orcl_1,PASSWORD ggadmin

Successfully logged into database

Check Extract Parameter file

GGSCI (gg.orcl.com as ggadmin@orcl) 2> view param EXTINT

EXTRACT EXTINT
SETENV(ORACLE_SID='ORCL')
SETENV(ORACLE_HOME='/oraeng/app/oracle/product/12.1.0')
userid ggadmin@orcl_1,password ggadmin
TRANLOGOPTIONS INTEGRATEDPARAMS(MAX_SGA_SIZE 100)
EXTTRAIL /u01/app/oracle/product/ogg_src/dirdat/lt
--DDL include all
--TABLE ajay.DEPT COLSEXCEPT(MOBILE,PIN);
--TABLE AJAY.DEPT,where (loc='US');
TABLE AJAY.DEPT; 
TABLE AJAY.EMP;

GGSCI (gg.orcl.com as ggadmin@orcl) 2> view param EXTINT

EXTRACT EXTINT

SETENV(ORACLE_SID='ORCL')

SETENV(ORACLE_HOME='/oraeng/app/oracle/product/12.1.0')

userid ggadmin@orcl_1,password ggadmin

TRANLOGOPTIONS INTEGRATEDPARAMS(MAX_SGA_SIZE 100)

EXTTRAIL /u01/app/oracle/product/ogg_src/dirdat/lt

--DDL include all

--TABLE ajay.DEPT COLSEXCEPT(MOBILE,PIN);

--TABLE AJAY.DEPT,where (loc='US');

TABLE AJAY.DEPT;

TABLE AJAY.EMP;

To clear view check here

In above image we used userid and password details which is not secured for GG.

To Secure GGADMIN user we have credential store feature in GoldenGate 12c.

Adding Credential Store

On Source database

Connect to GGSCI


[oracle@gg ogg_src]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.0 OGGCORE_19.1.0.0.0_PLATFORMS_190508.1447_FBO
Linux, x64, 64bit (optimized), Oracle 12c on May  9 2019 06:21:59
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (gg.orcl.com) 1>

[oracle@gg ogg_src]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 19.1.0.0.0 OGGCORE_19.1.0.0.0_PLATFORMS_190508.1447_FBO

Linux, x64, 64bit (optimized), Oracle 12c on May 9 2019 06:21:59

Operating system character set identified as UTF-8.

GGSCI (gg.orcl.com) 1>

Add credentialstore


GGSCI (gg.orcl.com as ggadmin@orcl) 3> add credentialstore

Credential store created.

GGSCI (gg.orcl.com as ggadmin@orcl) 3> add credentialstore

Credential store created.

Now go to dircrd directory, you will find one credentialstore is created here

Note :

We see that the credentialstore has been created in the dircrd sub directory located in the oracle goldengate software installation home (GG_Home). If we need to create it in any other location like a shared file system, we have to specify that via CREDENTIALSTORELOCATION parameter in the GLOBALS file.

Now we want to add some users to the credential store.

Add credential inside credentialstore

GGSCI (gg.orcl.com as ggadmin@orcl) 5> alter credentialstore add user ggadmin@orcl_1,password ggadmin,alias ggadmin_src
Credential store altered.

1 2	GGSCI (gg.orcl.com as ggadmin@orcl) 5> alter credentialstore add user ggadmin@orcl_1,password ggadmin,alias ggadmin_src Credential store altered.

Verify the credentials

GGSCI (gg.orcl.com as ggadmin@orcl) 6> dblogin useridalias ggadmin_src
Successfully logged into database.

1 2	GGSCI (gg.orcl.com as ggadmin@orcl) 6> dblogin useridalias ggadmin_src Successfully logged into database.

Check and stop all the processes

GGSCI (gg.orcl.com as ggadmin@orcl) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     DPINT       00:00:00      00:00:07    
EXTRACT     RUNNING     EXTINT      00:00:10      00:00:01    


GGSCI (gg.orcl.com as ggadmin@orcl) 8> stop extint

Sending STOP request to EXTRACT EXTINT ...
Request processed.


GGSCI (gg.orcl.com as ggadmin@orcl) 9> stop dpint

Sending STOP request to EXTRACT DPINT ...
Request processed.

GGSCI (gg.orcl.com as ggadmin@orcl) 7> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING DPINT 00:00:00 00:00:07

EXTRACT RUNNING EXTINT 00:00:10 00:00:01

GGSCI (gg.orcl.com as ggadmin@orcl) 8> stop extint

Sending STOP request to EXTRACT EXTINT ...

Request processed.

GGSCI (gg.orcl.com as ggadmin@orcl) 9> stop dpint

Sending STOP request to EXTRACT DPINT ...

Request processed.

Edit extint parameter file, comment out userid line and add useridalias ggadmin_src as alias

GGSCI (gg.orcl.com as ggadmin@orcl) 10> edit param extint

1	GGSCI (gg.orcl.com as ggadmin@orcl) 10> edit param extint

Edit Dpint parameter file, comment out userid line and add useridalias ggadmin_src as

Now start Extint and Dpint process

GGSCI (gg.orcl.com as ggadmin@orcl) 24> start *

Sending START request to MANAGER ...
EXTRACT DPINT starting

Sending START request to MANAGER ...
EXTRACT EXTINT starting

GGSCI (gg.orcl.com as ggadmin@orcl) 24> start *

Sending START request to MANAGER ...

EXTRACT DPINT starting

Sending START request to MANAGER ...

EXTRACT EXTINT starting

Check processors are running fine

GGSCI (gg.orcl.com as ggadmin@orcl) 25> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     DPINT       00:00:00      00:00:00    
EXTRACT     RUNNING     EXTINT      00:00:09      00:00:03

GGSCI (gg.orcl.com as ggadmin@orcl) 25> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING DPINT 00:00:00 00:00:00

EXTRACT RUNNING EXTINT 00:00:09 00:00:03

Note : Now we can do Transactions on Source to check replication is happening to Target or not

Verify the credentials

Note: Why we can check dblogin because before making changes to the parameter file we are ensuring that whatever credentials we are providing they are correct.