Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

UserNotes
Extraction user

CAST highly recommends using a dedicated extraction user with specific privileges in order to access the required data for extraction. When using this dedicated extraction user, CAST will query the DBA_* views to access the required data - these views give access to ALL objects. Please also read the section below entitled "Note about the DBA_ARGUMENTS view and the dedicated extraction user".

To create the dedicated extraction user, please run the following script as the SYS user - it will create the dedicated user and then grant the required privileges to the user (where USER_FOR_EXTRACTION is the dedicated extraction user):

Code Block
languagesql
themeDJango
create user USER_FOR_EXTRACTION identified by cast
/
grant connect to USER_FOR_EXTRACTION
/
grant create session to USER_FOR_EXTRACTION
/
grant select on dba_arguments to USER_FOR_EXTRACTION
/ 
grant select on dba_col_comments to USER_FOR_EXTRACTION
/
grant select on dba_tab_comments to USER_FOR_EXTRACTION
/
/* 	
*   The view dba_mview_comments does not exist on Oracle 9.x, therefore the
*   following grant should not be executed when running the extraction on Oracle 9i.
*/
grant select on dba_mview_comments to USER_FOR_EXTRACTION
/ 
grant select on dba_coll_types to USER_FOR_EXTRACTION
/ 
grant select on dba_cons_columns to USER_FOR_EXTRACTION
/ 
grant select on dba_constraints to USER_FOR_EXTRACTION
/
grant select on dba_db_links to USER_FOR_EXTRACTION
/
grant select on dba_dependencies to USER_FOR_EXTRACTION
/ 
grant select on dba_ind_columns to USER_FOR_EXTRACTION
/
grant select on dba_ind_expressions to USER_FOR_EXTRACTION
/
grant select on dba_indexes to USER_FOR_EXTRACTION
/
grant select on dba_mviews to USER_FOR_EXTRACTION
/
grant select on dba_object_tables to USER_FOR_EXTRACTION
/
grant select on dba_objects to USER_FOR_EXTRACTION
/ 
grant select on dba_procedures to USER_FOR_EXTRACTION
/ 
grant select on dba_sequences to USER_FOR_EXTRACTION
/
grant select on dba_source to USER_FOR_EXTRACTION
/
grant select on dba_synonyms to USER_FOR_EXTRACTION
/
grant select on dba_tab_columns to USER_FOR_EXTRACTION
/
grant select on dba_tables to USER_FOR_EXTRACTION
/ 
grant select on dba_triggers to USER_FOR_EXTRACTION
/  
grant select on dba_types to USER_FOR_EXTRACTION
/
grant select on dba_users to USER_FOR_EXTRACTION
/ 
grant select on dba_views to USER_FOR_EXTRACTION
/

Note about the DBA_USERS view and the dedicated extraction user

Situation

  • If you are using the dedicated extraction user (as recommended and outlined above) to perform your extraction, a view called DBA_USERS is queried to gain access to the data required for extraction
  • If you do not want to grant the select right on this view for security reasons, you can change the script above to use a synonym instead of the a direct grant on the DBA_USERS view

Action

  • Comment the following lines in the above script as follows:
Code Block
languagesql
themeDJango
-- grant select on dba_users to USER_FOR_EXTRACTION
-- /
  • Add two new lines to the script as follows:
Code Block
languagesql
themeDJango
create synonym USER_FOR_EXTRACTION.DBA_USERS for SYS.ALL_USERS
/
  • Re-run the script as the SYS user
  • Use the dedicated extraction user in any future extractions

Impacts

  • There are no impacts - results when using the synonym instead of the grant select on the DBA_USERS view are identical.

Note about DBA_MVIEW_COMMENTS / ALL_MVIEW_COMMENTS views and Oracle 9.x

The views DBA_MVIEW_COMMENTS / ALL_MVIEW_COMMENTS do not exist on Oracle 9.x, therefore when running an extraction on Oracle 9.x in whatever mode (extraction user/SYSTEM/other Oracle user), the extractor will use DBA_TAB_COMMENTS to extract comments on materialized views instead. Results are not impacted. This will typically be displayed in the log as follows:

