|
Hello, we have encountered a strange situation. One of our clients has several legacy applications, which were not written by our company and are not supported. These applications connect to their old DB. It appears that one of them uses entity framework and changes table schema. They all use the same DB user. Question: how can we log schema changes into the DB, so it would register the full app info of the connection making the change? This way we will find the problematic application and disable just it, leaving the rest of them running. Thank you Arcady |
|
Well, I don't think there's a direct way to log the application doing the schema changes, as SQL Anywhere does not (yet?) support DDL triggers which would be helpful here. But you could add a login_procedure that logs the connection number and application info of the new connection, say to the console log (via MESSAGE statements) or into a "login_info" table. Later on, when you notice schema changes, you can check the translated transaction log and connect the connection id info from the translated log with the info taken during the login to identify the application. If the application in question is the only one doing schema changes (i.e. the other applications do not need to make those), you might also revoke the database user to do so (unless it has DBA privilege...). FWIW, the SYSTAB table has a column "last_modified_at_utc" that tells the time of the last table schema modification - that could also help to identify the apllication. Can you give me an example of launching the login procedure? I seem to be unable to write a row into a DB table using it.
(28 Jan '19, 04:19)
Arcady Abramov
Replies hidden
You do not launch the procedure itself, it is automatically called during a login - it's like a hook/callback procedure. Here's a simple sample that expect a "MyLogins" table with some typical columns and some default columns for PK (default autoincrement) and default timestamp... - of course error handling is critial here, otherwise every connect could fail... create procedure "DBA".STP_Login()
begin
insert "DBA".MyLogins(UserName, ConnNumber, ConnInfo)
values(
current user,
connection_property('Number'),
connection_property('AppInfo'));
-- Call the default login procedure
call sp_login_environment;
end;
grant execute on "DBA".STP_Login to PUBLIC;
set option PUBLIC.Login_procedure='DBA.STP_Login';
(28 Jan '19, 05:19)
Volker Barth
Thank you, I shall try that
(28 Jan '19, 05:40)
Arcady Abramov
FWIW, you can also use system events of type "Connect" and "Disconnect" to log information about connections and their according AppInfo. In contrast to the login_procedure approach, you could thereby also track when each connection is closed, and might also check whether SYSTAB.ast_modified_at_utc has been changed during the connection's life time...
(28 Jan '19, 07:54)
Volker Barth
|
