Re: Perl Issues - checking
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 22 Feb 2008 11:03:29 -0800 (PST)
Jack
ALL_TABLES is a meaty view - look at all the tables and X$ tables it joins.
Have you considered making a stripped down version which returns only what you
need?
- if you want tables owned by current user
select name
from obj$
where type#=2
and owner# = userenv('SCHEMAID')
- if you want tables granted directly to user
create view TABLES_I_CAN_SEE as
select u.name owner
, o.name table_name
from sys.user$ u, sys.tab$ t, sys.obj$ o
where o.owner# = u.user#
and o.obj# = t.obj#
and bitand(t.property, 1) = 0
and bitand(o.flags, 128) = 0
and (o.owner# = userenv('SCHEMAID')
or o.obj# in
(select oa.obj#
from sys.objauth$ oa
where grantee# = userenv('SCHEMAID') -- this finds direct grants
only
-- where grantee in ( select kzsrorol from x$kzsro) -- the original
subquery checks roles
)
or /* user has system privileges */
exists (select null from v$enabledprivs
where priv_number in (-45 /* LOCK ANY TABLE */,
-47 /* SELECT ANY TABLE */,
-48 /* INSERT ANY TABLE */,
-49 /* UPDATE ANY TABLE */,
-50 /* DELETE ANY TABLE */)
)
)
/
Should reduce the consistent gets more than a little - and depending on your
role-based access model, you may be able to lose the system privilege subquery.
Possibly your problem is aggravated by the sheer number of entries in objauth$.
If the app is always run in schemas which don't own the tables, you could
flatten out the query - don't compare o.owner# to SCHEMAID and convert the
o.obj# in subquery into a join.
You may be concerned about maintainability over Oracle releases. However OBJ$,
TAB$ and USER$ are at the core of the online dictionary and the fundamentals
haven't changed for as long as I can remember - certainly not since 5.0; if
these change on a database upgrade I expect it will be the least of your
problems (fancy migrating 150k tables?)
Regards Nigel
Other related posts:
