"Norman Dunbar" <Norman.Dunbar_at_lfs.co.uk> wrote in message
news:E2F6A70FE45242488C865C3BC1245DA703F9EA9A_at_lnewton.leeds.lfs.co.uk...
> 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 !)
Hi Norm
I see no way for what the DBA is telling you to be true. I do see some ways
for what he is describing to be happening.
- Have you verified that the stored proc really doesn't have AUTHID
CURRENT_USER in it. What I mean is does the text of the SP reflect what your
4GL would produce or has it been massaged b a DBA on site at some previous
time. select text from user_source where ... order by line;
- Have you checked for the existence of yet another object with the same
name, IE a procedure owned by batch?
--
Niall Litchfield
Oracle DBA
Audit Commission UK
Received on Fri Aug 01 2003 - 05:47:56 CDT
