4 Establishing Oracle GoldenGate Credentials

This chapter provides guidelines for creating database users for the processes that will interact with the database, assigning the correct privileges, and securing the credentials from unauthorized use.

This chapter includes the following sections:

4.1 Assigning Credentials to Oracle GoldenGate

The Oracle GoldenGate processes require one or more database credentials with the correct database privileges for the database version, database configuration, and Oracle GoldenGate features that you are using. Create a source database user and a target database user, each one dedicated to Oracle GoldenGate on the source and target systems. The assigned user can be the same user for all of the Oracle GoldenGate processes that must connect to a source or target Oracle database.

The following sections outline the Oracle GoldenGate processes that require user credentials:

Section 4.1.4, "Granting the Appropriate User Privileges" outlines the database privileges that each of those processes requires.

4.1.1 Extract User

The Extract user performs metadata queries on the source database and fetches data from the source tables when needed. In a local mining deployment of integrated capture, this user also creates, alters, and connects to the logmining server and receives logical change records (LCR) from it. (See Section 5.2, "Deciding Which Capture Method to Use" for more information about capture modes.)

If the source database is a multitenant container database, the Extract user must be a common user and must log into the root container. See Chapter 6, "Configuring Oracle GoldenGate in a Multitenant Container Database" for more information.

You need to assign an additional user if Extract will be operating in integrated capture mode and you are using a downstream mining database. This user will be the mining user and is created in the downstream database. The mining user creates, alters, and connects to the logmining server on the mining database, and it receives logical change records (LCR) from it. This user can be the same as the source Extract user or different. Choose the name of the mining user carefully. Once created by this user, the database logmining server cannot be altered or used by another user. See Appendix B, "Configuring a Downstream Mining Database" for more information about configuring downstream mining.

4.1.2 Replicat User

The Replicat user creates the Replicat checkpoint table (if used) and applies DML and DDL operations through Oracle Call Interface or through a database inbound server, depending on the Replicat mode. (See Section 5.3, "Deciding Which Apply Method to Use" for more information about Replicat modes.)

4.1.3 Other Oracle GoldenGate Users

A user is required in the source database for the Manager process if you are using Oracle GoldenGate DDL support. This user performs maintenance on the Oracle GoldenGate database objects that support DDL capture.

A user is required in either the source or target database for the DEFGEN utility. The location depends on where the data definition file is being generated. This user performs local metadata queries to build a data-definitions file that supplies the metadata to remote Oracle GoldenGate instances. For more information about the data-definitions file, see Administering Oracle GoldenGate for Windows and UNIX.

Additional users or privileges may be required to use the following features, if Extract will run in classic capture mode:

4.1.4 Granting the Appropriate User Privileges

The user privileges that are required for Oracle GoldenGate depend on the database version and the Extract or Replicat process mode. For more information about process modes, see Chapter 5, "Choosing Capture and Apply Modes".

4.1.4.1 Oracle 11.2.0.4 or Later Database Privileges

The following privileges apply to Oracle versions 11.2.0.4 or later.

Table 4-1 Oracle GoldenGate Privileges, Version 11.2.0.4 or Later

Privilege ExtractClassic Mode Extract Integrated Mode ReplicatAll Modes Purpose

CREATE SESSION

X

X

X

Connect to the database

CONNECT

X

X

X

For Replicat, required only if Replicat owns target objects. Alternatively, use CREATE object.

RESOURCE

X

X

X

Create objects

If RESOURCE cannot be granted to Replicat, use:

ALTER USER user QUOTA {size | UNLIMITED} ON tablespace;

ALTER ANY TABLE

X

X

 

Required for Oracle 12.1.0.1 only to issue the ADD TRANDATA command.

ALTER SYSTEM

X

X

 

Perform administrative changes, such as enabling logging

Privileges granted through dbms_goldengate_auth.grant_admin_privilege

X

X

X

