1 2 Previous Next 18 Replies Latest reply on Oct 7, 2016 1:18 AM by Solomon Yakobson Go to original post
      • 15. Re: Calling dbms_metadata.get_ddl() from pl/sql
        Scott Swank

        Thanks again Solomon. I'd discovered that before I even read your response.

         

        Ultimately, I just moved the metadata.get_ddl() call into a package owned by a schema that already has escalated privs. Then I wrap it, and call that API from my code.

         

        Cheers.

        • 16. Re: Calling dbms_metadata.get_ddl() from pl/sql
          Solomon Yakobson

          rp0428 wrote:

           

          Ok - I'm confused.

           

          Please explain.

          Example I posted shows that same procedure which calls DBMS_METADATA works fine when called directly since it runs with invoker rights. Same procedure called from a trigger (and I chose AFTER DDL trigger just because Scott mentioned it) fails. In fact, I believe DBMS_METADATA would fail even if trigger owner would have all these 3676 grants Scott mentioned originally since trigger is called after DDL is run but before DDL is committed and therefore isn't yet visible.

           

          SY.

          • 17. Re: Calling dbms_metadata.get_ddl() from pl/sql

            Example I posted shows that same procedure which calls DBMS_METADATA works fine when called directly since it runs with invoker rights. Same procedure called from a trigger (and I chose AFTER DDL trigger just because Scott mentioned it) fails.

            Ok - I understand the FIRST example showed that. But what I ask about was this

            SQL> create table x(x number);

            create table x(x number)

            *

            ERROR at line 1:

            ORA-00604: error occurred at recursive SQL level 1

            ORA-31603: object "TBL_PK" of type CONSTRAINT not found in schema "SCOTT"

            There IS NOT PK or ANY constraint in that 'create table' statement.

             

            So how is it that the exception mentions 'TBL_PK' of type 'CONSTRAINT'?

             

            That is what I ask you to explain.

            • 18. Re: Calling dbms_metadata.get_ddl() from pl/sql
              Solomon Yakobson

              rp0428 wrote:

               

              Ok - I understand the FIRST example showed that. But what I ask about was this

              SQL> create table x(x number);

              create table x(x number)

              *

              ERROR at line 1:

              ORA-00604: error occurred at recursive SQL level 1

              ORA-31603: object "TBL_PK" of type CONSTRAINT not found in schema "SCOTT"

              There IS NOT PK or ANY constraint in that 'create table' statement.

               

              So how is it that the exception mentions 'TBL_PK' of type 'CONSTRAINT'?

               

              That is what I ask you to explain.

              Error ORA-31603 is raised by oracle because DBMS_METADATA is run against object in other schema while caller doesn't have SELECT_CATALOG_ROLE. Check my first reply where I create procedure P1 which asks DBMS_METADATA to get DDL for primary key constraint TBL_PK in schema SCOTT. First I create P1 with definer rights which ignores roles - it fails with exact same error. Then I create P1 with invoker rights and it succeeds. And then I create AFTER DDL trigger that calls invoker rights P1 and show that even though P1 has invoker rights it fails when called from a trigger. Trigger is always definer rights, so all roles are disabled within a trigger. As a result, even though invoker rights P1 honors all currently enabled roles, there is nothing to honor. Hope it is clear now. Error message has same logic as when referencing either non-existent object or object caller has no rights to. Same is here - P1 asks DBMS_METADATA to get DDL for primary key constraint TBL_PK in schema SCOTT. And DBMS_METADATA after checking caller's privileges throws a variation of "object doesn't exist". Same ORA-31603 would be raised if constraint TBL_PK itself wouldn't exits.

               

              So to answer your question - it doesn't matter what DDL we issue. AFTER DDL trigger I created always calls procedure P1 which tries to get DDL for constraint TBL_PK in schema SCOTT and always fails.

               

              SY.

              1 2 Previous Next