1 2 Previous Next 15 Replies Latest reply: Aug 23, 2012 2:42 PM by David Last-Oracle RSS

    reverse engineer did not import grants

    user1577093
      Hi you gurus out there :-)
      I tried to reverse engineer a database into Oracle DM and nearly everything landed in the model, except all the individual grants (reference grants on table or columns, select, insert, update , delete)
      Is this normal ?
      Since there are thousands of grants, it is not going to be at all easy to add them manually.
      Is there a bug or did I miss something somewhere ?
        • 1. Re: reverse engineer did not import grants
          David Last-Oracle
          Hi,

          What version of Data Modeler are you using?

          I assume you are importing from an Oracle database. Are you connecting to it using a standard Oracle connection (rather than a JDBC connection)?

          In addition to the Tables, you also need to include the relevant Users and Roles when specifying the objects to import.

          The grants should then be imported, provided the user you are using for your connection has the privilege to access this information from the database.

          David
          • 2. Re: reverse engineer did not import grants
            user1577093
            I am using the latest version available 3.1.1.703 and yes, I am importing from an Oracle database 11.2.0.3.1
            • 3. Re: reverse engineer did not import grants
              user1577093
              I think the problem comes earlier on in the reverse engineer process.
              I tried with a small fake database with 2 users, some tables, some roles and some table grants. I reverse engineered those and the grants were imported.
              Good I thought !

              BUT .... BUT .... BUT

              When I import the complete real database (20 users, 350 tables, 500 synonyms, .... ), it gives an error and I see that a bunch of things are missing, such as grants and synonyms.

              I ran the import as user SYS and selected the list of users, then on the next screen with the various tabs, I go through every tab, carefully selecting all the objects that belong to the the users I selected in the first screen (and that selected again in this list of tabs).
              Before I click finish, I see a summary list of the things it will import. I guess it is correct, I didn't count them.
              Then I click finish and it goes on for a while until it says that there is an error.
              The log file gives a collection of errors referring to what looks appears to be partitioned tables like this :
              2012-08-13 15:02:10,007 [Thread-10] ERROR MOHTableOracle - error in DEF_MIN_EXTENTS
              java.lang.NullPointerException
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setPartitionInfo(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.generate(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.DBMExtractionController$EmptyRunner.run(Unknown Source)
                   at java.lang.Thread.run(Thread.java:662)
              2012-08-13 15:02:10,007 [Thread-10] ERROR MOHTableOracle - error in DEF_PCT_INCREASE
              java.lang.NullPointerException
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setPartitionInfo(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.generate(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.DBMExtractionController$EmptyRunner.run(Unknown Source)
                   at java.lang.Thread.run(Thread.java:662)
              2012-08-13 15:02:10,067 [Thread-10] ERROR MOHTableOracle - error in DEF_INITIAL_EXTENT
              java.lang.NullPointerException
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setPartitionInfo(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.generate(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.DBMExtractionController$EmptyRunner.run(Unknown Source)
                   at java.lang.Thread.run(Thread.java:662)
              2012-08-13 15:02:10,067 [Thread-10] ERROR MOHTableOracle - error in DEF_NEXT_EXTENT
              java.lang.NullPointerException
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setPartitionInfo(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.generate(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.DBMExtractionController$EmptyRunner.run(Unknown Source)
                   at java.lang.Thread.run(Thread.java:662)
              2012-08-13 15:02:10,067 [Thread-10] ERROR MOHTableOracle - error in DEF_MIN_EXTENTS
              java.lang.NullPointerException
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setPartitionInfo(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.generate(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.DBMExtractionController$EmptyRunner.run(Unknown Source)
                   at java.lang.Thread.run(Thread.java:662)
              2012-08-13 15:02:10,067 [Thread-10] ERROR MOHTableOracle - error in DEF_PCT_INCREASE
              java.lang.NullPointerException
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setPartitionInfo(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.generate(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.DBMExtractionController$EmptyRunner.run(Unknown Source)
                   at java.lang.Thread.run(Thread.java:662)
              2012-08-13 15:02:10,077 [Thread-10] ERROR MOHTableOracle - error in DEF_INITIAL_EXTENT
              java.lang.NullPointerException
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setPartitionInfo(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.generate(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.DBMExtractionController$EmptyRunner.run(Unknown Source)
                   at java.lang.Thread.run(Thread.java:662)
              2012-08-13 15:02:10,077 [Thread-10] ERROR MOHTableOracle - error in DEF_NEXT_EXTENT
              java.lang.NullPointerException
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setPartitionInfo(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.generate(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.DBMExtractionController$EmptyRunner.run(Unknown Source)
                   at java.lang.Thread.run(Thread.java:662)
              2012-08-13 15:02:10,077 [Thread-10] ERROR MOHTableOracle - error in DEF_MIN_EXTENTS
              java.lang.NullPointerException
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setPartitionInfo(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.generate(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.DBMExtractionController$EmptyRunner.run(Unknown Source)
                   at java.lang.Thread.run(Thread.java:662)
              2012-08-13 15:02:10,077 [Thread-10] ERROR MOHTableOracle - error in DEF_PCT_INCREASE
              java.lang.NullPointerException
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setPartitionInfo(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.generate(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.DBMExtractionController$EmptyRunner.run(Unknown Source)
                   at java.lang.Thread.run(Thread.java:662)
              2012-08-13 15:02:10,117 [Thread-10] ERROR MOHTableOracle - error in DEF_INITIAL_EXTENT
              java.lang.NullPointerException
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setPartitionInfo(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.generate(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.DBMExtractionController$EmptyRunner.run(Unknown Source)
                   at java.lang.Thread.run(Thread.java:662)
              2012-08-13 15:02:10,117 [Thread-10] ERROR MOHTableOracle - error in DEF_NEXT_EXTENT
              java.lang.NullPointerException
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setPartitionInfo(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.generate(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.DBMExtractionController$EmptyRunner.run(Unknown Source)
                   at java.lang.Thread.run(Thread.java:662)
              2012-08-13 15:02:10,117 [Thread-10] ERROR MOHTableOracle - error in DEF_MIN_EXTENTS
              java.lang.NullPointerException
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setPartitionInfo(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.generate(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.DBMExtractionController$EmptyRunner.run(Unknown Source)
                   at java.lang.Thread.run(Thread.java:662)
              2012-08-13 15:02:10,117 [Thread-10] ERROR MOHTableOracle - error in DEF_PCT_INCREASE
              java.lang.NullPointerException
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setPartitionInfo(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.generate(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.DBMExtractionController$EmptyRunner.run(Unknown Source)
                   at java.lang.Thread.run(Thread.java:662)
              2012-08-13 15:02:10,597 [Thread-10] ERROR MOHTableOracle - error in DEF_INITIAL_EXTENT
              java.lang.NullPointerException
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setPartitionInfo(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.generate(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.DBMExtractionController$EmptyRunner.run(Unknown Source)
                   at java.lang.Thread.run(Thread.java:662)
              2012-08-13 15:02:10,597 [Thread-10] ERROR MOHTableOracle - error in DEF_NEXT_EXTENT
              java.lang.NullPointerException
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setPartitionInfo(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.generate(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.DBMExtractionController$EmptyRunner.run(Unknown Source)
                   at java.lang.Thread.run(Thread.java:662)
              2012-08-13 15:02:10,597 [Thread-10] ERROR MOHTableOracle - error in DEF_MIN_EXTENTS
              java.lang.NullPointerException
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setPartitionInfo(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.generate(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.DBMExtractionController$EmptyRunner.run(Unknown Source)
                   at java.lang.Thread.run(Thread.java:662)
              2012-08-13 15:02:10,597 [Thread-10] ERROR MOHTableOracle - error in DEF_PCT_INCREASE
              java.lang.NullPointerException
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setPartitionInfo(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.generate(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(Unknown Source)
                   at oracle.dbtools.crest.imports.metadata.DBMExtractionController$EmptyRunner.run(Unknown Source)
                   at java.lang.Thread.run(Thread.java:662)
              2012-08-13 15:02:11,097 [Thread-10] ERROR AbstractDBMExtractionHandler - java.lang.NullPointerException

              So, it obviously had some problems and it also reports 700+ statements that were not processed.
              Since 700 is far more than the errors reported i n the logfile, I conclude that some of the errors it ran into caused other things not to get done such as grants perhaps.

              I'd like to avoid the errors I described above to have a successful import of the whole model.
              Can anyone help me to identify what is causing the problem ? The error message does not really tell me what object failed and what problem there was iwth the attribute in question.
              • 4. Re: reverse engineer did not import grants
                Philip Stoyanov-Oracle
                Hi,

                thanks for posting the log. I logged a bug for that.

                Philip
                • 5. Re: reverse engineer did not import grants
                  user1577093
                  Hi Phil, thanks !
                  Any idea how I can determine which objects are causing the probelm so I can eliminate them and -hopefully- get all the rest imported ?
                  The hundreds of missing elements is not reasonable to add manually.
                  So any help here will be more than welcome !
                  • 6. Re: reverse engineer did not import grants
                    David Last-Oracle
                    Hi,

                    The log has identified a bug in importing some partitioning details for Tables.
                    The Tables imported should be OK, except that some of the default storage characteristics (INITIAL_EXTENT, PCT_INCREASE, etc.) of partitioned Tables may be incompletely imported.

                    However this bug will not affect the import of grants or synonyms, and so does not explain why these are not being imported.

                    Is there anything else of significance later in the log following all these
                    java.lang.NullPointerException
                    at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                    entries?

                    Note that when selecting the objects to import in step 3 of the Data Dictionary Import Wizard, you should select all the Users and Roles that privileges are being granted to. To be on the safe side, I would suggest that you select all the available Users and Roles.

                    David
                    • 7. Re: reverse engineer did not import grants
                      user1577093
                      OK, so this time I selected all users and roles, even the ones that are not part of my application, in addition to all tables, synonyms, sequences, .... as I did before.
                      I hoped to attach screenshots o fthe various steps, but that's not allowed I see.
                      Basically at the end of the import it says that there were 1429 statements in total, 420 were processed, 0 failed and 1009 not recognized :-(
                      The resulting physical model has no sequences and no synonyms and no grants, yet there plenty in the original database.
                      As I wrote earlier, it gives me the impression that the failure at some point in the import process causes the whole thing to just stop right where it failed.
                      • 8. Re: reverse engineer did not import grants
                        David Last-Oracle
                        Hi,

                        Yes, it does sound like something has caused the whole thing to stop.

                        Is there nothing in the log, immediately after the last of the
                        java.lang.NullPointerException
                        at oracle.dbtools.crest.imports.metadata.oracle.MOHTableOracle.setDefProperties(Unknown Source)
                        stack track messages, that indicates the reason for the 1009 not recognized objects?

                        It sounds to me like it has probably processed all the Tables, but failed before processing the Sequences.
                        In this part of the import it processes
                        Bitmap Join Indexes
                        Object columns (i.e. columns defined using a Type), Nested Tables and REF Columns
                        Views
                        Stored Procedures
                        Dimensions

                        So, are you expecting some of these objects, and are they imported?
                        Do you have any Bitmap Join Indexes?
                        (If you're not sure, you could try something like
                        SELECT * FROM dba_indexes where join_index = 'YES';
                        to check.)

                        David
                        • 9. Re: reverse engineer did not import grants
                          user1577093
                          There are no join indexes.
                          I moved on a bit further and was able to identify one object that causes the failure.
                          It doesn't look any different from the other tables, except that for some strange reason, it has a hash partitioned PK index when the table itself is not partitioned.
                          I created the table in a separate schema and imported into DM just that schema to verify this.
                          With the partitioned PK, it crashed.
                          When I recreate the table without partitioning the PK, it doesn't crash on that table anymore.
                          Other tables further down cause the DM import to crash. I don't think they all have the same problem, but I now need to find out which tables are causing the crashes.
                          • 10. Re: reverse engineer did not import grants
                            scw2wi
                            I had a similar problem with DM 3.1.1.703 and oracle 11g.

                            Grants for UPDATE and SELECT are imported from data dictionary,
                            grants for INSERT and DELETE where not imported.

                            It looks as if the import of grants does not work very stable.

                            Walter
                            • 11. Re: reverse engineer did not import grants
                              user1577093
                              Hi Walter
                              The problem I have here is a bit more widespread.
                              It looks like all tables that have a globally partitioned PK on a table cause DM import to crash.
                              As a result, no grants (at all), no sequences and no synonyms are imported.
                              • 12. Re: reverse engineer did not import grants
                                David Last-Oracle
                                Hi,

                                There does indeed seem to be a problem with importing a globally partitioned PK from the database in release 3.1.1.703.
                                I have logged a bug on this.

                                This causes a message to be displayed:
                                An error occured while importing from database.
                                See the log file for more details.
                                And the log file contains a line like:
                                2012-08-20 13:50:41,335 [Thread-9] ERROR AbstractDBMExtractionHandler - java.lang.NullPointerException

                                Although the Tables are all imported (but with some partitioning details missing), some other objects (Sequence, Views, grants, Synonyms, etc.) will be completely missing.

                                Until this is fixed, I suggest you try to identify the problem tables and omit these from the initial import. You could then import the remaining tables to at least get the basic definition of these Tables in.

                                David
                                • 13. Re: reverse engineer did not import grants
                                  user1577093
                                  The problem is now clear : if you have a unique index (PK or UK) that is globally partitioned, the import will crash.
                                  It took quite a lot of trial and error to first find out which objects caused the crash, then to identify the pattern.
                                  I would like to stringly recommend to improve logigng to report the objects on which the crash occurs.
                                  I worked around the problem by changing all globally partitioned unique indexes (PK + UK) to non-partitioned, so all constraints remained enabled.
                                  This allowed the import to work without errors.
                                  I noticed that tablespaces quotas are not imported, but this is fairly minor and can be fixed manually.
                                  We also found some issues with IOTs for which the storage clauses (PCTUSED=40) got set up wrong and cause an invalid DDL to be generated from the imported DM.
                                  That is also manually fixable.
                                  • 14. Re: reverse engineer did not import grants
                                    Philip Stoyanov-Oracle
                                    Can you try the import with DM 3.1.2.704 http://www.oracle.com/technetwork/developer-tools/datamodeler/downloads/index.html

                                    Philip
                                    1 2 Previous Next