External database authentication - Oracle Database

External database authentication - Oracle Database

by Helder Pereira -
Number of replies: 9

Hello, I'm running Moodle 3.9 on a Oracle Database.

I am trying to implement "External database authentication". I've configured everything as explained in the documentation. The task get's user to create, but I keep getting the error "Error creating user XXXXX" and nothing more.

I'm reaching for help to know if anyone can direct me to where can I look to get more info about the error, since it's so vague I canoot understand what might be wrong.

I already run the task by hand, with the --showsql --showdebugging flags, I get every query the task uses, but the error is the same, "Error creating user XXXXX" and nothing more. No codes, no extra info to give a hint on what could be the problem...

Thanks in advance.

Average of ratings: -
In reply to Helder Pereira

Re: External database authentication - Oracle Database

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

It looks like the External Database authentication plugin catches any error and just shows Error inserting user with no details which isn't very helpful.

I suggest editing auth/db/auth.php and change line 468 to:

                    $trace->output(get_string('auth_dbinsertusererror', 'auth_db', array('username' => $user->username, 'error' => $e->getMessage())), 1); 

Then edit your language file to change the 'auth_dbinsertusererror' string. From your previous posts you may be using Portuguese in which case you need to edit moodledata/lang/pt/auth_db.php and change line 49 to:

$string['auth_dbinsertusererror'] = 'Erro ao criar o utilizador {$a->username} ({$a->error}).';

For the English language pack you would change line 43 of auth/db/lang/en/auth_db.php to:

$string['auth_dbinsertusererror'] = 'Error inserting user {$a->username} ({$a->error}).'; 

Then purge caches so the change to the string is read.

Hopefully now when you run the \auth_db\task\sync_users scheduled task you will also see an error like:

Error inserting user XXXXX (The username cannot be blank).

which may suggest what the problem is. Presumably the actual problem causing the error message is due to some validation in user_create_user() rejecting the user.

Average of ratings: Useful (1)
In reply to Leon Stringer

Re: External database authentication - Oracle Database

by Helder Pereira -

Hello Leon.

Your help was a life saver. I'm now debating with another problem, somehow even with the email field mapped in the configuration, the task (php code) does not use it in the insert of the user, and we have defined that the email is mandatory so now it stops in that insert.

Trying to make a workaround because of that detail.

Again, thank you for the help, it was a life saver and worked exactly has you explained.

In reply to Leon Stringer

Re: External database authentication - Oracle Database

by Helder Pereira -
Hi Leon, I came again to ask for some help. PHP is a new language to me and the Moodle code is not a good way to start if you don't know anything about PHP like it's my case big grin

I've been at it since your last post, and that really helped but now I'm stuck at another dead end for me.
If I create a user by the moodle UI, the email field is mandatory.
I have the email field in the external table, I have mapped the email field in the configuration.
But I get a error on the Insert statement of the user in the sync_users() function on the auth_db.php file. I suspect in the user_create_user() function. It says it cannot insert null into the email column of the table. Since I have the email in the external table with data, and the email field mapped in the configuration, how can it not use it in the Insert since it's mandatory?
In reply to Helder Pereira

Re: External database authentication - Oracle Database

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

You could edit auth/db/auth.php and insert the mtrace() line at 238 below:

236                     foreach (array_keys($selectfields) as $index => $localna    me) { 
237                         $value = $fields[$index];
238                         mtrace("DEBUG: $localname == $value");
239                         $result[$localname] = core_text::convert($value, $th    is->config->extencoding, 'utf-8');
240                      } 

and insert the mtrace() line at 466:

