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

asked 28 Jan, 02:46

Arcady%20Abramov's gravatar image

Arcady Abramov
26114
accept rate: 0%


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.

permanent link

answered 28 Jan, 04:08

Volker%20Barth's gravatar image

Volker Barth
32.8k328476700
accept rate: 32%

edited 28 Jan, 04:15

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, 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, 05:19) Volker Barth

Thank you, I shall try that

(28 Jan, 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, 07:54) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×40

question asked: 28 Jan, 02:46

question was seen: 78 times

last updated: 28 Jan, 07:54