(Extract) Grants privileges for both classic and integrated Extract, including the logmining server.(Replicat) Grants privileges for both nonintegrated and integrated replicat, including the database inbound server (Oracle 11.2.0.4 or later).

Any or all of optional privileges of dbms_goldengate_auth.grant_admin_privilege

X

X

X

  • Capture from Data Vault

  • Capture from Virtual Private Database

  • Capture redacted data

See Section 4.1.4.4, "Optional Grants for dbms_goldengate_auth.grant_admin_privilege" for more information.

INSERT, UPDATE, DELETE on target tables

   

X

Apply replicated DML to target objects

CREATE TABLE

   

X

Create a checkpoint table in target database

DDL privileges on target objects (if using DDL support)

   

X

Issue replicated DDL on target objects

DBA

X

X

X

DDL and sequence support

LOCK ANY TABLE

   

X

Lock target tables. Only required for initial load using direct bulk load to SQL*Loader.

sys.dbms_internal_clkm

X

   

Replicate Oracle Transparent Data Encryption (TDE)

SELECT ANY TRANSACTION

X

   

Use a newer Oracle ASM API. See Section 11.3, "Mining ASM-stored Logs in Classic Capture Mode".


4.1.4.2 Oracle 11.2.0.3 or Earlier Database Privileges

The following privileges apply to Oracle versions 11.2.0.3 or earlier.

Table 4-2 Oracle GoldenGate Privileges, Oracle 11.2.0.3 or Earlier

Privilege ExtractClassic Mode ExtractIntegrated Mode Replicat Manager Purpose

CREATE SESSION

and

ALTER SESSION

X

X

X

 

Connect to the database

ALTER SYSTEM

X

X

   

Perform administrative changes, such as enabling logging

RESOURCE

X

X

X

 

Create objects

If RESOURCE cannot be granted to Replicat, use:

ALTER USER user QUOTA {size | UNLIMITED} ON tablespace;

CONNECT

X

X

X

 

For Replicat, required only if Replicat owns target objects. Alternatively, use CREATE object.

SELECT ANY DICTIONARY

X

X

X

 

Query data dictionary objects in the SYS schema

FLASHBACK ANY TABLE

or

FLASHBACK ON schema.table

X

X

   

Make flashback queries

SELECT ANY TABLE

or

SELECT on a schema.table

X

X

X

 

Perform queries on any table

SELECT on dba_clusters

X

X

     

INSERT, UPDATE, DELETE on target tables

   

X

 

Apply replicated DML to target objects

CREATE TABLE

     

X

Create a checkpoint table in target database

EXECUTE on DBMS_FLASHBACK package

X

X

   

Call DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER

DDL privileges on target objects (if using DDL support)

   

X

 

Issue replicated DDL on target objects

GGS_GGSUSER_ROLE (if using DDL support)

X

X

   

DML privileges on Oracle GoldenGate DDL objects. Role is created by user with SYSDBA privilege during installation of DDL objects.

DELETE on Oracle GoldenGate DDL objects

     

X

Use parameters that maintain Oracle GoldenGate DDL objects

LOCK ANY TABLE

   

X

 

Lock target tables. Only required for initial load using direct bulk load to SQL*Loader.

sys.dbms_internal_clkm

X

     

Replicate Oracle Transparent Data Encryption (TDE)

SELECT ANY TRANSACTION

X

     

Use a newer Oracle ASM API. See Section 11.3, "Mining ASM-stored Logs in Classic Capture Mode".

Privileges granted through dbms_streams_auth.grant_admin_privilege

 

X

   

Interact with database logmining server

EXECUTE on dbms_logmnr_d package

 

X

   

Issue the REGISTER EXTRACT command

Required for Oracle version >= 11.1.0.5 and <= 11.2.0.1.

SELECT FROM sys.logmnr_buildlog

 

X

   

Issue the REGISTER EXTRACT command

