Oracle GodlenGate 12c New Feature – CREDENTIALSTORE AND USERIDALIAS

From Oracle GoldenGate 12c onwards, a new feature for security purpose has been introduced known as CREDENTIALSTORE. Using this feature, we can manage the USER ID’s and it’s corresponding passwords which are used by the Oracle GoldenGate processes to interact with the Local Databases for the use of Replication.

 

So the usernames and passwords will be secured. Upon using this feature the usernames and passwords used in the Oracle GoldenGate process groups or even when we login as an Admin user in the GGSCI, will not be displayed. An optional ALIAS name can be used instead of entering the username and password.

One Credential Store can be used or shared by multiple Oracle GoldenGate instances. This can be achieved by placing the CREDENTIALSTORE in the shared mount point or shared file system which is accessed by multiple Oracle GoldenGate instances.

 

The Credential Store is configured as an auto login wallet within the CSF – Oracle Credential Store Framework.

LDAP is not supported by this feature.

 

Only one Credential Store can be used at a time by any given instance of Oracle GoldenGate. But there is also another advantage in this feature called DOMAIN. The Credential store can be divided in to logical parts called as DOMAINS. Using DOMAINS we can use same ALIAS names for different systems or different Oracle GoldenGate Instances.

 

So, you all know what is Credential Store means. Now we will see on how to Configure Credential Store.

 

To Create or add a credential store simply use the below command,

ADD CREDENTIALSTORE
GGSCI (OGG1.localdomain) 2> ADD CREDENTIALSTORE

Credential store created in ./dircrd/.

The Credential Store is created in the default directory dircrd under the Oracle GoldenGate installation directory.

We can see a wallet file created in this directory.

GGSCI (OGG1.localdomain) 3> sh ls -lart dircrdtotal 12
drwxr-xr-x 29 oracle oinstall 4096 Jul 23 23:05 ..
-rw-r—– 1 oracle oinstall 324 Jul 25 23:17 cwallet.sso
drwxr-x— 2 oracle oinstall 4096 Jul 25 23:17 .

 

Upon executing this command, an empty credential store will be created. By default, the credential store will be created in the directory dircrd under the Oracle GoldenGate directory. But it is not necessary to create the Credential store in the default location. You can also change the location of the credential store. CREDENTIALSTORELOCATION is the parameter which should be mentioned in the GLOBALS parameter.

 

For example if you specify the below in the ./GLOBALS,

CREDENTIALSTORELOCATION /vol3/oggcs

Your credential store will be created in the above location instead of getting created in the default location /<OGG_HOME>/dircrd/

Example of creating the Credential Store in Non-Default location.,

Edit the ./GLOBALS parameter and enter the parameter CREDENTIALSTORELOCATION

GGSCI (OGG1.localdomain) 1> vi ./GLOBALS

CREDENTIALSTORELOCATION /vol3/oggcs

GGSCI (OGG1.localdomain) 2> ADD CREDENTIALSTORE

Credential store created in /vol3/oggcs/.

Note: If it throws error when adding, please logout from the GGSCI prompt and login.

You can see a wallet created in the directory /vol3/oggcs

GGSCI (OGG1.localdomain) 3> sh ls -lart /vol3/oggcs

total 12
drwxrwxrwx. 7 oracle oinstall 4096 Jul 26 12:45 ..
-rw-r—– 1 oracle oinstall 324 Jul 26 12:48 cwallet.sso
drwxr-xr-x 2 oracle oinstall 4096 Jul 26 12:48 .

 

Let us consider the default one for our upcoming scenarios. So, We have created an empty credential store and now we need to add the users and credentials to the Credential Store.

 

To add the user to the credential store ALTER CREDENTIALSTORE is used. Check the complete syntax below.,

ALTER CREDENTIALSTORE {
ADD USER userid |
REPLACE USER userid |
DELETE USER userid }
[PASSWORD password]
[ALIAS alias]
[DOMAIN domain]

