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

• 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.
• 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.

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.

• Oracle GoldenGate Users (Database)
  

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:

• RMAN log retention, see Log Retention Options.

• TDE support, see Configuring Oracle TDE Data in Classic Capture Mode.

• ASM, see Mining ASM-stored Logs in Classic Capture Mode.

• Granting the Appropriate User Privileges
  

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.

• Oracle Database Privileges
  
• About the dbms_goldengate_auth.grant_admin_privilege Package
  
• Optional Grants for dbms_goldengate_auth.grant_admin_privilege
  

Oracle Database Privileges

The following privileges apply to Oracle database.

Privilege	Extract	Replicat All Modes	Purpose
CREATE SESSION	No	No	Connect to the database
CREATE VIEW	No	No	Required to add the heartbeat table view.
RESOURCE	No	No	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	No	No	Perform administrative changes, such as enabling logging.
ALTER USER	No	No	Required for multitenant architecture and GGADMIN should be a valid Oracle GoldenGate administrator schema.
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE ('REPUSER', CONTAINER=>'PDB1');	Yes	Yes	This is required for Autonomous Databases (ATP and ADW) Extract and Replicat. Extracts in the Root container (CDB$ROOT)) might require a value of ALL or a specific PDB.
Privileges granted through DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE	No	No	(Extract) Grants privileges for 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	No	No	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 Extract and Replicat users: EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggadmin user','*',GRANT_OPTIONAL_PRIVILEGES=>'*'); GRANT DV_GOLDENGATE_ADMIN, DV_GOLDENGATE_REDO_ACCESS to 'ggadmin user';	No	No	Capture from Data Vault
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');	No	No	Capture from Data Vault
If DDL replication is performed, grant the following as Database Vault owner: EXECUTE DBMS_MACADM.AUTHORIZE_DDL (‘GGADMIN USER', ‘SCHEMA FOR DDL’);	No	No	Capture from Data Vault
INSERT, UPDATE, DELETE on target tables	NA	No	Apply replicated DML to target objects
GRANT INSERT ANY TO..., GRANT UPDATE ANY TO... and GRANT DELETE ANY TO...	NA	No	Use these privileges for the Replicat user, instead of granting INSERT, UPDATE, DELETE to every table, if replicating every table.
DDL privileges on target objects (if using DDL support)	NA	No	Issue replicated DDL on target objects
LOCK ANY TABLE	NA	No	Lock target tables. Only required for initial load using direct bulk load to SQL*Loader.
SELECT ANY DICTIONARY	No	No	Allow all privileges to work properly on dictionary tables.
SELECT ANY TRANSACTION	No	NA	Use a newer Oracle ASM API.

Here's an example of the list of permissions granted for the Oracle database root container:

DROP USER c##ggadmin CASCADE;
CREATE USER c##ggadmin IDENTIFIED BY passw0rd CONTAINER=all DEFAULT
TABLESPACE GG_DATA TEMPORARY TABLESPACE temp;
ALTER USER c##ggadmin SET CONTAINER_DATA=all CONTAINER=current;
GRANT CREATE SESSION to c##ggadmin;
GRANT CREATE VIEW to c##ggadmin;
GRANT CONNECT to c##ggadmin CONTAINER=all;
GRANT RESOURCE to c##ggadmin;
GRANT ALTER SYSTEM to c##ggadmin ;
GRANT SELECT ANY DICTIONARY to c##ggadmin ;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('c##ggadmin');
ALTER USER c##ggadmin QUOTA unlimited ON GG_DATA;

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='c##ggadmin' ORDER BY 2;

In this example, DBA privilege is not provided but the user will be able to access the DBA_SYS_PRIVS package.

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;

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

.

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.