RE: Access with ACCESS

They might drop it, but then you could have a DBMS-JOB that checks for this view in each schema and if it doesn't exist, re-creates it for them... saves having to remember doing it when creating new DEV schemas ...

In an attempt to see how tight we could make security we took this notion a step further and removed public access and ownership to ALL of the USER_, ALL_ and DBA_ views (in a separate test database of course). Worked surprisingly well. We are considering using this as part of our security strategy to lock down our database.

-----Original Message-----
Sent: Wednesday, October 16, 2002 7:54 PM To: Multiple recipients of list ORACLE-L

Here's another option which is far from perfect and is definitely sneaky (which is what I like about it)...

Depending on which ODBC driver is in use, upon connection the objects available for use will be queried via the ALL_OBJECTS database view. In the past, when we've wanted to limit the objects viewable from MS-Access and MS-Excel, we modified ALL_OBJECTS accordingly. For example, during a Sybase-to-Oracle conversion, the users were used to seeing all object names in lower-case and belonging to a schema named "dbo". So in Oracle, we created objects in a schema by that name (all in lower-case), but they always saw hundreds of other objects (usually related to public synonoyms) that they didn't care about. Instead of getting rid of all the public synonyms, we modified the view ALL_OBJECTS within their account to make it more selective about what it displayed. It seemed to work well and everyone was happy...

My suggestion to you is somewhat similar, and based upon the idea that "legitimate" users may likely not query the ALL_OBJECTS view (which could a heck of an assumption, but see how it fits). For these user accounts, create a private ALL_OBJECTS view within that account that queries from SYS.ALL_OBJECTS where 1 = 2, thereby guaranteeing that it returns no rows.

Thus, users of that ODBC driver will see nothing to query from...

Of course, they may notice the new view in their account and drop it. But I guess that depends on their level of sophistication. The upshot is that it is easy to implement and could be effective, depending on the sophistication of these users...

Just an idea; your requirements may vary and remember that different ODBC drivers may use other views or techniques to populate a list of objects. But it could be a very cheap way to lock out folks using ODBC applications...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, October 16, 2002 10:20 AM

> Some options could be:
>
> - password protected roles
> - application roles (9i)
> - virtual private database
>
> each being enabled via the applications that *you*
> control and thus not from Access.
>
> Some/all of the above could possibly be subverted in
> time by a smart and malicious user, but it should
> suffice in most cases
>
> hth
> connor
>
> --- "Jesse, Rich" <Rich.Jesse_at_qtiworld.com> wrote: >
> Unfortunately, not really. If your Oracle version
> > is at least 8i (you don't
> > say which you're using), you could create a LOGON
> > trigger where you could
> > glean the "PROGRAM" variable set by the client. The
> > problem with this is
> > that MS can sometimes have very long path names to
> > get to MSACCESS.EXE,
> > which will then be truncated by the "PROGRAM" size
> > of 50 chars (or is it
> > 64?). And different versions of Winders puts the
> > forsaken tool in different
> > locations. You could *mostly* prevent ACCESS from
> > access, but it can never
> > be 100%.
> >
> > Rich
> >
> >
> > Rich Jesse System/Database
> > Administrator
> > Rich.Jesse_at_qtiworld.com Quad/Tech
> > International, Sussex, WI USA
> >
> > -----Original Message-----
> > Sent: Tuesday, October 15, 2002 5:59 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > How do I stop users from accessing Oracle tables
> > with Access? They have
> > rights to the tables in Oracle and should have those
> > rights, yet I do not
> > want anyone to be able to pull the data off into an
> > access database. Is it
> > possible to stop this without taking their
> > privileges to the Oracle tables
> > away?
> > Thank you,
> > Laura
> > --
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Babette Turner-Underwood
  INET: babette_at_rogers.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).