Zend_Db_Select row level security

Question

How could I accomplish row level security using Zend_Db_Select? I can think of a few options, but they don't really seem to fit the pattern quite right.

Let's say I have users, content, and many different ACL levels. Here's one solution I've thought of:

$select = $db->select()
             ->from('content')
             ->where('content_type NOT IN (?)',
                     Model_Content::userAllowedContentTypes()
             )
             ->order('date DESC')
);

But the problem is, what if you decide to add another field for different types of security? So, I thought maybe something like this:

/**
 * @var Zend_Db_Select
 */
$where = Model_Content::getWhere();
$db->select()
    ->from('content')
    ->$where()
    ->order('date DESC');

Better.. but that doesn't feel quite right either. What I'd like is something more like this:

$db->select()
    ->from(array('c' => 'content'))
    ->getPlugin(new Model_Content_Security('c'))
    ->order('date DESC');

But that seems like I'm getting a little too close to extending or modifying the library to get something that might already be there, but I'm not quite seeing it.

Has anyone else had this sort of need, and how did you solve it?

Answer 1

If your security is controlled in the db you can left join to it:

    $select = $db->select()
             ->from('content')
             ->joinLeft( array( 'pt'=>'permissionTable' ),'content.id = pt.contentId AND pt.userId = ' . $escapedUserId, array() )
             ->where( pt.contentId IS NOT NULL )        
             ->order('date DESC');

Perhaps extend Zend_Db_Select to inspect the tables being accessed and have it make calls to security objects passing the select statement as a parameter. For instance, your content class could be responsible for adding the joinLeft() and where() statements.

public function addSecurityToSelect( Zend_Db_Select $select ){
    return $select->joinLeft( array( 'pt'=>'permissionTable' ),'content.id = pt.contentId AND pt.userId = ' . $escapedUserId, array() )
                  ->where( pt.contentId IS NOT NULL )  

}

Have your extended Zend_Db_Select class loop through the included tables, check to see if there is a security function it can call and then call it.

Another option (much less db efficient) would be to query for all items and do the security check on the returned rows while iterating through the result set.

Answer 2

Extend Zend_Db_Select. Make App_Db_Select_Security and init it appropriately in constructor.