Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: keeping odbc-users out?

Re: keeping odbc-users out?

From: Mark Wagoner <mwagoner_at_no.spam.iac.net>
Date: Tue, 29 Sep 1998 12:43:55 GMT
Message-ID: <3610d4c2.407208945@news.iac.net>


On Tue, 29 Sep 1998 12:43:17 +0200, Juergen Gmeiner <gj_at_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 - 07:43:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US