I have a case open with Rational on the same issue. Below is an email on how they are determining the schema list. I don't think that it is the correct way to do it, because I don't have any entries in the table for the schema of the user I'm logged into. And when I try to grant something to myself, Oracle says I can't do that. I think it is a bug in the data modeler. I think if you can select from the schema using oracle security, you should be able to reverse engineer it.
> ................................................................. > You NEED to have access to at least one table within the schema > you are trying to reverse engineer in order for this schema to > show up as a selection in the Data Modeler reverse engineering > wizard. Having DBA role has nothing to do with whether you see > a particular schema or not. > > The Data Modeler wizard sends the following SQL string to > populate the schema you can select here: > > select * from all_tab_privs where grantee='TheLoginUserName' or > table_schema <> 'SYS' and table_schema <> 'SYSTEM' and table_schema > <> 'ORDSYS' and table_schema <> 'MDSYS'; > > > For example, with user test (password test), if we issue this > command in SQL Plus we see these results: > > ******************************************************************* > SQL> connect test/test; > Connected. > SQL> > SQL> select * from all_tab_privs where grantee='TheLoginUserName' or > 2 table_schema <> 'SYS' and table_schema <> 'SYSTEM' and table_schema > 3 <> 'ORDSYS' and table_schema <> 'MDSYS'; > > GRANTOR GRANTEE TABLE_SCHEMA > ------------------------------ ------------------------------ ------------- > TABLE_NAME PRIVILEGE GRA > ------------------------------ ---------------------------------------- --- > DEMO PUBLIC DEMO > LOCATION SELECT NO > > DEMO PUBLIC DEMO > DEPARTMENT SELECT NO > > DEMO PUBLIC DEMO > JOB SELECT NO > > DEMO PUBLIC DEMO > EMPLOYEE SELECT NO > > DEMO PUBLIC DEMO > SALARY_GRADE SELECT NO > > DEMO PUBLIC DEMO > PRODUCT SELECT NO > > DEMO PUBLIC DEMO > > GRANTOR GRANTEE TABLE_SCHEMA > ------------------------------ ------------------------------ ------------- > TABLE_NAME PRIVILEGE GRA > ------------------------------ ---------------------------------------- --- > PRICE SELECT NO > > DEMO PUBLIC DEMO > CUSTOMER SELECT NO > > DEMO PUBLIC DEMO > SALES_ORDER SELECT NO > > DEMO PUBLIC DEMO > ITEM SELECT NO > > DEMO PUBLIC DEMO > SALES SELECT NO > > 11 rows selected. > *******************************************************************> > > > And in Rose Data Modeler Reverse engineering wizard, you have only > two schemas you can select from: > > [ ] DEMO > [ ] TEST > > If we GRANT SELECT to a Role and assign that Role to Public, > we will get the additional Schema TEST2. > > For example (NOTE: there are several ways to grant access to > objects in schemas, including using the Schema Manager, this > is just one simple example): > > > ******************************************************************* > SQL> connect system/manager; > Connected. > SQL> create role testrole; > > Role created. > > SQL> connect test2/test2; > Connected. > SQL> grant select on TABLE_1 to testrole; > > Grant succeeded. > > SQL> connect system/manager; > Connected. > SQL> grant testrole to public; > > Grant succeeded. > > SQL> connect test/test; > Connected. > SQL> > SQL> select * from all_tab_privs where grantee='TheLoginUserName' or > 2 table_schema <> 'SYS' and table_schema <> 'SYSTEM' and table_schema > 3 <> 'ORDSYS' and table_schema <> 'MDSYS'; > > GRANTOR GRANTEE TABLE_SCHEMA > ------------------------------ ------------------------------ > ------------------------------ > TABLE_NAME PRIVILEGE GRA > ------------------------------ ---------------------------------------- --- > DEMO PUBLIC DEMO > LOCATION SELECT NO > > DEMO PUBLIC DEMO > DEPARTMENT SELECT NO > > DEMO PUBLIC DEMO > JOB SELECT NO > > DEMO PUBLIC DEMO > EMPLOYEE SELECT NO > > DEMO PUBLIC DEMO > SALARY_GRADE SELECT NO > > DEMO PUBLIC DEMO > PRODUCT SELECT NO > > DEMO PUBLIC DEMO > > GRANTOR GRANTEE TABLE_SCHEMA > ------------------------------ ------------------------------ > ------------------------------ > TABLE_NAME PRIVILEGE GRA > ------------------------------ ---------------------------------------- --- > PRICE SELECT NO > > DEMO PUBLIC DEMO > CUSTOMER SELECT NO > > DEMO PUBLIC DEMO > SALES_ORDER SELECT NO > > DEMO PUBLIC DEMO > ITEM SELECT NO > > DEMO PUBLIC DEMO > SALES SELECT NO > > TEST2 TESTROLE TEST2 > TABLE_1 SELECT NO > > 12 rows selected. > ******************************************************************* > > > There is now an additional row returned above, TEST2 TABLE_1. > > And in Rose Data Modeler Reverse engineering wizard, you now have > three schemas you can select from: > > [ ] DEMO > [ ] TEST > [ ] TEST2 > > As long as you have access to at least one table, if you reverse > engineer TEST2, you will get ALL Tables, Primary keys, etc. > ...................................................................> > > > > ---------- > From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] > Reply To: [EMAIL PROTECTED] > Sent: Wednesday, October 03, 2001 5:38 PM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: (ROSE) RE: E: Oracle db - Analyze Schema Vs. Reverse Engineer problem > > > When I do the Data Modeler > Reverse Engineer menu item the wizard pops > up. I enter the database info then a schema selection box pops up, but > none of the schema's on the oracle database are listed. > > > > -----Original Message----- > From: dgornik [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, October 03, 2001 4:23 PM > To: Jason.Richardson; rose.forum > Cc: dgornik > Subject: (ROSE) RE: Oracle db - Analyze Schema Vs. Reverse Engineer > problem > > > > Here you go: > > Step 2: Tools > Data Modeler > Reverse Engineer > Step 3: Open the model browser, right-click on Schemas, select "Data > Modeler > -> Transform to Object Model..." and follow the wizard > You will get a package with your Java classes. > > Regards, > > Davor Gornik > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, October 03, 2001 2:14 PM > To: [EMAIL PROTECTED] > Subject: Oracle db - Analyze Schema Vs. Reverse Engineer problem > > Overall goal to create Java classes from the oracle database tables. > Here is the steps I was told to take in order to accomplish it. > > 1. set the default language to Java > 2. reverse engineer Oracle to data model > 3. convert data model to object model > > > Situation: > > For step 1- > - default language in options is set to Java, it is a new Oracle 8 > project. > > For step 2. (not sure which to do so I tried both... which of the below > is the correct way?) > From the menu... > > Tools > Oracle > Analyze Schema - this works fine I can retrieve the > schema I want from the database - but I can't convert this to java > > Tools > Data Modeler > Reverse Engineer - I select the oracle db from > the list but the schema selection box is blank. I clicked the test > connection button and it says I am connected. > > > > I hope this is enough information to explain my situation > > Jason Richardson > > > ************************************************************************ > * Rose Forum is a public venue for ideas and discussions. > * For technical support, visit http://www.rational.com/support > * > * Admin.Subscription Requests: [EMAIL PROTECTED] > * Archive of messages: > http://www.rational.com/support/usergroups/rose/rose_forum.jsp > * Other Requests: [EMAIL PROTECTED] > * > * To unsubscribe from the list, please send email > * > * To: [EMAIL PROTECTED] > * Subject:<BLANK> > * Body: unsubscribe rose_forum > * > ************************************************************************ > * > > > > ************************************************************************ > * Rose Forum is a public venue for ideas and discussions. > * For technical support, visit http://www.rational.com/support > * > * Admin.Subscription Requests: [EMAIL PROTECTED] > * Archive of messages: http://www.rational.com/support/usergroups/rose/rose_forum.jsp > * Other Requests: [EMAIL PROTECTED] > * > * To unsubscribe from the list, please send email > * > * To: [EMAIL PROTECTED] > * Subject:<BLANK> > * Body: unsubscribe rose_forum > * > ************************************************************************* > ************************************************************************ * Rose Forum is a public venue for ideas and discussions. * For technical support, visit http://www.rational.com/support * * Admin.Subscription Requests: [EMAIL PROTECTED] * Archive of messages: http://www.rational.com/support/usergroups/rose/rose_forum.jsp * Other Requests: [EMAIL PROTECTED] * * To unsubscribe from the list, please send email * * To: [EMAIL PROTECTED] * Subject:<BLANK> * Body: unsubscribe rose_forum * *************************************************************************
