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
- grant create any procedure
- grant drop any procedure