3 Establishing Oracle GoldenGate Credentials

Learn how to create database users for the processes that interacts with the database, assign the correct privileges, and secure the credentials from unauthorized use.

Topics

3.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 users for the source and target database instances, each one dedicated to Oracle GoldenGate. The assigned user can be the same user for all the Oracle GoldenGate processes that must connect to a source or target Oracle Database.

See Creating and Populating the Credential Store to learn about creating and using the credential store.

3.1.1 Oracle GoldenGate Users (Database)

A user is required in the source database for the Service Manager in MA or the Manager process in CA 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.

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

3.1.1.1 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 Choosing Capture and Apply Modes.

3.1.1.1.1 Oracle Database Privileges

The following privileges apply to Oracle database.

Privilege Extract Classic Mode Extract Integrated Mode Replicat All Modes Purpose

CREATE SESSION

X

X

X

Connect to the database

RESOURCE

X

X

X

Create objects

In Oracle Database 12cR1 and later, instead of RESOURCE, grant the following privilege:

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

ALTER SYSTEM

X

X

X

Perform administrative changes, such as enabling logging.

ALTER USER

SQL> exec dbms_goldengate_auth.grant_admin_privilege('REPUSER',container=>'PDB1');

X

X

X

Required for multitenant architecture and GGADMIN should be a valid Oracle GoldenGate administrator schema.

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 non-integrated and integrated Replicat, including the database inbound server.

Any or all of optional privileges of dbms_goldengate_auth.grant_admin_privilege

X

X

X

  • Capture from Virtual Private Database

  • Capture redacted data

See About the dbms_goldengate_auth.grant_admin_privilege Package for more information.

  • Grant the following privileges connected as SYS user to the capture and the apply user:

    exec dbms_goldengate_auth.grant_admin_privilege('GGADMIN User','*',
    grant_optional_privileges=>'*');
    grant DV_GOLDENGATE_ADMIN, DV_GOLDENGATE_REDO_ACCESS to GGADMIN USER;
  • Grant Replicat the privileges in DBMS_MACADM.ADD_AUTH_TO_REALM if applying to a realm.

    Connect as Database Vault owner and execute the following sctipts,
    BEGIN
    DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(
    REALM_NAME => 'Oracle Default Component Protection Realm',GRANTEE => 'GGADMIN USER',AUTH_OPTIONS => 1) ;
    END ;
    /
    execute dbms_macadm.authorize_ddl('SYS', 'SYSTEM');
  • If DDL replication is performed please grant the below as Database Vault owner:

    execute dbms_macadm.authorize_ddl
    (‘GGADMIN USER', ‘SCHEMA FOR DDL’);

X

X

X

Capture from Data Vault

INSERT, UPDATE, DELETE on target tables

NA

NA

X

Apply replicated DML to target objects

DDL privileges on target objects (if using DDL support)

NA

NA

X

Issue replicated DDL on target objects

LOCK ANY TABLE

NA

NA

X

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

SELECT ANY TRANSACTION

X

X

NA

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

3.1.1.1.2 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 first example is the default, which grants to both Extract and Replicat. The second shows how to explicitly grant to either Extract or Replicat (in this case, Extract).

grant_admin_privilege('ggadm')
grant_admin_privilege('ggadm','capture');

The following example shows Extract on Oracle 12c Multitenant Database:

BEGIN  
dbms_goldengate_auth.grant_admin_privilege 
(  grantee => 'C##GGADMIN',  privilege_type => 'CAPTURE',  grant_select_privileges => TRUE,  do_grants => TRUE,  container => 'ALL'  ); 
END;
3.1.1.1.3 Optional Grants for dbms_goldengate_auth.grant_admin_privilege

This procedure grants the privileges needed by a user to be a Oracle GoldenGate administrator.

See DBMS_GOLDENGATE_AUTH in Oracle Database PL/SQL Packages and Types Reference

.

3.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 log-in 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 Creating and Populating the Credential Store in Oracle GoldenGate Security Guide for more information about creating a credential store and adding user credentials.