Re: Acessing data - security versus ease of use

"Ed Stevens" <nospam_at_noway.nohow> wrote in message news:ibissvk77mvg27a0shc6oa3qg6nkm9qau3_at_4ax.com...
> Replies embedded . . . .
>
>
> On Wed, 03 Dec 2003 09:52:59 -0800, Daniel Morgan
> <damorgan_at_x.washington.edu> wrote:
>
> >Ed Stevens wrote:
> >
> >> On Tue, 02 Dec 2003 21:07:13 -0800, Daniel Morgan
> >> <damorgan_at_x.washington.edu> wrote:
> >>
> >>
> >>>Snid wrote:
> >>>
> >>>
> >>>>I was wondering how people allow clients to access the data from their
> >>>>databases?
> >>>>
> >>>>All of our machines are locked down with firewall rules, so that only
a few
> >>>>people are allowed through the firewall; however, this prevents people
> >>>>accessing the data with ODBC which means complex methods of
replicating data
> >>>>and allowing it to be accessed are used, ie dumping the data into
another
> >>>>database which is less secure.
> >>>>
> >>>>What sort of middle tier applications or gateways are people using?
> >>>>
> >>>>Are there any alternatives such as using some sort of ODBC connection
over
> >>>>https?
> >>>>
> >>>>
> >>>
> >>>It would be remarkably valuable to know a few things first:
> >>>1. Verion and edition of Oracle.
> >>>2. Hardware platform and operating system.
> >>>3. What front-end tools are being used.
> >>>
> >>>But in general ... I never ... and I mean NEVER ... use ODBC to connect
> >>>to a database. There are plenty of solutions. Knowing more about what
> >>>you are doing would be a first step to making a recommendation.
> >>
> >>
> >> Daniel,
> >>
> >> I would be interested in some of the alternatives to ODBC. We have a
> >> growing base of people using MS-Access to develop their own reports
> >> against Oracle db's. We give them a common user-id that has read-only
> >> access, but I've never been comfortable with this, for a couple of
> >> reasons. First, I foresee the day when they will start demanding
> >> update capability. If that is granted, all data integrity goes out
> >> the window. Second, ODBC drivers seem particularly brittle -- very
> >> dependant on exact version, release, patch of both the OS (Windows)
> >> and the Oracle client.
> >
> >If MS Access then your only choice is ODBC. But database security is
> >vested in the database ... not in the front-end. You need to learn about
> >system privileges, table privileges (really object privs), roles, and
> >profiles.
> >
> Absolutely agree. Our current practice is that the Access users are
> given a particular user-id (ODBCUSER) to which we have granted the
> system privilege of CREATE SESSION and object privilege of SELECT on
> the application owned tables.
>
>
> >No one connecting should ever have the ability to insert, update,
> >delete, select, or worse except as enforced on an object-by-object,
> >column-by-column and sometimes row-by-row basis: All of which can be
> >easily implemented in Oracle.
>
> I've been vaguely aware of finer access control but have not had the
> time to persue it. Probably need to spend some time on Pete's site.
>
> The use of Access is not widespread, but just enough to be a bother.
> The aforementioned brittleness of the drivers is the biggest on-going
> problem. The typical scenario is that a user dept. commissions a new
> app. Our older ones were done in Powerbuilder, and more recently have
> been web based using ASP and VB. After the app is rolled out, one of
> the users will go to his manager and say, "If I could just get to the
> database, I could use Access to create this really useful report." Or
> one of the more 'forward thinking' managers push it from the other
> end. Either way, it has been politically impossible to say "no."

For my sins, I once suggested an Access front-end to a property management system running on Oracle. The response of the project manager was illuminating: 'We didn't spend half a million dollars to start rolling out Access stuff".

Think Penfolds Grange and Clany's Fancy "Affordable" Shiraz, and I think he had a point.

Regards
HJR Received on Wed Dec 03 2003 - 22:43:35 CST