Valid For
Manager, Extract, Replicat, DEFGEN
Supported for
DB2 for i
DB2 LUW
DB2 on z/OS
Oracle
MySQL
SQL Server
Teradata
Description
Use the USERID
parameter to specify the type of authentication for an Oracle GoldenGate process to use when logging into a database, and to specify password encryption information. This parameter can be used instead of USERIDALIAS
when an Oracle GoldenGate credential store is not being used.00
Always use USERID
or USERIDALIAS
for a primary Extract and for Replicat. Always use USERID
or USERIDALIAS
for Replicat. Use USERID
or USERIDALIAS
for Manager only if using parameters that require Manager to log into the source or target database.
USERID Compared to USERIDALIAS
USERID
requires either specifying the clear-text password in the parameter file or encrypting it with the ENCRYPT PASSWORD
command and, optionally, storing an encryption key in an ENCKEYS
file. USERID
supports a broad range of the databases that Oracle GoldenGate supports.
USERIDALIAS
enables you to specify an alias, rather than a user ID and password, in the parameter file. The user IDs and encrypted passwords are stored in a credential store. USERIDALIAS
supports databases running on Linux, UNIX, and Windows platforms.
See Administering Oracle GoldenGate for more information about Oracle GoldenGate security features.
General Requirements for USERID
Specify USERID
before any TABLE
or MAP
entries in an Extract or Replicat parameter file. Specify USERID
in a Manager parameter file if Manager must access the database and a login is required.
USERID
is not always required, nor is PASSWORD
always required when USERID
is required. In some cases, it is sufficient just to use USERID
or even just to use the SOURCEDB
or TARGETDB
parameter, depending on how authentication for the database is configured.
See "SOURCEDB" and "TARGETDB" for more information.
Note:
The privileges that are required for the USERID
user vary by database. See the appropriate Oracle GoldenGate installation guide for your database to determine the privileges that are required for the Oracle GoldenGate database users.
USERID Requirements Per Database Type
The usage of USERID
varies depending on the database type.
DB2 for i
Use USERID
with PASSWORD
to specify the name and password of the user profile assigned to the Oracle GoldenGate process. Use SOURCEDB
or TARGETDB
with USERID
to specify the default DB2 for i database that is identified by the system name (in upper case). See Using Oracle GoldenGate for Heterogeneous Databases for more information.
DB2 for LUW
Use USERID
with PASSWORD
and preceded by SOURCEDB
or TARGETDB
for all Oracle GoldenGate processes that connect to a DB2 LUW database using database authentication. You can omit USERID
and PASSWORD
(and only use SOURCEDB
or TARGETDB
) if the database is configured allow authentication at the operating-system level.
DB2 for z/OS database
Use USERID
with PASSWORD
if the user that is assigned to the Oracle GoldenGate process does not have the DB2 privileges that are required for the process to function properly.
MySQL
Use USERID
with PASSWORD
for all Oracle GoldenGate processes that connect to a MySQL database.
Oracle
Use USERID
for Oracle GoldenGate processes that connect to an Oracle database as follows:
To use an operating system login, use USERID
with the /
argument.
To use a database user name and password, use USERID
with PASSWORD
.
Optionally, you can specify the user to log in as sysdba
.
(Oracle Enterprise Edition earlier than 11.2.0.2) Special database privileges are required for the USERID
user when Extract is configured to use LOGRETENTION
. These privileges might have been granted when Oracle GoldenGate was installed. See Log Retention Options in Using Oracle GoldenGate for Oracle Databasefor LOGRETENTION
information.
(Oracle Standard or Enterprise Edition 11.2.0.2 or later) To use USERID
for an Extract group that is configured for integrated capture, the user must have the privileges granted in the dbms_goldengate_auth.grant_admin_privilege
procedure, and the user must be the same one that issues DBLOGIN
and REGISTER EXTRACT
or UNREGISTER EXTRACT
for the Extract group that is associated with this USERID
.
To support capture from an Oracle container database, the user that is specified with USERID
must log into the root container and must be a common user. A connect string must be supplied for this user and must include the required C##
prefix of the common user, such as C##GGADMIN@FINANCE
. See, Other Requirements for Multitenant Container Databases in Using Oracle GoldenGate for Oracle Database.
If you are connecting a Replicat into a multitenant database that each Replicat connects into a single PDB and if your are applying data into multiple PDBs in the same multitenant database, then Oracle recommends that you use multiple Replicats.
Use NOUSERID
to allow Integrated Extract to run without a connection for fetching or metadata lookups, or any data dictionary calls. Essentially eliminating the need to connect to the source database at all. The NOUSERID
option requires an Integrated Dictionary. We should also include that when NOUSERID
is used, if the customer has an Active Data Guard Standby, they can set up fetching from that Standby database using the FETCHUSERID
parameter. The two can be used in conjunction with NOUSERID
. In the event where you are using downstream integrated extract (same caveats below) you can use FETCHUSERID
to fetch from the ADG Standby database and NOUSERID
to prevent the Extract from making a connection to the source database. This way, if Extract does need to fetch, it can do so.
SQL Server
Use USERID
with PASSWORD
if the ODBC data source connection that will be used by the Oracle GoldenGate process is configured to supply database authentication. USERID
can be a specific login that is assigned to the process or any member of an account in the System Administrators
or Server Administrators
fixed server role.
On a source SQL Server system, also use the SOURCEDB
parameter to specify the source ODBC data source.
On a target SQL Server system, also use the TARGETDB
parameter to specify the target ODBC data source.
Teradata
Use USERID
with PASSWORD
for Oracle GoldenGate processes that connect to a Teradata database.
On a source Teradata system, also use the SOURCEDB
parameter to specify the source ODBC data source.
On a target Teradata system, also use the TARGETDB
parameter to specify the target ODBC data source.
Default
None
Syntax
USERID {/ | user}[, PASSWORD password] [algorithm ENCRYPTKEY {key_name | DEFAULT}] [SYSDBA] [, THREADS (threadID[, threadID][, ...][, thread_range[, thread_range][, ...])]
/
Directs Oracle GoldenGate to use an operating-system login for Oracle, not a database user login. Use this argument only if the database allows authentication at the operating-system level. Bypassing database-level authentication eliminates the need to update Oracle GoldenGate parameter files if application passwords frequently change. To use this option, the correct user name must exist in the database, in relation to the value of the Oracle OS_AUTHENT_PREFIX
initialization parameter, as follows:
The value set with OS_AUTHENT_PREFIX
is concatenated to the beginning of a user's operating system account name and then compared to the database name. Those two names must match.
If OS_AUTHENT_PREFIX
is set to ' '
(a null string), the user name must be created with IDENTIFIED EXTERNALLY
. For example, if the OS user name is ogg
, you would use the following to create the database user:
CREATE USER ogg IDENTIFIED EXTERNALLY;
If OS_AUTHENT_PREFIX
is set to OPS$
or another string, the user name must be created in the following format:
OS_AUTHENT_PREFIX_value OS_user_name
For example, if the OS user name is ogg
, you would use the following to create the database user:
CREATE USER ops$ogg IDENTIFIED BY oggpassword;
user
Specifies the name of a database user or a schema, depending on the database configuration. For Oracle, a SQL*Net connect string can be used. Refer to USERID Requirements Per Database Type for additional guidelines.
password
Use when database authentication is required to specify the password for the database user. If the password was encrypted by means of the ENCRYPT PASSWORD
command, supply the encrypted password; otherwise, use the clear-text password. If the password is case-sensitive, type it that way.
If either the user ID or password changes, the change must be made in the Oracle GoldenGate parameter files, including the re-encryption of the password if necessary.
algorithm
Specifies the encryption algorithm that was used to encrypt the password with ENCRYPT PASSWORD
.
The algorithm can be one of:
AES128
AES192
AES256
BLOWFISH
ENCRYPTKEY {
key_name
| DEFAULT}
Specifies the encryption key that was specified with ENCRYPT PASSWORD
.
ENCRYPTKEY
key_name
specifies the logical name of a user-created encryption key in the ENCKEYS
lookup file. Use if ENCRYPT PASSWORD
was used with the KEYNAME key_name
option.
ENCRYPTKEY DEFAULT
directs Oracle GoldenGate to use a random key. Use if ENCRYPT PASSWORD
was used with the KEYNAME DEFAULT
option.
SYSDBA
(Oracle) Specifies that the user logs in as sysdba
.
THREADS (
threadID
[,
threadID
][, ...][,
thread_range
[,
thread_range
][, ...]
)
Valid for Replicat. Links the specified credential to one or more threads of a coordinated Replicat. Enables you to specify different logins for different threads.
threadID
[,
threadID
][, ...]
Specifies a thread ID or a comma-delimited list of threads in the format of threadID, threadID, threadID
.
[,
thread_range
[,
thread_range
][, ...]
Specifies a range of threads in the form of threadIDlow-threadIDhigh
or a comma-delimited list of ranges in the format of threadIDlow-threadIDhigh
, threadIDlow-threadIDhigh
.
A combination of these formats is permitted, such as threadID
, threadID
, threadIDlow-threadIDhigh
.
Examples
USERID /
USERID ogg
USERID ogg@ora1.ora, & PASSWORD AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC AES128, & ENCRYPTKEY securekey1
USERID ogg, PASSWORD AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC & AES128, ENCRYPTKEY securekey1
USERID ogg, PASSWORD AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC & BLOWFISH, ENCRYPTKEY DEFAULT
USERID ogg, & PASSWORD AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC AES128, & ENCRYPTKEY securekey1 SYSDBA