Granting required privileges to Oracle users using SQL scripts in the /samples directory
After installing CDC Replication, you must create the Oracle user and grant privileges to the user that will be used by the CDC Replication with your database before you create the CDC Replication.
You must run the ora-createuser-nodba.sql script located in the /samples directory of your installation of CDC Replication. If you have created a read-only user with your Oracle database, then you must grant required privileges for this user by running the ora-createuser-readonly.sql script.
Privileges for Oracle users
Use this chart to review the types of permissions that are required by Oracle users. After reviewing the permissions, you can proceed to install CDC Replication and locate the required scripts that will grant these permissions in the /samples directory of your installation folder.
| Permissions | Permissions required by CDC Replication for an Oracle user | Permissions required by CDC Replication for an Oracle user with a read-only connection to the source database |
|---|---|---|
| Basic Roles | ||
| grant connect to cdc_user; | ✓ | ✓ |
| grant resource to cdc_user; | ✓ | ✓ |
| grant select_catalog_role to cdc_user; | ✓ | ✓ |
| Table DDL Permissions | ||
| grant create any table to cdc_user; | ✓ | |
| grant alter any table to cdc_user; | ✓ | |
| grant drop any table to cdc_user; | ✓ | |
| grant lock any table to cdc_user; | ✓ | |
| Table DML Permissions | ||
| grant select any table to cdc_user; | ✓ | ✓ |
| grant insert any table to cdc_user; | ✓ | |
| grant update any table to cdc_user; | ✓ | |
| grant delete any table to cdc_user; | ✓ | |
| Index and View DML Permissions | ||
| grant create any index to cdc_user; | ✓ | |
| grant alter any index to cdc_user; | ✓ | |
| grant drop any index to cdc_user; | ✓ | |
| grant create any view to cdc_user; | ✓ | |
| grant drop any view to cdc_user; | ✓ | |
| Trigger DDL and DML Permissions | ||
| grant create any trigger to cdc_user; | ✓ | |
| grant alter any trigger to cdc_user; | ✓ | |
| grant drop any trigger to cdc_user; | ✓ | |
| Sequence DDL and DML Permissions | ||
| grant create any sequence to cdc_user; | ✓ | |
| grant select any sequence to cdc_user; | ✓ | |
| Procedure Permissions | ||
| grant create any procedure to cdc_user; | ✓ | |
| grant execute any procedure to cdc_user; | ✓ | |
| Procedure to perform v_$ on tables | ||
| grant select any dictionary to cdc_user; | ✓ | ✓ |
| General system views | ||
| grant select on sys.v_$database to cdc_user ; | ✓ | ✓ |
| grant select on sys.v_$controlfile to cdc_user ; | ✓ | ✓ |
| grant select on sys.v_$version to cdc_user; | ✓ | ✓ |
| grant select on sys.nls_database_parameters to cdc_user ; | ✓ | ✓ |
| Archive and redo logs | ||
| grant select on sys.v_$log to cdc_user; | ✓ | ✓ |
| grant select on sys.v_$logfile to cdc_user; | ✓ | ✓ |
| grant select on sys.v_$archived_log to cdc_user ; | ✓ | ✓ |
| grant select on sys.v_$log_history to cdc_user ; | ✓ | ✓ |
| Sessions and transactions | ||
| grant alter session to cdc_user; | ✓ | ✓ |
| grant select on sys.v_$session to cdc_user; | ✓ | ✓ |
| grant select on sys.gv_$session to cdc_user ; | ✓ | ✓ |
| grant select on sys.v_$transaction to cdc_user ; | ✓ | ✓ |
| grant select on sys.v_$mystat to cdc_user; | ✓ | ✓ |
| Tables, indexes, columns, and related views | ||
| grant select on sys.all_coll_types to cdc_user ; | ✓ | ✓ |
| grant select on sys.all_type_attrs to cdc_user ; | ✓ | ✓ |
| grant select on sys.dba_tables to cdc_user; | ✓ | ✓ |
| grant select on sys.dba_tab_comments to cdc_user; | ✓ | ✓ |
| grant select on sys.dba_tab_columns to cdc_user ; | ✓ | ✓ |
| grant select on sys.dba_col_comments to cdc_user; | ✓ | ✓ |
| grant select on sys.dba_indexes to cdc_user ; | ✓ | ✓ |
| grant select on sys.dba_ind_columns to cdc_user ; | ✓ | ✓ |
| grant select on sys.all_constraints to cdc_user ; | ✓ | ✓ |
| grant select on sys.dba_constraints to cdc_user ; | ✓ | ✓ |
| grant select on sys.all_cons_columns to cdc_user ; | ✓ | ✓ |
| grant select on sys.dba_cons_columns to cdc_user ; | ✓ | ✓ |
| grant select on sys.tab$ to cdc_user; | ✓ | ✓ |
| grant select on sys.ind$ to cdc_user; | ✓ | ✓ |
| grant select on sys.lob$ to cdc_user; | ✓ | ✓ |
| grant select on sys.col$ to cdc_user; | ✓ | ✓ |
| grant select on sys.icol$ to cdc_user; | ✓ | ✓ |
| grant select on sys.coltype$ to cdc_user; | ✓ | ✓ |
| grant select on sys.attrcol$ to cdc_user; | ✓ | ✓ |
| grant select on sys.ccol$ to cdc_user; | ✓ | ✓ |
| grant select on sys.cdef$ to cdc_user; | ✓ | ✓ |
| Miscellaneous other objects | ||
| grant select on sys.obj$ to cdc_user; | ✓ | ✓ |
| grant select on sys.dba_mviews to cdc_user; | ✓ | ✓ |
| grant select on sys.dba_objects to cdc_user ; | ✓ | ✓ |
| grant select on sys.dba_sequences to cdc_user ; | ✓ | ✓ |
| grant select on sys.hist_head$ to cdc_user; | ✓ | ✓ |
| grant select on sys.resource_cost to cdc_user ; | ✓ | ✓ |
| Storage | ||
| grant select on sys.dba_tablespaces to cdc_user ; | ✓ | ✓ |
| grant select on sys.dba_rollback_segs to cdc_user ; | ✓ | ✓ |
| Permissions | ||
| grant select on sys.dba_users to cdc_user; | ✓ | ✓ |
| grant select on sys.dba_sys_privs to cdc_user; | ✓ | ✓ |
| grant select on sys.dba_tab_privs to cdc_user; | ✓ | ✓ |
| grant select on sys.dba_profiles to cdc_user ; | ✓ | ✓ |
| grant select on sys.dba_roles to cdc_user; | ✓ | ✓ |
| grant select on sys.user$ to cdc_user; | ✓ | ✓ |
| grant select on user_role_privs to cdc_user ; | ✓ | ✓ |
| grant flashback any_table to cdc_user; | ✓ | |