mittalashish_at_my-dejanews.com wrote:
>How are the request made from a client program coded when they are
transmitted
>over SQL*NET to the database server? How are results sent back from the
server
>to the client?
That depends on the protocol used, the type of client driver used (it sets
the packet sizes) and whether or not encryption is being used.
>I need to know this because I am trying tom develop a middleware product
which
>needs to trap all SQL requests.
Well, you have basically three options to create a piece of software to that
sits outside Oracle to trap SQL statements:
- A network protocol sniffer and analyser. It put the network driver into
premiscious mode and try to read the SQL*Net packets it can find, uses some
logic to determine what is what and re-assemble SQL statements. Very
difficult to do IMO, especially as this will not work if encryption is used.
The nice feature about this option is that it is passive. No impact on the
network or Oracle client-server performance at all. An alternative to this
will be used to investigate the use of a proxy/firewall server and route all
SQL*Net traffic via that. And then use the firewall or proxy server's
features (some have a kind of scripting language) to extract the SQL from
the SQL*Net packets.
- OCI interface on the client. You can create a DLL on the client that sits
between the Oracle client software and the SQL*Net driver software. All OCI
calls that are made is goes via this DLL to the SQL*Net library. And you can
record/log whatever you need in this DLL. It can be a complex piece of code
to write however as you have to make sure you map every single OCI call
correctly. Alternatively you can use ODBC and enable the ODBC trace facility
for the Oracle ODBC driver. The log file will contains all the calls made to
the ODBC driver, including all SQL statements. This however has an impact on
the client's performance and can use a lot of diskspace.
- 3 tier architecture. The clients no longer talk to Oracle. They talk to
your server software (similar to a gateway). The server software then
analyses the SQL, decides what to do with it, call Oracle and returns the
data from Oracle to the client. There are products like this available on
the market. I know HP got one. They usually serve as a SQL governors, for
fine-tuning SQL statements, supporting partitioning of data at higher
levels, etc. etc.
No easy job to write an SQL "trapper" outside any database product. Make
sure of what the business reasons are before embarking on a very complex
technical solution that may not work and may not be cost justified. If there
are problems with users using incorrect SQL statements, rather address that
through user education and software controls in the client app than to try
and implement a policeman to trap SQL statements.
regards,
Billy
Received on Tue Oct 27 1998 - 23:45:38 CST
