| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Packages, tables, inbvoker and definer - wierd problem !
Oracle 817 on some Unix platform, I know not which (Sorry).
Two users, OLTP and BATCH.
The batch user has public synonyms to the OLTP user's tables and to some
packages.
Execute is granted on the packages and insert, update, delete on the
tables.
(There are other OPS$ users with the same access, but that is not the
issue here.)
User batch creates a work table as (select * from oltp.work_table); (Via
its public synonym.)
Then builds a couple of indexes and a PK on its own copy of the work
table.
The deletes from the oltp.work_table where there is a record in the
batch copy of the work table. (Using fully qualified name !!)
This means that the remaining rows in the oltp table are new since the
copy was made and will be processed later.
Batch user then reads through this work take it has created and processed the oltp user's data in assorted other tables as appropriate. All access to the rows in any and all tables is via a packaged procedure. (A 4GL is used to create scripts for the tables and these incluse packages to do any form of DML that the 4GL thinks is necessary.) There are no 'direct' SQL commands used to access the tables - unless the 4GL detects at run time when it 'opens' the table, that there is no package (or synonym leading to a package) - then it will use normal SQL commands to read and write data.
The packages are generated by the 4GL and it doesn't know about AUTHID DEFINER or AUTHID CURRENT_USER (yet).
Ok, here's the nasty bit.
Because the batch user has execute access to the oltp user's package via a public synonym which has the same name as the package, the batch process is reading the table using calls along the lines of
... PACKAGE_NAME.OPERATION(parameters); ...
Where the PACKAGE_NAME is (a) the name of the package in the oltp user and (b) the name of the public synonym created on the package - they are both the same name.
The package, to my mind, is running with definer rights (as the oltp user) and so is reading, writing from/to the oltp user's table.
The DBA on site assures me that this is not the case and that the rows in the batch user's copy of the table are being processed by the package.
I have built test cases where the oltp and batch users have a table, same name in both users but different contents. I have created a packaged procedure in the oltp user only to read the data from the table. I have created a public synonym for the oltp user's package and its table. The synonym is the same as the object it refers to - as per the live system. The batch user has been given execute rights to the package and full access to the table in the oltp user.
When I run the code in the oltp user, I get the data from the oltp
user's table - as expected.
When I run the code from the batch user, I get the data from the oltp
user's table - again as expected.
If I compile with AUTHID CURRENT_USER, I get the data back from the batch user - as expected - when run from the batch user and from the oltp user when run in the oltp user.
No matter what I do, I cannot get the test cases to replicate what the DBA is telling me.
Here's my question :
It it at all possible for the scenario the DBA is describing to be true, where the batch user manages to process it's own table using a packaged procedure owned by the oltp user which has definer rights (by default) ?
I've asked the DBA to prove his assertions, but so far, I've received nothing back ........
The reason he is so certain it is working as above is that this is part
of the batch processing for an application they are running and it 'just
works'. I have my doubts myself because I cannot reproduce the 'errant'
(to my mind) behaiviour.
The only way I can see it working as per the DBA is if the package is not being used, then access to the table will be to the one in the batch user and not to the oltp user - but as I said, the package is being used. (I'm told !)
Cheers,
Norm.
Tel: 0113 289 6265 Fax: 0113 289 3146 URL: http://www.Lynx-FS.com -------------------------------------Received on Thu Jul 31 2003 - 10:05:47 CDT
 |
 |