This document explains the security permissions required for all three Oracle Tools:
- Schema Compare for Oracle
- Data Compare for Oracle
- Source Control for Oracle
The basic security permissions are described in this article:
Permissions required to use Schema Compare for Oracle
However, users may experience an Oracle error ORA-00942 when attempting to perform a comparison that indicates that a Table or View does not exist. This may indicate that the user account specified to connect to the Oracle database does not have security permissions to read the Oracle Data Dictionary and other system objects.
When performing a comparison, the comparison engine queries a number of Data Dictionaries. It will first attempt to connect using dba_<data dictionary name>, for example dba_tables. If this fails, the comparison engine automatically switches to use the all_<data dictionary name>, for example all_tables.
Below is a list off all data dictionary views (or their all_ equivalents) that the comparison engine requires security privileges for:
| dba_attribute_transformations | dba_clu_columns |
| dba_cluster_hash_expressions | dba_clustering_dimensions |
| dba_clustering_keys | dba_clustering_tables |
| dba_col_comments | dba_col_privs |
| dba_cons_columns | dba_cons_obj_columns |
| dba_constraints | dba_db_links |
| dba_dependencies | dba_du_columns |
| dba_external_locations | dba_external_tables |
| dba_ind_columns | dba_ind_partitions |
| dba_ind_subpartitions | dba_indexes |
| dba_indextype_comments | dba_indextype_operators |
| dba_indextypes | dba_join_ind_columns |
| dba_lob_partitions | dba_lob_subpartitions |
| dba_lobs | dba_log_group_columns |
| dba_log_groups | dba_mview_comments |
| dba_mview_logs | dba_mviews |
| dba_nested_table_cols | dba_nested_tables |
| dba_obj_colattrs | dba_objects |
| dba_opancillary | dba_oparguments |
| dba_opbindings | dba_operator_comments |
| dba_operators | dba_part_indexes |
| dba_part_key_columns | dba_part_tables |
| dba_queue_subscribers | dba_queue_tables |
| dba_queues | dba_refs |
| dba_role_privs | dba_sequences |
| dba_snapshots | dba_source |
| dba_subpart_key_columns | dba_subpartition_templates |
| dba_synonyms | dba_sys_privs |
| dba_tab_cols | dba_tab_comments |
| dba_tab_partitions | dba_tab_privs |
| dba_tab_subpartitions | dba_tables |
| dba_triggers | dba_types |
| dba_users | dba_varrays |
| dba_views |
The comparison engine will query the following system tables, for example the sys.snap$ table is used to obtain information on the Materialized Views Reduced Precisions:
| all_users | m_owners |
| sys_ilm$ | sys_ilmobjects |
| sys.objects$ | sys.snap$ |
| sys.sys_fba_period | sys.tab$ |
If after granting select on all_objects to your user or signing in as a different (perhaps dba) user you still get the same problem you may have to check your user’s access to each of these.
Reviewing the verbose log file, you will be able to locate the query experiencing the ORA-00942 and identify the problem Data Dictionary or system table.