Privileges required

Dbvisit Replicate creates a user (also called a schema) with a small repository in the source and target database to keep track of the replication.

The database grants and privileges required by Dbvisit Replicate are generated automatically by running the setup wizard. The setup wizard generates all the necessary scripts that create a new database users and grant all the privileges needed. This means it is very easy to review what actual privileges are necessary prior to actually executing the scripts and issuing the privileges.

The following database privileges are required for Dbvisit Replicate:

Source database privileges

Privilege	Purpose	Revokable
alter user dbvrep quota unlimited on USERS	dbvrep schema stores repository tables and needs quota on the tablespace that you provided.	NO
grant create session to dbvrep	dbvrep needs to be able to log on to the source database.	NO
grant create table to dbvrep	dbvrep schema creates internal tables for our internal data dictionary	NO
grant create view to dbvrep	dbvrep schema creates views for our internal data dictionary	NO
grant create procedure to dbvrep	dbvrep schema creates procedures for our internal data dictionary	NO
grant create sequence to dbvrep	dbvrep schema creates sequences for our internal data dictionary	NO
grant alter database to dbvrep	during initial setup db supplemental logging is added.	After initial setup (all.sh), can be revoked
grant alter system to dbvrep	alter system switch logfile	replicate will work without this grant, but in some special cases may take longer to send a file (does not usually happen)
grant select, flashback on sys.col$ to dbvrep	to load our internal dictionary (we need to translate object and column ids to names and datatypes)	NO
grant select, flashback on sys.tab$ to dbvrep	to load our internal dictionary (we need to translate object and column ids to names and datatypes)	NO
grant select, flashback on sys.obj$ to dbvrep	to load our internal dictionary (we need to translate object and column ids to names and datatypes)	NO
grant select, flashback on sys.user$ to dbvrep	to load our internal dictionary (we need to translate object and column ids to names and datatypes)	NO
grant select, flashback on sys.ind$ to dbvrep	to load our internal dictionary (we need to translate object and column ids to names and datatypes)	NO
grant execute on dbms_flashback to dbvrep	to get current SCN	NO
grant select on v_$log to dbvrep	The dbvrep schema needs to be able to view the Oracle Data Dictionary views to gain information regarding the redologs and archivelogs	NO
grant select on v_$thread to dbvrep	Same as v_$log grants above	NO
grant select on v_$logfile to dbvrep	Same as v_$log grants above	NO
grant select on v_$archived_log to dbvrep	Same as v_$log grants above	NO
grant select on v_$database_incarnation to dbvrep	dbvrep needs to obtain current resetlogs instantiation and SCN	NO
grant select on v_$database to dbvrep	dbvrep needs to obtain current resetlogs instantiation and SCN	NO
grant select on v_$transaction to dbvrep	Monitoring of opened transactions	NO
grant select on gv_$transaction to dbvrep	Monitoring of opened transactions	NO
grant select on gv_$asm_client	Detection if ASM is used, and what ASM instance	NO
grant alter, select, flashback on replicated tables	alter needed for adding supplemental logging	select and flashback can be revoked after LOAD instantiation method (or if it is not used) alter table can be revoked after initial phase - all.sh script is run
grant alter any table to dbvrep	Needed for adding supplemental logging to newly created objects by DDL replication	ONLY granted when DDL replication enabled.
grant lock any table to dbvrep	Prepare needs some safe point in time, when there are no transactions over the tables. In DDL replication needs obtain locks for newly created objects	ONLY granted when DDL replication enabled.

Target database privileges

grant create session
grant create table
grant create view
grant create sequence
grant create procedure
grant execute on dbms_flashback
grant select on v_$database
grant select on v_$mystat
grant select on v_$session
grant select on v_$lock
grant select on sys.user$
grant alter system
grant alter session
grants to insert, update and delete on replicated tables only

DDL replication

If DDL replication is enabled then the following grants are also performed:

Source database

grant alter any table
grant lock any table

Target database

grant select any table
grant update any table
grant insert any table
grant delete any table
grant create any table
grant alter any table
grant drop any table
grant create any index
grant alter any index
grant drop any index
grant create any view
grant drop any view