Restricting ODBC access

Jacob Salomon

unread,

Feb 11, 1998, 3:00:00 AM2/11/98

to

Hi Family.

I have a client with a serious concern.

Say I (jake) am a regular end user - when I log in to my Unix box, I
fall into a captive session, the application's main menu (no shell). In
this way I can hit at the database only through the application. BTW,
database access happens to be through a shared memory connection,
although I suppose socket access should also be set up for this system.

Now I get clever - the greatest nightmare of an operations manager. I go
back to my PC and access the database via an Excel spreadsheet with
ODBC. I run a query and get the results in my sheet. I then update some
rows in the sheet (for what-if analysis - innocent) and inadvertently
save the data back to the database.

Whoops! I have just messed with some data without going into the
application.

Can anyone come up with a way to prevent database access via ODBC? I
can't simply deny ODBC drivers to all PC users - there are other
databases to be accessed. The restriction I need should be settable on
a database by database basis. (Say that fast 10 times. ;-) Ideally, it
should be able to restrict data modifications via ODBC.

The one idea I came up with I really don't like: Give everyone different
user-id's on the Unix box and on their PC's. Since the PC-based user ID
has not been granted priveleges, the database is protected.

Now I have noted that whe I access the database from Relational Object
Manager (where does Informix come up with these silly product names?)
and run onstat -u, my PC session shows up as user JAKE while a regular
unix based session shows up as user jake. Might this be suffucient to
prevent updates via ODBC connections? I can't test this myself because
I have no ODBC drivers on my PC.

Any better ideas?

Thanks.
--
-- Jake (Pondering the color of an asphyxiated smurf)
+------------------------------------------------------------+
| The expedient performance of a task with excessive concern |
| regarding its duration-to-completion engenders a virtual |
| certainty of diminished benefit therefrom. |
| -- Benjamin Franklin (but he said it in 3 words) |
+------------------------------------------------------------+

Dave Otto

unread,

Feb 11, 1998, 3:00:00 AM2/11/98

to

Easy if you are running 7.1+. Use ROLES. If there is no default role
defined, then the first the application must do is execute the
"set role..." statement. Unless the user has access to a SQL
command AND knows the appropriate role to set, s/he will be
locked out.

Jacob Salomon wrote in message <34E224B1...@garpac.com>...

>Hi Family.
>
>I have a client with a serious concern.
>

....

Michael Segel

unread,

Feb 11, 1998, 3:00:00 AM2/11/98

to

Dave Otto wrote:

> Easy if you are running 7.1+. Use ROLES. If there is no default role
> defined, then the first the application must do is execute the
> "set role..." statement. Unless the user has access to a SQL
> command AND knows the appropriate role to set, s/he will be
> locked out.

Uhmm, no, not so easy.
You said it yourself.
If you know what role to set, voila!

Straight client/server with Informix isn't a very good idea.

1) Must have UNIX id and passwd for each user,
(That's if you want to use Informix to authenticate the user.)

2) PC must become trusted by system.
Forget .rhosts. Biggest security nightmare.
That means hosts.equiv.
Hosts equiv is literally just that. ;-)

3) You now must manage PCs and Users which can become a nightmare.
(You're not going to trust every user from the PC right? And you aren't
going to let them store the access info on their PC right?)

4) Limitations in choice of connecting platforms.
VB? Delphi? VC++? whatever? Guess what? They be all Microsloft access
clients.

Now if you have smart cards, or smart rings, and or consider biometrics,
then
you have a touch more security.

But you may still want to consider 3 tier approach to a design.
Web based client, or Java based client, with an app server.
Then you only have to trust the app server.
Smart card auth still applicable.

Informix works better in a three tier environment.

Just a few wise words from the paranoid man from UNCLE!

-Mikey

Irwin Goldstein

unread,

Feb 12, 1998, 3:00:00 AM2/12/98

to

Jacob Salomon <ja...@garpac.com> wrote in article

<34E224B1...@garpac.com>...
> Hi Family.
>
> I have a client with a serious concern.
>

> Say I (jake) am a regular end user - when I log in to my Unix box, I
> fall into a captive session, the application's main menu (no shell). In
> this way I can hit at the database only through the application. BTW,
> database access happens to be through a shared memory connection,
> although I suppose socket access should also be set up for this system.
>
> Now I get clever - the greatest nightmare of an operations manager. I go
> back to my PC and access the database via an Excel spreadsheet with
> ODBC. I run a query and get the results in my sheet. I then update some
> rows in the sheet (for what-if analysis - innocent) and inadvertently
> save the data back to the database.
>
> Whoops! I have just messed with some data without going into the
> application.
>

