DokuWiki

It's better when it's simple

User Tools

Site Tools


Sidebar

Translations of this page?:

Learn about DokuWiki

Advanced Use

Corporate Use

Our Community


Follow us on Facebook, Twitter and other social networks.

auth:mysql_typo3

« MySQL Authentification Backend

Typo3 MySQL Authentication

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 = '';

SQL User Authentication

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

checkPass

$conf['auth']['mysql']['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['auth']['mysql']['getUserInfo'] = "SELECT password AS pass, name, email AS mail
                                         FROM fe_users
                                         WHERE username = '%{user}'";

getGroups

$conf['auth']['mysql']['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:

checkPass

$conf['auth']['mysql']['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['auth']['mysql']['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'
    )
";
auth/mysql_typo3.txt · Last modified: 2011-04-07 16:43 by 87.213.53.82