Re: ODBC ignores priveleges?

From: Gerard Tromp <tromp_at_sanger.med.wayne.edu>
Date: Thu, 24 Jun 1999 13:42:28 -0400
Message-ID: <37726E04.70008AE@sanger.med.wayne.edu>

Gerard Tromp 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?
>
> Details:
> ========
> Database : 7.3.2.0.0
> Server_HW: SparcStation 20
> Server_OS: Solaris 2.5.1
>
> ODBC driver on Win95: 2.5.3.1
>
> Gerard
> PS: Please cc me by e-mail; while I will try to read replies on the
> newsgroup, I have noticed that there are sometimes messages that appear
> on my newsfeed a week or more after being posted. I would prefer not to
> miss any responses. Thank you.
> --

Received a few responses and read some others in the newsgroups. It appears that I should clarify. The problem is _not_ with seeing all table names (that may be annoying since one may not be interested in seeing _all_ the system tables, but it is not a problem). It has to do with being able to access data to which a user should _not_ have access. Herewith, some clarification.

ODBC does a 'select * from all_tables', or perhaps more correctly, at least 'select table_name from all_tables'. Although that is annoying, the problem I have is with priveleges to see/obtain the data in the tables themselves.

More details as follows:

Created new user, granted priveleges to 'select' from two tables.
Check above user using sqlplus login on server, select from any table other than the ones granted, returns 'table or view not found'. SQL> select * from emp; select * from emp * ERROR at line 1: ORA-00942: table or view does not exist

Good thus far.

3. Deleted and recreated ODBC sources on Win95 client with the above

user specified (just to be sure).
4. Used the ODBC driver on Win95 client to select data from a file for

which no permission was granted as same new user as above, and _voila!_ 'ze data are zere'.

e.g. from the demo table emp (scott/tiger).

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 SMITH      CLERK	      7902 17-DEC-80	    800 		   20
      7499 ALLEN      SALESMAN	      7698 20-FEB-81	   1600       
300	   30

[SNIP remainder]

Hmm! Something fishy!. 5. Login using from Win95 client using sqlplus. Select from table with no permission and _tada_ 'table or view not found.

Conclusion -- the combination of ODBC driver and other software (dll's) on the Win95 client somehow are able to ignore priveleges and, although, I have not tried each table, I have been able to download from any table that I have tried, specifically those not listed in the USER_TAB_PRIVS_RECD (the new user has not tables of his own).

:SQL>  select * from user_tables;
:
:no rows selected


	I'm confused as to what conspires to generate this situation.

Perhaps I'm missing something elementary -- that's why I'm asking around -- although I think that I have taken reasonable steps to rule out the obvious mistakes.

Gerard
--

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Gerard Tromp, Ph.D.
CMMG, Wayne State University    vox:	313-577-8773
3116, Scott Hall		fax: 	313-577-5218
540 E Canfield Ave		e-mail: tromp_at_sanger.med.wayne.edu
Detroit, MI 48201                       gtromp_at_cmb.biosci.wayne.edu

Received on Thu Jun 24 1999 - 12:42:28 CDT