The metadata extraction process for Oracle is always read-only. It does not modify data and therefore requires fewer permissions than users who need read/write access.
Required permissions
- Option 1: Recommended method
Grant the user permission to create a session and assign the built-in role
SELECT_CATALOG_ROLE:
GRANT CREATE SESSION TO <user>;
GRANT SELECT_CATALOG_ROLE TO <user>;This role provides sufficient privileges to query system catalog views and extract metadata.
- Option 2: Alternative (individual grants)
If you do not wish to use SELECT_CATALOG_ROLE, the
user can be explicitly granted SELECT on all required system
tables and views used in metadata extraction:
GRANT CREATE SESSION TO <user>;
GRANT SELECT ON all_users TO <user>;
GRANT SELECT ON dba_objects TO <user>;
GRANT SELECT ON dba_tab_comments TO <user>;
GRANT SELECT ON dba_external_locations TO <user>;
GRANT SELECT ON dba_directories TO <user>;
GRANT SELECT ON dba_mviews TO <user>;
GRANT SELECT ON dba_clu_columns TO <user>;
GRANT SELECT ON dba_tab_columns TO <user>;
GRANT SELECT ON dba_col_comments TO <user>;
GRANT SELECT ON dba_constraints TO <user>;
GRANT SELECT ON dba_cons_columns TO <user>;
GRANT SELECT ON dba_indexes TO <user>;
GRANT SELECT ON dba_ind_columns TO <user>;
GRANT SELECT ON dba_procedures TO <user>;
GRANT SELECT ON dba_synonyms TO <user>;
GRANT SELECT ON dba_views TO <user>;
GRANT SELECT ON dba_source TO <user>;
GRANT SELECT ON dba_triggers TO <user>;
GRANT SELECT ON dba_arguments TO <user>;
GRANT SELECT ON dba_sequences TO <user>;
GRANT SELECT ON dba_dependencies TO <user>;
GRANT SELECT ON V_$INSTANCE TO <user>;
GRANT SELECT ON v_$database TO <user>;-
Option 1 (using
SELECT_CATALOG_ROLE) is simpler and generally preferred. -
Option 2 provides more granular control but requires significantly more setup and maintenance.
-
Consult your Database Administrator (DBA) to decide which approach best fits your security policies.
- The permission requirements described here are subject to change depending on the database version, configuration, and future updates.