Re: logon trigger - getting program version information of application connecting

On Sun, 14 Mar 2004 16:16:22 -0000, "DJ" <nospamplease_at_goaway.com> wrote:

>
>"bennies" <test_at_test.org> wrote in message
>news:405429f1$0$41748$5fc3050_at_dreader2.news.tiscali.nl...
>> How about changing the password they logon with.
>> Or let me guess that's hardcoded in the application ;-)
>>
>> OracleSupport-dropthis_at_shaw.ca wrote:
>> > Not sure if this is possible, but just in case...
>> >
>> > I have a logon trigger that I would like to modify to prevent users
>> > from connecting to an Oracle schema if the version of software they
>> > are using isn't current.
>> >
>> > Oracle 8.1.7.4.12 (client and server)
>> > TCP/IP - net8
>> > Windows 2000 (clients and server)
>> >
>> > I'm working with a 3rd party application that we are about to upgrade
>> > (major release) and have over 500 client installs using this
>> > application / database. I basically have no control over the
>> > application to prevent version mismatches, but due to the number of
>> > client installs, there's a potential for data problems because of
>> > missed client upgrades. I've already confirmed an older version of the
>> > client program can connect to the upgraded schema, and the software
>> > vendor is less than helpful in providing reassurance that the data
>> > will be protected (the "older versions aren't certified to run on the
>> > upgraded schema, but why wouldn't you upgrade all the client machines"
>> > type of response)
>> >
>> > Oracle receives the application executable name in v$session (hasn't
>> > changed between versions), but I can't figure out how to get the
>> > version (or timestamp) of the executable. Either of these would allow
>> > me to drop the connection.
>> >
>> > So far I've come up blank on all the searches I've done.
>> >
>> > Any ideas? Can it be done? Any assistance appreciated.
>> >
>> > Thanks,
>> >
>> > Brad
>
>whats the password got to do with dropping a session due to a version.
>
>Also I dont think it is possible since, if you block 8.0 sqlplus, I will
>trick it to make sure I get in anyway.
>
>Wahst wrong with older clients anyway, as long as they connect and is a
>supported combination by oracle - whats the problem?
>

The versioning isn't an Oracle issue, it's the 3rd party app that causes the problems.

Unfortunately the 3rd party application does the stupid, almost industry wide, practice of putting components in the middle tier where they cause the most problem. They also did not put any checks into their application to prevent multiple versions of the application connecting to an upgraded (read their upgrades) database. I reviewed the database modifications they made and it's almost certain that corruption of data can occur by using an older version of the application with the upgraded database.

The 3rd party application has a security module maintained by the application super-user. I also put security in the database, but it's quite limited because of the "outside interference". If a user is an authenticated user, the only two ways I can think of to prevent multiple version use is by client machine IP or name (we would have to construct a table with all known machines that have the correct version of the application), or via executable version or timestamp (which it looks like I can't get to).

It is unfortunate these applications become the industry standard for their isolated segment, otherwise I would prevent their use altogether. What I would give to work on properly constructed database applications (out of the 40 or so application databases I'm responsible for, only 2 can even be considered good).

Thanks anyway.

Brad Received on Sun Mar 14 2004 - 12:28:34 CST