[Bug] Ranger column-level privilege bypass when CTE (WITH ... AS) is combined with JOIN

[smith1000]

Search before asking

• I had searched in the issues 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

• I agree to follow this project's Code of Conduct