smith1000 opened a new issue, #61631: URL: https://github.com/apache/doris/issues/61631
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Version Doris FE/BE 4.0.3 (doris-4.0.3-rc03-e9096296b8b) Also reproduced on 4.0.2 (doris-4.0.2-rc02-30d2df0459) Ranger Admin: apache/ranger:2.7.0 ### What's Wrong? When `access_controller_type = ranger-doris` is enabled in FE, Ranger column-level privilege checks are **completely bypassed** if the SQL query uses a **CTE (`WITH ... AS`) combined with any type of JOIN** (`LEFT JOIN` / `INNER JOIN`). A user who has NO privilege on a table can successfully query all data from that table by wrapping the query in a CTE + JOIN structure. This is a **security vulnerability** that allows unauthorized data access. **Key findings from systematic testing:** | # | SQL Structure | Expected | Actual | |---|-------------|----------|--------| | 1 | `SELECT * FROM restricted_table` | Denied | Denied ✅ | | 2 | Single CTE, no JOIN: `WITH base AS (...) SELECT * FROM base` | Denied | Denied ✅ | | 3 | Single CTE + aggregation (GROUP BY) | Denied | Denied ✅ | | 4 | Multiple CTEs referencing each other, no JOIN | Denied | Denied ✅ | | 5 | No CTE, subquery + LEFT JOIN | Denied | Denied ✅ | | **6** | **Single CTE + LEFT JOIN (CTE self-join)** | Denied | **Bypassed!** | | **7** | **Single CTE + INNER JOIN** | Denied | **Bypassed!** | | **8** | **Multiple CTEs + LEFT JOIN** | Denied | **Bypassed!** | **Trigger condition: CTE + JOIN = privilege bypass.** The number of CTEs and the type of JOIN do not matter. As long as a CTE exists and a JOIN references it, column-level privilege checks are skipped entirely. The root cause appears to be in the Ranger plugin's SQL analysis logic: when a CTE is referenced via JOIN, the plugin fails to recursively resolve the physical tables inside the CTE for privilege checking. ### What You Expected? All SQL queries should be subject to Ranger column-level privilege checks regardless of SQL structure. A user without SELECT privilege on a table should receive "Permission denied" whether the query uses CTEs, JOINs, subqueries, or any combination thereof. Specifically, a query like: SELECT t.col FROM ( WITH base AS (SELECT * FROM restricted_table) SELECT b1.col FROM base b1 LEFT JOIN base b2 ON b1.id = b2.id ) AS t should return "Permission denied" if the user has no privilege on `restricted_table`. ### How to Reproduce? **Environment setup:** - Doris FE with `access_controller_type = ranger-doris` and `skip_localhost_auth_check = false` - Ranger Admin 2.7.0 connected to Doris FE - Single FE + single BE, Docker deployment **FE config:** access_controller_type = ranger-doris skip_localhost_auth_check = false **Step 1: Create test database and tables (as root)** CREATE DATABASE IF NOT EXISTS test_db; CREATE TABLE IF NOT EXISTS test_db.restricted_table ( id VARCHAR(50), name VARCHAR(100), department VARCHAR(200), salary DECIMAL(10,2) ) DISTRIBUTED BY HASH(id) BUCKETS 1 PROPERTIES ("replication_num" = "1"); INSERT INTO test_db.restricted_table VALUES ('E001', 'Alice', 'Engineering', 10000), ('E002', 'Bob', 'Product', 12000), ('E003', 'Charlie', 'Engineering', 11000); CREATE TABLE IF NOT EXISTS test_db.public_table ( id VARCHAR(50), info VARCHAR(100) ) DISTRIBUTED BY HASH(id) BUCKETS 1 PROPERTIES ("replication_num" = "1"); INSERT INTO test_db.public_table VALUES ('E001', 'public info'); CREATE USER IF NOT EXISTS 'test_user'@'%' IDENTIFIED BY 'TestPass123!'; **Step 2: Configure Ranger policies** Via Ranger Admin API or UI, create policies so that `test_user`: - HAS `SELECT` privilege on `test_db.public_table` (table + all columns) - Does NOT have any privilege on `test_db.restricted_table` **Step 3: Wait for policy sync (10-15 seconds)** **Step 4: Verify basic privilege enforcement (login as test_user)** -- This should succeed: SELECT * FROM test_db.public_table; -- OK -- This should be denied: SELECT * FROM test_db.restricted_table LIMIT 1; -- ERROR: Permission denied ✅ **Step 5: Demonstrate the bypass (still as test_user)** -- This should be denied but SUCCEEDS: SELECT t.id, t.name FROM ( WITH base AS ( SELECT * FROM `internal`.`test_db`.`restricted_table` ) SELECT b1.id, b1.name FROM base b1 LEFT JOIN base b2 ON b1.id = b2.id ) AS t LIMIT 3; -- Returns data! Privilege check bypassed! ### Anything Else? **Additional observations:** 1. This bug exists in both 4.0.2-rc02 and 4.0.3-rc03 — upgrading does not fix it. 2. Without CTE (pure subquery + JOIN), privilege checks work correctly. 3. With CTE but without JOIN, privilege checks work correctly. 4. Only the specific combination of CTE + JOIN triggers the bypass. 5. This is a security vulnerability — in our production environment, an AI-powered data analysis agent generates complex SQL with CTEs + JOINs and executes it with per-user Doris credentials. Users who should not have access to certain tables can see the data because of this bug. **Suggested fix area:** The Ranger plugin's SQL statement analysis in Doris FE — specifically the logic that extracts table/column references from SQL for privilege checking. The CTE table references are likely not being resolved to physical tables when they appear in JOIN clauses. ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
