Oracle GoldenGate for Windows and UNIX 12c (12.2.0.1)
First, I am disappointed that Oracle does not go above and beyond to provide SQL scripts to create GoldenGate users for the database.
There are different set of privileges depending on the version of the database:
4.1.4.2 Oracle 11.2.0.3 or Earlier Database Privileges 4.1.4.1 Oracle 11.2.0.4 or Later Database Privileges
PDB is not being used and it’s different for PDB.
Depending on whether you want to practice the least principle privileges, ggadmin user can be create with privileges for both extract (capture) and replicat (apply).
Please don’t forget to change the password from the script since it is hard coded to be same as username :=)
cr_ggadmin_12c.sql
-- 4.1.4.1 Oracle 11.2.0.4 or Later Database Privileges
set echo on lines 200 pages 1000 trimspool on tab off
define _username='GGADMIN'
-- grant privileges for capture
create user &_username identified by &_username default tablespace ggdata;
select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='&_username';
grant create session, connect, resource, alter any table, alter system, dba, select any transaction to &_username;
-- grant privileges for replicat
grant create table, lock any table to &_username;
-- grant both capture and apply
exec dbms_goldengate_auth.grant_admin_privilege('&_username')
-- grant capture
-- exec dbms_goldengate_auth.grant_admin_privilege('&_username','capture');
-- grant apply
-- exec dbms_goldengate_auth.grant_admin_privilege('&_username','apply');
Demo:
oracle@arrow:tiger:/media/sf_working/ggs
$ sysdba @cr_ggadmin_12c.sql
SQL*Plus: Release 11.2.0.4.0 Production on Sat Apr 9 07:06:41 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ARROW:(SYS@tiger):PRIMARY> define _username='GGADMIN'
ARROW:(SYS@tiger):PRIMARY> -- grant privileges for capture
ARROW:(SYS@tiger):PRIMARY> create user &_username identified by &_username default tablespace ggdata;
User created.
ARROW:(SYS@tiger):PRIMARY> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='&_username';
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------
GGDATA TEMP
ARROW:(SYS@tiger):PRIMARY> grant create session, connect, resource, alter any table, alter system, dba, select any transaction to &_username;
Grant succeeded.
ARROW:(SYS@tiger):PRIMARY> -- grant privileges for replicat
ARROW:(SYS@tiger):PRIMARY> grant create table, lock any table to &_username;
Grant succeeded.
ARROW:(SYS@tiger):PRIMARY> -- grant both capture and apply
ARROW:(SYS@tiger):PRIMARY> exec dbms_goldengate_auth.grant_admin_privilege('&_username')
PL/SQL procedure successfully completed.
ARROW:(SYS@tiger):PRIMARY> -- grant capture
ARROW:(SYS@tiger):PRIMARY> -- exec dbms_goldengate_auth.grant_admin_privilege('&_username','capture');
ARROW:(SYS@tiger):PRIMARY> -- grant apply
ARROW:(SYS@tiger):PRIMARY> -- exec dbms_goldengate_auth.grant_admin_privilege('&_username','apply');
ARROW:(SYS@tiger):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:tiger:/media/sf_working/ggs
$
Thinking Out Loud 