Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Query Table Privilege Drill Through Role Hierarchy
What do you want to achieve by such a query?
You also have to consider the case that somebody has access to the table by views or by procedures. This you cannot that easily detect because you have to visit dba_views and dba_dependencies.
And you have to build the convex hull. This is usually a big operation.
Regards,
Martin
Richard Pike wrote:
>
> I need a highly optimized query (in EE 8.1.7) to determine if a specific
> user has a given privilege on a given table. It would be easy except that
> the table privilege is granted at an unknown level in a hierarchy of roles.
> Even a simple join of assuming a role depth of 1 takes too long for our
> needs; walking the role would take far too long.
>
> My other idea is to build a materialized view of users, tables, and
> privileges with an automatic refresh. Would this work?
>
> Thanks.
> dgmorris2_at_airbridge.net
Received on Wed Dec 19 2001 - 09:44:56 CST