This discussion is archived
5 Replies Latest reply: Mar 26, 2012 6:54 AM by JorgeB-Oracle RSS

Cross-Schema Insert using Stored Procedure

damien.claveau Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points