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)
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.
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
Parent topic: Oracle GoldenGate Users (Database)
Oracle Database Privileges
Privilege | Extract | Replicat All Modes | Purpose |
---|---|---|---|
|
No |
No |
Connect to the database |
|
No |
No |
Required to add the heartbeat table view. |
|
No |
No |
Create objects. In Oracle Database 12cR1 and later, instead of
|
|
No |
No |
Perform administrative changes, such as enabling logging. |
|
No |
No |
Required for multitenant architecture and
|
|
Yes | Yes |
This is required for Autonomous Databases (ATP
and ADW) Extract and Replicat. Extracts in the Root
container ( |
Privileges granted through
|
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
|
No |
No |
See About the dbms_goldengate_auth.grant_admin_privilege Package for more information. |
Grant the following privileges connected as
|
No |
No |
Capture from Data Vault |
Grant Replicat the privileges in
|
No |
No |
Capture from Data Vault |
If DDL replication is performed, grant the following as Database Vault owner:
|
No |
No |
Capture from Data Vault |
|
NA |
No |
Apply replicated DML to target objects |
|
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 |
|
NA |
No |
Lock target tables. Only required for initial load using direct bulk load to SQL*Loader. |
|
No | No |
Allow all privileges to work properly on dictionary tables. |
|
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.
Parent topic: Granting the Appropriate User Privileges
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;
Parent topic: Granting the Appropriate User Privileges
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
.
Parent topic: Granting the Appropriate User Privileges
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.
Parent topic: Establishing Oracle GoldenGate Credentials