DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:to:subject:mime-version:content-type;
        b=QxyQkhYpx2UAuhWe4qI5vexehuBJ8a44i9lfAnyIwxCqO+coDA4kKueeIrj3KGLs/f1EOZL8GIzr/a1zh5fCOWpTsFd1kcyfYleZp6wYtw+4YOvQ5/ukj9dVe4vrcghfmPwwuXVFV6y1lYD7L0MRHu1UxYyn3fY+RfrECERzSbU=
Message-ID: <de807caa0708081352v5f84b0b6w5a0a13bcca194656@mail.gmail.com>
Date: Wed, 8 Aug 2007 15:52:33 -0500
From: "Dennis Williams" <oracledba.williams@gmail.com>
To: "oracle-l@freelists.org" <oracle-l@freelists.org>
Subject: Unexpected result from ALL_TABLES
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="----=_Part_126621_32263676.1186606353404"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: oracledba.williams@gmail.com
------=_Part_126621_32263676.1186606353404
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_126621_32263676.1186606353404
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<div>List,</div>
<div>&nbsp;</div>
<div>
<div>This is Oracle 8i.</div>
<div>&nbsp;</div></div>
<div>I need to write a script to ensure a&nbsp;user has access to all the tables, views, etc. that&nbsp;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&#39;t been granted to the user. Granting the role corrected the problem.
</div>
<div>&nbsp;</div>
<div>My question is why a user can see tables in ALL_TABLES that they can&#39;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&#39;t that simple.
</div>
<div>&nbsp;</div>
<div>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&#39;s there? Does every entry mean it applies to this user, or do I need to include GRANTEE in the query?
</div>
<div>&nbsp;</div>
<div>Thanks for any suggestions.</div>
<div>Dennis Williams</div>

------=_Part_126621_32263676.1186606353404--
--
http://www.freelists.org/webpage/oracle-l