7 Replies Latest reply: Jul 29, 2013 8:57 AM by odie_63 RSS

    Permission issues accross schemas to load XMLTYPE column - structured storage

    beta32c

      Hi,

      We have a table in BIUSER schema this table is object-realtionally stored with XMLs. When we are trying to load receords from ETLUSER schema we are getting the error as

      Record 1: Rejected - Error on table "BIUSER"."PWAYWORKFILE_TABLE".
      ORA-00604: error occurred at recursive SQL level 1
      ORA-01031: insufficient privileges

      Heres the oracle installation details
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      "CORE 11.2.0.3.0 Production"


      All the records are moving to the bad file, whereas the same load happens normally in its own schema i.e. BIUSER

      Suspecting this as permission issues we have already given the permission to the table sysnonym as given below in the registration script.

      We googled and found few things about ACLs that we are not sure of , its that is the issue please let us know if this table can be created and loaded from different schema

      Heres the table creation and registration script

      set echo on
      spool regschema.log
      set define on
      set timing on
      set long 100000 pages 0 lines 256 trimspool on timing on
      drop table PWAYWORKFILE_TABLE;
      drop sequence PWAYWORKFILE_TABLE_SEQ;
      begin
      dbms_xmlschema.deleteschema('workfile.xsd',dbms_xmlschema.DELETE_CASCADE);
      end;
      /
      begin
      dbms_xmlschema.deleteschema('TotalLoss.xsd',dbms_xmlschema.DELETE_CASCADE);
      end;
      /
      begin
      dbms_xmlschema.deleteschema('Salvage.xsd',dbms_xmlschema.DELETE_CASCADE);
      end;
      /
      begin
      dbms_xmlschema.deleteschema('rate.xsd',dbms_xmlschema.DELETE_CASCADE);
      end;
      /
      begin
      dbms_xmlschema.deleteschema('notes.xsd',dbms_xmlschema.DELETE_CASCADE);
      end;
      /
      begin
      dbms_xmlschema.deleteschema('Image.xsd',dbms_xmlschema.DELETE_CASCADE);
      end;
      /
      begin
      dbms_xmlschema.deleteschema('Event.xsd',dbms_xmlschema.DELETE_CASCADE);
      end;
      /
      begin
      dbms_xmlschema.deleteschema('estimate.xsd',dbms_xmlschema.DELETE_CASCADE);
      end;
      /
      begin
      dbms_xmlschema.deleteschema('CoTotals.xsd',dbms_xmlschema.DELETE_CASCADE);
      end;
      /
      begin
      dbms_xmlschema.deleteschema('corr.xsd',dbms_xmlschema.DELETE_CASCADE);
      end;
      /
      begin
      dbms_xmlschema.deleteschema('Admin.xsd',dbms_xmlschema.DELETE_CASCADE);
      end;
      /
      begin
      dbms_xmlschema.deleteschema('Vins.xsd',dbms_xmlschema.DELETE_CASCADE);
      end;
      /
      begin
      dbms_xmlschema.deleteschema('commonType.xsd',dbms_xmlschema.DELETE_CASCADE);
      end;
      /
      declare
        V_XML_SCHEMA_NAME       VARCHAR2(700) := 'commonType.xsd';
        V_XML_SCHEMA             XMLType       := xmlType(BfileName('XSD_DIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
        V_SCHEMA_LOCATION_HINT   VARCHAR2(700) := 'commonType.xsd';
      BEGIN
        DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
        DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.SETTIMESTAMPWITHTIMEZONE(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.SETTIMESTAMPWITHTIMEZONE(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
        DBMS_XMLSCHEMA.registerSchema(
          SCHEMAURL       => V_SCHEMA_LOCATION_HINT
         ,SCHEMADOC       => V_XML_SCHEMA
         ,LOCAL           => TRUE
         ,GENTYPES        => TRUE
         ,GENTABLES       => TRUE
         ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
        );
      end;
      /
      declare
        V_XML_SCHEMA_NAME       VARCHAR2(700) := 'Admin.xsd';
        V_XML_SCHEMA             XMLType       := xmlType(BfileName('XSD_DIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
        V_SCHEMA_LOCATION_HINT   VARCHAR2(700) := 'Admin.xsd';
      begin
        DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
        DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.SETTIMESTAMPWITHTIMEZONE(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
        DBMS_XMLSCHEMA.registerSchema(
          SCHEMAURL       => V_SCHEMA_LOCATION_HINT
         ,SCHEMADOC       => V_XML_SCHEMA
         ,LOCAL           => TRUE
         ,GENTYPES        => TRUE
         ,GENTABLES       => TRUE
         ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
        );
      end;
      /
      declare
        V_XML_SCHEMA_NAME       VARCHAR2(700) := 'CoTotals.xsd';
        V_XML_SCHEMA             XMLType       := xmlType(BfileName('XSD_DIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
        V_SCHEMA_LOCATION_HINT   VARCHAR2(700) := 'CoTotals.xsd';
      begin
        DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
        DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.SETTIMESTAMPWITHTIMEZONE(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
        DBMS_XMLSCHEMA.registerSchema(
          SCHEMAURL       => V_SCHEMA_LOCATION_HINT
         ,SCHEMADOC       => V_XML_SCHEMA
         ,LOCAL           => TRUE
         ,GENTYPES        => TRUE
         ,GENTABLES       => TRUE
         ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
        );
      end;
      /
      declare
        V_XML_SCHEMA_NAME       VARCHAR2(700) := 'Event.xsd';
        V_XML_SCHEMA             XMLType       := xmlType(BfileName('XSD_DIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
        V_SCHEMA_LOCATION_HINT   VARCHAR2(700) := 'Event.xsd';
      begin
        DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
        DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.SETTIMESTAMPWITHTIMEZONE(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
        DBMS_XMLSCHEMA.registerSchema(
          SCHEMAURL       => V_SCHEMA_LOCATION_HINT
         ,SCHEMADOC       => V_XML_SCHEMA
         ,LOCAL           => TRUE
         ,GENTYPES        => TRUE
         ,GENTABLES       => TRUE
         ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
        );
      end;
      /
      declare
        V_XML_SCHEMA_NAME       VARCHAR2(700) := 'Image.xsd';
        V_XML_SCHEMA             XMLType       := xmlType(BfileName('XSD_DIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
        V_SCHEMA_LOCATION_HINT   VARCHAR2(700) := 'Image.xsd';
      begin
        DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
        DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.SETTIMESTAMPWITHTIMEZONE(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
        DBMS_XMLSCHEMA.registerSchema(
          SCHEMAURL       => V_SCHEMA_LOCATION_HINT
         ,SCHEMADOC       => V_XML_SCHEMA
         ,LOCAL           => TRUE
         ,GENTYPES        => TRUE
         ,GENTABLES       => TRUE
         ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
        );
      end;
      /
      declare
        V_XML_SCHEMA_NAME       VARCHAR2(700) := 'Salvage.xsd';
        V_XML_SCHEMA             XMLType       := xmlType(BfileName('XSD_DIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
        V_SCHEMA_LOCATION_HINT   VARCHAR2(700) := 'Salvage.xsd';
      begin
        DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
        DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.SETTIMESTAMPWITHTIMEZONE(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
        DBMS_XMLSCHEMA.registerSchema(
          SCHEMAURL       => V_SCHEMA_LOCATION_HINT
         ,SCHEMADOC       => V_XML_SCHEMA
         ,LOCAL           => TRUE
         ,GENTYPES        => TRUE
         ,GENTABLES       => TRUE
         ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
        );
      end;
      /
      declare
        V_XML_SCHEMA_NAME       VARCHAR2(700) := 'TotalLoss.xsd';
        V_XML_SCHEMA             XMLType       := xmlType(BfileName('XSD_DIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
        V_SCHEMA_LOCATION_HINT   VARCHAR2(700) := 'TotalLoss.xsd';
      begin
        DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
        DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.SETTIMESTAMPWITHTIMEZONE(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
        DBMS_XMLSCHEMA.registerSchema(
          SCHEMAURL       => V_SCHEMA_LOCATION_HINT
         ,SCHEMADOC       => V_XML_SCHEMA
         ,LOCAL           => TRUE
         ,GENTYPES        => TRUE
         ,GENTABLES       => TRUE
         ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
        );
      end;
      /
      declare
        V_XML_SCHEMA_NAME        VARCHAR2(700) := 'Vins.xsd';
        V_XML_SCHEMA             XMLType       := xmlType(BfileName('XSD_DIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
        V_SCHEMA_LOCATION_HINT   VARCHAR2(700) := 'Vins.xsd';
      begin
        DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
        DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.SETTIMESTAMPWITHTIMEZONE(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
        -- DOM Fidelity enabled due to presence of mixed text, substitution group heads, or repeating choice structures in complex type defintion :-
        DBMS_XMLSCHEMA_ANNOTATE.enableMaintainDOM(V_XML_SCHEMA,'RefurbMgr',TRUE);
        select /*+ NO_XML_QUERY_REWRITE */
               XMLQuery(
                 'declare namespace xdb = "http://xmlns.oracle.com/xdb"; (:
                  copy $NEWSCH := $SCHEMA modify (
                                            let $MODEL := $NEWSCH/xs:schema/xs:complexType[11]/xs:all
                                            return (
                                              replace value of node $MODEL/xs:element[2]/xs:complexType/@xdb:maintainDOM with "false",
                                              replace value of node $MODEL/xs:element[3]/xs:complexType/@xdb:maintainDOM with "false",
                                              replace value of node $MODEL/xs:element[4]/xs:complexType/@xdb:maintainDOM with "false"
                                            )
                                          )
                   return $NEWSCH'
                 passing V_XML_SCHEMA as "SCHEMA"
                 returning content
               )
          into V_XML_SCHEMA
          from dual;

        DBMS_XMLSCHEMA.registerSchema(
          SCHEMAURL       => V_SCHEMA_LOCATION_HINT
         ,SCHEMADOC       => V_XML_SCHEMA
         ,LOCAL           => TRUE
         ,GENTYPES        => TRUE
         ,GENTABLES       => TRUE
         ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
        );
      end;
      /
      declare
        V_XML_SCHEMA_NAME       VARCHAR2(700) := 'corr.xsd';
        V_XML_SCHEMA             XMLType       := xmlType(BfileName('XSD_DIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
        V_SCHEMA_LOCATION_HINT   VARCHAR2(700) := 'corr.xsd';
      begin
        DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
        DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.SETTIMESTAMPWITHTIMEZONE(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
        DBMS_XMLSCHEMA.registerSchema(
          SCHEMAURL       => V_SCHEMA_LOCATION_HINT
         ,SCHEMADOC       => V_XML_SCHEMA
         ,LOCAL           => TRUE
         ,GENTYPES        => TRUE
         ,GENTABLES       => TRUE
         ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
        );
      end;
      /
      declare
        V_XML_SCHEMA_NAME       VARCHAR2(700) := 'estimate.xsd';
        V_XML_SCHEMA             XMLType       := xmlType(BfileName('XSD_DIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
        V_SCHEMA_LOCATION_HINT   VARCHAR2(700) := 'estimate.xsd';
      begin
        DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
        DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.SETTIMESTAMPWITHTIMEZONE(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
        DBMS_XMLSCHEMA.registerSchema(
          SCHEMAURL       => V_SCHEMA_LOCATION_HINT
         ,SCHEMADOC       => V_XML_SCHEMA
         ,LOCAL           => TRUE
         ,GENTYPES        => TRUE
         ,GENTABLES       => TRUE
         ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
        );
      end;
      /
      declare
        V_XML_SCHEMA_NAME       VARCHAR2(700) := 'notes.xsd';
        V_XML_SCHEMA             XMLType       := xmlType(BfileName('XSD_DIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
        V_SCHEMA_LOCATION_HINT   VARCHAR2(700) := 'notes.xsd';
      begin
        DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
        DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.SETTIMESTAMPWITHTIMEZONE(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
        DBMS_XMLSCHEMA.registerSchema(
          SCHEMAURL       => V_SCHEMA_LOCATION_HINT
         ,SCHEMADOC       => V_XML_SCHEMA
         ,LOCAL           => TRUE
         ,GENTYPES        => TRUE
         ,GENTABLES       => TRUE
         ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
        );
      end;
      /
      declare
        V_XML_SCHEMA_NAME       VARCHAR2(700) := 'rate.xsd';
        V_XML_SCHEMA             XMLType       := xmlType(BfileName('XSD_DIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
        V_SCHEMA_LOCATION_HINT   VARCHAR2(700) := 'rate.xsd';
      begin
        DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
        DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.SETTIMESTAMPWITHTIMEZONE(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
        DBMS_XMLSCHEMA.registerSchema(
          SCHEMAURL       => V_SCHEMA_LOCATION_HINT
         ,SCHEMADOC       => V_XML_SCHEMA
         ,LOCAL           => TRUE
         ,GENTYPES        => TRUE
         ,GENTABLES       => TRUE
         ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
        );
      end;
      /
      declare
        V_XML_SCHEMA_NAME       VARCHAR2(700) := 'workfile.xsd';
        V_XML_SCHEMA             XMLType       := xmlType(BfileName('XSD_DIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
        V_SCHEMA_LOCATION_HINT   VARCHAR2(700) := 'workfile.xsd';
      begin
        DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
        DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.SETTIMESTAMPWITHTIMEZONE(V_XML_SCHEMA);
        DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
        -- Out-of-Line mappings for 1000 Column optimization :-
        DBMS_XMLSCHEMA_ANNOTATE.setOutOfLine(V_XML_SCHEMA,DBMS_XDB_CONSTANTS.XSD_COMPLEX_TYPE,'WorkfileType', 'AdminComp','ADMINCOMP_XML');
        DBMS_XMLSCHEMA_ANNOTATE.setOutOfLine(V_XML_SCHEMA,DBMS_XDB_CONSTANTS.XSD_COMPLEX_TYPE,'WorkfileType', 'NotesComp','NOTESCOMP_XML');
        DBMS_XMLSCHEMA_ANNOTATE.setOutOfLine(V_XML_SCHEMA,DBMS_XDB_CONSTANTS.XSD_COMPLEX_TYPE,'WorkfileType', 'SalvageComp','SALVGCOMP_XML');
        DBMS_XMLSCHEMA_ANNOTATE.setOutOfLine(V_XML_SCHEMA,DBMS_XDB_CONSTANTS.XSD_COMPLEX_TYPE,'WorkfileType', 'CorrComp','CORRCOMP_XML');
        DBMS_XMLSCHEMA_ANNOTATE.setOutOfLine(V_XML_SCHEMA,DBMS_XDB_CONSTANTS.XSD_COMPLEX_TYPE,'WorkfileType', 'ImageComp','IMAGECOMP_XML');
        DBMS_XMLSCHEMA_ANNOTATE.setOutOfLine(V_XML_SCHEMA,DBMS_XDB_CONSTANTS.XSD_COMPLEX_TYPE,'WorkfileType', 'EventInterfaceManagerComp','EVIFCMGRCOMP_XML');
        DBMS_XMLSCHEMA_ANNOTATE.setOutOfLine(V_XML_SCHEMA,DBMS_XDB_CONSTANTS.XSD_COMPLEX_TYPE,'WorkfileType', 'TotalLossComp','TOTALLOSSCOMP_XML');
        DBMS_XMLSCHEMA.registerSchema(
          SCHEMAURL       => V_SCHEMA_LOCATION_HINT
         ,SCHEMADOC       => V_XML_SCHEMA
         ,LOCAL           => TRUE
         ,GENTYPES        => TRUE
         ,GENTABLES       => TRUE
         ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
        );
      end;
      /
      -- Table creation for namespace "http://www.cccis.com/Pathways/Workfile"
      set lines 80
      CREATE TABLE "PWAYWORKFILE_TABLE"
      (
            SequenceID NUMBER,
            DL_CLM_FOLDER_ID   VARCHAR2(30),
            CUST_CLM_REF_ID VARCHAR(25),
            ems_file_nm               varchar2(256),
            EST_IND         VARCHAR2(3),
            rec_dt date default sysdate,
            filesent_datetime date,
            CLM_TYP_CD               VARCHAR2(2 CHAR),
            WORKFILE  XMLTYPE
      )
      XMLTYPE COLUMN WORKFILE
      STORE AS OBJECT RELATIONAL
      XMLSCHEMA "workfile.xsd" ELEMENT "PwayWorkfile"
      /
      create sequence PWAYWORKFILE_TABLE_SEQ
      start with 1
      increment by 1
      nomaxvalue
      /
      create trigger PWAYWORKFILE_TABLE_TRIGGER
      before insert on PWAYWORKFILE_TABLE
      for each row
      begin
      select PWAYWORKFILE_TABLE_SEQ.nextval into :new.SequenceID from dual;
      end;
      /
      desc PWAYWORKFILE_TABLE
      /* create synonym */
      create or replace public synonym PWAYWORKFILE_TABLE for PWAYWORKFILE_TABLE;

      grant select on PWAYWORKFILE_TABLE to BIUSER_RO;

      grant select, insert, update,delete on PWAYWORKFILE_TABLE to biuser_full;
      exit;

      Regards,
      Arghyadip

        • 1. Re: Permission issues accross schemas to load XMLTYPE column - structured storage
          odie_63

          Suspecting this as permission issues we have already given the permission to the table sysnonym as given below in the registration script.

           

          I don't see anything granted to ETLUSER, so how about :

          grant insert on PWAYWORKFILE_TABLE to ETLUSER;

          • 2. Re: Permission issues accross schemas to load XMLTYPE column - structured storage
            beta32c

            Sorry my mistake i forgot to mention that this line takes care of giving permission to ETLUSER as well which is included int BIUSER_FULLL

             

            grant select, insert, update,delete on PWAYWORKFILE_TABLE to biuser_full;

             

            I tried separately to give ETLUSER insert permission and tried to load which resulted everything in the bad file,

            This is how i ran the ctl

            sqlldr etluser/pwd@myDb control=Load_ALPWXML_Table.ctl DIRECT=FALSE log=Load_ALPWXML_Table.ctl.out

             

            heres the CTL

            OPTIONS (ERRORS=100000, SILENT=(HEADER,FEEDBACK),ROWS=500, BINDSIZE=3072000 , READSIZE=3072000)

            load data

            infile '/data/dw/alpwxml/ALPWXML_FileList_20.dat'

            BADFILE '/apps/dev/logs//Allstate_XML_FileList_07052013:13:56.bad'

            DISCARDFILE '/apps/dev/logs//Allstate_XML_FileList_07052013:13:56.dsc'

            append

            into table PWAYWORKFILE_TABLE

            (

            filename filler char(120),

            WORKFILE lobfile(filename) terminated by eof)

             

            WORKFILE is the XMLTYPE column here

             

             

            The error i got for each record :

             

            Record 20: Rejected - Error on table "BIUSER"."PWAYWORKFILE_TABLE".

            ORA-00604: error occurred at recursive SQL level 1

            ORA-01031: insufficient privileges

            • 3. Re: Permission issues accross schemas to load XMLTYPE column - structured storage
              Marco Gralike

              This sounds familiar to me. Not sure though what the exact solution was, but due to the fact that ACL security in XMLDB is implemented via VPD and you used LOCAL (=> TRUE) during registration of the XML Schema, you would need at least SELECT ANY TABLE privs

              • 4. Re: Permission issues accross schemas to load XMLTYPE column - structured storage
                beta32c

                Is there any means to load the schema registered table from another user even though if the registered schema is Local schema (LOCAL => TRUE)

                I did some check to find out through the SYS_CHECKACL filter in the explain plan if the Hierarchy is enabled or not and  did not get any ACL check in the predicate information also my script has 'ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE' .

                 

                If the ACL check is off then any idea why i am running in to insufficient priviledge issues.

                • 5. Re: Permission issues accross schemas to load XMLTYPE column - structured storage
                  Marco Gralike

                  Can't test it because I don't have the XML Schemas but Object Relational registration creates also a lot of objects based on the schema. If it is not the ACL bit then this means that you don't hold all privileges needed to access some or one of these created OR objects.

                  • 6. Re: Permission issues accross schemas to load XMLTYPE column - structured storage
                    beta32c

                    Hi MarcoGralike,

                     

                    I have finally acquired a sample schema and xmls to reproduce the errors that i am getting even after acquiring XDBADMIN privilege and registering my schema as GLOBAL.
                    Here's the problem i am facing, whenever i intend to store the PublisherList (publisher.xsd) out of line while registration i am running into insufficient privilege issues even if i have the schema registered using (LOCAL => FALSE), whereas it runs smooth in BIUSER and if i dont set it out of line it works in ETLUSER as well.


                    Here are the 2 XSD files
                    -----------------------
                    books.xsd   --- this is the root element
                    <xs:schema  xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb"  version="1.0" xdb:storeVarrayAsTable="true">
                      <xs:include schemaLocation="publisher.xsd"/>
                      <xs:element name="books" type="bookType"/>
                      <xs:complexType name="bookType" abstract="true">
                        <xs:sequence>
                        <xs:element name="author" type="xs:string" minOccurs="0"/>
                        <xs:element name="title" type="xs:string" minOccurs="0"/>
                        <xs:element name="genre" type="xs:string" minOccurs="0"/>
                        <xs:element ref="PublisherList" minOccurs="0"/>
                       </xs:sequence>
                       </xs:complexType>
                    </xs:schema>


                    publisher.xsd -- this is a child elelment which in my actual scenario is so big that i must keep it out of line during registration
                    <xs:schema  xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb"  version="1.0" xdb:storeVarrayAsTable="true">
                      <xs:element name="PublisherList" type="PublisherListType"/>
                      <xs:complexType name="PublisherListType">
                        <xs:sequence>
                        <xs:element name="Name" type="xs:string" minOccurs="0"/>
                        <xs:element name="Office" type="xs:string" minOccurs="0"/>
                       </xs:sequence>
                       </xs:complexType>
                    </xs:schema>


                    Here's the sample XML
                    -----------------------
                    <?xml version="1.0"?>
                    <books xmlns:xs="http://www.w3.org/2001/XMLSchema">
                          <author>Writer</author>
                          <title>The First Book</title>
                          <genre>Fiction</genre>
                          <PublisherList>
                           <Name>Penguin</Name>
                           <Office>London</Office>
                          </PublisherList>
                    </books>


                    Here's how i am registering the Schemas in BIUSER which has XDBADMIN privilege
                    ------------------------------------------------------------------------------
                    DROP TABLE BOOKS_TABLE;
                    begin
                    dbms_xmlschema.deleteschema('books.xsd',dbms_xmlschema.DELETE_CASCADE);
                    end;
                    /
                    begin
                    dbms_xmlschema.deleteschema('publisher.xsd',dbms_xmlschema.DELETE_CASCADE);
                    end;
                    /
                    declare
                      V_XML_SCHEMA_NAME       VARCHAR2(700) := 'publisher.xsd';
                      V_XML_SCHEMA             XMLType       := xmlType(BfileName('XSD_DIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
                      V_SCHEMA_LOCATION_HINT   VARCHAR2(700) := 'publisher.xsd';
                    begin
                      DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
                      DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
                      DBMS_XMLSCHEMA_ANNOTATE.SETTIMESTAMPWITHTIMEZONE(V_XML_SCHEMA);
                      DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
                        DBMS_XMLSCHEMA.registerSchema(
                        SCHEMAURL       => V_SCHEMA_LOCATION_HINT
                       ,SCHEMADOC       => V_XML_SCHEMA
                       ,LOCAL           => FALSE
                       ,GENTYPES        => TRUE
                       ,GENTABLES       => TRUE
                       ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
                      );
                    end;
                    /
                    declare
                      V_XML_SCHEMA_NAME       VARCHAR2(700) := 'books.xsd';
                      V_XML_SCHEMA             XMLType       := xmlType(BfileName('XSD_DIR',V_XML_SCHEMA_NAME),nls_charset_id('AL32UTF8'));
                      V_SCHEMA_LOCATION_HINT   VARCHAR2(700) := 'books.xsd';
                    begin
                      DBMS_XMLSCHEMA_ANNOTATE.printWarnings(FALSE);
                      DBMS_XMLSCHEMA_ANNOTATE.disableDefaultTableCreation(V_XML_SCHEMA);
                      DBMS_XMLSCHEMA_ANNOTATE.SETTIMESTAMPWITHTIMEZONE(V_XML_SCHEMA);
                      DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(V_XML_SCHEMA,FALSE);
                    DBMS_XMLSCHEMA_ANNOTATE.setOutOfLine(V_XML_SCHEMA,DBMS_XDB_CONSTANTS.XSD_COMPLEX_TYPE,'bookType', 'PublisherList','PUBLISHERLIST_XML');
                        DBMS_XMLSCHEMA.registerSchema(
                        SCHEMAURL       => V_SCHEMA_LOCATION_HINT
                       ,SCHEMADOC       => V_XML_SCHEMA
                       ,LOCAL           => FALSE
                       ,GENTYPES        => TRUE
                       ,GENTABLES       => TRUE
                       ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
                      );
                    end;
                    /
                    CREATE TABLE BOOKS_TABLE
                    (
                          BOOKS  XMLTYPE
                    )
                    XMLTYPE COLUMN BOOKS
                    STORE AS OBJECT RELATIONAL
                    XMLSCHEMA "books.xsd" ELEMENT "books"
                    /

                    DROP PUBLIC SYNONYM BOOKS_TABLE;
                    create or replace public synonym BOOKS_TABLE for BOOKS_TABLE;

                    grant select, insert, update,delete on BOOKS_TABLE to ETLUSER;

                     


                    Heres the ctl file that i am using
                    -------------------------------------
                    Load_Books.ctl
                    OPTIONS (ERRORS=100000, SILENT=(HEADER,FEEDBACK),ROWS=500, BINDSIZE=3072000 , READSIZE=3072000)
                    load data
                    infile '/apps/dev/PWXML-10/ctl/load_xml.txt'
                    BADFILE '/apps/dev/PWXML-10/ctl/load_xml.txt.bad'
                    DISCARDFILE '/apps/dev/PWXML-10/ctl/load_xml.txt.dsc'
                    append
                    into table BOOKS_TABLE
                    (
                    filename filler char(120),
                    BOOKS lobfile(filename) terminated by eof)

                    '/apps/dev/PWXML-10/ctl/load_xml.txt' would contain the XML file path that i gave

                     

                    Heres how i am loading the XML through sqlldr in ETLUSER
                    ----------------------------------------------
                    sqlldr etluser/etluserpassword@MYXMLDBNAME control=Load_Books.ctl log=Load_Books.ctl.log

                     

                    Here's the error i am getting

                    -----------------------------
                    Record 1: Rejected - Error on table "BIUSER"."BOOKS_TABLE".
                    ORA-00604: error occurred at recursive SQL level 1
                    ORA-01031: insufficient privileges


                    Hopefully i have given you all the set up required to pin point the evil error.

                    Please let me know if i have missed something.

                    • 7. Re: Permission issues accross schemas to load XMLTYPE column - structured storage
                      odie_63

                      A SQL trace shows that the error occurs at this point :

                      PARSE ERROR #838545848:len=112 dep=1 uid=82 oct=2 lid=82 tim=2408837943 err=942

                      INSERT /*+ REF_CASCADE_CURSOR */ INTO "DEV"."PUBLISHERLIST_XML"("SYS_NC_OID$","SYS_NC_ROWINFO$") VALUES (:1,:2)

                      So I suppose you also have to grant INSERT privilege on the out-of-line table :

                      GRANT INSERT ON publisherlist_xml TO etluser;