> Can anyone come up with a way to prevent database access via ODBC? I
> can't simply deny ODBC drivers to all PC users - there are other
> databases to be accessed. The restriction I need should be settable on
> a database by database basis. (Say that fast 10 times. ;-) Ideally, it
> should be able to restrict data modifications via ODBC.
>

<snip>

Normally with Informix ODBC drivers you will have to set up a socket
connection to the server. Once this is done, anyone with the Informix ODBC
drivers on their PC and a Unix log in will be able to connect to the
database with whatever permissions are associated with their log in. If
you use a server based ODBC product such as OpenLink, you can avoid the
socket connection. The OpenLink server piece will talk to Informix through
the shared memory connection. Thus the only way to the database over the
network will be through OpenLink. The OpenLink request broker on the
server has a configurable rule book that will allow you to restrict access
by user, host (client), application, database, client O/S, etc. The one
caveat here is that is fairly easy (using something like MS Access) to
change the name of the "application", so this should not usually be relied
upon as airtight. Assuming you have NO ODBC applications which need R/W
access for the users you are worried about, you can make things pretty
tight by restricting EVERYONE, from every machine using every application
to read only (or no) access. Then enable R/W access for those users you
"trust". The possible combinations are numerous.

HTH
Irwin Goldstein
Objective Software Systems, Inc.
http://www.objectsoft.com

Remove anti-spam characters from e-mail address when replying.

Allan Gould

unread,

Feb 12, 1998, 3:00:00 AM2/12/98

to Jacob Salomon

Jacob Salomon wrote:

> Can anyone come up with a way to prevent database access via ODBC? I
> can't simply deny ODBC drivers to all PC users - there are other
> databases to be accessed. The restriction I need should be settable on
> a database by database basis. (Say that fast 10 times. ;-) Ideally, it
> should be able to restrict data modifications via ODBC.

Our ODBC driver has a security component which allows you to restrict access
to your database via ODBC by table, user, SQL statement (i.e. allow
read-write/read-only access etc) in various combinations. Take at loook at
SCO SQL-Retriever. http://www.sco.com/vision/products/sqlretriever/ for more
information and a downloadable eval.

Allan Gould
(allang at sco dot com)
(Please remove anti-spam measures if replying)

Richard Spitz

unread,

Feb 12, 1998, 3:00:00 AM2/12/98

to

Jacob Salomon wrote:

