Audit Logs on (Search, Create, and Update)

  • Due to the nature our of application, we would like to keep a record of each activity:

    1. Who is doing what kind of search
    2. Who viewed which record
    3. Who is creating and updating which record

    If I take an example of WPDA Student Management System:

    1. Who Create the Student
    2. Who update the Student
    3. Who searched/view the student.

    as part of the table design, I have created triggers on audit_create_ts and audit_update_ts on all tables. For all create records, I can pass $$USER$$ to the database as a hidden field.

    Question:
    1. How to handle this for an update?
    2. Keep tracking to searchers by users, I can put a trigger on a table for every SELECT but how can I get the $$USER$$ info, the person who performed the search?
    3. Is info recorded in WPDA tables or WordPress Log that I can take advantage of it?

    I hope this make sense, happy to provide more context if required.

    Thanks

    Rahim

Viewing 4 replies - 1 through 4 (of 4 total)
  • Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Rahim,

    That is a tough issue! All database connections handled by WordPress are executed by the same database user. As a result you don’t have access to any user specific information when you execute a sql statement, which would be the simplest option.

    You can add the WordPress user info to your requests, as you suggested, but you will need to add code in many different places. The plugin supports a number of hooks and filters that might allow you to add this functionality, but it doesn’t feel like the right thing to me.

    I have been thinking about another alternative, which you might like a lot more. The plugin contains class WPDADB_WPDB, which inherits WordPress class wpdb. We could do some research and try to add a MySQL user defined variable that contains the WordPress user info. You would have access to that user defined variable in all your sql statements, including database triggers and procedures, eliminating the need to push the same data around in multiple code blocks.

    I think this should be possible, but there is a limitation. Since WPDADB_WPDB inherits wpdb, this feature will be available for code that uses WPDADB_WPDB only. It will not work for local connections, which are using wpdb. It only works for remote databases. If your data is stored in a remote database, you won’t have to change anything. If your data is stored in your local WordPress database, you can create a remote loopback connection and use that connection in your WP Data Access applications.

    What do you think?

    I don’t have the time to do research right now. If you want to start on your own, please have a look at class WPDADB_WPDB->db_connect and try to add a user defined variable right after the connection was created. Here is the MySQL documentation:
    https://dev.mysql.com/doc/refman/8.0/en/user-variables.html

    Let me know…
    Peter

    Thread Starter rladha

    (@rladha)

    Thanks, @peterschulznl

    1. Let me look into to above suggestion. Currently, my app tables with missed with wordpress tables.
    2. Is there a log table in WP which keeps track for every SQL execution by all plug-ins? Perhaps, I can use that?

    Thanks again, let me dive deeper as this scenario is important for my application.

    R

    Thread Starter rladha

    (@rladha)

    FYI: Using this plug-in, I am able to see the backend queries generated by WPDA… https://en-ca.www.ads-software.com/plugins/query-monitor/

    I need to research if this data can be stored in the table, then it will solve my ask..

    others have created further add-ons to this plug, perhaps, WPDA can also consider it… https://github.com/johnbillion/query-monitor/wiki/Query-Monitor-Add-on-Plugins

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Rahim,

    I don’t know the Query Monitor plugin. From your requirements I understand that you want to be able to see the transactions per (WordPress) user. Is that correct? I don’t think this plugin supports user specific logs, but I’d be greatful if you could share your knowledge! ??

    Thanks,
    Peter

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Audit Logs on (Search, Create, and Update)’ is closed to new replies.