ODBC Bypassing Oracle's Security :-(

ODBC Bypassing Oracle's Security :-(

Post by Brian Graha » Wed, 18 Mar 1998 04:00:00



I've set up an Oracle account where I've granted select priviledges
only on the desired tables. I've then set up synonyms to the tables.

  When I used ODBC32 and MsAccess '95, I find that I can update the
owner's tables directly, and via the synonyms I created. Again, the key
word is UPDATE. I wanted read-only access.

  I've already tried adding a entries into PRODUCT_USER_PROFILE
disabling updates and ODBC but that didn't resolve the issue. We are
using Oracle7 32 bit ODBC driver (production) 2.5.3.1.0B .

  Any suggestions? I don't intend to turn an inexperienced user loose
under these conditions. The intent here is to have LINKS to the tables,
so the user gets updated information. For now I guess I have to go with
snapshots of the database..
--

 
 
 

ODBC Bypassing Oracle's Security :-(

Post by Thomas Ky » Wed, 18 Mar 1998 04:00:00



(if that email address didn't require changing)


>I've set up an Oracle account where I've granted select priviledges
>only on the desired tables. I've then set up synonyms to the tables.

>  When I used ODBC32 and MsAccess '95, I find that I can update the
>owner's tables directly, and via the synonyms I created. Again, the key
>word is UPDATE. I wanted read-only access.

>  I've already tried adding a entries into PRODUCT_USER_PROFILE
>disabling updates and ODBC but that didn't resolve the issue. We are
>using Oracle7 32 bit ODBC driver (production) 2.5.3.1.0B .

>  Any suggestions? I don't intend to turn an inexperienced user loose
>under these conditions. The intent here is to have LINKS to the tables,
>so the user gets updated information. For now I guess I have to go with
>snapshots of the database..

ODBC cannot bypass Oracle security.  

That use must have update priveleges as well.

Have you tried logging in via SQL*Plus and seeing if that same user can update
the tables?

PRODUCT_USER_PROFILE is a table used by SQL*Plus and will not affect 3'rd party
applications.

Thomas Kyte

Oracle Government
Herndon VA

http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

 
 
 

ODBC Bypassing Oracle's Security :-(

Post by Piotr Kolodzie » Wed, 18 Mar 1998 04:00:00


Quote:>  When I used ODBC32 and MsAccess '95, I find that I can update the
>owner's tables directly, and via the synonyms I created. Again, the key
>word is UPDATE. I wanted read-only access.

>  I've already tried adding a entries into PRODUCT_USER_PROFILE
>disabling updates and ODBC but that didn't resolve the issue. We are
>using Oracle7 32 bit ODBC driver (production) 2.5.3.1.0B .

ODBC works using SQL*Net connectivity to Oracle database.
It allows you to perform such actions that are permitted
in other Oracle sessions such as in SQL*Plus, for example
and not other.
It _does not_ add any rights and privileges.
So I would connect to Oracle via SQL*Plus using account identical
as in ODBC and try to perform the same actions. If they succeed,
it means you have given too wide grants.

Other possibility (sometimes it happens): If you connect to Oracle
via ODBC (and SQL*Net) without supplying the password, it means that
operating system user verification was performed by Oracle DB.
For example -- if you have created user AAA, and in the DB there
exists user OPS$AAA and you connect to DB without supplying the password,
there is in fact a OPS$AAA user session.
In first while it looks, that ODBC ommits the user verification.

So check object permissions and the possibility of connecting
as OPS$AAA instead of AAA user.

HTH,

Piotr

 
 
 

ODBC Bypassing Oracle's Security :-(

Post by H. John C. Hopkin » Wed, 18 Mar 1998 04:00:00


Brian:

ODBC cannot bypass Oracle's security.  It's not entirely clear to me how
you've set up privileges and schemas (example--with names changed to protect
the innocent--would help).  Are you logging in under the user name that OWNS
the tables you're updating?  That would seem likely.    Are these read-only
users assigned a role with an UPDATE ANY system privilege or UPDATE object
privileges?

PRODUCT_USER_PROFILE is fine for SQL*Plus but won't help you with Access.
Let me know if you'd like a way of controlling access to Access forms and
reports.  I'm working on a VERY simple scheme for doing so.

Don't lose faith... you don't need snapshots!

:)

-John C. Hopkins
Programmer/Analyst


>I've set up an Oracle account where I've granted select priviledges
>only on the desired tables. I've then set up synonyms to the tables.

>  When I used ODBC32 and MsAccess '95, I find that I can update the
>owner's tables directly, and via the synonyms I created. Again, the key
>word is UPDATE. I wanted read-only access.

>  I've already tried adding a entries into PRODUCT_USER_PROFILE
>disabling updates and ODBC but that didn't resolve the issue. We are
>using Oracle7 32 bit ODBC driver (production) 2.5.3.1.0B .

>  Any suggestions? I don't intend to turn an inexperienced user loose
>under these conditions. The intent here is to have LINKS to the tables,
>so the user gets updated information. For now I guess I have to go with
>snapshots of the database..
>--


 
 
 

ODBC Bypassing Oracle's Security :-(

Post by Brian Graha » Thu, 19 Mar 1998 04:00:00



> I've set up an Oracle account where I've granted select priviledges
> only on the desired tables. I've then set up synonyms to the tables.

>   When I used ODBC32 and MsAccess '95, I find that I can update the
> owner's tables directly, and via the synonyms I created.

  Well, I found out the answer. Never let your manager loose with the
DBA account! ;-)  Although I ran a script granting select only on the
tables, my manager had previously "grant ALL on.. to PUBLIC" . So, it
was picking up the public rights.
  How does Murphy's law go? In any collection of data, the piece most
certainly correct beyond all need of checking is the error. Something
like that.
  Thanks to all who responded.
--

 
 
 

1. CopyObject code (Access Security bypass)

In my secured db (Access 7.0 mdb) I had 2 Tables:
Table1 was secure
Table2 was not secure

Running Peter's code indeed created the new mdb, but...
only the non-secure table made it into the new database.
The protected table was NOT copied with CopyObject.

( I had revoked Admin's priveleges and made the original
database under the new priveleged owner, per the
security guidelines)

Am I missing something?   Or is Access 7.0 immune?


2. Diagram of Indexes and Tables?

3. Bypassing JET Security

4. ADO Error when using OraOLEDB.Oracle on a .Net machine

5. HOW TO BYPASS FINE-GRAINED Security Enforcement !!!

6. Replication from Oracle and DB2

7. Bypassing NET with better security

8. - Oracle Financials Systems Analyst

9. Numeric Output problem with 'MS ODBC Driver to Oracle'/Access'97/Oracle

10. Send mail bypassing ISP's SMTP server.

11. Don't want to use Bypass Key in Access97

12. Bypassing the OCI and using SQL NET's inteface to check on a Server

13. I'm searching to bypass passwords