DokuWiki

It's better when it's simple

User Tools

Site Tools


plugin:authmysql:typo3

Typo3

Configuration for authMySQL Auth plugin to authenticate with Typo3, which let's do:

  • Only basic authentication
  • No user modification/adding. This assumes that all user/group accounts will be created and maintained through Typo3.

Database settings

Settings like database name, host, user and password can be obtained from the file typo3conf/localconf.php in the Typo3 install directory.

$typo_db_username = '';
$typo_db_password = '';
$typo_db_host = ''; 
$typo_db = '';

How Typo3 manages loginusers

Typo3 uses “backend” and “frontend” users.

Frontend users are stored in the table fe_users, their groups in table fe_groups.

fe_users
uid username password usergroup
1 testuser secret 1,3
fe_groups
uid title
1 Registered
2 Employees
3 Editor

usergroup: contains comma separated list of uids from table fe_groups

Configuration

Use the Config Manager or add it to the conf/local.protected.php to store the config protected.

conf/local.protected.php
<?php
/**
 * Typo3 configuration for MySQL Auth Plugin
 * See https://www.dokuwiki.org/plugin:authmysql:typo3 for details and explanation
 */
 
/* 
 * Options to configure database access. 
 */
$conf['plugin']['authmysql']['server']   = '';
$conf['plugin']['authmysql']['user']     = '';
$conf['plugin']['authmysql']['password'] = '';
$conf['plugin']['authmysql']['database'] = '';
 
/**
 * checkPass
 */
$conf['plugin']['authmysql']['checkPass']   = "
  SELECT password as pass
  FROM fe_users, fe_groups
  where fe_users.username='%{user}'
    and fe_groups.title ='%{dgroup}'
    and ( fe_users.usergroup REGEXP CONCAT( ',', CONVERT(fe_groups.uid, CHAR), ',') 
      or fe_users.usergroup REGEXP CONCAT( '^', CONVERT(fe_groups.uid, CHAR), ',')
      or fe_users.usergroup REGEXP CONCAT( ',', CONVERT(fe_groups.uid, CHAR), '$')
      or fe_users.usergroup REGEXP CONCAT( '^', CONVERT(fe_groups.uid, CHAR), '$')
    )
";
 
/**
 * getUserInfo
 */
$conf['plugin']['authmysql']['getUserInfo'] = "
    SELECT password AS pass, name, email AS mail
    FROM fe_users
    WHERE username = '%{user}'";
 
/**
 * getGroups
 */
$conf['plugin']['authmysql']['getGroups']   = "
    SELECT fe_groups.title as `group`
    FROM fe_users, fe_groups
    where fe_users.username='test'
      and ( fe_users.usergroup REGEXP CONCAT( ',', CONVERT(fe_groups.uid, CHAR), ',')
        or fe_users.usergroup REGEXP CONCAT( '^', CONVERT(fe_groups.uid, CHAR), ',')
        or fe_users.usergroup REGEXP CONCAT( ',', CONVERT(fe_groups.uid, CHAR), '$')
        or fe_users.usergroup REGEXP CONCAT( '^', CONVERT(fe_groups.uid, CHAR), '$')
      )";

COLLATE Error

CONCAT output may be in different charset-collate. This may help:

conf/local.protected.php
<?php
/**
 * checkPass
 */
$conf['plugin']['authmysql']['checkPass'] = "
  SELECT password as pass
  FROM fe_users, fe_groups
  where fe_users.username='%{user}'
    and fe_groups.title ='%{dgroup}'
    and ( fe_users.usergroup REGEXP CAST( CONCAT( ',', CONVERT(fe_groups.uid, CHAR), ',') 
          AS CHAR CHARACTER SET latin1) collate 'latin1_german1_ci'
      or fe_users.usergroup REGEXP CAST( CONCAT( '^', CONVERT(fe_groups.uid, CHAR), ',') 
          AS CHAR CHARACTER SET latin1) collate 'latin1_german1_ci'
      or fe_users.usergroup REGEXP CAST( CONCAT( ',', CONVERT(fe_groups.uid, CHAR), '$') 
          AS CHAR CHARACTER SET latin1) collate 'latin1_german1_ci'
      or fe_users.usergroup REGEXP CAST( CONCAT( '^', CONVERT(fe_groups.uid, CHAR), '$') 
          AS CHAR CHARACTER SET latin1) collate 'latin1_german1_ci'
    )
";
 
/**
 * getGroups
 */
 
$conf['plugin']['authmysql']['getGroups']   = "
  SELECT fe_groups.title as `group`
  FROM fe_users, fe_groups
  where fe_users.username='%{user}'
    and ( fe_users.usergroup REGEXP CAST( CONCAT( ',', CONVERT(fe_groups.uid, CHAR), ',') 
          AS CHAR CHARACTER SET latin1) collate 'latin1_german1_ci'
      or fe_users.usergroup REGEXP CAST( CONCAT( '^', CONVERT(fe_groups.uid, CHAR), ',') 
          AS CHAR CHARACTER SET latin1) collate 'latin1_german1_ci'
      or fe_users.usergroup REGEXP CAST( CONCAT( ',', CONVERT(fe_groups.uid, CHAR), '$') 
          AS CHAR CHARACTER SET latin1) collate 'latin1_german1_ci'
      or fe_users.usergroup REGEXP CAST( CONCAT( '^', CONVERT(fe_groups.uid, CHAR), '$') 
          AS CHAR CHARACTER SET latin1) collate 'latin1_german1_ci'
    )
";
plugin/authmysql/typo3.txt · Last modified: 2013-03-16 21:37 by Klap-in