Difference between revisions of "External database authentication"

Jump to: navigation, search
(Automatic Synchronisation: MDL-56093 was resolved in 3.5.2)
 
(25 intermediate revisions by 15 users not shown)
Line 1: Line 1:
Location: Settings link in ''Administration > Users > [[Authentication]]''
+
{{Authentication}}
 +
Location: '' Site administration > Plugins > Authentication > External database''
  
  
This method uses an external database table to check whether a given username and password is valid. If the account is a new one, then information from other fields may also be copied across into Moodle.
+
This method uses an external database table to check whether a given username (which must be varchar)  and password is valid. If the user does not currently exist in Moodle a new account will be created and their information copied from the external database.  
 +
 
 +
== Field mappings ==
  
 
This is done by mapping fields at the bottom of the database authentication page. Each data field in the user profile has a text field next to it. Enter the name of the column in the external database that maps to the profile data field.
 
This is done by mapping fields at the bottom of the database authentication page. Each data field in the user profile has a text field next to it. Enter the name of the column in the external database that maps to the profile data field.
  
Update Local - Specifies that the external data will be entered into the local field in question
+
'''Update Local''' - Specifies that the external data will be entered into the local field in question
    * On Creation - specifies that this will only happen on the original login when the account is created for the first time.
+
* On Creation - specifies that this will only happen on the original login when the account is created for the first time.
    * On Every Login - specifies that changes in the external data will be updated on the local Moodle field in question the next time the user logs in again.
+
* On Every Login - specifies that changes in the external data will be updated on the local Moodle field in question the next time the user logs in again.
 +
 
 +
'''Update External''' - Specifies just the opposite, meaning changes in the local Moodle field in question will update the corresponding field in the external database
 +
* Never - Specifies this is disabled
 +
* On Update - Enables this to happen if a change is made locally (additional configuration is probably required)
 +
 
 +
'''Lock Value''' - Only determines whether the local user can make a change in the Moodle field and does not affect the two settings above.
 +
