Re: keeping odbc-users out?

From: John P. Higgins <jh33378_at_deere.com>
Date: Tue, 29 Sep 1998 17:26:52 -0500
Message-ID: <36115EAB.35442938@deere.com>

In SQL*Net's protocol.ora file, you can list approved nodes or list dis-approved nodes. This might help.

Mark Wagoner wrote:

On Tue, 29 Sep 1998 12:43:17 +0200, Juergen Gmeiner
<gj@moc.nospam.inait> wrote:

>hi there,
>
>some of our customers are apparently starting to access our database
>(oracle wgs 7.3.2) via odbc.
>
>this is a medical application (archival of medical images) and somehow
>this makes me very nervous, imagining some crazy luser starting to
>delete stuff in ms-acce*s or something ...
>
>is there a way to restrict odbc-access to a read-only user?
>
>only solution i've come up with so far is to use "identified externally"
>for
>our application user and ensuring REMOTE_OS_AUTHENT is set
>to false.  "read-only" users would be created with "identified by
>SOMEPASSWD",
>so the customers would be forced to use those via odbc.
>
>only drawback is that we are currently developping a java-based intranet
>client
>who depends on sqlnet access.
>
>anyone been there? what did you do?
>
>regards,
>juergen
>

Since you can't tell an ODBC user from a SQL*Net user on the server
side, about the only option I have found is to restrict access to
certain tables through roles.

We created a role that allows read-only access to some tables and
another role (multiple roles, actually) that allow
insert/update/delete on the tables.  The read-only role is the user's
default role, so when the user connects they have read-only access by
default.  You must explicitly issue a SET ROLE statement to enable the
other roles granted.  If you really want to lock it down, you can
assign a password to these roles as well.

Of course, this change would require you to go through your client
apps and have it issue the SET ROLE statement when it starts up.  This
should not be a big change but could take some time, depending on the
size of your application.  We use the SET ROLE within the app as
another safeguard.  If the SET ROLE statement returns an error, the
user has not been granted the role and, therefore, is not authorized
to use the application.  This is how we restrict what user can do what
functions.

HTH

--
Mark Wagoner
To reply, remove no.spam from my e-mail address

  Received on Tue Sep 29 1998 - 17:26:52 CDT