To do this in a module, I guess you'd need to do something like this:
4. example_db_rewrite_sql()
add a join on the new table and betwen the new table and {role} rl and a
where clause like WHERE rl.rid IN ('. implode(',', array_keys($user->roles)
.')';
