vTiger Customizations – Part 5 – Implementing User Account Lockout in vTiger CRM

This is a continuation of a Part 4 – Implementing User Account Lockout in the Customer Portal, regarding implementing account lockout in the Customer Portal.  Now, I’ll go through how to do this in the vTiger CRM application.

PLEASE NOTE: The edits were done on a system with all the previous changes that I have made in previous vTiger Customization posts.  If you haven’t made those changes to your code, then the line numbers might be off.  I tried to be as descriptive as possible as to the location of the edits, and I have used the syntax of “Around line” so that you understand that the code change line is not exact.  Code changes and statements to run in mysql are noted in BOLD.  Anything in italics is to help you understand the surrounding code.

ADDITIONAL NOTE: For some reason I was only able to get this working for quick edits in the DetailView.  When updating this field using the EditView, the field is set back to 0.  If anyone can tell me what I’m missing here, I’ll add it into the HOWTO.  Also, this feature customization will only work if you are using vTiger’s Integrated/SQL Authentication.

The strange part about this is feature is that vTiger already tracks login attempts using a session variable: $_SESSION[‘loginattempts’].  These attempts are logged to the Log4PHP log, but these attempts are never logged to the database.

DATABASE UPDATES

First, we need to create the new field in the vtiger_users table:

ALTER TABLE vtiger_users ADD failed_login_attempts INT(11) DEFAULT 0;

Next, you need to let vtiger know that the field exists by adding an entry to the vtiger_field table:

insert into vtiger_field (tabid, columnname, tablename, generatedtype, uitype, fieldname, fieldlabel, readonly, presence, selected, maximumlength, sequence, block, displaytype, typeofdata, quickcreate, quickcreatesequence, info_type, masseditable, helpinfo)
select 29, ‘failed_login_attempts’, ‘vtiger_users’, 1, 7, ‘failed_login_attempts’, ‘Number of Failed Login Attempts’, 1, 2, 0, 100, 15, 77, 1, ‘I~O’, 1, NULL, ‘BAS’, 1, NULL;

Finally, the documentation suggests that entries should be added to these tables:

insert into vtiger_profile2field (profileid, tabid, fieldid, visible, readonly)
select 1, tabid, fieldid, 0, 1 from vtiger_field where columnname=’failed_login_attempts’;

insert into vtiger_def_org_field (tabid, fieldid, visible, readonly)
select tabid, fieldid, 0, 1 from vtiger_field where columnname=’failed_login_attempts’;

From what I’ve tested, the inserts to vtiger_profile2field and vtiger_def_org_field aren’t necessary, but the documentation on the vtiger website suggests they should be done.

vTiger CRM Code Updates

In vtigercrm/modules/Users/Users.php:

Around line 394, update the query statement:
default:
$this->log->debug(“Using integrated/SQL authentication”);
$encrypted_password = $this->encrypt_password($user_password);
$query = “SELECT * from $this->table_name where user_name=? AND user_password=? AND COALESCE(failed_login_attempts,0)<5”;
$result = $this->db->requirePsSingleResult($query, array($usr_name, $encrypted_password), false);

Around line 438, update the query statement in the if block:
if(!$authCheck)
{
$this->log->warn(“User authentication for $usr_name failed”);
// Increment number of failed login attempts
$query = “UPDATE $this->table_name SET failed_login_attempts=COALESCE(failed_login_attempts,0)+1 where user_name=?”;
$this->db->requirePsSingleResult($query, array($this->column_fields[“user_name”]), false);
return null;
}

Around line 470, add this code near the end of the function:
if ($row[‘status’] != “Inactive”) $this->authenticated = true;
// Reset number of failed logins
if ($this->authenticated) {
// Increment number of failed login attempts
$query = “UPDATE $this->table_name SET failed_login_attempts=0 where user_name=?”;
$this->db->requirePsSingleResult($query, array($this->column_fields[“user_name”]), false);
}
unset($_SESSION[‘loginattempts’]);
return $this;

After those modifications, you should have User Account Lockout Implemented on vTiger CRM.

Resources:
1.) Creating New Fields in Existing Modules
2.) Implementing Access Controls