Re: ODBC ignores priveleges?

select USER from dual

via odbc and see who the odbc driver is logging in as. Perhaps you have stored a fixed username/password wit the odbc setup.

odbc, nor anything, can bypass 'security'. You must be logging in as someone you are not expecting to be logged in as.

A copy of this was sent to Gerard Tromp <tromp_at_sanger.med.wayne.edu> (if that email address didn't require changing) On Thu, 24 Jun 1999 13:42:28 -0400, you wrote:

>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

--
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 - 13:08:54 CDT