GoldenGate 12 Users, Privileges and Security on Multitenant DBs

I. Users and Privileges

With the release of the latest versions of GG and database, there have been many modifications that require changes in how users are created, logins and the privileges granted.

A. Database Users

If the source database is a multitenant container database, the Extract user must be a common user and must log into the root container.

One extract capture process can capture from multiple pluggable databases to a single trail.

A replicat process can apply transactions to only one pluggable database.

B. Privileges

Click the following link to see a list of privileges required for a GoldenGate user on a 11.2.0.4 or greater database.

Users Privileges for GolgenGate Users for DB 11.2.0.4 or Higher

CREATE USER c##ogg IDENTIFIED BY ogg
DEFAULT TABLESPACE ogg_tbsp
TEMPORARY TABLESPACE temp
PROFILE DEFAULT ACCOUNT UNLOCK
CONTAINER=ALL;

ALTER USER c##ogg DEFAULT ROLE ALL CONTAINER=ALL;
ALTER USER c##ogg QUOTA UNLIMITED ON ogg_tbsp CONTAINER=ALL;

GRANT CREATE SESSION TO c##ogg CONTAINER=ALL;
GRANT CONNECT TO c##ogg CONTAINER=ALL;
GRANT RESOURCE TO c##ogg CONTAINER=ALL;
GRANT ALTER SYSTEM TO c##ogg CONTAINER=ALL;
GRANT DBA TO c##ogg CONTAINER=ALL;
GRANT SELECT ANY TRANSACTION TO c##ogg CONTAINER=ALL;
GRANT ALTER ANY TABLE TO c##ogg CONTAINER=ALL;

BEGIN
 dbms_goldengate_auth.grant_admin_privilege
 (
 grantee => 'C##OGG',
 privilege_type => '*',
 grant_select_privileges => TRUE,
 do_grants => TRUE,
 container => 'ALL'
 );
END;
/

C. Logins

1. Capture DB Login

DBLogin UserId c##ogg, Password ogg

2. Pluggable DB Login

DBLogin UserId c##ogg@pdbogg, Password ogg

II. Security

A. Credential Store

The credential store is used to maintain encrypted database passwords, associating an alias with the userid.

ADD CREDENTIALSTORE

ALTER CREDENTIALSTORE ADD USER c##ogg PASSWORD ogg ALIAS ogg_user

ALTER CREDENTIALSTORE ADD USER c##ogg@pdbogg PASSWORD ogg ALIAS pdbogg_user

B. Logins

1. Capture DB Login

DBLOGIN UserIdAlias ogg_user

2. Pluggable DB Login

DBLOGIN UserIdAlias pdbogg_user
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s