Re: Acessing data - security versus ease of use

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." Received on Wed Dec 03 2003 - 14:51:40 CST