Permissions required to use Schema Compare for Oracle
Published 26 July 2017
Selecting schemas to compare
On the Project Configuration dialog box, all schemas in a database are available to select from the Schemas list:
You don't require any privileges to select schemas for comparison.
If possible, the owner of the schema has all the privileges needed to compare everything.
If you are not the owner of the schemas you want to compare, the following privileges are required to compare objects:
- Ideally system or sysdba privileges provide the best option as provides access to all the dba_dictionaries/views.
- If system or sysdba user is not an option, the SELECT_CATALOG_ROLE provides all the permissions needed for the comparison and does not grant any access to the data, so very secure.
- SELECT ANY DICTIONARY permissions, similar to SELECT_CATALOG_ROLE. However in testing we found being granted the SELECT_CATALOG_ROLE the better option.
- Non-PL/SQL objects (for example TABLES, VIEWS, SYNONYMS) require the
- PL/SQL objects (for example PROCEDURES, FUNCTIONS, PACKAGES) require the
If you have insufficient privileges to compare an object, it isn't displayed in the comparison results.
To run a deployment script created by Schema Compare for Oracle, the following system privileges may be required:
ALTER <OBJECT TYPE>or
ALTER ANY <OBJECT TYPE>to alter objects
ALTER ANY PROCEDURE
CREATE <OBJECT TYPE>or
CREATE ANY <OBJECT TYPE>to create objects
DROP <OBJECT TYPE>or
DROP ANY <OBJECT TYPE>to drop objects
GRANT GRANT ANY OBJECT PRIVILEGE
- If you have insufficient privileges to perform the actions in the deployment script, the script will fail.
- To deploy using Schema Compare for Oracle, we recommended you have DBA privileges.
- If you create a deployment script to run later, we recommended you run the script as the schema owner.
Users may experience an Oracle error ORA-00942 when attempting to perform a comparison that informs 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 Oracle 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 to (grouped by type, to be able to compare for example Tables comparison engine needs access to more "dba_" dictionary views than just "dba_tables"):
If option "lobs and varrays" is selected:
If option "tablespace and partitioning" is selected:
If clustering is available and database version is 12c or higher:
If queues tables are available:
If database version is 9i:
If identity columns are available and database version is 12c or higher :
If database version is 9i:
Index joined columns
Materialized View Logs
PL/SQL objects (function, procedure, package, package body)
Synonym and public synonym
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:
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.