Print Thread
Page 1 of 5 1 2 3 4 5
Compare objects with only select_catalog_role on target user
#36367 11/03/09 04:05 PM
Joined: Nov 2009
Posts: 20
New Jersey, USA
J
Member
OP Offline
Member
J
Joined: Nov 2009
Posts: 20
New Jersey, USA
Hi,

This just maybe to be a limitiation of V8. Here is my problem. I want to compare the objects in a schema in dev with prod. The prod target user, not a schema, has select_catalog_role so I can see all DDL for any user on the prod instance when logged in as target user through the data dictionary dba views. I can not select on any views or tables however. This is to be expected as target user does not have select permission on any schema objects. However, compare from dev does not see the DDL on prod. A blank file is returned when I bring up ExamDiff. Any ideas why compare does not work under the above situtation.

Thanks

Re: Compare objects with only select_catalog_role on target user
Jeff_1947 #36379 11/04/09 12:15 PM
Joined: Aug 1999
Posts: 22,487
Member
Offline
Member
Joined: Aug 1999
Posts: 22,487
I'm not sure I understand. Can you provide an example?


Marco Kalter
Allround Automations
Re: Compare objects with only select_catalog_role on target user
Marco Kalter #36393 11/04/09 04:31 PM
Joined: Nov 2009
Posts: 20
New Jersey, USA
J
Member
OP Offline
Member
J
Joined: Nov 2009
Posts: 20
New Jersey, USA
Here goes.

A user , let's say DDLUSER, on our production server is created with role SELECT_CATALOG_ROLE so this user can select all data dictionary view. Log into a schema, let's say APP1, on development server. Run compare tool which brings up a list of all objects owned by APP1. Then login into target session DDLUSER on production server and set Target Schema to APP1 on Options tab. Select an object on the list and compare. The compare results in differences whether differences exist or not since the object is not found in the target session DDLUSER on the production server. However, when logged into DDLUSER on production the PLSQL dev browser shows all objects on the instance and can view all DDL for these objects. This is Oracle 10.2.0.4.

Hope this helps.


Re: Compare objects with only select_catalog_role on target user
Jeff_1947 #36403 11/05/09 10:43 AM
Joined: Aug 1999
Posts: 22,487
Member
Offline
Member
Joined: Aug 1999
Posts: 22,487
Okay, I understand. Have you enabled the "Use DBA views if available" option (Tools > Preferences > Oracle / Options)?


Marco Kalter
Allround Automations
Re: Compare objects with only select_catalog_role on target user
Marco Kalter #36411 11/05/09 01:44 PM
Joined: Nov 2009
Posts: 20
New Jersey, USA
J
Member
OP Offline
Member
J
Joined: Nov 2009
Posts: 20
New Jersey, USA
Yes it is set to Use DBA Views. Remember the user APP1 which is running the compare does not have access to the DBA views but DDLUSER on the production schema does.


Re: Compare objects with only select_catalog_role on target user
Jeff_1947 #36424 11/06/09 10:35 AM
Joined: Aug 1999
Posts: 22,487
Member
Offline
Member
Joined: Aug 1999
Posts: 22,487
Okay. We will check it out.


Marco Kalter
Allround Automations
Re: Compare objects with only select_catalog_role on target user
Marco Kalter #36546 11/20/09 02:43 PM
Joined: Nov 2009
Posts: 20
New Jersey, USA
J
Member
OP Offline
Member
J
Joined: Nov 2009
Posts: 20
New Jersey, USA
Any update on this

Re: Compare objects with only select_catalog_role on target user
Jeff_1947 #36682 12/04/09 02:59 PM
Joined: Aug 2003
Posts: 63
V
Member
Offline
Member
V
Joined: Aug 2003
Posts: 63
Not sure if this is related, but it looks to me like it can be.
When I go to "Export User Objects" and select some schema - I can see all the objects and successfully export them.
When I go to "Compare User Objects" and select the same schema - I see only a limited subset of objects. To me these sets should be identical. Am I missing something?

Re: Compare objects with only select_catalog_role on target user
vllr #36683 12/04/09 03:55 PM
Joined: Nov 2009
Posts: 20
New Jersey, USA
J
Member
OP Offline
Member
J
Joined: Nov 2009
Posts: 20
New Jersey, USA
I think the problem is that compare objects is using the SYS.ALL_... views and not the SYS.DBA_.... views even if "Ues DBA views if available" option is checked. If you have permission to an object in another schema it will appear in the compare list since it is in the SYS.ALL_... views.

--jeff

Re: Compare objects with only select_catalog_role on target user
Jeff_1947 #36689 12/04/09 08:04 PM
Joined: Aug 2003
Posts: 63
V
Member
Offline
Member
V
Joined: Aug 2003
Posts: 63
I think this is exactly right.
Marco, PLEASE, PLEASE, PLEASE fix it :-) It can simplify greatly our life!

Page 1 of 5 1 2 3 4 5

Moderated by  support 

Link Copied to Clipboard
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.1.33 Page Time: 0.032s Queries: 14 (0.007s) Memory: 2.5621 MB (Peak: 3.0400 MB) Data Comp: Off Server Time: 2024-12-04 06:59:28 UTC
Valid HTML 5 and Valid CSS