> [ DBA's nightmare with ODBC access ]

> Can anyone come up with a way to prevent database access via ODBC? I
> can't simply deny ODBC drivers to all PC users - there are other
> databases to be accessed.

Jake,

I share your nightmare. We have the same setup: UNIX accounts which
only access the database via an application are ok, but ODBC access
with the UNIX privileges can wreak havoc on the database.

Using ROLEs in 7.x may help, no option for us since we are still in
5.x. Using Openlink or another server-based ODBC engine is an option,
since it allows you to administer ODBC access privileges centrally.
However, this solution will only be secure if you can totally shut
off Informix-Net or -Star (in 5.x). We cannot because we have our
4GL and ESQL/C applications on an application server that has to
access the Online database server via I-Net.

I don't know if it is possible to replace the native Informix network
access via I-Net (which requires no change in the application, just
an appropriate setting of DBPATH) with Openlink or similar. Even if
an Openlink client were available for our UNIX platform (SINIX),
I'm afraid I'd have to considerably change our application so it
could make use of it.

John H. Frantz

unread,

Feb 12, 1998, 3:00:00 AM2/12/98

to

Someone once mentioned making the role name a secret, i.e. the name of
the role itself becomes a sort of password which would be built into the
applications. Does this not work?

--
----------------------------------------------------------------------
John H. Frantz Power-4gl: Extending Informix-4gl
jo...@rl.is http://www.rl.is/~john/pow4gl.html

Michael Segel

unread,

Feb 12, 1998, 3:00:00 AM2/12/98

to

John H. Frantz wrote:

> Someone once mentioned making the role name a secret, i.e. the name of
> the role itself becomes a sort of password which would be built into the
> applications. Does this not work?
>

Sure,
Only it will come back to haunt you, and still, even though its *sekret*, it
doesn't really solve the problem.

First, it could get *lost* meaning that over time your development team changes,
and
well, things are not always documented.....

But while this is really minor, the exact oposite may occur.
Developers have to know the role, and that role may be used by several different
applications.
So while you may limit the audience, you still have a threat of a disgruntled
employee
or someone going after different data that he or she is not normally allowed to
see.

By going to a three tier arch. you can limit direct access to your production
data.
You limit the access to the servers, you limit access to your app servers (if
they are not
the same machine.) Data is now more secure.

Does it solve all the problems?
No. But it reduces the threat risk.

-Don't mind me, I'm paid to be paranoid.
Mikey

Jacob Salomon

unread,

Feb 12, 1998, 3:00:00 AM2/12/98

to

I, Jacob Salomon, posted a request for solutions (RFS? ;-):

> Can anyone come up with a way to prevent database access via ODBC? I
> can't simply deny ODBC drivers to all PC users - there are other

> databases to be accessed. The restriction I need should be settable

> on a database by database basis. (Say that fast 10 times. ;-) Idealy,

> it should be able to restrict data modifications via ODBC.

Thanks to all who responded, both by e-mail and posting. For the benefit
of the rest of the family, I am summarizing the answers I got. I
believe all of them will help get my client going.

"Dave Otto" <do...@themoneystore.com> posted:

|Easy if you are running 7.1+. Use ROLES. If there is no default role
|defined, then the first the application must do is execute the
|"set role..." statement. Unless the user has access to a SQL
|command AND knows the appropriate role to set, s/he will be
|locked out.

As I recall, roles are a feature of 7.2+, but since this user is on the
upgrade path (as opposed to the warpath ;-) this should not be a
problem. Since the client's main concern is an inadvertent update by a
user of Excel (or equivalent application), this alone might be adequate.
I suppose this depends on if there is a way in Excel to issue commands
like "set role". I kinda doubt it.

Michael Segel <Mi...@NOSPAM.KingofMyDomain.MAPSON.Segel.com> posted an
objection relating to the PC management issues. Valid objections I will
raise with other clients but (I think) not a major concern to this
client at this time.

Irwin Goldstein <irwin...@NOSPAMobjectsoft.comNOSPAM> posted a small
manual on using OpenLink to diverting the socket connection from the
database server to the server piece of OpenLink. This looks like a very
viable solution to the problem if roles don't do the job.

Allan Gould <all...@sco.com_no_spam> posted:

|Our ODBC driver has a security component which allows you to restrict
|access to your database via ODBC by table, user, SQL statement (i.e.
|allow read-write/read-only access etc) in various combinations. Take

|at look at SCO SQL-Retriever.

| http://www.sco.com/vision/products/sqlretriever/
|for more information and a downloadable eval.

Another possibility, although the issue of PC management raises its
head. What's to enforce the use of this ODBC driver if a stubborn &
clever user wants to use the ODBC driver he already had? Again, not a
real objection in this case, since the concern here is over inadvertent
updates.

Eric e-mailed me the following:

|Try OPENPATH (from Trilogy) ODBC driver or OPENLINK ODBC drivers both
|offer access to the database based upon the user name and also allow
|read or read/write capabilities.

That's the second time I've seen OpenLink mentioned as a solution. I
will look into OpenPath as well.

Thanks all. I think with all this info to digest, my client will not
bother me about it for another few months! ;-))

Eric R. Gavin

unread,

Feb 12, 1998, 3:00:00 AM2/12/98

to

