Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (6)

Resolved Auditing Triggers + Single SQLCA Transaction

  1. How-to
0
Votes
Undo
  1. Aron Cox
    Aron Cox
  2. PowerBuilder
  3. Monday, 2 September 2019 14:52 PM UTC

PowerBuilder classic, currently using PB 2017

SQL Server 

 

I have a large old PowerBuilder application + satellite applications. Like many large old PowerBuilder applications it uses SQLCA for 99% of its transaction handling. It also uses stored procedures, datawindows, and embedded SQL to update data.

We want to add auding via database triggers, so we can track anything that changes data in the database no matter who write the application in question. We'd like to know what application and what window was being used when the update took place, when the updates come from our applications

As far as I can tell I either have to pass that information in with every SQL statement, something that's not going to be possible, or use the SQL Server SET CONTEXT_INFO command to store 128 bytes of useful information that the triggers can read. However since a user could be using multiple windows at the same time(ish, I mean it is a single transaction, and that could get confusing) that not really going to cover it.

So I think I need to change every window to have its own transaction, and that's a lot of quite difficult work that I'm not relishing.

Anyone have any ideas as to the best way to go about this?

Note: The trigger generator is written and working, and in use, are is all the SET CONTEXT_INFO stuff, it's just a matter of updating the large behemoth applications to use the triggers (currently turned off via a single SET CONTEXT_INFO call)

Some things I am thinking of:

   * Bah, don't worry about two windows a tthe same time, it's not as if PowerBuilder / our application can really handle that, I mean wouldn't commits and rollbacks get all confused already??? (Is that true?)

   * Use the SQLPreview event to call SET CONTEXT_INFO everywhere, perhaps reading the call stack to get the required information (see fastfuncs for more details https://github.com/lakeman/fastfuncs)

   * Shut up and get on with creating a single transaction for each window

Thanks, any thoughts are welcome :)

  1. Embedded SQL
  2. PowerBuilder
  3. Trigger
SySAM license server issue
Datawindow Validation Expression help
Responses (6)
Matthew Balent
Matthew Balent Accepted Answer Pending Moderation
  1. Friday, 6 September 2019 16:54 PM UTC
  2. PowerBuilder
  3. # 1

Trigger based auditing can be a big PITA and performance killer.  SQL Server has some auditing capabilities which may be of use to you.  The link below refers to C2 auditing in SQL Server.

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-server-audit-specification?view=sql-server-2016

 

https://www.sqlservercentral.com/articles/basics-of-c2-auditing

 

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/c2-audit-mode-server-configuration-option?view=sql-server-2016

Comment
Aron Cox
  1. Aron Cox
  2. Friday, 6 September 2019 19:13 PM UTC
Thanks.



Unfortunately our application logs on to the database as a single user with our user id stored on the client end, so to know who made the change, which is a pretty important thing, we have to be able to use CONTEXT_INFO (or, I suppose, pass it on with every call, which would be a lot of work) which isn't something any in-built auditing uses as far as I can see.



Also we have to support all editions of SQL Server from 2008 R2 up, as well as customers with small or large servers.



There are also several other requirements in that client-side auditing is already implemented in parts of the code (i.e. after doing an update another updates it called to create an audit record) along with reports and filters and all the stuff so the user can find the information they're interested in. The new audit triggers will continue to use the same tables allowing for the reports etc to carry on working.



If only I was designing a brand-new application there are so many nice features nowadays.
  1. Helpful
There are no comments made yet.
Aron Cox
Aron Cox Accepted Answer Pending Moderation
  1. Thursday, 5 September 2019 18:20 PM UTC
  2. PowerBuilder
  3. # 2

So far I haven't come up with anything useful, and for now I am going to ignore the requirement for the window name to at least get something out. I will be able to see what applications the updates are coming from, and who did it and when, so it's better than nothing.

As SQLCA is not attached to a window I can't generically find the window name in the SQLPreview event, which is a shame, and I thought about using the stack trace function in fastfuncs (see original post for link) to find the last window up the trace, but after downloading and building the fastfuncs dlls I can see they only go up to PB120, and I'm not sure exactly how to use them. For example, do they just go alongside the executable? Do I need to put them in System32? Do I need to register them? Or something like that?

If anyone has used fastfuncs and has any ideas on getting them to work in PowerBuilder 2017 then let me know and I'll give that a try :)

Thanks.

Comment
There are no comments made yet.
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Tuesday, 3 September 2019 12:39 PM UTC
  2. PowerBuilder
  3. # 3

Not sure,how much this will be useful

Why not set the SET CONTEXT_INFO  at activate event of a window ? 

Happiness Always
BKR Sivaprakash

 

Comment
Aron Cox
  1. Aron Cox
  2. Thursday, 5 September 2019 18:14 PM UTC
Good idea, but I thought about that, and there is an issue I can think of.



The user may make a window active kick off a long process, then make another window active. In that case the later on updates from the first window would be labelled as coming from the second window (I think)



  1. Helpful
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Friday, 6 September 2019 07:44 AM UTC
Ok. May not workout, in your scenario, unless other wise SET CONTEXT_INFO works for every transaction.
  1. Helpful
There are no comments made yet.
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Monday, 2 September 2019 20:09 PM UTC
  2. PowerBuilder
  3. # 4

I expect you need to log because at some future time you need to extract data for particular business transaction. Or your business needs to continuously track for certain behavior requiring alerts.

Do your business transactions (read: what the user sees as transactions) match one-to-one with your system transactions (read: your DB transactions)?

Does any data ever arrive in your database outside your app's UI?
EX: Data loaded by services having no UI?

Your app may have DB transactions involving many rows across many tables. The business transaction may even include DB transactions across multiple DB connections - and REST calls - and SOAP calls - and ...

The amount of UI related info may differ greatly among DB transactions and over time.

Identifying each system transaction by a GUID and each business transaction by its own GUID enables a wealth of auditing options:

  • Your "standard log" only needs to store the system transaction GUID along with the actual data being logged
  • Transaction description can be kept in separate storage (EX: a document DB containing GUID with dictionary of key/value pairs)
  • You may assign UI info to a system or business transaction as part of this "dictionary".
  • You can filter on given system transaction using its GUID.
  • Likewise for any business transaction: First identify its system transactions, then filter for each.
  • "Standard log" doesn't leak sensitive or person-identifying data from the transaction description. You need access to the "transaction storage" to find any such data.

 

 

Comment
Aron Cox
  1. Aron Cox
  2. Tuesday, 3 September 2019 10:41 AM UTC
Mostly we'd like to track who changed the data, reagardless of the application that is changing it. The database can be updated by our application, third-party applications, direct SQL and anything else you can think of. We don't hold the database, nor manage it, so pretty much anything can and is going on. However there are times when we'd like to know if it was a bug in our software that caused a weird thing to happen, or if it was somethign else. Hence the triggers.



I think your idea sounds great, if I was designing a new system I'd definitely be interested in something like that, however I have a legacy system I need to retrofit to handle the auditing triggers.



  1. Helpful
Michael Kramer
  1. Michael Kramer
  2. Tuesday, 3 September 2019 19:48 PM UTC
Sometimes such coding feels like in-flight open-heart surgery. I know the feeling.

If I was to track how a certain user action evolved into a potential data inconsistency then I would look for what started the sequence of events. So I would like to know the most recent user action leading to the transaction starting. That action I could ask for - or see in an escalated issue report: … and then I clicked on the Save Now button. Afterwards, when I also clicked Save on the "other" window, the system started hanging - so I clicked Save a few more times.

User can often recall what they did leading up to "agonizing data loss"; unfortunately less precise what the system did. THAT recalled user signal I would like to use when searching the logs. Can I find a CONTEXT where = ?

HTH /Michael
  1. Helpful
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Monday, 2 September 2019 16:21 PM UTC
  2. PowerBuilder
  3. # 5

i would use sqlpreview to set a window id in context_info for the triggers.  do a getparent in a loop until the type of the parent is window and then you have the window name/id.  I would also check to see if it is a select vs update/delete/insert in powerscript so as to avoid setting context_info for any selects

 

a per window connection would take a lot of connections to the database.  It would be faster in terms of not having to set the context info each time, but i doubt it would be worth your time.

Comment
Aron Cox
  1. Aron Cox
  2. Monday, 2 September 2019 19:51 PM UTC
Thanks Mike. Yeah I thought of that, I'm pretty sure I could find the parent window for datawindows, not sure about embedded SQL though. I know (now) that since PB 11.5 there is a SQLPreview event on the transaction object, but since SQLCA is a global variable I assume it doesn't below to any window??? I guess I'll have to experiment when I get a chance. It's why I think I may have to use the call stack to find the calling window - which doesn't sound simple.



And of course there is the small chance of something getting inbetween the SQLPreview and the actual execute I suppose.



Hmmmm, it definitely sounds like it's going to be a pain whatever way I go.

  1. Helpful
mike S
  1. mike S
  2. Tuesday, 3 September 2019 10:28 AM UTC
PB is single threaded, so no chance that something comes between sqlpreview and execute.



do you have a somewhat standard update routine (user must click on a save button for the updates to occur?) if so, that would be the place to set the audit info window name.



Do you really need the window name as part of your audit data? I've done a few of these types of audit processes, and window name (or object name) never really comes up as being a need. Its always wanting to know the name of the person who changed the data and what it was changed from/to. The tables and columns that changed will typically identify the window/object that made the change. I would save off the application name since you say you have several of those....
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 2 September 2019 15:30 PM UTC
  2. PowerBuilder
  3. # 6

Hi Aaron;

     I use the HOST & APPNAME parameters in the SQLCA.DBParm field all the time to pass into SS & ASE the application & workstation names being used in any DML interaction. SQLCA already has the UserID field. 

     What I never thought of before was the "window name". I wonder if you could just pass that in within the APPNAME - say for example "MYApp/WinName". Then parse out the Application & Window names on the SS side when required.

Food for thought.

HTH

Regards... Chris

 

Comment
Aron Cox
  1. Aron Cox
  2. Tuesday, 3 September 2019 10:53 AM UTC
Thanks Chris. Yeah, it's something I've thought about, but it doesn't really add much to the SET CONTEXT_INFO which I could just call after connect.



The problem is, as you say, when the window name is a requirement. A user could open Window A, then Window B, then go back to Window A and do the update. If I'm not careful the update will be marked as coming from Window B when it really came from Window A.



I've thought about using the Activate event to set the context again, but on a long process there's nothing stopping the user switching back to Window B while it's running on Window A (apart for the general unresponsiveness of a busy PowerBuilder).
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.