Holy Cdbauthmanager Queries, Batman!

Just implemented some rbac rules, with a few checks on the page (visible sections). 125 queries! This more than doubles the page generation time!

Why is this in the bug forum? Because any reasonable RBAC implementation in CDbAuthManager causes a severe slowdown on the site.

I will be workin on extending CDbAuthManager::checkAccessRecursive() to optimize it.

Ok, so I extenderd CDbAuthManager to load each of the tables only once. The way I have extended it is so that with these default options nothing will change if you use this instead of CDbAuthManager. So set the extra options like below.

The way CDbAuthManager operates by default is to load each item from the item table and from the relations table with it’s own query, discarding the results when done. I call it “singleLoad”, and it is very inefficient. Set these two options (singleLoadAuthItems and singleLoadAuthParents) to false will load it all in a query and keep the results.

For the assignments, it will be load from the DB each time a check is made, even if it hasn’t changed. How often does the assignments change on the same page request? Set keepAuthAssignments to true to only load it once per request.

Here are some typical settings for protected/config/main.php:




'authManager'=>array(

    'class' => 'EDbAuthManager',

    'connectionID' => 'db',

    'assignmentTable' => 'rbac_assignments',

    'itemChildTable' => 'rbac_relations',

    'itemTable' => 'rbac_items',

    'showErrors' => YII_DEBUG,

    'singleLoadAuthItems' => false,

    'singleLoadAuthParents' => false,

    'keepAuthAssignments' => true,

),



And here is the code. checkAccessRecursive() is identical to the CDbAuthManager one, except I moved the part where the parents are loaded to getAuthParents(). I also overwrote getAuthItem() and getAuthAssignments().




class EDbAuthManager extends CDbAuthManager

{

    public $singleLoadAuthItems = true;

    public $singleLoadAuthParents = true;

    public $keepAuthAssignments = false;


    protected $_items;

    protected $_parents;

    protected $_assignments = array();


    protected function checkAccessRecursive($itemName, $userId, $params, $assignments)

    {

        if (($item = $this->getAuthItem($itemName)) === null)

            return false;


        Yii::trace('Checking permission "'.$item->getName().'"','system.web.auth.CDbAuthManager');


        if (!isset($params['userId']))

            $params['userId'] = $userId;


        if ($this->executeBizRule($item->getBizRule(), $params, $item->getData()))

        {

            if (in_array($itemName, $this->defaultRoles))

                return true;


            if (isset($assignments[$itemName]))

            {

                $assignment = $assignments[$itemName];

                if ($this->executeBizRule($assignment->getBizRule(), $params, $assignment->getData()))

                    return true;

            }


            foreach ($this->getAuthParents($itemName) as $parent)

            {

                if ($this->checkAccessRecursive($parent, $userId, $params, $assignments))

                    return true;

            }

        }


        return false;

    }


    public function getAuthItem($name)

    {

        if ($this->singleLoadAuthItems)

            return parent::getAuthItem($name);


        if (!isset($this->_items))

        {

            $rows = $this->db->createCommand()

                ->select()

                ->from($this->itemTable)

                ->queryAll();


            $this->_items = array();

            foreach($rows as $row)

            {

                if (($data = @unserialize($row['data'])) === false)

                    $data = null;


                $this->_items[$row['name']] = new CAuthItem($this, $row['name'], $row['type'], $row['description'], $row['bizrule'], $data);

            }

        }


        if (isset($this->_items[$name]))

            return $this->_items[$name];

        else

            return null;

    }


    public function getAuthAssignments($userId)

    {

        if ($this->keepAuthAssignments)

        {

            if (!isset($this->_assignments[$userId]))

                $this->_assignments[$userId] = parent::getAuthAssignments($userId);


            $assignments = $this->_assignments[$userId];

        }

        else

        {

            $assignments = parent::getAuthAssignments($userId);

        }


        return $assignments;

    }


    public function getAuthParents($itemName)

    {

        if ($this->singleLoadAuthParents)

        {

            return $this->db->createCommand()

                ->select('parent')

                ->from($this->itemChildTable)

                ->where('child=:name', array(':name'=>$itemName))

                ->queryColumn();

        }


        if (!isset($this->_parents))

        {

            $relations = $this->db->createCommand()

                ->select()

                ->from($this->itemChildTable)

                ->queryAll();


            $this->_parents = array();

            foreach ($relations as $row)

                $this->_parents[$row['child']][$row['parent']] = $row['parent'];

        }


        if (isset($this->_parents[$itemName]))

            return array_values($this->_parents[$itemName]);

        else

            return array();

    }


}



I’ve made a class called CachingDBAuthManager found here https://github.com/yiisoft/yii/issues/1533#issuecomment-9207350

it does a single query with left join fetching every thing at once




        $rows=$this->db->createCommand()

                    ->select('name, type, description, bizrule, data, parent')

                    ->from(array(

                    $this->itemTable,

                    $this->itemChildTable

                    ))

                    ->where('name=child')

                    ->queryAll();



this is the only query and it’s done once aday by default

you may call purge in afterSave of itemTable and itemChildTable modules if any

to invalidate the cache

or made a command line that calls purge




    if (Yii::app()->authManager instanceof CachingDbAuthManager) Yii::app()->authManager->purgeCache();



I am using this extension:

http://www.yiiframework.com/extension/ecacheddbauthmanager/