ALTER CREDENTIALSTORE

Use this command to create credential store aliases which contains database credentials that includes username and password with the connection details. The credential store aliases can then be used to establish database connections within deployment.

See Add and Alter Database Credentials to configure the database connections from the MA web interface.

The use of a credential store is not supported for the NonStop platforms.

Syntax

ALTER CREDENTIALSTORE 
{ADD USER {userid|userid@dbhost:dbport/dbname[;connection_options]|userid@odbc-dsn
|DELETE USER userid| REPLACE USER userid}
[ALIAS alias]
[Domain domain]
[PASSWORD password|NOPASSWORD]

ADD USER {userid|userid@dbhost:dbport/dbname[;connection_options]|userid@odbc-dsn}

userid

Adds the specified user to the credential store alias. If the ALIAS option is not used, the alias name defaults to that of the userid. If the DOMAIN option is not supplied, the domain defaults to the OracleGoldenGate domain. For multitenant Oracle databases with different users for the CDB and the PDB, you need to specify @tns_service_name when adding a database user to the credential store.

userid@dbhost:dbport/dbname[;connection_options]

For databases that support connection strings such as Oracle database, MySQL, SQL Server, PostgreSQL, and Db2, adds the specified user and connection information to a credential store alias. Connection string information should include the database server name, database port, and database name. Following are the additional connection options (per database) that are available to add to the connection string:

The ALIAS option is required when creating an alias with database connection details

The required connection format is:

userid@databasehost:databaseport/databasename;option1;option2;option3

For example:

ALTER CREDENTIALSTORE ADD USER ggadmin@server_west:1434/sourcedb 
PASSWORD ***** ALIAS SourceMSSQLConnection

userid @ odbc-dsn

For databases that support DSN connections over ODBC, including Db2 for i Series, Db2 LUW, Db2 z/OS, SQL Server, and PostgreSQL, adds the DSN connection information to the credential store. The DSN connection details must exist on the system, such as in an odbc.ini file for Linux or as a System DSN created under Windows using the ODBC data sources 64-bit client.

The ALIAS option is required when creating an alias with a DSN.

The required connection format is:

userid@odbc-dsn

For example:

ALTER CREDENTIALSTORE ADD USER ggadmin@sourcedsn 
PASSWORD ***** ALIAS SourceMSSQLConnection

DELETE USER userid

Removes the credential alias of a specified user, alias, and domain. If the ALIAS option is not supplied, the alias name defaults to that of the userid. If the DOMAIN option is not supplied, the credential store defaults to the OracleGoldenGate domain.

For example:

ALTER CREDENTIALSTORE DELETE USER ggadmin ALIAS src_user

REPLACE USER userid

Replaces the user of a given alias or can be used to change the password of an existing user. If the ALIAS option is not supplied, then the alias name defaults to that of the userid. If the DOMAIN option is not supplied, then the credential store defaults to the OracleGoldenGate domain.

Unless the PASSWORD option is used, the command prompts to enter a password for the specified user. When changing the password for a user that includes database connection information, you must include the full value of the current userid, otherwise this command will replace the user with the new value supplied.

The following example shows how to change the password of an existing user ggadmin@sourcedsn, alias SourceMSSQLConnection.

ALTER CREDENTIALSTORE REPLACE USER ggadmin@sourcedsn PASSWORD newpassword 
ALIAS SourceMSSQLConnection

The example shows how to change the user ggadmin of the pdbeast alias to a new user, which then prompts for the password.

ALTER CREDENTIALSTORE REPLACE USER ggadmin ALIAS pdbeast DOMAIN Production

ALIAS alias: Specifies an alias for the user name. Use this option if you do not want the user name to be in a parameter file or command. If ALIAS is not provided, the alias defaults to the USER name, which then must be used in parameter files and commands where a login is required. You can create multiple entries for a user, each with a different alias, by using the ADD USER option with ALIAS.

Connection Options

These are optional connection string settings that can be added to a credential alias, including the SSL attributes. They must be specified as semicolon separated list of attributes, for example, option1=value1;option2=value2;option3=value3, where the options are the database specific properties along with their allowed values.

Following are a list of additional connection string options that can be added to a credential alias, including the SSL attributes.

Note:

Oracle GoldenGate Microservices Architecture supports SSL connections by configuring certain SSL parameters as environment variables. However, this configuration limits the Extract and Replicat process to connect using only the defined SSL environment variables for a deployment and no other SSL or Non-SSL connections are possible. However, a credential store alias also provides the flexibility of configuring the credentials with different SSL or non SSL parameters for different database connections within the same deployment.

sslCA

For MySQL and PostgreSQL only. The certificate used to verify the authenticity of the SSL certificates in the PEM format.

sslCert

For MySQL and PostgreSQL. The client's SSL certificate file in the PEM format.

sslCrl

For MySQL. The name of the file containing certification revocation lists in the PEM format.

sslKey

For MySQL and PostgreSQL. The private key file of client in the PEM format.

sslMode

For MySQL and PostgreSQL. The sslMode parameter values are specified in uppercase only. The sslMode options for MySQL are VERIFY_IDENTITY, VERIFY_CA, REQUIRED, PREFERRED. The sslMode options for PostgreSQL are VERIFY-FULL, VERIFY-CA, REQUIRE, PREFER.

For sslMode=VERIFY_IDENTITY, it is mandatory to provide sslPath, sslCa, sslCert and sslKey. For sslMode=VERIFY_CA, it is mandatory to provide sslPath and sslCa.

sslPath

For MySQL only. The path of the directory that contains trusted ssl Certificate Authority (CA) certificate files in PEM format.

DOMAIN domain

Saves the credential alias under the supplied domain name.

The default domain is OracleGoldenGate. By choosing unique domain names, the same alias can be used by multiple Oracle GoldenGate installations that use the same credential store. For example, the administrators of system 1 might not want system 2 to have access to the same credentials that are used on system 1. Those credentials can be stored as ALIAS pdbeast, for example, under DOMAIN system1, while a different set of credentials can be stored for ALIAS pdbeast under DOMAIN system2.

NOPASSWORD | PASSWORD password

Specify the user's password using the PASSWORD option. The password is echoed (not obfuscated) when this option is used. If this option is omitted, the command prompts for the password, which is obfuscated as it is typed (recommended as more secure).

Oracle GoldenGate 23ai supports a maximum password length of 1024 bytes.

The NOPASSWORD option is the alternative to the PASSWORD option when using external authentication because password is not required for external authentication such as using Kerberos authentication or IDCS. After the NOPASSWORD option is set, the DBLOGIN command can be used to access the database without a password.

Also see USERIDALIAS parameter in the Reference for Oracle GoldenGate.

Examples

Oracle

This example adds a user named ggadmin but omits the PASSWORD specification, so the command prompts for the password of the pluggable database, pdbeast.

ALTER CREDENTIALSTORE ADD USER ggadmin
Password: ********

This example adds the user ggadmin with this password tiger and specifies the alias as pdbeast.

ALTER CREDENTIALSTORE ADD USER ggadmin PASSWORD tiger ALIAS pdbeast

This example adds the user ggadmin under the domain of OracleGoldenGate.

ALTER CREDENTIALSTORE ADD USER ggadmin@dbnorth ALIAS pdbeast DOMAIN OracleGoldenGate
Password: ********

This example adds the user ggadmin using the Easy Connect Naming Method.

ALTER CREDENTIALSTORE ADD USER ggadmin@dbnorth:1521/dbservice_north
ALIAS pdbnorth

This example issues two ALTER CREDENTIALSTORE commands, each of which adds a ggadmin entry, but with a different alias.

ALTER CREDENTIALSTORE ADD USER ggadmin@dbeast ALIAS pdbeast
Password: ********
ALTER CREDENTIALSTORE ADD USER ggadmin@dbwest ALIAS pdbwest
Password: ********

The following shows how the DELETE USER option works with and without the ALIAS option.

The following command deletes the user1 entry for which the ALIAS is the same as the user name.

ALTER CREDENTIALSTORE DELETE USER ggadmin
Alias: pdbeast
Userid: ggadmin

The following command deletes the entry for user ggadmin that is associated with the alias pdbeast.

ALTER CREDENTIALSTORE DELETE USER ggadmin ALIAS pdbeast
Alias: pdbeast
Userid: ggadmin

This example uses a SQL*Net connect string as the user value. In this case, the PASSWORD option is omitted. The person issuing the command is prompted for the password, which is hidden.

ALTER CREDENTIALSTORE ADD USER ggadmin@pdbeast ALIAS pdbeast

This example creates a domain name Oracle GoldenGate with user ID ggadmin and alias as pdbeast in the Admin Client.

ALTER CREDENTIALSTORE ADD USER ggadmin ALIAS pdbeast
Password:

Following example connects using a connection qualifier if using a BEQ-Bequeath Protocol adapter. For more information on establishing a secure connection using a BEQ-Bequeath Protocol adapter, see Configure Secure Database Connection

ALTER CREDENTIALSTORE ADD USER ggadmin@inst1_beq

The following example (Admin Client) adds a user named ggadmin but with external authentication and therefore uses the NOPASSWORD option.

ALTER CREDENTIALSTORE ADD USER ggadmin@dbeast nopassword alias pdbeast

The output shows:

2020-06-22T21:08:33Z  INFO    OGG-15102  Credential store created.

Running the INFO CREDENTIALSTORE command, you can check the add user to the credential store:

INFO CREDENTIALSTORE
Default domain: OracleGoldenGate
  Alias: pdbeast
  Userid: @ggadmin

After you update the credential store to use the NOPASSWORD option, you can use the DBLOGIN command with Kerberos authentication for your database.

DBLOGIN USERIDALIAS pdbeast

Output:

Successfully logged into database pdbeast.

MySQL

Following are some examples to create credential in Admin Client for MySQL.

This example adds database credentials with no SSL attribute in the connection string.

ALTER CREDENTIALSTORE ADD USER ggadmin@mysqlsrvr.myorg.com/my_db 
PASSWORD:******  ALIAS pdbeast

This example adds database connection details in the credential store with SSL attributes added in the connection string.

ALTER CREDENTIALSTORE ADD USER ggadmin1@192.168.254.2:3306/atssrc;
sslMode=VERIFY_IDENTITY;sslPath=/var/lib/mysql/data;
sslCert=client-cert.pem;sslCa=ca.pem;sslKey=client-key.pem 
PASSWORD ****** ALIAS pdbeast

SQL Server

The following example adds database connection details in the credential store using the DSN.

ALTER CREDENTIALSTORE ADD USER ggadmin@mydsn PASSWORD ***** 
ALIAS pdbeast

The following example adds database connection details in the credential store using the DSNless based connection.

ALTER CREDENTIALSTORE ADD USER ggadmin@100.70.96.45:10043/qadb;Encrypt=NO; 
TrustServerCertificate=YES" PASSWORD***** ALIAS pdbwest

PostgreSQL

The following example adds database connection details in the credential store with SSL attributes added in the connection string.

ALTER CREDENTIALSTORE ADD USER ggadmin@abc.com:7432/mydb;sslmode=disable; 
sslCa=/u01/ogg/certs/root.pem  PASSWORD ***** ALIAS pdbeast

The following example adds database connection details in the credential store using the DSN.

ALTER CREDENTIALSTORE ADD USER ggadmin@mydsn PASSWORD ***** 
ALIAS pdbeast

The following example adds database connection details in the credential store using the DSNless based connection.

ALTER CREDENTIALSTORE ADD USER ggadmin@100.70.96.45:10043/qadb;Encrypt=NO; 
TrustServerCertificate=YES" PASSWORD***** ALIAS pdbwest

Sybase

Following are some examples to create credential in Admin Client for Sybase.

The following example displays a host and port based connection with no additional attributes to the Sybase database.

ALTER CREDENTIALSTORE ADD USER ggadmin@sybdb.myorg.com:5055/qadb PASSWORD****  
ALIAS pdbeast

The following example displays a host and port based connection to the Sybase database with additional attributes and SSL enabled.

ALTER CREDENTIALSTORE ADD USER ggadmin@sybdb.myorg.com:5055/qadb;OGG_USE_SSL; 
CS_OPT_CHARSET=UTF-8;CS_LOGIN_TIMEOUT=10 PASSWORD**** ALIAS pdbeast

DB2 for i and DB2 for z/OS

The following example adds database connection details in the credential store using the DSN.

ALTER CREDENTIALSTORE ADD USER ggadmin@mydsn PASSWORD ***** 
ALIAS pdbeast

The following example adds database connection details in the credential store using the DSNless based connection.

ALTER CREDENTIALSTORE ADD USER ggadmin@100.70.96.45:10043/qadb;Encrypt=NO; 
TrustServerCertificate=YES" PASSWORD***** ALIAS pdbwest