For ODBC tables used in FM this is the same as FM record control and logging. Keeping the logs in a separate table would be a good idea. For access from outside it is not as easy and I have not done it, but I am sure you will get some kind of advice from someone who has.
The connection history is stored in the FM Server "Access Log", enabled when you select the "Access" checkbox on the Database Server->Logging tab in the Admin Console.
You can view the connection events by selecting "Log Viewer" tab in the Admin Console, pressing the "Logs..." button, and selecting the "Server Access" checkbox. When an ODBC or JDBC client connect to an FM database hosted on FM Server, you will see entries in the log such as:
2016-01-26 21:47:20.507 -0600 Information 638 FIDEO.local Client "bob_developer" opening a connection from "127.0.0.1 (127.0.0.1)" using "Excel [fmxdbc]". 2016-01-26 21:47:20.507 -0600 Information 94 FIDEO.local Client "bob_developer (127.0.0.1) [127.0.0.1]" opening database "test" as "bob_developer".
The log information is also stored in a file named "Access.log" in the FM Server log directory, so you could theoretically parse the file if your FM application needed the history. There's no way to reliably update an FM table when an xDBC client connects, since FM does not support triggers on the tables themselves.
Actual Technologies - ODBC for OS X
AppleScript - to access the file it has to be fully open with user logged in on the target workstation. AppleScript will emulate the actions which normally a logged in user does. Instead of using AppleScript, "hacker" can switch to FIleMaker and do exactly the same thing - delete record, run script etc..., whatever is allowed to logged in user. If you have any log solution implemented similar to UltraLog Audit, it will log the action against currently logged in user.
So, where is the security flaw, Jack?
Filemaker can give up data to ODBC, JDBC, AppleScript
This sounds like you can get to the data unauthorised and bypassing security using one of the three, which is simply not true.
At the server level, you can log all the connections to the database as previously stated by actualjon.
If you want to develop a custom logging system for ODBC clients, I guess you can use [ Get ( ApplicationVersion ) = "xDBC" ] with a record or layout-level script trigger to initiate a logging script.
Can go like this :
If [ LeftWords ( Get ( ApplicationVersion ); 1 ) <> "xDBC" ]
... the script steps to log what you need
Hope that helps.
Edited: Get ( ApplicationVersion ) returns the client product AND the client version, so I added LeftWords function to get only the product name.
In the "external application", for example, if it's Java, you could use a (free, of course) logging framework like "log4j". Log4j is extremely flexible with different logging levels you can turn on and off in a simple configuration file. The log output can be to the console or to a file.
I use log4j in every application I write. It's amazing!
So, if in some method where there is a call to a FileMaker database -- in some JDBC code, -- you could have a statement like this:
log.debug("making call to external filemaker JDBC");
(of course, that log message could be improved dramatically, but you get the idea.)
Using JDBC to access a FileMaker database is extremely simple. FileMaker includes the JDBC driver you place into your Java application's "CLASSPATH" and you start writing JDBC code! Very easy to get started.
Back to logging ... you could then later set the "logging level" in the configuration file to ignore the "debug" log level or even include other log levels like "info".
Here's a great intro example of how to use log4j:
Hope this helps.
P.S. I love JDBC since it doesn't require the up-front configuration that ODBC does.