Programmatically Change Isql Settings

[Former Member]

Question: I'm looking for a way to programmatically turn off the Enable fast launcher setting (under Tools->Options... menu) in Interactive SQL (ISQL). Is there a way to do this; such as modifying a settings file or changing a database option?

Background: Our company writes Point-of-Sale software. Credit card regulations (PA-DSS/PCI) require the tracking of all user SQL statements run against a database that contains cardholder information - this includes SQL run through ISQL. In SQL Anywhere 9 we met this requirement by insuring the ISQL_log option was always active. We did this by requiring users to run ISQL through a "wrapper" program (this program was written by us). The wrapper program (I'll call it "IsqlWrap") would first set the ISQL_log option and then start ISQL. In SQL Anywhere 12 (SA-9 is at end-of-life) this option no longer exists. So we need to come up with a different approach.

Our thought is to use Diagnostic Tracing to log SQL statements run through ISQL. IsqlWrap (wrapper program written by us) will be used to insure Diagnostic Tracing is turned on before it kicks off ISQL. However, Diagnostic Tracing is somewhat resource-intensive, so we want to turn it off as soon as the user closes ISQL. Plus we need to dump the tracing info at the end of each ISQL session. To facilitate this, once IsqlWrap starts ISQL, IsqlWrap will monitor the ISQL process. When it detects that the ISQL process has shut down it will perform cleanup, including stopping Diagnostic Tracing and dumping of tracing data. The problem is that with Enable fast launcher turned on the process does not immediately shut down when the GUI is closed - it will remain running anywhere from 15 minutes to "forever" after shutdown, depending on configuration settings (under the fast launcher Configure... section). So, from within IsqlWrap, we need a way insure the Enable fast launcher option is off before starting ISQL.

[VolkerBarth]

What about monitoring the ISQL connection and disconnection with the help of the according system events? (As you seem to be able to control when DBISQL is run, you might also be able to identify DBISQL connections within the database...) - That might allow to "stop the tracing" as soon as DBISQL has disconnected.

FWIW, would using dbisqlc (which does not use a fast launcher as it is "fast" by design:)) instead of DBISQL be of help? What about the builtin auditing facility?

[Former Member]

-- What about monitoring the ISQL connection and disconnection with the help of the according system events? Response: This could work for us but the level of effort would be a little high. I was hoping for a 'quick and easy' solution.

-- What about the builtin auditing facility? Response: What builtin auditing facility are you referring to?

[VolkerBarth]

FWIW, auditing in SQL Anywhere is based on the transaction log, cf. this general introduction from the v12.0.1 docs. - AFAIK, it does log all DDL and DML statements. However, it does not log select queries itself but can be configured to logs accesses (i.e. permission checks) to all database objects, if desired. I don't know if this fits your requirements.

[Former Member]

Unfortunately, we need to log select statements, as well. The idea being that is someone were to do a "SELECT card_number FROM credit_table" they (credit card companies) want this query logged.

[VolkerBarth]

Well, then why don't you revoke SELECT permission on that table from your users and let them access that table only through a stored procedure that

• returns the table's contents
• but also logs that access (i.e. the current query) in another monitoring table?

---

If something like that would be of interest, I'd recommend to ask a separate question like "How can I log that someone queries a particular table/column" or the like... - certainly there are more methods to do so.

[Breck_Carter]

This may or may not help...

The OEM.INI file was introduced in SQL Anywhere 10: http://dcx.sybase.com/1101/en/dbprogramming_en11/deploy-s-6007623.html

For Version 9, you might try fiddling around with C:\\Users\\Breck\\[user]\\.isqlPreferences9

#
#Wed Jun 13 17:29:01 EDT 2012
showMultipleResultSets=true
useGridsForResults=true
resultSetFont=<system>
printResultSetToConsole=2
executeToolbarButtonSemantics=all
fastLauncher=true
showUltraLitePlan=true
showRowNumber=true
defaultMessagesPaneHeight=3
showSeparateMessagesPane=false
setInitialFolderForFileBrowsers=true
maxRowsFetchedForUI=500

[VolkerBarth]

For v12, the OEM.ini option "lockedPreferences" seems to be feasible to have a "fastLauncherEnabled" disabling option - cf. the docs...

[Former Member]

This has promise, but from what I can tell the OEM.ini only allows you to lock an option, not set it. If we could set the Enable fast launcher option through the OEM.ini, that would be perfect.