Introduction
The CAST Delivery Manager Tool provides the means for Delivery Managers to configure a connection to a live Oracle Server/Microsoft SQL Server/Sybase ASE and then use this connection to perform an extraction of the relevant schemas to file using CAST's own SQL extractor. Delivery Managers must ensure that they configure their package with a user that has sufficient rights to perform an extraction, otherwise essential data may not be included. This page provides the required rights for all RDBMS systems supported by CAST for live extraction.
Oracle Server
In order to carry out an extraction of the required schemas, the person in charge of the extraction (Delivery Manager or DBA) must use one of the following Oracle users:
User | Notes |
---|---|
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): 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 userSituation
Action
-- grant select on dba_users to USER_FOR_EXTRACTION -- /
create synonym USER_FOR_EXTRACTION.DBA_USERS for SYS.ALL_USERS /
Impacts
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: 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 userSituation
ImpactsThe extraction will succeed, but:
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. OptionsTwo options exist if you find yourself in this situation:
|
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:
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. Please avoid using the SYS user to perform extractions. Results cannot be guaranteed. |
Microsoft SQL Server
In order to carry out an extraction of the required databases, the person in charge of the extraction (Delivery Manager or DBA) must use a Microsoft SQL Server login (whether using Windows or SQL authentication) that has the following roles and permissions:
User | Required roles and permissions |
---|---|
Any user with the following permissions and roles |
An example script to assign the required role and permissions is shown below: -- Create an SQL Server login CREATE LOGIN <login> WITH PASSWORD = '<password>'; GO -- Create a database user (on all databases that require extraction) for the login created above -- this will also automatically assign the CONNECT + GRANT explicit permission Use <database> GO CREATE USER <user> FOR LOGIN <login>; GO -- Assign the db_datareader database role to the user created above for all databases that require extraction Use <database> GO exec sp_addrolemember 'db_datareader', <user> GO -- Assign your user the View definition + GRANT explicit permission on all databases that require extraction Use <database> GO GRANT View definition TO <user> GO The following screenshots show the same changes performed in the Microsoft SQL Server GUI:
|
Extraction user | When it is not possible to grant a user the "public" Server Role and the "db-datareader" Database Role as outlined above, CAST recommends using a dedicated extraction user with specific privileges in order to access the required data for extraction. An example script to assign the required role and permissions is shown below: -- Create an SQL Server login CREATE LOGIN <login> WITH PASSWORD = '<password>'; GO -- Create a database user (on all databases that require extraction) for the login created above -- this will also automatically assign the CONNECT + GRANT explicit permission Use <database> GO CREATE USER <user> FOR LOGIN <login>; GO -- Issue grant on specific tables GRANT SELECT ON master.dbo.sysdatabases TO <user> GO GRANT SELECT ON master.dbo.spt_values TO <user> GO GRANT SELECT ON master.dbo.syscharsets TO <user> GO GRANT SELECT ON master.dbo.syscurconfigs TO <user> GO GRANT SELECT ON sys.databases TO <user> GO GRANT SELECT ON sys.schemas TO <user> GO GRANT SELECT ON sys.columns TO <user> GO GRANT SELECT ON sys.types TO <user> GO GRANT SELECT ON sys.foreign_keys TO <user> GO GRANT SELECT ON sys.sysforeignkeys TO <user> GO GRANT SELECT ON sys.tables TO <user> GO GRANT SELECT ON sys.foreign_key_columns TO <user> GO GRANT SELECT ON sys.views TO <user> GO GRANT SELECT ON sys.procedures TO <user> GO GRANT SELECT ON sys.numbered_procedures TO <user> GO GRANT SELECT ON sys.objects TO <user> GO GRANT SELECT ON sys.trigger_events TO <user> GO GRANT SELECT ON sys.triggers TO <user> GO GRANT SELECT ON dbo.sysobjects TO <user> GO GRANT SELECT ON dbo.sysusers TO <user> GO GRANT SELECT ON dbo.systypes TO <user> GO GRANT SELECT ON dbo.sysforeignkeys TO <user> GO GRANT SELECT ON dbo.syscomments TO <user> GO --Issue grant select to extraction user on databases that require extraction use <database> GO GRANT VIEW ANY DEFINITION TO <user> GO |
Sybase ASE
In order to carry out an extraction of the required databases, the person in charge of the extraction (Delivery Manager or DBA) must use a Sybase ASE login that has the following roles and permissions:
- CONNECT role
- SELECT permission on the following tables:
- master.dbo.sysdatabases
- master.dbo.spt_values
- master.dbo.syscurconfigs
- For each target database you want to extract, the SELECT permission is required on the following tables:
- [%SQLdatabase%].dbo.sysusers
- [%SQLdatabase%].dbo.sysconstraints
- [%SQLdatabase%].dbo.sysreferences
- [%SQLdatabase%].dbo.sysobjects
- [%SQLdatabase%].dbo.syscolumns
- [%SQLdatabase%].dbo.sysindexes
- [%SQLdatabase%].dbo.syscomments
- [%SQLdatabase%].dbo.systypes