* Unlocked - A user can make changes locally in the Moodle field (assumably even if it contradicts the external database the next login would change it again if Update Local is set
 +
* Locked - A user can never make changes
 +
* Unlocked if empty - A user can only make changes if the field is not populated already from the external database (this would seem to indicate a user could only enter something into this field once and could not change it after saving)
 +
 
 +
== Automatic Synchronisation ==
 +
 
 +
The above method adds new users only when they log in. If you want user accounts to be created in Moodle independent of actual logins, the db authentication plugin offers a script you can run regularly via 'cron'. A typical cron entry will be as follows...
 +
 
 +
<pre>
 +
0 * * * *    /usr/bin/php /path/to/moodle/auth/db/cli/sync_users.php >dev/null
 +
</pre>
 +
 
 +
This runs every hour (adjust as required). It reads the entire table updating and creating local Moodle users.
 +
 
 +
NB when you run this you get the error "[AUTH DB] The sync users cron has been depricated.  Please use the scheduled task instead."
 +
Information on scheduled tasks can be found here: [https://docs.moodle.org/37/en/Scheduled_tasks]
 +
 
 +
If you have custom profile fields on your site, these can be synced with External database authentication.
 +
 
 +
==Additional Notes==
  
Update External - Specifies just the opposite, meaning changes in the local Moodle field in question will update the corresponding field in the external database
+
It is now possible to  use Salted Crypt passwords for the password format with external database authentication from the Password format setting:
    * Never - Specifies this is disabled
+
[[File:dbsaltedcrypt.png|thumb|300px|center]]
    * On Update - Enables this to happen if a change is made locally (additional configuration is probably required)
 
  
Lock Value - Only determines whether the local user can make a change in the Moodle field and does not affect the two settings above.
 
    * Unlocked - A user can make changes locally in the Moodle field (assumably even if it contradicts the external database the next login would change it again if Update Local is set
 
    * Locked - A user can never make changes
 
    * Unlocked if empty - A user can only make changes if the field is not populated already from the external database (this would seem to indicate a user could only enter something into this field once and could not change it after saving)
 
  
 +
* Plain text password matching is now always case sensitive
 +
* sha1/md5 hash comparisons are now enforced case insensitive (as underlying they are hexadecimal values)
  
 +
* Some of the things that apply to [[Upload users]] apply to the External database, but not all of the fields in the [[Upload users]] are available for the External Database authentication. The only available fields are the fields listed in the data mapping section of the admin page for the External Database connection.
 +
* Note that for MySQL databases moodle will store date custom profile fields in a bigint field (as a unix timestamp). Using a timestamp or datetime field will not work
 
==See also==
 
==See also==
*Using Moodle [http://moodle.org/mod/forum/discuss.php?d=45444 Special and characters con tilde (accute accent) when connected to external database] forum discussion
 
*Using Moodle [http://moodle.org/mod/forum/discuss.php?d=75519 HELP!--User Authentication problem] forum discussion
 
  
[[Category:Authentication]]
+
*[[External database enrolment]]
  
[[fr:Utiliser une base de données externe]]
+
[[de:Externe Datenbank]]
 +
[[fr:Authentification par base de données externe]]
 +
[[ja:外部データベース認証]]
 +
[[es:Autenticación con BasedeDatos externa]]

Latest revision as of 15:07, 9 March 2019

Location: Site administration > Plugins > Authentication > External database


This method uses an external database table to check whether a given username (which must be varchar) and password is valid. If the user does not currently exist in Moodle a new account will be created and their information copied from the external database.

Field mappings

This is done by mapping fields at the bottom of the database authentication page. Each data field in the user profile has a text field next to it. Enter the name of the column in the external database that maps to the profile data field.

Update Local - Specifies that the external data will be entered into the local field in question

  • On Creation - specifies that this will only happen on the original login when the account is created for the first time.
  • On Every Login - specifies that changes in the external data will be updated on the local Moodle field in question the next time the user logs in again.

Update External - Specifies just the opposite, meaning changes in the local Moodle field in question will update the corresponding field in the external database

  • Never - Specifies this is disabled
  • On Update - Enables this to happen if a change is made locally (additional configuration is probably required)

Lock Value - Only determines whether the local user can make a change in the Moodle field and does not affect the two settings above.

  • Unlocked - A user can make changes locally in the Moodle field (assumably even if it contradicts the external database the next login would change it again if Update Local is set
  • Locked - A user can never make changes
  • Unlocked if empty - A user can only make changes if the field is not populated already from the external database (this would seem to indicate a user could only enter something into this field once and could not change it after saving)

Automatic Synchronisation

The above method adds new users only when they log in. If you want user accounts to be created in Moodle independent of actual logins, the db authentication plugin offers a script you can run regularly via 'cron'. A typical cron entry will be as follows...

0 * * * *    /usr/bin/php /path/to/moodle/auth/db/cli/sync_users.php >dev/null

This runs every hour (adjust as required). It reads the entire table updating and creating local Moodle users.

NB when you run this you get the error "[AUTH DB] The sync users cron has been depricated. Please use the scheduled task instead." Information on scheduled tasks can be found here: [1]

If you have custom profile fields on your site, these can be synced with External database authentication.

Additional Notes

It is now possible to use Salted Crypt passwords for the password format with external database authentication from the Password format setting:

dbsaltedcrypt.png


  • Plain text password matching is now always case sensitive
  • sha1/md5 hash comparisons are now enforced case insensitive (as underlying they are hexadecimal values)
  • Some of the things that apply to Upload users apply to the External database, but not all of the fields in the Upload users are available for the External Database authentication. The only available fields are the fields listed in the data mapping section of the admin page for the External Database connection.
  • Note that for MySQL databases moodle will store date custom profile fields in a bigint field (as a unix timestamp). Using a timestamp or datetime field will not work

See also