1. Forum
  3. Joomla
  5. Joomla Audit
  7. changed by

changed by

9 years 1 month ago - 9 years 1 month ago #7 by JFS
Replied by JFS on topic changed by
Wow I will start testing asap. Unfortunately I'm a little handicapped at the moment and can't spend as much time as I want to at my desk. I will let you know the results.

Please Log in or Create an account to join the conversation.

9 years 1 month ago #8 by JFS
Replied by JFS on topic changed by
Okay here are my results.

I enabled an item under 2 different users and Jaudit logs them under the Super user id (or better no ID and the SU is shown). BTW I'm logged in as Super User.

No I need my component to supply the current user to JAudit. Do you have any hints how this can be done?
Thanks
J.

Please Log in or Create an account to join the conversation.

9 years 1 month ago #9 by geoffc
Replied by geoffc on topic changed by
This is the challenge I still have to resolve. If the table upon which the trigger is applied does not have a 'changed_by' or a 'modified_by' column populated by Joomla when the record is saved, the database doesn't know who has made the change. This is because all interactions with the database are made by the single user configured/defined when the Joomla system is installed. I spent a lot of time trying to decipher the session table entries but have yet to get a user (or id for a user) that I can use to populate the audit record.

Some components such as com_content do have these columns but most do not alas. In our own components we tend to add these for audit recording, but unfortunately most components do not. One could make a Joomla core change but for obvious reasons this is not a good solution.
I am open to any other suggested mechanisms?
Regards
Geoff
The following user(s) said Thank You: JFS

Please Log in or Create an account to join the conversation.

9 years 1 month ago #10 by JFS
Replied by JFS on topic changed by
Geoff,

let me think about this problem. I will talk to one of my developers as well.

Thansk a lot.
J.

Please Log in or Create an account to join the conversation.

9 years 1 month ago - 9 years 1 month ago #11 by geoffc
Replied by geoffc on topic changed by
I think that we should step back a little and review the problem after a good nights sleep. The following may be a little verbose but hopefully will provide a clear understanding to anyone who reads it. I will try and avoid it becoming a lesson in application architectures.

When one installs a database of any type, one will as an initial step create specific users within the database. These users (i.e DBUser1, DBUser2, etc.) are then provided with database privileges to perform actions such as create tables, insert data, delete data from tables etc. These actions are then permitted to the connecting user irrespective of which 'application' they are using to connect to the database. i.e. MyPHPadmin etc.

When one installs Joomla upon a system one of the early steps to to specify the database connection details and the database user (i.e. DBUser1) who then proceeds to load the Joomla tables into the database. All other actions performed by Joomla that access/update the database are then carried out by this specified user (i.e. DBUser1).

When we have users registering with Joomla, these users (i.e. John Doe etc) perform actions, such as creating articles etc and these in turn add records to the database tables. However the actual access to the database is performed by the database user (i.e. DBUser1). To the database the user John Doe does not exists, the database only knows about its own database users (i.e. DBuser1). Any audit performed by the database will record that the user DBUser1 added (or updated/deleted etc) the records. This is the reason why the audit records created by JAudit (usually) which records actions performed in the database by Joomla does not have a valid 'changed_by' entry, since the user DBUser1 is NOT a Joomla user. Since it is not a Joomla 'registered' user it will NOT have an entry in the #__users table, which holds the details of the Joomla users.

Joomla itself keeps track of its changes that it makes to the database, and associates them with the specific user sessions. It stores details of user sessions in the #__sessions table, but this is used only by Joomla itself. The information is not linked within the database itself (it doesn't need to let the database know). There may be multiple database connections at any one time and each of these will be performed as the database user (DBUser1). Within these connections all the INSERT, UPDATE and DELETE statements are executed as the database user (i.e. DBUser1).

Why then do we have a column 'change_by' for each generated audit record you might ask? We mentioned that any application can connect to the database as a database user (i.e. DBUser1). There is nothing therefore to prevent a user with the details of the database user name and password from using say MyPHPadmin to connect to the database and, as an example update a specific column in a table. This 'change' should also be audited, and JAudit does capture the change, but it is not a Joomla user making the change so we shouldn't expect the value in the audit record 'change_by' field to be a Joomla user id.

The JAudit component tries (tried?) to be helpful is determining the Joomla user on whose behalf the database user is performing a change. It does this by checking for a named field such as 'changed_by' or 'modified_by' in the table being changed. If these fields are populated then it can make a good assumption that these are the 'real' users. This is reasonable since the fields are probably (usually) populated in the Joomla JTable method. However not all database tables have these fields (and the names can be a little arbitrary) so all we know for sure is that the database user (DBUser1) made a change.

The original question was why is the individual audit record 'change_by' field showing as the 'Super User', and this is because the form renderer 'assumed' that since the field was blank (or zero) it must be the Super User. This is not necessarily unreasonable, but is misleading. What it should contain is the name of the database user i.e. DBuser1, since that is really who made the change. The 'change_by' field in the audit record list display was (prior to 1.0.2) usually blank and this was possibly a more accurate reflection.

My thoughts are that the audit record 'change_by' field should be a simple text field with the name of the 'database user' by default shown in text. Only if one of the specifically named fields such as 'changed_by' or 'modified_by' actually exist in the table being monitored can we insert the 'name' of the Joomla user. We can get the name from the #__users table since we know the id of the real Joomla user since Joomla has provided it.
If one or more of the 'specifically named fields' exist in a table and they are empty then we have to assume that an application other than Joomla performed the change and insert the database user name in the change_by field.

I think that the next version will make a few extra changes in this area and display the database user name, IF one of the 'specifically named fields is not populated. This will be another database trigger change, since the 'auditing' is performed at the database level. The list of field names to check could also be a specified component option perhaps, rather than being hard coded?

The original intent of the post was to be able to follow changes made at the database level. Unfortunately the database does not (usually) know the Joomla user making the change unless the specific Joomla component has been written in suc a way as to enable the database to make the determination. If this is a custom component then it would be possible to design the 'custom component' such that the
information is available within the database. This is relatively easy to do. One would add one (or two) columns to the required tables, named 'created_by' and/or 'modified_by' and then in the Joomla JTable store and delete methods add some code such as the following:
$date  = JFactory::getDate();
      $user  = JFactory::getUser();

      // Set up audit fields in here, and app defaults in the model.
      if ( ! $this->id ) {  // New issue. A record created_on and created_by field can not be set by the user,
         $this->created_on = $date->toSql();
         $this->created_by = $user->get('username');
      }
      $this->modified_on   = $date->toSql();
      $this->modified_by   = $user->get('username');
Regards
Geoff

Please Log in or Create an account to join the conversation.

  1. Forum
  3. Joomla
  5. Joomla Audit
  7. changed by
Time to create page: 0.163 seconds
Go To Top

Joomla! Debug Console

Session

Profile Information

Memory Usage

Database Queries