>> [ DBA's nightmare with ODBC access ]

>> Can anyone come up with a way to prevent database access via ODBC? I
>> can't simply deny ODBC drivers to all PC users - there are other
>> databases to be accessed.

Uh, I'm not sure what the problem is, but the ODBC driver that I use asks me
for a password.

Informix has built-in security in it's roles and priveleges. What's the
problem?

If it's password encryption that's a problem, that's out of my field. But
the ODBC I rely on is not a double-click and you're in kind of thing. You
need a password.

Eric Gavin

Peter Tashkoff

unread,

Feb 13, 1998, 3:00:00 AM2/13/98

to

howdy
This does not answer all your questions, nor all the questions that the =
whole issue of ad-hoc database access leads to, but it is a practical =
solution in place here and it seems to work.

This solution requires the use of an ODBC driver that supports security =
features. We use Openlink which you can download from www.openlinksw.com. =
(Non expiring 2 user eval).

This is how I do it.

1. I have created an end-user-computing (euc) instance of Online on the =
same host as the production instance.
2. In this instance are databases named euc_[LiveDatabaseName].
3. Nightly, cron runs a short script that drops all views in the above and =
rebuilds them on the basis of the structures in the live production =
databases. These are read-only views on the live system.
4. Specific application access is allowed to the production system for =
specific users using the openlink security features. Access to the euc =
instance is open.

No-one has I-Net (or it's equivalent these days). Only openlink, which is =
an open driver.
If a user tries to access the production instance via ODBC and if they are =
not an authorised user and application, they are rejected by Openlink.
Access to the euc instance is open for anyone with connect permission, but =
the views are read-only. =20

There are a few gotchas, people have had to learn to be wise in how they =
structure their selects as remote joins etc can come into play if they are =
creating temp tables in euc.
But on the whole it has been very successful. We are in our third year of =
doing this., and it is practical and works.

It would not stop a determined attack by a knowledgeable end-user but it =
does stop the kind of problem you are referring to.

I can post the script which creates the views if you like.

rgds

Zespri New Zealand Kiwifruit, The Worlds Finest

>>> Jacob Salomon <ja...@garpac.com> 12/02/98 11:22:41 >>>
Hi Family.

I have a client with a serious concern.

Say I (jake) am a regular end user - when I log in to my Unix box, I
fall into a captive session, the application's main menu (no shell). In
this way I can hit at the database only through the application. BTW,
database access happens to be through a shared memory connection,
although I suppose socket access should also be set up for this system.

Now I get clever - the greatest nightmare of an operations manager. I go
back to my PC and access the database via an Excel spreadsheet with
ODBC. I run a query and get the results in my sheet. I then update some
rows in the sheet (for what-if analysis - innocent) and inadvertently
save the data back to the database.

Whoops! I have just messed with some data without going into the
application.

Can anyone come up with a way to prevent database access via ODBC? I

can't simply deny ODBC drivers to all PC users - there are other

databases to be accessed. The restriction I need should be settable on

a database by database basis. (Say that fast 10 times. ;-) Ideally, it

should be able to restrict data modifications via ODBC.

The one idea I came up with I really don't like: Give everyone different

user-id's on the Unix box and on their PC's. Since the PC-based user ID
has not been granted priveleges, the database is protected.

Now I have noted that whe I access the database from Relational Object
Manager (where does Informix come up with these silly product names?)
and run onstat -u, my PC session shows up as user JAKE while a regular
unix based session shows up as user jake. Might this be suffucient to
prevent updates via ODBC connections? I can't test this myself because
I have no ODBC drivers on my PC.

Any better ideas?

Thanks.
--=20

Emmon Simbo

unread,

Feb 13, 1998, 3:00:00 AM2/13/98

to

Hi Jake

As you might have gathered, Openlink does provide you with the
functionality, amongst other things, to restrict access to Databases.

On the server side, we have a rulebook. Within this rulebook, you can set
up different environments for your Database tables and databases if you
have more than 1. An example is, if you had five users in Finance needing
Payroll information but you wanted to restrict the other 100 users form
seeing this information or having Read-Only access, all you do, is create
an environment called Finance or Payroll or whatever name you desire.
Within this environment you can set up the users who need access. On the
client side, you create datasources with the specific user names and
passwords. When a connection is made to the server, our Rulebook verifies
if the user is valid and the type of access s/he has and allows the
appropriate action

We now provide a new functionality within our new drivers called the Web
Configurator that allows the broker to be configured without the need to
having to log into the server(bar starting the server up). All the setup
for access is done through the Rulebook.

Why not download an evaluation copy from www.openlinksw.com or
www.openlink.co.uk. We offer a non-expiry evaluation driver for you to test
functionality.

Emmon Simbo
OpenLink Software
.

Jacob Salomon <ja...@garpac.com> wrote in article
<34E224B1...@garpac.com>...

Richard Spitz

unread,

Feb 13, 1998, 3:00:00 AM2/13/98

to Eric R. Gavin

Eric R. Gavin wrote:
> Uh, I'm not sure what the problem is, but the ODBC driver that I use
> asks me for a password.

Of course it does. The problem is that "normal" users use that login
and password to log into a UNIX machine, where they directly enter
an application that communicates with the database. No shell access,
no SQL or other direct database access.

However, nothing prevents one of these users to install Informix-CLI
or another I-Net based ODBC driver on any PC and use their UNIX login
and password to directly access the database. Ouch!

> Informix has built-in security in it's roles and priveleges. What's
> the problem?

Roles are not available in version 5.x of the engines. So database
privileges have to be allocated on a per-user basis, and can be abused
in the above described manner.

Kelly Ringwald

unread,

Feb 13, 1998, 3:00:00 AM2/13/98

to

An option at design time, is to control modifications to the database
by making all updates/inserts through stored procedures. This
approach would allow read-only access to the DB via ODBC.

A really proficient user could get around this, but not without
deliberate intent, which can be remedied with other tools, such as a
blunt instrument. ;-)

Regards,
Kelly

On Wed, 11 Feb 1998 22:22:41 GMT, Jacob Salomon <ja...@garpac.com>
wrote: