Unexpected result from ALL_TABLES
- From: "Dennis Williams" <oracledba.williams@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 8 Aug 2007 15:52:33 -0500
List,
This is Oracle 8i.
I need to write a script to ensure a user has access to all the tables,
views, etc. that are needed. So I assumed that I just needed to query
ALL_TABLES as the user. Did that and the needed tables were listed. Then I
tried doing a describe on one of the tables and received the old ORA-00942:
table or view does not exist. Turned out that the tables were granted to a
role, but that role hadn't been granted to the user. Granting the role
corrected the problem.
My question is why a user can see tables in ALL_TABLES that they can't
select? Is there a better way to do this? I thought about doing this from
DBA_TABLES, but was concerned about the different permutations of whether a
table was granted to a role, then granted to the users. I thought if the
user could see the table in ALL_TABLES, then the user would indeed have
access, but apparently it isn't that simple.
For some tables, I also want to ensure the user can update, insert, whatever
they should be able to do. Should I use ALL_TAB_PRIVS for that? Does anyone
know if there are any gotcha's there? Does every entry mean it applies to
this user, or do I need to include GRANTEE in the query?
Thanks for any suggestions.
Dennis Williams
Other related posts:
