Re: Acessing data - security versus ease of use

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Wed, 03 Dec 2003 13:30:00 -0800
Message-ID: <1070487034.606087@yasure>

Ed Stevens wrote:

> 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."

What you present is not a problem except, as I suspect, you have a database in which no one has invested any effort on security. Why I'll bet you foks give people access to the database by granting them CONNECT.

If you had created roles and properly applied database security what you wrote wouldn't be possible. No database is brittle. Reconsider your statement. What you are describing is the result of people that don't know how, or don't care, to do a good job.

Build a proper security structure and you can grant access to anyone with any tool anytime at all.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Wed Dec 03 2003 - 15:30:00 CST