Required for Oracle version >= 11.1.0.5 and <= 11.2.0.1.


4.1.4.3 About the dbms_goldengate_auth.grant_admin_privilege Package

Most of the privileges that are needed for Extract and Replicat to operate in classic and integrated mode are granted through the dbms_goldengate_auth.grant_admin_privilege package.

  • The following grants base privileges for Oracle 11.2.0.4 and later. The first example is the default, which grants to both capture and apply. The second shows how to explicitly grant to either capture or apply (in this case, capture).

    grant_admin_privilege('ggadm')
    grant_admin_privilege('ggadm','capture');
    
  • The following grants base privileges for Oracle 11.2.0.3. The first example is the default, which grants to both capture and apply. The second shows how to explicitly grant to capture.

    grant_admin_privilege('ggadm',grant_select_privileges=>true)
    grant_admin_privilege('ggadm','capture',grant_select_privileges=>true)
    

4.1.4.4 Optional Grants for dbms_goldengate_auth.grant_admin_privilege

Additional grants can be added to dbms_goldengate_auth.grant_admin_privilege to support the optional features shown in Table 4-3.

Table 4-3 Optional dbms_goldngate_auth.grant_admin_privilege Grants

Role/Privilege ExtractClassic Mode ExtractIntegrated Mode Replicat Purpose

DV_GOLDENGATE_ADMIN

X

X

X

Capture or apply when Oracle Data Vault is being used.

Also grant DV_GOLDENGATE_REDO_ACCESS if using classic capture with TRANLOGOPTIONS DBLOGREADER on Oracle Data Vault. See Section 11.3, "Mining ASM-stored Logs in Classic Capture Mode."

Also grant Replicat the privileges in DBMS_MACADM.ADD_AUTH_TO_REALM if applying to a realm.

EXEMPT ACCESS POLICY

X

X

X

Capture or apply when Oracle Virtual Private Database is being used.

EXEMPT REDACTION POLICY

X

X

X

Capture or apply redacted data


One or more of these privileges can be granted as a comma-separated list to 'CAPTURE', 'APPLY', or '*'. The default is * (capture and apply).

  • In the following example, the Extract (capture) user ggadm is being granted DV_GOLDENGATE_ADMIN and EXEMPT REDACTION POLICY in addition to the privileges needed for Extract.

    dbms_goldengate_auth.grant_admin_privilege('ggadm','capture', grant_optional_privilege=>'DV_GOLDENGATE_ADMIN,EXEMPT REDACTION POLICY')
    
  • In the following example, the Extract (capture) user ggadm is being granted all optional privileges in addition to the privileges needed for Extract:

    dbms_goldengate_auth.grant_admin_privilege('ggadm','capture', grant_optional_privileges=>'*')
    

For more information about dbms_goldengate_auth.grant_admin_privilege, see Oracle Database PL/SQL Packages and Types Reference.

For more information about Data Vault requirements, see Oracle Database Vault Administrator's Guide.

4.2 Securing the Oracle GoldenGate Credentials

To preserve the security of your data, and to monitor Oracle GoldenGate processing accurately, do not permit other users, applications, or processes to log on as, or operate as, an Oracle GoldenGate database user.

Oracle GoldenGate provides different options for securing the login credentials assigned to Oracle GoldenGate processes. The recommended option is to use a credential store. You can create one credential store and store it in a shared location where all installations of Oracle GoldenGate can access it, or you can create a separate one on each system where Oracle GoldenGate is installed.

The credential store stores the user name and password for each of the assigned Oracle GoldenGate users. A user ID is associated with one or more aliases, and it is the alias that is supplied in commands and parameter files, not the actual user name or password. The credential file can be partitioned into domains, allowing a standard set of aliases to be used for the processes, while allowing the administrator on each system to manage credentials locally.

See Administering Oracle GoldenGate for Windows and UNIX for more information about creating a credential store and adding user credentials.