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

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