Re: ODBC ignores priveleges?

Just one quick note
USER_TAB_PRIVS_RECD will show only the objects granted to the user DIRECTLY. Objects via grants show up in ALL_TAB_PRIVS_RECD, where the role will show up in the grantee column. Could it be tables have been granted to PUBLIC (a role), or your user or public has been granted select any table privilege?

I am aware of the issue you are describing, but I have been unable to select from tables, for which I was not granted.

Hth,

Sybrand Bakker, Oracle DBA

Gerard Tromp wrote in message <37726E04.70008AE_at_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:
>
>1. Created new user, granted priveleges to 'select' from two tables.
>2. 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 - 13:17:19 CDT