Re: ODBC ignores priveleges?

A copy of this was sent to granta_at_nospam.student.canberra.edu.au (Fuzzy) (if that email address didn't require changing) On Wed, 23 Jun 1999 23:24:24 GMT, you wrote:

>On Tue, 22 Jun 1999 14:20:51 -0400, Gerard Tromp
><tromp_at_sanger.med.wayne.edu> wrote:
>
>>Greetings,
>>
>> I have a peculiar situation. When using sqlplus on the server, any
>>particular user can only see the tables created by, or granted to, that
>>user (direcly or via role). When the database is queried using ODBC from
>>a Win95 client, however, all the tables in the tablespace are visible
>>and are selectable. Any clues?
>
>Yes, all your users are by default a member of PUBLIC, and PUBLIC by
>default has select privileges on many system catalog tables, including
>sys.all_tables.
>
>This is a gigantic security hole that I wish Oracle would remove.

why would you think this is a security hole? The all_* views limit the data returned to the end user so they can only see objects they have ACCESS to.

Saying the All_* views is a security is similar to saying "dir *.*" is a security hole -- actually, maybe "dir *.*" is since it shows me files I may not be able to see -- select * from all_objects will only show me objects I can access.

It is the dba_* views that offer unfiltered access to see all objects in the database and the dba_* views

• are not granted to public
• are generally not accessible to end users unless you make it so.

>Thankfully, you can do it manually by either droping PUBLIC, or
>revoking it from all users.
>

better not drop public (does that actually even work -- no:

SQL> drop user public;
drop user public
 *
ERROR at line 1:
ORA-01935: missing user or role name

SQL> drop role public;
drop role public
 *
ERROR at line 1:
ORA-01935: missing user or role name
SQL> and revoking select on the all_* views will break many things -- like ODBC here which is querying this view.

>Ciao
>Fuzzy
>:-)

--
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 Thu Jun 24 1999 - 06:22:21 CDT