Re: APEX_PUBLIC_USER + Library Cache Pin/Library Cache Lock Problem (Grants Mystery too!)

That seems to explain the library cache issue as well that I don't get with my own DBA account. Since its going through a package to execute SQL commands, the library cache issues could be in a few places. (Reminds me of dynamic SQL performance problems seen in the past)

Chris

On Tue, Apr 2, 2019 at 10:16 AM Howard Latham <howard.latham_at_gmail.com> wrote:

> Here is the link to the Article!
>
> https://dsavenko.me/how-apex-executes-sql-statements-without-having-permissions/
>
>
> Howard A. Latham
>
>
>
>
> On Tue, 2 Apr 2019 at 16:11, Chris Taylor <
> christopherdtaylor1994_at_gmail.com> wrote:
>
>> Env:
>> Exadata Cloud _at_ Customer
>> RAC DB: 12.1.0.2 (Multi-tenant)
>>
>> Ok team, I'll be the first to admit I don't know a lot about APEX and how
>> it plays with the database.
>>
>> We have a query that runs from a middleware server where the page is
>> built in APEX and connects to the DB as APEX_PUBLIC_USER to execute queries.
>>
>> This one query (which is really ugly and annoying) involves SEVERAL
>> views from another schema like so:
>>
>> (Owners & Names changed to protect the innocent)
>>
>> USERNAME.VIEW_1
>> USERNAME.VIEW_2
>> USERNAME.VIEW_3
>>
>> The APEX_PUBLIC_USER session goes into a spin on library cache
>> lock/library cache pin waits when trying to run this particular query.
>>
>> I spent all day yesterday trying to tune the query from my DBA account
>> and couldn't figure it out.
>>
>> Today I logged in as the APEX_PUBLIC_USER account and I figure out that
>> there are NO grants on these views to ANYONE. Yet, the query will
>> eventually return when executed from the middleware server.
>>
>> The query will error if I login as APEX_PUBLIC_USER directly with "Object
>> does not exist" (because no grants).
>>
>> Gathering FIXED_OBJECT_STATISTICS & GATHER_DICTIONARY_STATS seems to fix
>> the problem but I'm super confused about how this query can even run when
>> its executing as APEX_PUBLIC_USER when it doesn't have privileges on the
>> VIEWs used by the query.
>>
>> TL;DR:
>> 1. Gathering FIXED_OBJECT_STATISTICS & GATHER_DICTIONARY_STATS helps
>> resolve the library cache lock/library cache pin spins and query returns
>> 2. How does APEX_PUBLIC_USER get the necessary rights to the Views if
>> there are no grants on the views in the database?
>>
>> Chris Taylor
>>
>>

--
http://www.freelists.org/webpage/oracle-l