Cross-Schema Insert using Stored Procedure
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