基于Codeigniter与Ion_Auth的多租户应用全局租户数据过滤问询

Great question—this is a common pain point with shared-schema multi-tenancy, and you’re right to avoid repeating that WHERE tenant_id = [current_user_id] clause in every query. Let’s walk through the most practical, maintainable solutions tailored to your stack:

1. CodeIgniter Database Hooks (Full Global Automation) #

CodeIgniter’s database class supports hooks that let you modify queries before they’re executed. This is the most "set it and forget it" approach for global filtering.

How to Implement: #

1. Enable hooks in application/config/config.php:

   $config['enable_hooks'] = TRUE;
   

2. Define a before_query hook in application/config/hooks.php:

   $hook['before_query'] = [
       'class'    => 'TenantFilterHook',
       'function' => 'filter_tenant_data',
       'filename' => 'TenantFilterHook.php',
       'filepath' => 'hooks',
       'params'   => []
   ];
   

3. Create the hook class in application/hooks/TenantFilterHook.php:

   class TenantFilterHook {
       public function filter_tenant_data($query) {
           // Skip if no user is logged in or it's a super admin
           if (!get_instance()->ion_auth->logged_in() || get_instance()->ion_auth->is_admin()) {
               return;
           }
   
           $user = get_instance()->ion_auth->user()->row();
           $tenant_id = $user->tenant_id;
   
           // Define which tables are tenant-specific (exclude system tables like users, groups)
           $tenant_tables = ['orders', 'products', 'invoices'];
           $query_str = $query->query_str;
   
           // Only modify SELECT/UPDATE/DELETE queries for tenant tables
           if (preg_match('/^(SELECT|UPDATE|DELETE)\s+FROM\s+(`?)(\w+)\2/i', $query_str, $matches)) {
               $table = $matches[3];
               if (in_array($table, $tenant_tables)) {
                   // Append tenant filter to SELECT/DELETE
                   if (str_starts_with(strtoupper($query_str), 'SELECT') || str_starts_with(strtoupper($query_str), 'DELETE')) {
                       $query->query_str .= (strpos($query_str, 'WHERE') !== false ? ' AND ' : ' WHERE ') . "tenant_id = {$tenant_id}";
                   }
                   // Add tenant filter to UPDATE
                   elseif (str_starts_with(strtoupper($query_str), 'UPDATE')) {
                       $query->query_str = preg_replace('/SET\s+/i', "SET tenant_id = {$tenant_id}, ", $query_str);
                   }
               }
           }
       }
   }
   

Notes:

• Adjust $tenant_tables to include all your tenant-specific tables.
• Add a check for super admins to bypass filtering (critical for admin access to all tenant data).
• The regex is basic—you might need to refine it for complex queries (e.g., JOINs), but it works for most standard cases.

2. Base Tenant Model (Controlled, Model-Level Filtering) #

If you prefer more control over which models apply tenant filtering, create a base model that all tenant-specific models inherit from. This avoids modifying every query and keeps logic centralized.

How to Implement: #

1. Create application/models/BaseTenantModel.php:

   class BaseTenantModel extends CI_Model {
       protected $tenant_id;
   
       public function __construct() {
           parent::__construct();
           if ($this->ion_auth->logged_in() && !$this->ion_auth->is_admin()) {
               $this->tenant_id = $this->ion_auth->user()->row()->tenant_id;
           }
       }
   
       // Override CodeIgniter's get_where method
       public function get_where($params = [], $limit = null, $offset = null) {
           if ($this->tenant_id) {
               $params['tenant_id'] = $this->tenant_id;
           }
           return parent::get_where($params, $limit, $offset);
       }
   
       // Override find method (if using CodeIgniter 3's query builder)
       public function find($id) {
           $this->db->where('id', $id);
           if ($this->tenant_id) {
               $this->db->where('tenant_id', $this->tenant_id);
           }
           return $this->db->get($this->table)->row();
       }
   
       // Override update method
       public function update($id, $data) {
           if ($this->tenant_id) {
               $this->db->where('tenant_id', $this->tenant_id);
           }
           return parent::update($id, $data);
       }
   
       // Add similar overrides for delete, get, etc.
   }
   

2. Have your tenant models inherit from this base model:

   class OrderModel extends BaseTenantModel {
       protected $table = 'orders';
   
       // Your custom model methods here—they'll automatically inherit tenant filtering
   }
   

Advantages:

• Explicitly controls which models apply filtering (no risk of accidentally modifying system tables).
• Easier to debug since filtering is tied to model logic, not global hooks.
• Works well with complex queries where you might need to adjust the tenant condition manually.

3. Why Ion_Auth Groups Aren’t Enough #

You mentioned using Ion_Auth groups as a base solution—and while groups are great for permission control (e.g., "tenant admin" vs "regular user"), they don’t solve the data isolation problem. Groups can restrict what actions a user can take, but they won’t automatically filter rows in every query. You’d still need to add the tenant_id condition somewhere, so the above global/model-level approaches are necessary.

Final Recommendations #

• Use the database hook if you want full automation and don’t need granular control over individual models.
• Use the base tenant model if you prefer explicit, maintainable logic that’s tied to your data models.
• Always include a super admin bypass to ensure you can access all tenant data for maintenance or support.

内容的提问来源于stack exchange，提问作者Robin Bantjes