Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Unexpected result from ALL_TABLES
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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 08 2007 - 15:52:33 CDT