You are currently reading the 1.2 documentation. Switch to 2.1  2.0 

Record Based Retrieval Security Template

Introduction

This is a tutorial & how-to on using a security template and listener to restrict a user to specific records, or a range of specific records based on credentials and a user table association. Basically fine grained user access control.

This template was created for a project which had a few credentials, division_manager, district_manager, branch_manager, and salesperson. We have a list of accounts, their related sales and all sorts of sensitive information for each account. Each logged in user should be allowed to only view the accounts and related information based off their credentials + either the division, district, branch or salesperson they are allowed to view.

So a division manager can view all info for all accounts within his division. A salesperson can only view the accounts they are assign.

The template has been a work in progress so the code below may not actually be the final code I'm using today. But since it is now working for all situations I'm asking of it, I thought I would post it as is.

Template

class gsSecurityTemplate extends Doctrine_Template
{
    protected $_options = array();

    /**
     * __construct
     *
     * @param string $options
     * @return void
     */
    public function __construct(array $options)
    {
        if (!isset($options['conditions']) || empty($options['conditions'])) {
            throw new Doctrine_Exception('Unable to create security template without conditions');
        }

        $this->_options = $options;
    }

    public function setUp()
    {
        $this->addListener(new gsSecurityListener($this->_options));
    }
}

class gsSecurityListener extends Doctrine_Record_Listener
{
    private static
        $_user_id = 0,
        $_credentials = array(),
        $_alias_count = 30;

    protected $_options = array();

    /**
     * __construct
     *
     * @param string $options
     * @return void
     */
    public function __construct(array $options)
    {
        $this->_options = $options;
    }

    public function preDqlSelect(Doctrine_Event $event)
    {
        $invoker = $event->getInvoker();
        $class   = get_class($invoker);
        $params  = $event->getParams();

        if($class == $params['alias']) {
            return;
        }

        $q       = $event->getQuery();

        // only apply to the main protected table not chained tables... may break some situations
        if(!$q->contains('FROM '.$class)) {
            return;
        }

        $wheres = array();
        $pars   = array();

        $from = $q->getDqlPart('from');

        foreach ($this->_options['conditions'] as $rel_name => $conditions) {
            $apply = false;
            foreach ($conditions['apply_to'] as $val) {
                if (in_array($val,self::$_credentials)) {
                    $apply = true;
                    break;
                }
            }

            if ($apply) {
                $alias = $params['alias'];
                $aliases = array();
                $aliases[] = $alias;

                foreach ($conditions['through'] as $key => $table) {
                    $index = 0;
                    $found = false;
                    foreach ($from as $index => $val) {
                        if (strpos($val,$table) !== false) {
                            $found = true;
                            break;
                        }

                    }

                    if ($found) {
                        $vals = explode(' ', substr($from[$index],strpos($from[$index],$table)));
                        $alias = (count($vals) == 2) ? $vals[1]:$vals[0];
                        $aliases[] = $alias;
                    } else {
                        $newalias = strtolower(substr($table,0,3)).self::$_alias_count++;
                        $q->leftJoin(end($aliases).'.'.$table.' '.$newalias);
                        $aliases[] = $newalias;
                    }
                }

                $wheres[] = '('.end($aliases).'.'.$conditions['field'].' = ? )';
                $pars[] = self::$_user_id;
            }
        }

        if(!empty($wheres)) {
            $q->addWhere( '('.implode(' OR ',$wheres).')',$pars);
        }
    }

    static public function setUserId($id)
    {
        self::$_user_id = $id;
    }

    static public function setCredentials($vals)
    {
        self::$_credentials = $vals;
    }
}

YAML schema syntax

Here is the schema I used this template with. I've removed lots of extra options, other templates I was using, indexes and table names. It may not work out of the box without the indexes - YMMV.