ADD USER – Adds a new user and it’s alias to the Credential Store. If you do not specify the ALIAS name, then by default user name will be set as the ALIAS. If you do not use the PASSWORD option, the command automatically prompts for the PASSWORD. The user can be either an actual user id or it can be an SQL*Net connect string.

REPLACE USER – Change the password or credentials of the existing User. You can not change the ALIAS or DOMAIN.

DELETE USER –  To the delete the credential or password of the User specified.

PASSWORD – User’s password. If this option is not used, then the command prompts for the password and should be typed. This is more secured
than typing the password using the PASSWORD option in the credential store.

ALIAS – An alias name for the User. We can use this option when we do not want the username in the parameter files (parameter files of Extract, Replicat etc.,)

DOMAIN – Which allows multiple GoldenGate instances to use the same ALIAS names which are configured in the same Credential Store. For example, if suppose ALIAS GG_ROOT is used in Domain GG_SYSTEM1, then the same ALIAS GG_ROOT can be used by the GG_SYSTEM2. But the default DOMAIN name is set to “Oracle GoldenGate”.

 

WORKOUTS

 

ADD USER

Adding the user without specifying an ALIAS name to it. By default Credential Store will take the actual username as the ALIAS. Here in the upcoming examples/workouts my username is ggadmin

ALTER CREDENTIALSTORE ADD USER ggadmin
GGSCI (OGG1.localdomain) 2> ALTER CREDENTIALSTORE ADD USER ggadmin
Password:

Credential store in ./dircrd/ altered.

Since I have not specified the PASSWORD option, it is prompting for the password.

To view the information of the Credential Store created issue the command INFO CREDENTIALSTORE

GGSCI (OGG1.localdomain) 3> INFO CREDENTIALSTORE
Reading from ./dircrd/:

Domain: OracleGoldenGate

Alias: ggadmin
Userid: ggadmin

You can see in the above example, I have not specified the ALIAS but automatically it has taken the username as ALIAS.

 

ALIAS, DOMAIN & PASSWORD

Now I am adding the user with ALIAS and DOMAIN names with PASSWORD option.,

ALTER CREDENTIALSTORE ADD USER ggadmin PASSWORD oracle ALIAS gg_root domain ogg
GGSCI (OGG1.localdomain) 4> ALTER CREDENTIALSTORE ADD USER ggadmin PASSWORD oracle ALIAS gg_root DOMAIN gg

Credential store in ./dircrd/ altered. 

To view the information of the Credential Store created, issue the command INFO CREDENTIALSTORE with the DOMAIN name as below,

GGSCI (OGG1.localdomain) 5> INFO CREDENTIALSTORE DOMAIN gg
Reading from ./dircrd/:

Domain: gg

Alias: gg_root
Userid: ggadmin

When you specify the DOMAIN in adding user, then normal INFO CREDENTIALSTORE command will not give any output.,

GGSCI (OGG1.localdomain) 4> INFO CREDENTIALSTORE
Reading from ./dircrd/:

No information found in credential store.

 

If you do not wish to display the password for security reasons, the above command can be as below without the PASSWORD option. But it will ask for the PASSWORD which you have to type it manually.

ALTER CREDENTIALSTORE ADD USER ggadmin ALIAS gg_root domain ogg
Password:
GGSCI (OGG1.localdomain) 2> ALTER CREDENTIALSTORE ADD USER ggadmin ALIAS gg_root DOMAIN gg
Password:

Credential store in ./dircrd/ altered.

GGSCI (OGG1.localdomain) 3> INFO CREDENTIALSTORE DOMAIN gg
Reading from ./dircrd/:

Domain: gg

Alias: gg_root
Userid: ggadmin 

 

As I mentioned earlier, when you specify the DOMAIN name then normal INFO CREDENTIALSTORE command will not give any output. If you add the user without mentioning the DOMAIN name then INFO CREDENTIALSTORE command will display output. So for the users added with domain name, DOMAIN keyword should be specified when looking the info of it.

 

For example see the below where I have not added the user with DOMAIN option.

GGSCI (OGG1.localdomain) 1> ALTER CREDENTIALSTORE ADD USER ggadmin ALIAS gg_root
Password:

