Re: Limiting Oracle ODBC functionality

In article <3deslk$1tn_at_aggedor.rmit.EDU.AU> dtb_at_hera.bf.rmit.edu.au (David Bath) writes:
>From: dtb_at_hera.bf.rmit.edu.au (David Bath)
>Subject: Re: Limiting Oracle ODBC functionality
>Date: 23 Dec 1994 16:08:20 GMT
 

>wmeahan_at_ef0424.efhd.ford.com (Bill Meahan) writes:
 

>>negandhi_at_io.org (Neil Negandhi) wrote:
>>>We've just installed the new Oracle ODBC driver and everything seems to
>>>work perfectly (we're running SQL*NET). However, before this tool is
>>>released to our users we'd like to limit its functionality. Right now,
>>>anyone with the driver and a little know-how can attach Oracle tables
>>>in Access and start to modify the data. Is there a way to limit users
>>>to read-access only? That way they could use Microsoft Query to pull
>>>data into Excel without data integrity being compromised.

>>Sure. Don't grant them update or insert permissions on the tables!
 

>>Of course, if you want them to have update/insert with
>> **some** applications but not others, you're SOL.
>Not quite SOL (which I don't know, buyu guess it means stuffed)
>If you have v7 you are laughing. Executing procedures run with procedure
>owner permissions - kindof like setuid bits in UNIX.
>--
>---
>David T. Bath | dtb_at_ftp.bf.rmit.edu.au (mail=dtb_at_bf.rmit.edu.au)
>3/175 Wattletree Rd | Snr Tech Consultant, Global Consulting Services
>MALVERN 3144 VIC | 179 Grattan St, Carlton, Vic 3053, Australia
>+61 3 500 9337 | Ph:+61 3 3477411 Fx:+61 3 3470182 Mb: +61 15 824171
>WWW pages incl. ORA FAQ: lynx http://www.bf.rmit.edu.au/~dtb

A common practice, (not fool proof but what is) is to use non default roles. Make your user's default roles with select only grants. Then in you application enable the additional roles they need to do the updates. This 'enable' is only valid for the connection so when they connect from the adhoc tool they will not have the role. Then in the application enable the additional role(s) needed. The hole is the user that knows the 'SET ROLE...' command. Of course some adhoc tools wouldn't let them enter it anyway since it's not DML.

Lawrence..... Received on Tue Dec 27 1994 - 09:10:04 CET