FW: Security and the ODBC
Tim Kelly
-----Original Message-----
From: Tim Kelly
Sent: Monday, September 08, 1997 10:57 PM
To: 'Mi...@NOSPAM.KingofMyDomain.MAPSON.Segel.com'
Subject: RE: Security and the ODBC
The first idea just doesn't cut it. The permissions and etc just don't
cut it. Remember, you are trying to limit what they see or can do based
on certain data like a user, workstation, security level etc.; much more
than what the simple permissions allow. Just think, if you wanted to
also limit what the user could do at each workstation based on the
client id and not the user id. For example, if they are logged in on a
machine in a different office (other than their primary office) they are
not allowed to access a printer etc.
But, the second idea has merit; or at least has me thinking you may be
on to something. I need to think it through and that just might be what
I'm looking for.
Thanks!
:-)
-----Original Message-----
From: Mike Segel [SMTP:Postm...@127.0.0.1]
Sent: Saturday, September 06, 1997 11:41 AM
To: inform...@rmy.emory.edu
Subject: Re: Security and the ODBC
Tim Kelly wrote:
> MS-Access. Now MS-Access (along with others) can just do what
ever it
> wants to my data. In the above example the user is allowed to
delete from
> the table and the application is ensuring the rsrc_id matches.
So, is
> there a way to tell Informix to accept connections based on an
application?
> Any ideas other then writing a billion triggers, SPL etc etc?
First idea:
Use the permissions of the database to control user's
permissions.
Second, and a different tact.
Create a *user* per application. Then control to the database
based on the application id. Of course, then you need to have
your
application authenticate the actual user id.
At first blush, either one will work.
--
#include <std_disclaimer.h> /* Mike Segel (MS385) */
#include <No_Spam.h>
#ifdef OFFENDED_BY_CONTENT
The author takes no responsibility for this post.
Any resemblence to a coherent rational thought is purely
coincidence.
-The Management.
#endif
*****************************
Due to AGIS's Refusal to Act Responsibly
We are blocking all of their domains at the packet level.
This block will exist until AGIS modifies their policies to
conform to existing RFCs and net community standards.
We encourage all ISPs and domain holders to do the same.
*****************************
Tim Kelly
Let me take another stab at this. I'm not suggesting you're totally
wrong, but you cannot create a View on every relationship that you want.
It isn't that way. I have a very complex database schema with 56 tables
and some of them with over 25 million rows. Views are inefficient and
CANNOT always be used. The point of the original question was not to be
taken literally as the only example. Just think of it in several
different situations. If you give a user an ODBC connection (using the
Informix CLI) there is no way to ensure they are coming from your
application and your application only. That's the question.
Yes, views do restrict certain things. But, you cannot do
certain things like inserts, deletes, and etc on certain types of views.
So in those cases views are not usable. Also, this is even more complex
as I'm running 4 instances of online and 12 databases. The application
dynamically builds a select statement across multiple instances and
multiple databases. The database names and tables change dynamically
and are not always the same. There is no way a view would even work.
The only thing I've seen as a possible solution is using a ROLE.
But, again building dynamic database selects seems not to work. For
example, if you switch ROLE in database A and now try to select from
database A, B, and C or just C; you are not known to those foreign
database as your new ROLE.
I stand corrected on the use of the TERM security. Yes, I
agree building security into the application was a miss use of the term.
Security is NOT what I'm trying to enforce in the literal since. I was
speaking figuratively. I need to restrict access based on business
rules. In my business, and company, we call that security. Like for
example: We have a business rule that says if you are logged on to a
certain workstations you CANNOT print or fax. This would be if you left
your office and logged on from another PC at one of our remote
locations. Yes, this is a business rule. But, as far as my manger is
concerned this is security.
Actually, the application is a Medical Records Imaging database
and there are thousands of business rules. Without these business rules
(a.k.a. security) a user could gain access to confidential patient
information. The rules and security can be enforced by the application.
But, again they can circumvent them if they use another ODBC client
application on that workstation. You cannot use the authorization of the
database engine to restrict information based on a business rule.
Another example: If you have HIV Test results available in your
database and a user is allowed to view those only if they are logged in
form terminal #55 using an application called HIV-View which is in a
restricted and secured area (so you can use physical security to prevent
someone from taking that information). How do you code a view to only
allow access to the information in this case? The user can go down the
street to another workstation that has CLI loaded (but doesn't have
HIV-View) and use Crystal Reports to access that view and printout all
the information.
-----Original Message-----
From: PETER.LANC...@bayer.co.uk
[SMTP:PETER.LANC...@bayer.co.uk]
Sent: Tuesday, September 09, 1997 5:34 AM
To: Tim Kelly
Subject: RE: Security and the ODBC
Tim,
I don't think I did miss the point. Excuse me if I have this
wrong but ODBC
goes via the database engine. The engine gives access to the
tables according
to the user authorisation identifier (in SQL-speak). ODBC
demands a user name
and password if it is set up correctly. So, any database access
privileges will
be enforced by the engine. Therefore your privileges and views
will work.
If this did not work I suspect nobody would be using ODBC
because it would
render all database security useless.
It would be possible to design your application so it gave the
username, not
the users. This is a glaring security hole and should be fixed
(like passwords
programmed into function keys).
Designing business logic into applications is fine. Designing
security is not
for the reasons you give.
The view and privilege statements are:
create view myview select * from mytable where rsrc_id = USER;
revoke all on mytable from public, user1, user2, etc;
grant select, delete, update on myview to user1, user2, etc;
See page 10-22 in Informix Guide to SQL, Tutorial version 7.1.
Peter
To: Peter Lancashire@BAYER-UK-NOTES
cc:
"DDA.RFC-822=inform...@rmy.emory.edu/P=BAYER/A=DBP/C=DE"@X400
From: Tke...@svhs.org @ INTERNET @ BAYERNOTES
Date: 09-09-97 04:44:00 CET
Subject: RE: Security and the ODBC
You missed the point. If the ODBC allows access to the database
they
get access to all tables and all views. Once you leave your
application, there is NOTHING stopping them from using any ODBC
compliant application to access your database and totally bypass
your
view. You can switch roles and do a few tricky things like
that, but
again this is some what limited. If you install the 32-CLI on
the
client PC and they have a passwd to access your database they
can access
it with Crystal Reports to MS-Access or whatever they want.
That's the
issue, and the view you suggest does nothing.
*-----Original Message-----
*From:*Peter Lancashire [SMTP:Peter.Lanc...@bayer.co.uk]
*Sent:*Monday, September 08, 1997 5:51 AM
*To:*inform...@rmy.emory.edu
*Subject:*Re: Security and the ODBC
*Tim Kelly wrote:
*>
*> Ok, quick one. I have a Informix 7.x server running with a
database. The
*> users connect to it via Informix-CLI client from within Win95
using a
*> complied program. The complied program handles the "business
rules" of the
*> database as far as insuring they have security etc. Simple
example:
*>
*> One table they are allowed to insert rows and delete
etc. But, the
*> application checks a column called rsrc_id and doesn't allow
them to delete
*> or change a row if it isn't theirs (the rsrc_id is users
name). It works
*> fine. Remember this is just an example and I'm not wanting
to
know about
*> Triggers, SPL etc.
*>
*> The issue is this: The CLI client is an ODBC driver.
The user can use
*> any ODBC application and point it to my Informix Server.
Example being
*> MS-Access. Now MS-Access (along with others) can just do
what
ever it
*> wants to my data. In the above example the user is allowed
to
delete from
*> the table and the application is ensuring the rsrc_id
matches.
So, is
*> there a way to tell Informix to accept connections based on
an
application?
*> Any ideas other then writing a billion triggers, SPL etc
etc?
*Try creating a view which includes all the columns of the base
table and
*selects on the USER name and the user's name stored in the
table. This
*view then contains only rows the user "owns". Grant only select
to the
*base table and insert and delete to the view. This is textbook
stuff.
*Peter
*--
*Peter Lancashire
*Mail: Peter.Lanc...@bayer.co.uk
*Information Systems Specialist, Bayer plc
*Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK
*Tel: +44-1635-562258, Fax: +44-1635-562281
*All opinions are my own and not those of Bayer plc.
Dave Otto
You have raised a number of very valid points. These issues are
what we have to deal with on a daily basis. So far we haven't
hit upon "the universal sol'n".
At 07:38 AM 9/9/97 -0400, Tim Kelly wrote:
}different situations. If you give a user an ODBC connection (using the
}Informix CLI) there is no way to ensure they are coming from your
}application and your application only. That's the question.
Most solutions we have tried in this area are overly complex and
rely heavily on "security through obscurity" :-(
} Yes, views do restrict certain things. But, you cannot do
}certain things like inserts, deletes, and etc on certain types of views.
Correct, many-to-one views will not allow data alterations.
}multiple databases. The database names and tables change dynamically
}and are not always the same. There is no way a view would even work.
Hiding a privileged login inside the app' and building views on the
fly based on the meta-data is an option, but has its' own risks
(obviously).
} The only thing I've seen as a possible solution is using a ROLE.
}But, again building dynamic database selects seems not to work. For
}example, if you switch ROLE in database A and now try to select from
}database A, B, and C or just C; you are not known to those foreign
}database as your new ROLE.
How about using CDR and making copies of data local?
} Actually, the application is a Medical Records Imaging database
}and there are thousands of business rules. Without these business rules
}(a.k.a. security) a user could gain access to confidential patient
}information. The rules and security can be enforced by the application.
}But, again they can circumvent them if they use another ODBC client
}application on that workstation. You cannot use the authorization of the
}database engine to restrict information based on a business rule.
It sounds like you might want to look at a 3-tier architecture with
an application server in the middle. This would allow you to define
the business rules and "screen" all access requests.
} Another example: If you have HIV Test results available in your
}database and a user is allowed to view those only if they are logged in
}form terminal #55 using an application called HIV-View which is in a
}restricted and secured area (so you can use physical security to prevent
}someone from taking that information). How do you code a view to only
}allow access to the information in this case? The user can go down the
Declare the current DB as the master - use CDR to create a secondary
DB w/o the HIV info - use router access lists to control connections to
the master DB.
-----------
The bottom line is that complex requirements rarely have trivial
solutions. I deal with confidential financial information and
have to address these types of issues every day. I would be very
interested in continuing this discussion here or off-line.
dave otto
dave...@acm.org
Tim Kelly
This is great! I always say that if I'm having a problem then someone
must have run into this before.
Currently, the ODBC specification seems to ignore this and
probably must people never give it a thought. In my case I cannot
afford to have the data compromised and assuming the users are to stupid
to figure it out is like 'closing the barn door after the horse got
away'.
But, of course we can purchase other drivers (or write my own)
and therefor not load the CLI Client. But why should I? My gosh, I'm
paying Informix enough as it is. Also, think of this, even if I do NOT
load the CLI-Informix ODBC driver but the Server is ODBC compliant;
what's stopping a user from installing the ODBC on a client and gaining
access? I've had users purchase Crystal Reports and then configure it
to connect to our SQL-Server. Grant it, the Crystal Report package
comes with SQL-Server drivers and not Informix. But, a quick phone call
to Crystal Reports and there's an Intersolv driver on the way.
We're not even talking 'rocket science' hacking here. If the
Informix ODBC driver is loaded, you can point and click to the Informix
system in minutes. You click and it prompts you for all the information
required (oh well).
Todd Bernhardt
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------ =_NextPart_000_01BCBD26.CB84DBA0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
If you are going to continue this off-line, please keep me in the
loop. I have been looking for a good solution to this exact problem
for several years. Matter of fact, 3 years ago I started this same
discussion in this news group and received the same basic answers with
the exception that 'ROLEs' were not around back then--or if they were,
they were not well known..
At some point, in the near future, I would really like to be able to
diffuse this time-bomb as it *is* ticking (ie. ignorance of tools like
MS Access is fading fast).
My situation is somewhat more complicated than what has been described
so far as the database contains around 500 tables (many we don't
use--but even so...), 300+ applications and comes from an outside
vendor with limited resources
Does anyone know if Informix has officially addressed this issue, or
has any plans to?
-Todd
tbern...@wii.com
----------
From: Dave Otto[SMTP:do...@themoneystore.com]
Sent: Tuesday, September 09, 1997 7:18 AM
To: Tim Kelly; 'inform...@rmy.emory.edu'
Subject: Re: FW: Security and the ODBC
You have raised a number of very valid points. These issues are
what we have to deal with on a daily basis. So far we haven't
hit upon "the universal sol'n".
--clipped a great discussion--
The bottom line is that complex requirements rarely have trivial
solutions. I deal with confidential financial information and
have to address these types of issues every day. I would be very
interested in continuing this discussion here or off-line.
dave otto
dave...@acm.org
------ =_NextPart_000_01BCBD26.CB84DBA0
Content-Type: application/ms-tnef
Content-Transfer-Encoding: base64
<encoded_portion_removed>
MS53aWkuY29tPgAAUHg=
------ =_NextPart_000_01BCBD26.CB84DBA0--
Bryan Tonnet
In <5v3eq6$i...@cssun.mathcs.emory.edu> Tke...@svhs.org (Tim Kelly) writes:
>Let me take another stab at this. I'm not suggesting you're totally
>wrong, but you cannot create a View on every relationship that you want.
>It isn't that way. I have a very complex database schema with 56 tables
>and some of them with over 25 million rows. Views are inefficient and
>CANNOT always be used. The point of the original question was not to be
>taken literally as the only example. Just think of it in several
>different situations. If you give a user an ODBC connection (using the
>Informix CLI) there is no way to ensure they are coming from your
>application and your application only. That's the question.
[Big Snip]
Sorry to come in late on this, but is it not an option to use SQL Retriever
or OpenLink which allows ODBC access from selected applications/users/machines
et al.? It would seem the simplest alternative, and both these products
provide some measure of security at not a huge cost. From what you're
saying in this post, the cost would be more than offset by the time taken to
implement some of the measures you are discussing.
--
Bryan Tonnet
bato...@phase4.com.au
Nils Myklebust
This whole issue of security or access rights to Informix (and other
database engines) when ODBC is used has been discussed several times
before in this news group. You may find those discussions at
http://www.dejanews.com or at http://www.iiug.org in the repository
for c.d.i.
I would urge everybody who are interested to read these old posts, and
then keep posting about it here in c.d.i. This is important enough for
everyone that it should *not* be taken offline.
The result of these prior discussions has allways been the same. The
only solution arround is to use the OpenLink ODBC drivers (or may be
some others have done similar things lately). They have implemented
security/access rights options as far as it possible within ODBC.
Check information about this at their web site
(http://www.openlinksw.com).
Even their solution doesn't help if you have set up your database with
tcp/ip access. In that case it's too easy for anyone to load another
ODBC driver on any PC and connect directly to the database. The only
way arround this is if you can live with no tcp/ip connection. The
OpenLink drivers can use shared memory access on the server.
This is one of the main reasons why I have kept harping to everybody
who wanted to listen that they should use the OpenLink ODBC drivers.
The only proper solution to all this would be for Informix to
implement a similar security/access right system within the
I-Connect/I-Net protocol so these rights could be set up on the
server.
Informix is interested in these issues. I promised Paula Hawthorn who
is in charge of this an explanation of the details arround these
things and suggestions on how to solve them at the user conference
this summer. I have not sent that yet due to a very though scedule
since then for other work I have to do. I will soon however. I do
think Informix will both understand the problem and come up with a
good solution. I would expect it to take some time though. I do
however believe that when it's put on their scedule people could still
wait some time for it as long as they know it will be solved.
Nils.My...@idg.no
NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway
My opinions are those of my company
The Informix FAQ is at http://www.iiug.org