---
Account:
  actAs:
    gsSecurityTemplate:
      conditions:
        Division:
          through:  [ Division, UserDivision ]
          field: user_id
          apply_to: [ division_manager ]
        Branch:
          through:   [ Branch, UserBranch ]
          field: user_id
          apply_to: [ branch_manager ]
        Salesperson:
          through:  [ Salesperson, UserSalesperson ]
          field: user_id
          apply_to: [ salesperson ]
        District:
          through:  [ Branch, District, UserDistrict ]
          field: user_id
          apply_to: [ district_manager ]
  columns:
    id: { type: integer(4), primary: true, autoincrement: true, unsigned: true }
    parent_id: { type: integer(4), primary: false, autoincrement: false, unsigned: true}
    business_class_id: { type: integer(2), unsigned: true }
    salesperson_id: { type: integer(4), unsigned: true }
    branch_id: { type: integer(4), unsigned: true }
    division_id: { type: integer(1), unsigned: true }
    sold_to: { type: integer(4), unsigned: true }

Division:
  columns:
    id: { type: integer(1), autoincrement: true, primary: true, unsigned: true }
    name: { type: string(32) }
    code: { type: string(4) }

District:
  actAs:
    gsSecurityTemplate:
      conditions:
        Division:
          through:  [ Division, UserDivision ]
          field: user_id
          apply_to: [ division_manager ]
  relations:
    Division:
      foreignAlias: Districts
      local: division_id
      onDelete: RESTRICT
  columns:
    id: { type: integer(4), autoincrement: true, primary: true, unsigned: true }
    name: { type: string(64) }
    code: { type: string(4) }
    division_id: { type: integer(1), unsigned: true }

Branch:
  actAs:
    gsSecurityTemplate:
      conditions:
        Division:
          through: [ Division, UserDivision ]
          field: user_id
          apply_to: [ division_manager ]
        District:
          through:  [ District, UserDistrict ]
          field: user_id
          apply_to: [ district_manager ]
  relations:
    Division:
      local: division_id
      foreignAlias: Branches
      onDelete: CASCADE
    District:
      foreignAlias: Branches
      local: district_id
      onDelete: RESTRICT
  columns:
    id: { type: integer(4), primary: true, autoincrement: true, unsigned: true }
    name: { type: string(64) }
    code: { type: string(4) }
    district_id: { type: integer(4), unsigned: true }
    division_id: { type: integer(1), unsigned: true }
    is_active: { type: boolean, default: true }

User:
  relations:
    Divisions:
      class: Division
      refClass: UserDivision
      local: user_id
      foreign: division_id
    Districts:
      class: District
      refClass: UserDistrict
      local: user_id
      foreign: district_id
    Branches:
      class: Branch
      refClass: UserBranch
      local: user_id
      foreign: branch_id
    Salespersons:
      class: Salesperson
      refClass: UserSalesperson
      local: user_id
      foreign: salespersons_id
  columns:
    id: { type: integer(4), autoincrement: true, primary: true, unsigned: true }
    name: { type: string(128) }
    is_admin: { type: boolean, default: false }
    is_active: { type: boolean, default: true }
    is_division_manager: { type: boolean, default: false }
    is_district_manager: { type: boolean, default: false }
    is_branch_manager: { type: boolean, default: false }
    is_salesperson: { type: boolean, default: false }
    last_login: { type: timestamp }

UserDivision:
  tableName: user_divisions
  columns:
    id: { type: integer(4), autoincrement: true, primary: true, unsigned: true }
    user_id: { type: integer(4), primary: true, unsigned: true }
    division_id: { type: integer(1), primary: true, unsigned: true }

UserDistrict:
  tableName: user_districts
  columns:
    id: { type: integer(4), autoincrement: true, primary: true, unsigned: true }
    user_id: { type: integer(4), primary: true, unsigned: true }
    district_id: { type: integer(4), primary: true, unsigned: true }

UserBranch:
  tableName: user_branches
  columns:
    id: { type: integer(4), autoincrement: true, primary: true, unsigned: true }
    user_id: { type: integer(4), primary: true, unsigned: true }
    branch_id: { type: integer(4), primary: true, unsigned: true }

