Re: Acessing data - security versus ease of use

On Wed, 03 Dec 2003 13:30:00 -0800, Daniel Morgan <damorgan_at_x.washington.edu> wrote:

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

On the security questions, you won't get any argument from me that we could do a lot better. (We don't grant the CONNECT role, however.) And you won't get any argument from me that there is a lot of DBA laziness involved. Don't even get me started on *that* soapbox! Just imagine trying to pull a wagon with a double-hitch -- one a horse and the other a mule . . .

My reference to brittleness wasn't to the database but to ODBC drivers themselves. It's not at all unusual for us to uncover a particular query that works with one combination of Windows/Access/ODBC driver/Oracle client/Oracle db, and not with some other combination. This is my real concern with ODBC pe se. Received on Thu Dec 04 2003 - 09:26:55 CST