如何基于用户权限限制Solr搜索范围并实现数据权限管控？

Hey there! Let's walk through how to build this access control system to ensure authorized users only access the data they're permitted to see. We'll cover everything from foundational data models to real-time query filtering.

First, you need to formalize how users, teams, and system permissions relate. Based on your example, a team-based permission model (with room for individual overrides later) works perfectly:

• Create core database tables to map relationships:
  • users: Stores user details (user_id, name, team_id, etc.)
  • teams: Stores team info (team_id, team_name, description)
  • team_system_permissions: Maps teams to allowed system ranges (team_id, system_min, system_max) — this directly supports your use case where Equity has 1-10 and Audit has 1-200.
  • (Optional) user_system_permissions: For individual users who need exceptions to team permissions (e.g., an Equity member who can access system 11)

Here's a simplified SQL schema example:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    team_id INT REFERENCES teams(team_id)
);

CREATE TABLE teams (
    team_id INT PRIMARY KEY,
    team_name VARCHAR(50) UNIQUE
);

CREATE TABLE team_system_permissions (
    permission_id INT PRIMARY KEY,
    team_id INT REFERENCES teams(team_id),
    system_min INT,
    system_max INT
);

Don't rely solely on application code — add safeguards at the database level to block unauthorized access even if app logic fails.

• Use Database Views: Create a view that automatically filters data based on the current authenticated user. For example, in PostgreSQL, you can pass the user_id via current_setting:

  CREATE VIEW accessible_data AS
  SELECT * FROM your_data_table
  WHERE system BETWEEN (
      SELECT system_min FROM team_system_permissions tsp
      JOIN users u ON tsp.team_id = u.team_id
      WHERE u.user_id = current_setting('app.current_user')::INT
  ) AND (
      SELECT system_max FROM team_system_permissions tsp
      JOIN users u ON tsp.team_id = u.team_id
      WHERE u.user_id = current_setting('app.current_user')::INT
  );
  

• Row-Level Security (RLS): If your database supports it (like PostgreSQL, Snowflake), enable RLS on your data table. Create a policy that restricts rows to only those where the system falls within the user's allowed range.

When handling user requests, automatically add permission filters to every search query:

1. Authenticate the User: Verify the user's identity (via JWT, session token, etc.), then retrieve their user_id and associated team permissions.
2. Cache Allowed System Range: Store the user's system_min and system_max in their session or a fast cache (like Redis) to avoid repeated database calls for every request.
3. Filter Search Queries: Modify every search request to include the system range filter. Examples:
   • Raw SQL: SELECT * FROM your_data_table WHERE system >= ? AND system <= ? AND [your_search_conditions]
   • Django ORM:

     allowed_min = request.user.team.system_permissions.system_min
     allowed_max = request.user.team.system_permissions.system_max
     results = DataModel.objects.filter(
         system__range=(allowed_min, allowed_max),
         **search_filters
     )
     

If you're using a search engine like Elasticsearch or Solr for fast data retrieval:

• Index the system Field: Ensure every document in the search index includes the system field.
• Add a Filter to Search Requests: When building the search query, include a range filter for the user's allowed systems. For Elasticsearch:

  {
    "query": {
      "bool": {
        "must": [/* Your main search query */],
        "filter": [
          {
            "range": {
              "system": {
                "gte": 1,
                "lte": 10
              }
            }
          }
        ]
      }
    }
  }
  

• (Optional) Use Document-Level Security: Some search engines offer built-in features (e.g., Elasticsearch's Document Level Security plugin) to simplify restricting document access.

To meet audit requirements and track access, log every user's data access:

• Record details like: user_id, timestamp, search_query, allowed_system_range, results_returned_count.
• Store logs in a separate, immutable database to prevent tampering.

1. Adam logs in, the system authenticates him, and retrieves his team (Equity) with system_min=1 and system_max=10.
2. Adam submits a search query for "error logs".
3. The application automatically adds the filter system BETWEEN 1 AND 10 to the query.
4. The database/search engine returns only results where system is 1-10.
5. The access is logged with Adam's user ID, query, and system range.

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