UserSalesperson:
  tableName: user_salespersons
  columns:
    id: { type: integer(4), autoincrement: true, primary: true, unsigned: true }
    user_id: { type: integer(4), primary: true, unsigned: true }
    salespersons_id: { type: integer(4), primary: true, unsigned: true }

You can see from the User model that the credentials are set within the db. All hardcoded in this situation.

Using the template

Once you've built your models from the schema, you should see something like the following in your model's setUp function.

$gssecuritytemplate0 = new gsSecurityTemplate(array('conditions' => array('Division' =>  array( 'through' =>  array( 0 => 'Division',  1 => 'UserDivision',  ),  'field' => 'user_id',  'apply_to' =>  array( 0 => 'division_manager',  ),  'exclude_for' =>  array( 0 => 'admin',  ), ), 'Branch' =>  array( 'through' =>  array( 0 => 'Branch',  1 => 'UserBranch',  ),  'field' => 'user_id',  'apply_to' =>  array( 0 => 'branch_manager',  ),  'exclude_for' =>  array( 0 => 'admin',  1 => 'division_manager',  2 => 'district_manager',  ), ), 'Salesperson' =>  array( 'through' =>  array( 0 => 'Salesperson',  1 => 'UserSalesperson',  ),  'field' => 'user_id',  'apply_to' =>  array( 0 => 'salesperson',  ),  'exclude_for' =>  array( 0 => 'admin',  1 => 'division_manager',  2 => 'district_manager',  3 => 'branch_manager',  ), ), 'District' =>  array( 'through' =>  array( 0 => 'Branch',  1 => 'District',  2 => 'UserDistrict',  ),  'field' => 'user_id',  'apply_to' =>  array( 0 => 'district_manager',  ),  'exclude_for' =>  array( 0 => 'admin',  1 => 'division_manager',  ), ))));
$this->actAs($gssecuritytemplate0);

The last part you need to use is to provide the template with the running user's credentials and id. In my project's session bootstrapping I have the following ( I use the symfony MVC framework ).

public function initialize($context, $parameters = null)
{
    parent::initialize($context, $parameters = null);
    gsSecurityListener::setUserId($this->getAttribute('user_id'));
    gsSecurityListener::setCredentials($this->listCredentials());

}

This provides the credentials the user was given when they logged in as well as their id.

User setup

In my case, I create users and provide a checkbox for their credentials, one for each type I have. Lets take Division Manager as an example. In my case we have 3 divisions, East, Central, West. When I create a user I assign it the West division, and check off that they are a division manager. I can then proceed to login, and my account listing page will restrict the accounts I see automatically to my division.

Querying

Now if you query the Account model, the template is triggered and based on your credentials the results will be restricted.

The query below

$accounts = Doctrine_Query::create()->from('Account a')->leftJoin('a.Branches b')->where('a.company_name LIKE ?','A%')->execute();

produces the resulting sql.

SELECT 
...
FROM accounts a2
LEFT JOIN branches b2 ON a2.branch_id = b2.id
LEFT JOIN divisions d2 ON a2.division_id = d2.id
LEFT JOIN user_divisions u2 ON d2.id = u2.division_id
WHERE a2.company_name LIKE ?
AND u2.user_id = ?
ORDER BY a2.company_name

The results you get back will always be restricted to the division you have been assigned. Since in our schema we've defined restrictions on the Branch and Districts as well if I were to want to provide a user with a drop down of potential branches, I can simply query the branches as I normally would, and only the ones in my division would be returned to choose from.

Restrictions

For the time being, this module only protects tables in the FROM clause, since doctrine currently runs the query listener for the new tables added to the query by the template, and thus we get a pretty nasty query in the end that doesn't work. If I can figure out how to detect such situations reliably I'll update the article.


Questions and Feedback

If you find a problem with the documentation or have a suggestion, please register and open a ticket.

If you need support or have a technical question, you can post to the user mailing-list.