a couple questions about customizing Zend_Auth

[David Mintz-3]

I have tried getting Zend_Auth_Adapter_DbTable's authenticate() to fetch data from multiple joined tables so that getResultRowObject() would give me everything about the user that I wanted to load into the session.  I used getDbSelect() to get the select and join() a table, and could not get it to work. (I actually thought I had it the other day, but it was a false positive.) Is this supposed to be possible? For example, I tried various variations on

$select = $authAdapter->getDbSelect();
$select
    ->where('users.active');
    ->join(array('groups'), 'groups.id = users.group_id',array('groups.name'=>'group'),array())
    ->join(array('people'),'users.person_id = people.id',array('lastname','firstname',))

and kept getting Zend_Auth_Adapter_Exception, invalid SQL statement.

In any event, I gave up on this approach and am now extending Zend_Auth_Adapter_DbTable and overriding authenticate(). Is this a reasonable solution?

Which brings me to the next question:  suppose you want to add an authentication failure code that means 'account disabled.'  Extend Zend_Auth_Result and have your authenticate return an instance of it?

Another possible solution:  create a view (at the database level) that pulls together all the columns I want from the different tables. Any thoughts?

Thanks.

--
David Mintz
http://davidmintz.org/

The subtle source is clear and bright
The tributary streams flow through the darkness

[Ralph Schindler-2]

> used getDbSelect() to get the select and join() a table, and could not

This is a bug in Zend_Db_Select. Currently, calling join before from
causes odd SQL to be produced.  It seems like fixing this issue:

http://framework.zend.com/issues/browse/ZF-6653

will probably solve yours.  I have this slated for an upcoming release.

>
> and kept getting Zend_Auth_Adapter_Exception, invalid SQL statement.
>
> In any event, I gave up on this approach and am now extending
> Zend_Auth_Adapter_DbTable and overriding authenticate(). Is this a
> reasonable solution?

This is a very reasonable solution, in fact, it was designed for.  You
can see that since there are several small succinct methods that you can
override in Zend_Auth_Adapter_DbTable.

> Which brings me to the next question:  suppose you want to add an
> authentication failure code that means 'account disabled.'  Extend
> Zend_Auth_Result and have your authenticate return an instance of it?

It sounds like you want to build an Auth model, and inside that model,
extends Zend_Auth_Adapter_DbTable to detect this situation.  I would
return the standard failure constant, but also add the message that you
feel you might want to use as the failure message.

-ralph

[David Mintz-3]

On Tue, Sep 29, 2009 at 2:04 PM, Ralph Schindler <[hidden email]> wrote:

used getDbSelect() to get the select and join() a table, and could not

This is a bug in Zend_Db_Select. Currently, calling join before from
causes odd SQL to be produced.  It seems like fixing this issue:

http://framework.zend.com/issues/browse/ZF-6653

will probably solve yours.  I have this slated for an upcoming release.

Interesting, and not entirely surprising. Most people probably think of building their SQL from left to right, so the join() before from() would be an unusual case that you can easily forgive developers for not anticipating.


 

and kept getting Zend_Auth_Adapter_Exception, invalid SQL statement.

In any event, I gave up on this approach and am now extending Zend_Auth_Adapter_DbTable and overriding authenticate(). Is this a reasonable solution?

This is a very reasonable solution, in fact, it was designed for.  You
can see that since there are several small succinct methods that you can
override in Zend_Auth_Adapter_DbTable.

I extended Zend_Auth_Adapter_DbTable and implemented my own authenticate(), leveraging a couple of protected methods from the parent, stealing whoever's clever snippet that builds the boolean 'zend_auth_credential_match' expression, and adding the stuff I needed, e.g, a rather tortured SQL query because of my weird database structure, and a database update to set the users last_login timestamp. Works great.

 

Which brings me to the next question:  suppose you want to add an authentication failure code that means 'account disabled.'  Extend Zend_Auth_Result and have your authenticate return an instance of it?

It sounds like you want to build an Auth model, and inside that model,
extends Zend_Auth_Adapter_DbTable to detect this situation.  I would
return the standard failure constant, but also add the message that you
feel you might want to use as the failure message.

Well, yeah -- if I understand correctly, that's what I've done.

Model_AuthAdapter extends Zend_Auth_Adapter_DbTable
{

const FAILURE_ACCOUNT_DISABLED = 'Account disabled.';

function authenticate() {
      
        $this->_authenticateResultInfo['identity'] = $this->_identity;
         // stolen from parent
        if (empty($this->_credentialTreatment) || (strpos($this->_credentialTreatment, '?') === false)) {
            $this->_credentialTreatment = '?';
        }
        $credentialExpression = new Zend_Db_Expr(
            '(CASE WHEN ' .
            $this->_zendDb->quoteInto(
                $this->_zendDb->quoteIdentifier($this->_credentialColumn, true)
                . ' = ' . $this->_credentialTreatment, $this->_credential
                )
            . ' THEN 1 ELSE 0 END) AS '
            . $this->_zendDb->quoteIdentifier('zend_auth_credential_match')
            );
        // end stolen part
        if ($this->_identityColumn == 'username') {
            $identityColumn = 'users.username';
        } elseif ($this->_identityColumn == 'email') {
            $identityColumn = 'people.email';
        } else {
            throw new Exception('invalid identity column: '.$this->_identityColumn);
        }
        $select = $this->_zendDb->select();
        $select
            ->from($this->_tableName, array('username','group_id','active','last_login', $credentialExpression))
            ->join('groups','users.group_id = groups.id',array('group'=>'groups.name'))
            ->join('people','users.person_id = people.id',array('id','lastname','firstname','email'))
            ->join('person_types','people.person_type_id = person_types.id',array('person_type'=>'flavor'))
            ->where("$identityColumn = ? ",$this->_identity) ;
      
        $this->_zendDb->setFetchMode(Zend_Db::FETCH_OBJ);
        $results = $this->_zendDb->fetchAll($select);
        // borrowed from parent
        if ( ($authResult = $this->_authenticateValidateResultset($results)) instanceof Zend_Auth_Result) {
            return $authResult;
        }
        $user = array_shift($results);
        $authResult = $this->_authenticateValidateResult((array)$user);
        // if everything is good so far, make sure account is active
        if ($authResult->isValid()) {
            if (! $user->active) {
                return new Zend_Auth_Result(Zend_Auth_Result::FAILURE_UNCATEGORIZED,
                    $this->_identity,array(self::FAILURE_ACCOUNT_DISABLED));
            }
        }
        $this->_zendDb->update('users',   array('last_login' => time()),           $this->_zendDb->quoteInto( 'person_id = ?', $user->id ) );
         return $authResult;
    }

}

Thank you Ralph.

--
David Mintz
http://davidmintz.org/

The subtle source is clear and bright
The tributary streams flow through the darkness