4 Replies Latest reply: Feb 22, 2013 10:53 AM by Marco Gralike RSS

    Strange behaviour of dbms_xmlschema.registerSchema

    Martin1
      Hi,

      when i execute the dbms_xmlschema.registerSchema in SQL*Plus it works fine. Also the appropriate db objects (XMLTYPE table and object types) are created. But when i call dbms_xmlschema.registerSchema with the same parameters in a package procedure with the same db connection it takes the same time to finish, no error occurs but the db objects are not created.

      Has somebody an idea what's going wrong?

      Regards,
      Martin
        • 1. Re: Strange behaviour of dbms_xmlschema.registerSchema
          Marco Gralike
          Like all stuff done via PL/SQL, grant must be explicitly granted to the executing user/process and can not be granted via a ROLE.
          So grant execute on dbms_xmlschema directly to the user.

          What statement is actually executed / called in the PL/SQL package?
          • 2. Re: Strange behaviour of dbms_xmlschema.registerSchema
            Martin1
            Hi Marco,

            the following procedures i use in a package procedure and they work fine:
            - dbms_xdb.createResource
            - dbms_xdb.deleteResource
            - dbms_xmlSchema.deleteSchema

            Both packages (dbms_xdb and dbms_xmlSchema) are granted to public.

            Only the procedure dbms_xmlschema.registerSchema seems to work but generates no db objects with no error!
            I have to wait on our dba. Afterwards i can test it again.

            Regards,
            Martin
            • 3. Re: Strange behaviour of dbms_xmlschema.registerSchema
              Marco Gralike
              As I said you have to grant these DIRECTLY to the user that executes the package (and NOT via a ROLE or via PUBLIC).

              so
              -- can't check if /don't know by hart if the packages are owned by XDB or SYS
              
              grant execute on dbms_xmlschema to "your invoker/definer"
              grant execute on dbms_xdb.createresource to "your invoker/definer"
              grant execute on dbms_xdb.deleteresource to "your invoker/definer"
              Depending on implicit or explicit use of invokers/definer rights in your PL/SQL code, the grantee can be different.

              http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/subprograms.htm#i18574

              Edited by: Marco Gralike on Feb 22, 2013 5:46 PM
              • 4. Re: Strange behaviour of dbms_xmlschema.registerSchema
                Marco Gralike
                Internally Oracle makes use of VPD. If you register a XML Schema via a package for public use, that is use the parameter LOCAL => FALSE while using DBMS_XMLSCHEMA, you also need direct grants of all the privileges that are defined in the role XDBADMIN...

                http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_xmlsch.htm#i1003177

                Users that do not have the XDBADMIN role are not allowed to register XML Schemas for public usage. Therefore I would really advise you to register schemas always for LOCAL usage. Granting XDBADMIN privileges to "the world", "PUBLIC", is not a good thing.

                Edited by: Marco Gralike on Feb 22, 2013 5:50 PM