Credential store in ./dircrd/ altered.

GGSCI (OGG1.localdomain) 2> INFO CREDENTIALSTORE
Reading from ./dircrd/:

Domain: OracleGoldenGate

Alias: gg_root
Userid: ggadmin

 

We now know how to create Credential Store, add users to it with and without ALIAS and DOMAIN names. Now we will see how to use this.,

Here I have to tell about one more thing. After we enter the Oracle GoldenGate Prompt GGSCI, we normally login as GoldenGate admin user as below.

DBLOGIN USERID ggadmin, PASSWORD oracle

But after configuring the Credential Store it will be as below. USERIDALIAS is the keyword used here to login.

DBLOGIN USERIDALIAS <ALIAS_NAME>

DBLOGIN USERIDALIAS <ALIAS_NAME> DOMAIN <DOMAIN_NAME>

When the user is added without a DOMAIN. So we have to login as below.,

GGSCI (OGG1.localdomain) 3> DBLOGIN USERIDALIAS gg_root
Successfully logged into database.

But when using the DOMAIN name, we should add the DOMAIN information with the USERIDALIAS, else it will throw an error like the below one.,

GGSCI (OGG1.localdomain) 5> ALTER CREDENTIALSTORE ADD USER ggadmin ALIAS gg_root DOMAIN gg
Password:

Credential store in ./dircrd/ altered.

GGSCI (OGG1.localdomain) 6> DBLOGIN USERIDALIAS gg_root

ERROR: Alias ‘gg_root’ not found in credential store..

The Correct one is below,

GGSCI (OGG1.localdomain) 7> DBLOGIN USERIDALIAS gg_root DOMAIN gg
Successfully logged into database.

 

DELETE USER

Now we will see how to Delete the user from the Credential Store with ALIAS and without ALIAS

Deleting the User without ALIAS.

GGSCI (OGG1.localdomain) 9> ALTER CREDENTIALSTORE DELETE USER ggadmin

Credential store in ./dircrd/ altered.

GGSCI (OGG1.localdomain) 10> INFO CREDENTIALSTORE
Reading from ./dircrd/:

No information found in credential store.

Deleting the user with ALIAS.

GGSCI (OGG1.localdomain) 12> ALTER CREDENTIALSTORE DELETE USER ggadmin ALIAS gg_root DOMAIN gg

Credential store in ./dircrd/ altered.

GGSCI (OGG1.localdomain) 13> INFO CREDENTIALSTORE DOMAIN gg
Reading from ./dircrd/:

No information found in credential store.

 

REPLACE USER

As I stated earlier, REPLACE USER is used to change the password or credentials of the existing User. You can not change the ALIAS or DOMAIN.

For security concerns, we all use to change the passwords every 15 days or 1month etc., So REPLACE USER option is very useful during this situation.

Here my Oracle GoldenGate admin’s password is oracle. Now I am going to change it to oracle123.

SQL> alter user ggadmin identified by oracle123;

User altered.

After the changing the password at Database level, let us try to login at GGSCI prompt. Remember that we haven’t changed the password at GoldenGate level.

GGSCI (OGG1.localdomain) 1> DBLOGIN USERIDALIAS gg_root DOMAIN gg
ERROR: Unable to connect to database using user ggadmin. Please check privileges.
ORA-01017: invalid username/password; logon denied.

We can clearly see that it thrown an error. So now we will use the option REPLACE USER to change the password/credential in the Credential Store. Also remember that, only when using the Credential Store, you need need to change the password at GoldenGate level, else it is not required.

GGSCI (OGG1.localdomain) 2> ALTER CREDENTIALSTORE REPLACE USER ggadmin ALIAS gg_root DOMAIN gg
Password:

Credential store in ./dircrd/ altered.

Type in the new password when prompted.

Now try to login as usual.,

GGSCI (OGG1.localdomain) 3> DBLOGIN USERIDALIAS gg_root DOMAIN gg
Successfully logged into database.