Code Block
themeDJango
linenumberstrue
Unable to use access mode: Extracting comments on materialized views using DBA_MVIEW_COMMENTS
No access to: DBA_MVIEW_COMMENTS
Unable to use access mode: Extracting comments on materialized views using ALL_MVIEW_COMMENTS. Using ALL_MVIEW_COMMENTS has no impact on results, including AFP.
No access to: ALL_MVIEW_COMMENTS
Using access mode: Extracting comments on materialized views using DBA_TAB_COMMENTS on Oracle 9i

Note about the DBA_ARGUMENTS view and the dedicated extraction user

Situation

  • If you are using the dedicated extraction user (as recommended and outlined above) to perform your extraction, a view called DBA_ARGUMENTS is queried to gain access to the data required for extraction
  • On some Oracle Servers (Oracle 10.2.0.4.0 or any earlier Oracle 10 version and all Oracle 9 versions) this view is not present by default, therefore the following is true:

Impacts

The extraction will succeed, but:

  • Oracle extractor log contains ‘Extraction error: ORA-00942: table or view does not exist’ in section ‘Extracting: Oracle wrapped valid functions...’ ; and:
  • Extraction will be incomplete - i.e. the following information will be missing:
    • IN/OUT parameters will be missing for all procedures and functions outside of the user’s own schema, unless the user has the EXECUTE privilege on those procedures and functions. The same is true for the return code of functions. This will impact the analysis and Quality Rules based on datatypes of functions/procedures/parameters or the return code of functions.

    • IN/OUT parameters will be missing from the source code generated for wrapped procedures or functions, therefore they will not be visible in CAST Enlighten or in the CAST Engineering Dashboard.

    • Return code will be missing from the source code generated for wrapped functions, therefore they will not be visible in CAST Enlighten or in the CAST Engineering Dashboard.

Info
Note that there is no impact on the source code for unwrapped function/procedures because this code is not generated and is instead extracted as is.

Options

Two options exist if you find yourself in this situation:

  1. Manually create the DBA_ARGUMENTS view on the Oracle server prior to running the extraction. To do so, please use this script. Note that the script must be run by the Oracle SYS user. When the view has been created, you can perform an extraction and analysis results will be correct.
  2. If it is not possible to create the DBA_ARGUMENTS view

    , then

    :

    1. then if the "ALL Access" mode is unchecked the extraction will not be possible and will fail

    2. then if the "ALL Access" mode is checked the extraction will be possible but you must accept that the extraction results will be incomplete.

SYSTEM

When it is not possible to use the dedicated extraction user, CAST recommends using the SYSTEM user instead. CAST will query the DBA_* views to access the required data. These views give access to ALL objects.

Other Oracle users

When it is not possible to use the dedicated extraction user or the SYSTEM user as outlined above, it is possible to use any other user (i.e. non-DBA user) to run your extraction - for example: the schema owner user. However, there are several drawbacks to doing this and CAST therefore does not recommend using this type of user:

  • CAST will query the ALL_* views to access the required data -  these views only give access to objects that the user is entitled to access. This means that CAST cannot guarantee that the extraction will contain all the required data.
  • Performance of the extraction will be reduced.
Info

Note also that in order for the ALL_* views to be queried, the ALL access mode option must be explicitly selected in the CAST Delivery Manager Tool:

This option (when selected) explicitly allows the CAST Delivery Manager Tool to query the ALL_* views to access the required data -  these views only give access to objects that the user is entitled to access. This means that CAST cannot guarantee that the extraction will contain all the required data. In addition, performance of the extraction will be reduced.

By default this option is not selected, which automatically prevents the CAST Delivery Manager Tool from using the ALL_* views to access the required data. In other words, if the user name that you have entered into the "User Name" field above does not have sufficient rights to query the DBA_* views then the CAST Delivery Manager Tool will attempt to query the ALL_* views and if the ALL access mode option is not selected, then the extraction will fail.

Note
Please avoid using the SYS user to perform extractions. Results cannot be guaranteed.

...