5 Replies Latest reply: Mar 26, 2012 8:54 AM by JorgeB-Oracle RSS

    Cross-Schema Insert using Stored Procedure

    damien.claveau
      Hi all.

      I am currently migrating from 11.2.0.1 to 11.2.0.3,
      and I have to face to an unexpected privileges error "ORA-13199: Insufficient privilege for application table of model xxx"

      while inserting triples into the application table of the model
      using a stored procedure that is
      owned by the model and app table owner,
      but called by another granted user.

      It has never been a problem on 10.2.0.4, nor on 11.2.0.1.
      It seems that something has really changed, (maybe a pragma authid or something like that in the SDO_RDF_TRIPLE internal code ?)
      The two users are needed on my system for security strategy reasons.



      Below is a simplified script to reproduce the success on 1.2.0.1 and the error on 11.2.0.3.
      The users are : ADMWAT = model owner, USEWAT= calling application


      SQL> connect SYS/xxxxx@DB_*11201*.world as sysdba
      Connected.
      SQL> select value from MDSYS.RDF_PARAMETER where namespace = 'MDSYS' and attribute = 'SEM_VERSION';

      VALUE
      --------------------------------------------------------------------------------
      *112*

      SQL>
      SQL> connect ADMWAT/xxxxx@DB_11201.world
      Connected.
      SQL> CREATE TABLE family_rdf_data (id NUMBER, triple SDO_RDF_TRIPLE_S);

      Table created.

      SQL>
      SQL> exec SEM_APIS.create_rdf_model('family', 'family_rdf_data', 'triple');

      PL/SQL procedure successfully completed.

      SQL>
      SQL> GRANT INSERT ON family_rdf_data TO MDSYS;

      Grant succeeded.

      SQL>
      SQL> create or replace PROCEDURE NEW_TRIPLE IS
      2 BEGIN
      3 INSERT INTO family_rdf_data VALUES (1,
      4 SDO_RDF_TRIPLE_S('family',
      5 'http://www.example.org/family/John',
      6 'http://www.example.org/family/fatherOf',
      7 'http://www.example.org/family/Suzie'));
      8 END;
      9 /

      Procedure created.

      SQL>
      SQL> GRANT EXECUTE ON NEW_TRIPLE TO USEWAT;

      Grant succeeded.

      SQL>
      SQL> connect ADMWAT/xxxxx@DB_11201.world
      Connected.
      SQL> exec ADMWAT.NEW_TRIPLE();

      PL/SQL procedure successfully completed.

      SQL> rollback;

      Rollback complete.

      SQL>
      SQL> connect USEWAT/xxxxx@DB_11201.world
      Connected.
      SQL> exec ADMWAT.NEW_TRIPLE();

      PL/SQL procedure successfully completed.

      SQL>
      SQL>
      SQL>

      ************************************************************************************************************
      ************************************************************************************************************
      ************************************************************************************************************

      SQL> connect SYS/xxxxx@DB_*11203*.world as sysdba
      Connected.
      SQL> select value from MDSYS.RDF_PARAMETER where namespace = 'MDSYS' and attribute = 'SEM_VERSION';

      VALUE
      --------------------------------------------------------------------------------
      *11203*

      SQL>
      SQL> connect ADMWAT/xxxxx@DB_11203.world
      Connected.
      SQL>
      SQL> CREATE TABLE family_rdf_data (id NUMBER, triple SDO_RDF_TRIPLE_S);

      Table created.

      SQL>
      SQL> exec SEM_APIS.create_rdf_model('family', 'family_rdf_data', 'triple');

      PL/SQL procedure successfully completed.

      SQL>
      SQL> GRANT INSERT ON family_rdf_data TO MDSYS;

      Grant succeeded.

      SQL>
      SQL> create or replace PROCEDURE NEW_TRIPLE IS
      2 BEGIN
      3 INSERT INTO family_rdf_data VALUES (1,
      4 SDO_RDF_TRIPLE_S('family',
      5 'http://www.example.org/family/John',
      6 'http://www.example.org/family/fatherOf',
      7 'http://www.example.org/family/Suzie'));
      8 END;
      9 /

      Procedure created.

      SQL>
      SQL> GRANT EXECUTE ON NEW_TRIPLE TO USEWAT;
      Grant succeeded.

      SQL>
      SQL> connect ADMWAT/xxxxx@DB_11203.world
      Connected.
      SQL> exec ADMWAT.NEW_TRIPLE();

      PL/SQL procedure successfully completed.

      SQL> rollback;

      Rollback complete.

      SQL>
      SQL> connect USEWAT/xxxxx@DB_11203.world
      Connected.
      SQL> exec ADMWAT.NEW_TRIPLE();
      BEGIN ADMWAT.NEW_TRIPLE(); END;

      *
      ERROR at line 1:
      ORA-55303: SDO_RDF_TRIPLE_S constructor failed:

      SQLERRM=ORA-13199: Insufficient privilege for application table of model family [
      ORA-06512: at "MDSYS.MD", line 1723
      ORA-06512: at "MDSYS.MDERR", line 17
      ORA-06512: at "MDSYS.SDO_RDF_TRIPLE_S", line 41
      ]
      ORA-06512: at "MDSYS.MD", line 1723
      ORA-06512: at "MDSYS.MDERR", line 17
      ORA-06512: at "MDSYS.SDO_RDF_TRIPLE_S", line 68
      ORA-06512: at "ADMWAT.NEW_TRIPLE", line 3
      ORA-06512: at line 1


      SQL>

      Edited by: damien.claveau on 26 mars 2012 02:02
        • 1. Re: Cross-Schema Insert using Stored Procedure
          sdas
          Damien,

          We have reproduced the problem locally. At the moment, we do require that in order to insert an RDF triple into an application table, invoker has to either be the owner of the corresponding semantic model OR have INSERT privilege on the application table.

          We will look into addressing the problem you are seeing, where invoker has EXECUTE privilege on a procedure that attempts to insert into the application table and the procedure has been defined by a user who has INSERT privilege on the application table.

          Thanks for pointing out this issue.
          • 2. Re: Cross-Schema Insert using Stored Procedure
            damien.claveau
            Sdas,

            Thank you for having studied and confirmed the issue.

            But, the workaround you propose (grant INSERTprivilege to the invoker) doesn't really solve the issue in my test example ....
            Is there something wrong below ?

            SQL> connect ADMWAT/xxxxx@DB_11203.world
            Connected.
            SQL> GRANT INSERT ON ADMWAT.family_rdf_data TO USEWAT;
            Grant succeeded.
            SQL> connect USEWAT/xxxxx@DB_11203.world
            Connected.
            SQL> exec ADMWAT.NEW_TRIPLE();
            BEGIN ADMWAT.NEW_TRIPLE(); END;

            *
            ERROR at line 1:
            ORA-55303: SDO_RDF_TRIPLE_S constructor failed:
            SQLERRM=ORA-13199: Insufficient privilege for application table of model family
            ...

            So I get the error, even when the family_rdf_data is owned by ADMWAT, INSERT is granted to USEWAT and to MDSYS (initially for bulk_load).

            The only workaround that seems to work is to grant INSERT privilege on table to PUBLIC.
            It would be quite a serious security issue for the data, not very desirable in a sensible system.

            SQL> connect ADMWAT/xxxxx@DB_11203.world
            Connected.
            SQL> GRANT INSERT ON ADMWAT.family_rdf_data TO PUBLIC;
            Grant succeeded.
            SQL> connect USEWAT/xxxxx@DB_11203.world
            Connected.
            SQL> exec ADMWAT.NEW_TRIPLE();
            PL/SQL procedure successfully completed.

            Would you please correct me if I am wrong, or confirm my observations ?
            Regards

            Edited by: damien.claveau on 26 mars 2012 02:04
            • 3. Re: Cross-Schema Insert using Stored Procedure
              sdas
              Damien,

              Please submit a Service Request for this problem to Oracle Support.

              In the mean time, until a fix becomes available, another possible workaround you could consider using is to grant DBA role to ADMWAT.

              If you would like, please contact me directly by email: souripriya dot das at oracle dot com.

              Thanks,
              - Souri.
              • 4. Re: Cross-Schema Insert using Stored Procedure
                damien.claveau
                Thank you Souri,

                Granting DBA role to the stored procedure solves effectively the issue.

                We will ask support on Metalink.

                Damien
                • 5. Re: Cross-Schema Insert using Stored Procedure
                  JorgeB-Oracle
                  Hi Damien,

                  Once you filed the Service Request you can send me an email at jorge dot barba at oracle dot com with the Service Request number, so I can take ownership and do the follow up

                  Regards!
                  Jorge