465                 try { 
466                     mtrace("DEBUG: Calling user_create_user() with \$user->email == " . (isset($user->email) ? "\"$user->email\"" : '(not set)'));
467                     $id = user_create_user($user, false, false); // It is truly a new user.
468                     $trace->output(get_string('auth_dbinsertuser', 'auth_db', array('name'=>$user->username, 'id'=>$id)), 1);
469                 } catch (moodle_exception $e) { 

Then when you run \auth_db\task\sync_users you will see debug output like:

DEBUG: firstname == Jo
DEBUG: lastname == Bloggs
DEBUG: email == jobloggs@example.com
DEBUG: username == jobloggs
DEBUG: Calling user_create_user() with $user->email == "jobloggs@example.com"

The first four lines are the values being read from the external database, and the last line is the email value being passed to user_create_user(). This might help determine whether the email address is being read from the external database as expected.

If it's still not working please share exactly what output you get, either copy and paste the text or share a screenshot. It's the errors that are important, hide or change any personal data of users. It doesn't matter if the messages are in Portuguese, if we can see the exact message we can match it in the Moodle language pack with the string ID.

While an email address is mandatory for Moodle users it can be blank (an empty string) when created by the External Database plugin. If so then when the user logs in they are taken to their profile page and must add their email address before continuing. This is also what happens if you leave auth_db | field_map_email empty in the mapped fields. But if I have NULL for the email address in the external database then $user->email is an empty string when user_create_user() is called. But I am using MariaDB for my Moodle database and external database, it's possible there's some different behaviour with Oracle.

Also, exactly what version of Moodle 3.9 do you have? 3.9.11?

Average of ratings: Useful (1)
In reply to Leon Stringer

Re: External database authentication - Oracle Database

by Helder Pereira -
Hello again.
Something strange is happening.
I implemented the changes you described. And found that for some reason the scritp does not reach the for statement in line 238.
With some more investigation, I found that it jumps out of scope in the if clause on line 232.
Here are the debug lines I added:

// If at least one field is mapped from external db, get that mapped data.
if ($selectfields) {
$select = array();
$fieldcount = 0;
foreach ($selectfields as $localname=>$externalname) {
// Without aliasing, multiple occurrences of the same external
// name can coalesce in only occurrence in the result.
$select[] = "$externalname AS F".$fieldcount;
$fieldcount++;
}
$select = implode(', ', $select);
$sql = "SELECT $select
FROM {$this->config->table}
WHERE {$this->config->fielduser} = '".$this->ext_addslashes($extusername)."'";
// HMP
mtrace("DEBUG-3: Select Columns == $select");
mtrace("DEBUG-4: Sql Query = $sql");
if ($rs = $authdb->Execute($sql)) {
mtrace("DEBUG-5: RecordSet == $rs");
if (!$rs->EOF) {
mtrace("DEBUG-6: ");
$fields = $rs->FetchRow();
// Convert the associative array to an array of its values so we don't have to worry about the case of its keys.
$fields = array_values($fields);
foreach (array_keys($selectfields) as $index => $localname) {
$value = $fields[$index];
// HMP
mtrace("DEBUG-1: $localname == $value");
$result[$localname] = core_text::convert($value, $this->config->extencoding, 'utf-8');
}
}
$rs->Close();

And here is the result from running the script on command line:
--------------------------------
DEBUG-3: Select Columns == FIRSTNAME AS F0, LASTNAME AS F1, EMAIL AS F2, INSTITUTION AS F3, PROFILE_FIELD_ENTIDADE AS F4, USERNAME AS F5
DEBUG-4: Sql Query = SELECT FIRSTNAME AS F0, LASTNAME AS F1, EMAIL AS F2, INSTITUTION AS F3, PROFILE_FIELD_ENTIDADE AS F4, USERNAME AS F5
FROM M_CREATE_USER
WHERE USERNAME = ''
DEBUG-5: RecordSet == F0,F1,F2,F3,F4,F5^M

--------------------------------
SELECT id,username,auth FROM m_user WHERE username = :o_username AND mnethostid = :o_mnethostid AND auth <> :o_auth
[array (
'o_username' => 'syncuser01',
'o_mnethostid' => '1',
'o_auth' => 'db',
)]
--------------------------------
Query took: 0.0021381378173828 seconds.
--------------------------------
DEBUG-2: Calling user_create_user() with $user->email == (not set)
--------------------------------
INSERT INTO m_user (username,confirmed,auth,mnethostid,lang,calendartype,maildisplay,mailformat,maildigest,autosubscribe,trackforums,timecreated,timemodified) VALUES (:username,:confirmed,:auth,:mnethostid,:lang,:calendartype,:maildisplay,:mailformat,:maildigest,:autosubscribe,:trackforums,:timecreated,:timemodified) RETURNING id INTO :oracle_id
[array (
'username' => 'syncuser01',
'confirmed' => 1,
'auth' => 'db',
'mnethostid' => '1',
'lang' => 'pt',
'calendartype' => 'gregorian',


Has it can be seen, I do not reach DEBUG-1 which is your statement, nor do I get to DEBUG-6 that I added, but I do get DEBUG-5.
Between DEBUG-5 and DEBUG-6 is the if clause. Something is wrong in that recordset, the if clause is to test EOF in the recordset, but apparently from DEBUG-5 there is data on the recordset...
Maybe something wrong from using Oracle DB?
In reply to Helder Pereira

Re: External database authentication - Oracle Database

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

Try adding something like this to the top of get_userinfo():

        mtrace("DEBUG-A: \$username == \"$username\""); 
        $extusername = core_text::convert($username, 'utf-8', $this->config->extencoding);
        mtrace("DEBUG-B: \$extusername == \"$extusername\"");

Both should show the same value. I'm wondering if there's a problem with character encoding causing this. Because In your output you have:

DEBUG-4: Sql Query = SELECT FIRSTNAME AS F0, LASTNAME AS F1, EMAIL AS F2, INSTITUTION AS F3, PROFILE_FIELD_ENTIDADE AS F4, USERNAME AS F5 
FROM M_CREATE_USER
WHERE USERNAME = ''

That last line should be WHERE USERNAME = 'syncuser01' or whatever the currently syncing username is. And the username is getting correctly read from the external database because it appears here:

INSERT INTO m_user (username,confirmed,auth,...) VALUES (:username,:confirmed,:auth,...) RETURNING id INTO :oracle_id 
[array (
'username' => 'syncuser01',

So I'm wondering if the call to core_text::convert() – and its call to iconv() – is losing the string value for some reason. There is a auth_db | extencoding setting for the External Database plugin which might need a specific value for your database but because I don't know Oracle I can't guess what it should be, but maybe you can.

Average of ratings: Useful (1)
In reply to Leon Stringer

Re: External database authentication - Oracle Database

by Helder Pereira -
Hello Leon.

I'm sorry I took this long to answer. I am trying to implement this on my free time and the last couple of weeks have been a nightmare.

Your advice was spot on, I went to the config of the external character set and changed from the Oracle way to the PHP way and things got better, the recordset showed the fields.

Then I added your DEBUG lines and with the change in the setting I started to see the username ok.

But, with this changes, the error still happens. Still says the email is null.

For some reason it does not enter the clause "if (!$rs->EOF) {"

I share the debug from running by command line:

DEBUG-A: $username == "syncuser01"
DEBUG-B: $extusername == "syncuser01"
--------------------------------
SELECT * FROM m_user_info_field
[array (
)]
--------------------------------
Query took: 0.0029289722442627 seconds.
--------------------------------
DEBUG-3: Select Columns == FIRSTNAME AS F0, LASTNAME AS F1, EMAIL AS F2, INSTITUTION AS F3, PROFILE_FIELD_ENTIDADE AS F4, USERNAME AS F5
DEBUG-4: Sql Query = SELECT FIRSTNAME AS F0, LASTNAME AS F1, EMAIL AS F2, INSTITUTION AS F3, PROFILE_FIELD_ENTIDADE AS F4, USERNAME AS F5
FROM M_CREATE_USER
WHERE USERNAME = 'syncuser01'
DEBUG-5: RecordSet == F0,F1,F2,F3,F4,F5^M
Sync,User01,syncuser01@gmail.com,IFB,IFB,syncuser01^M

--------------------------------
SELECT id,username,auth FROM m_user WHERE username = :o_username AND mnethostid = :o_mnethostid AND auth <> :o_auth
[array (
'o_username' => 'syncuser01',
'o_mnethostid' => '1',
'o_auth' => 'db',
)]
--------------------------------
Query took: 0.0021331310272217 seconds.
--------------------------------
DEBUG-2: Calling user_create_user() with $user->email == (not set)
--------------------------------
INSERT INTO m_user (username,confirmed,auth,mnethostid,lang,calendartype,maildisplay,mailformat,maildigest,autosubscribe,trackforums,timecreated,timemodified) VALUES (:username,:confirmed,:auth,:mnethostid,:lang,:calendartype,:maildisplay,:mailformat,:maildigest,:autosubscribe,:trackforums,:timecreated,:timemodified) RETURNING id INTO :oracle_id
[array (
'username' => 'syncuser01',
'confirmed' => 1,
'auth' => 'db',
'mnethostid' => '1',
'lang' => 'pt',
'calendartype' => 'gregorian',
'maildisplay' => '0',
'mailformat' => '1',
'maildigest' => '0',
'autosubscribe' => '0',
'trackforums' => '0',
'timecreated' => 1639999471,
'timemodified' => 1639999471,
)]
--------------------------------
Erro ao criar o utilizador syncuser01 (Ocorreu um erro ao guardar na base de dados (ORA-01400: cannot insert NULL into ("WB_MOODLE_PRE"."M_USER"."EMAIL")
INSERT INTO m_user (username,confirmed,auth,mnethostid,lang,calendartype,maildisplay,mailformat,maildigest,autosubscribe,trackforums,timecreated,timemodified) VALUES (:username,:confirmed,:auth,:mnethostid,:lang,:calendartype,:maildisplay,:mailformat,:maildigest,:autosubscribe,:trackforums,:timecreated,:timemodified) RETURNING id INTO :oracle_id
[array (
'username' => 'syncuser01',
'confirmed' => 1,
'auth' => 'db',
'mnethostid' => '1',
'lang' => 'pt',
'calendartype' => 'gregorian',
'maildisplay' => '0',
'mailformat' => '1',
'maildigest' => '0',
'autosubscribe' => '0',
'trackforums' => '0',
'timecreated' => 1639999471,
'timemodified' => 1639999471,
)])).

We now can see correct data in the recordset but it does not enter the if clause...

Again, many thanks.
In reply to Helder Pereira

Re: External database authentication - Oracle Database

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

For some reason it does not enter the clause "if (!$rs->EOF) {"

I think the following debug code is causing this:

mtrace("DEBUG-5: RecordSet == $rs");

$rs is an object so implicitly casting it to a string – by placing it in double quotes, "… $rs" – calls the object's __toString() function and I think this will cause the record set (row) to advance to the next one, and if there's only one record set then $rs->EOF becomes true thus if (!$rs->EOF) is false.

So hopefully if you remove (or comment out) the above mtrace("DEBUG-5: …") the if () will now be entered. Hopefully you will see the retrieved values with mtrace("DEBUG-1: $localname == $value").

Average of ratings: Useful (1)
In reply to Leon Stringer

Re: External database authentication - Oracle Database

by Helder Pereira -
Touché!!

Removing that mtrace made everything work.

So for the sake of the completeness, the real problem was the encoding. I tried to give the Oracle way and it needed PHP way.

I cannot express my thank you for your time and patient with my problem.
I must express that your explanation also gave me a lot on inside on the PHP code.

If you ever come by Lisbon, Portugal, I will be glad to pay you a beer or coffee .

Thank you!!