Re: Problem with Security

A copy of this was sent to Dieter Ratsch <extern.dieter.ratsch_at_volkswagen.de> (if that email address didn't require changing) On Fri, 02 Jul 1999 12:39:27 +0200, you wrote:

>Hi there,
>we are using a new Oracle 8.0.5 Server with some tables of the owner
>ZBM and a few users.
>Our problem is, that a user should have access to only one table and
>not to the other tables, but all users can access all tables?!
>
>For example:
>User Willy has no roles and even no rights for any objects, no
>connect-rights and no resource-rights. We expected, that Willy can
>do nothing with the Oracle-DB.
>Fact is, he can access to all tables by using the SQL-Worksheet or by
>using MS-ACCESS and with ODBC-Driver!
>
>We revoked all rights from the tables and we expected, that Willy
>finally now has no access - but he can connect and select all tables!!!
>We deleted Willy and created a new user - the same problem.
>
>What the hell is the reason? Can anybody help me?
>Regards
>Dieter

Tell us what the following queries return:

SQL> select * from dba_sys_privs where grantee = 'PUBLIC';

no rows selected

SQL> select * from dba_role_privs where grantee = 'PUBLIC';

no rows selected

SQL> l
  1 select * from dba_tab_privs
  2* where grantee = 'PUBLIC' and owner not in ( 'SYS', 'SYSTEM' )

that will show you the privs that EVERYONE has. pay particular attention to the dba_sys_privs and dba_role_privs. For example, if you grant select any table to public (i've seen people do it) and grant connect to public, everyone can log in and everyone can select from any table.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jul 02 1999 - 07:11:24 CDT