You cannot alter or change the ALIAS or DOMAIN names using this options. See the below examples.,

Try changing the ALIAS name from gg_root to gg_admin.,

GGSCI (OGG1.localdomain) 4> ALTER CREDENTIALSTORE REPLACE USER ggadmin ALIAS gg_admin DOMAIN gg
Password:

ERROR: Alias ‘gg_admin’ not found in credential store.

Try changing the DOMAIN name from gg to ggdomain.,

GGSCI (OGG1.localdomain) 5> ALTER CREDENTIALSTORE REPLACE USER ggadmin ALIAS gg_root DOMAIN ggdomain
Password:

ERROR: Credential domain ‘ggdomain’ not found in credential store.

USER with CONNECT STRING

You can also create the user with specifying the CONNECT STRING to it. Check the below example.,

GGSCI (OGG1.localdomain) 8> ALTER CREDENTIALSTORE ADD USER ggadmin@GGDB1 ALIAS gg_root DOMAIN gg
Password:

Credential store in ./dircrd/ altered.

GGSCI (OGG1.localdomain) 9> DBLOGIN USERIDALIAS gg_root DOMAIN gg
Successfully logged into database.

 

When using the Credential Store, the dblogin in Extract and Replicat parameters should be specified as below.,

 

 GGSCI (OGG1.localdomain) 24> view params ext2
–EXTRACT GROUPNAME
EXTRACT EXT2
–SOURCE DB GG USERNAME AND PASSWORD
–USERID ggadmin, PASSWORD oracle  ——-> I have commented the usual way of logging.
USERIDALIAS gg_root, domain gg
–SOURCE TRAIL FILE PATH AND PREFIX
TRANLOGOPTIONS EXCLUDEUSER GGADMIN
DDL INCLUDE ALL;
DDLOPTIONS ADDTRANDATA, REPORT
–GETUPDATEBEFORES
EXTTRAIL /vol3/ogg/dirdat/ft
–SOURCE TABLE NAMES THAT ARE TO BE REPLICATED
TABLE test.*;

 

Hope the above article clearly explains about one of the new features of the Oracle GoldenGate 12c – CREDENTIALSTORE.

 

 

You may also like...

8 Responses

  1. naseer says:

    excellent post.. no need to check any other website for credentialstore

  2. Abdisamad says:

    I was trying to implement Bidirectional Replication from Oracle 11gR2 to Oracle 11gR2

    Created a user
    SQL> CREATE USER oggadm1 IDENTIFIED BY pswd1a;
    User created.

    Granted access to dba role

    SQL> GRANT dba TO oggadm1;
    Grant succeeded

    Granted to ‘capture’ priviledge

    SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE (grantee=>’OGGADM1′,privile
    ge_type=>’capture’,grant_select_privileges=>true, do_grants=>TRUE);

    Created startup.oby file and put in it the following lines of code

    DBLogin UserID oggadm1@orcl01, Password pswd1a
    Start Mgr
    Info Mgr
    Info CheckpointTable
    Set Editor gedit

    The question is: when I try to connect using the above oggadm1@orcl01, Password pswd1a it shows the following error:

    GGSCI (host1) 1> Obey startup.oby

    GGSCI (host1) 2> DBLogin UserID oggadm1@orcl01, Password pswd1a

    ERROR: Unable to connect to database using user oggadm1@orcl01. Please check privileges.
    ORA-01017: invalid username/password; logon denied.

    GGSCI (PB-UAT-IMAL-DB) 3> Start Mgr

    Manager started.

    I’m still confused the relationship between the Oracle GoldenGate users/passwords (in CREDENTIALSTORE) and the user/password of Oracle instance installed in my host1 (I’m following this document http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/goldengate/11g/orcl_orcl/index.html but using Windows instead of Linux which the above Oracle-By-Example (OBE) is based on.

  3. Abdisamad says:

    I solved my problem. Thanks. It was incorrect schema name specified in oggadm1@xxxxxx.

  4. Abdisamad says:

    By the way nice info.

Leave a Reply

Your email address will not be published. Required fields are marked *