raghav-reglobe opened a new issue, #70831:
URL: https://github.com/apache/doris/issues/70831

   ### Search before asking
   
   - [x] I had searched in the 
[issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no 
similar issues.
   
   ### Description
   
   **What happens today**
   
   With column privileges, a user who was granted some columns of a table, and 
not the table, cannot run a statement that reads rows of the table and none of 
its columns:
   
   ```sql
   GRANT SELECT_PRIV(id) ON db.t TO u;
   
   -- as u
   SELECT id FROM db.t;            -- ok, every row
   SELECT COUNT(id) FROM db.t;     -- ok
   SELECT COUNT(*) FROM db.t;      -- denied
   SELECT COUNT(1) FROM db.t;      -- denied
   SELECT 1 FROM db.t LIMIT 1;     -- denied
   ```
   
   When a statement needs no column of an internal table, 
`UserAuthentication.checkPermission` asks for SELECT on the whole table (the 
rule comes from #35465 and is pinned by 
`auth_p0/test_select_count_auth.groovy`). #35465 fixed a real problem, a user 
with no privilege at all being able to count a table, and that stays fixed in 
what is proposed here.
   
   Two things follow from the current rule:
   
   1. The refusal protects nothing. The same user can fetch `id` for every row, 
so the row count is already theirs.
   2. The answer depends on how the statement is written. A table written with 
an alias, a view and a subquery sit under a project, and when nothing is read 
from that project the planner keeps one column of its own choosing in it (the 
narrowest type). The privilege check is then made on that column. So `SELECT 
COUNT(*) FROM db.t a` passes when the kept column happens to be one the user 
holds, and is refused when it is not, for a column the statement never reads.
   
   Checked on master (d556f32bf8c9) in an FE unit test with real `GRANT` 
statements, on a table `t(id INT, name VARCHAR(32), secret BIGINT)`:
   
   | Statement | holder of `name` | holder of `id` |
   |---|---|---|
   | `SELECT COUNT(*) FROM t` | denied (asks for SELECT on the table) | denied 
(asks for SELECT on the table) |
   | `SELECT COUNT(*) FROM t a` | denied, naming column `id` | allowed |
   | `SELECT COUNT(*) FROM v` (a view over `t`) | denied, naming column `id` | 
not run |
   | `SELECT COUNT(*) FROM (SELECT id, secret FROM t) q` | denied, naming 
column `id` | not run |
   
   **What other engines do**
   
   An account holding SELECT on one column (`id`) of a table and of a view over 
it. MySQL 8.4.13 and PostgreSQL 16.15 were run; Trino was read in source.
   
   | Statement | MySQL 8.4 | PostgreSQL 16 |
   |---|---|---|
   | `SELECT COUNT(*) FROM t` | allowed | allowed |
   | `SELECT COUNT(1) FROM t` | allowed | allowed |
   | `SELECT COUNT(*) FROM t a` | allowed | allowed |
   | `SELECT COUNT(*) FROM v` | allowed | allowed |
   | `SELECT 1 FROM t LIMIT 1` | allowed | allowed |
   | `SELECT COUNT(secret) FROM t` (not granted) | denied | denied |
   | `SELECT COUNT(*) FROM (SELECT secret FROM t) q` | allowed | denied |
   | `SELECT COUNT(*) FROM t`, no privilege at all | denied | denied |
   
   - **PostgreSQL** states the rule in its source and cites the SQL standard 
for it: "When the query doesn't explicitly reference any columns (for example, 
SELECT COUNT(*) FROM table), allow the query if we have SELECT on any column of 
the rel, as per SQL spec." 
([execMain.c](https://github.com/postgres/postgres/blob/207b5293b8d9a217bb918b5b44774b3b4f1e2f89/src/backend/executor/execMain.c#L706-L716))
   - **MySQL**: in the run, every statement that used no column passed with one 
column granted, and a column named in a subquery that nothing reads needed no 
privilege (row 7). Doris also decides what to check from the columns a 
statement uses. The table-level step in the source accepts a privilege held on 
any column of the table 
([sql_authorization.cc](https://github.com/mysql/mysql-server/blob/bf5a74e48423ac51200987edcd72a678772cb2ee/sql/auth/sql_authorization.cc#L3949-L3955)).
   - **Trino** hands the access control an empty column set for such a 
statement ("The column set can be empty", 
[SystemAccessControl.java](https://github.com/trinodb/trino/blob/6678854c112d055a2f093e61a3a219003a52a5be/core/trino-spi/src/main/java/io/trino/spi/security/SystemAccessControl.java#L514))
 and each implementation decides. Its file-based rules allow it for a user who 
may select from the table with some columns restricted 
([TableAccessControlRule.java](https://github.com/trinodb/trino/blob/6678854c112d055a2f093e61a3a219003a52a5be/lib/trino-plugin-toolkit/src/main/java/io/trino/plugin/base/security/TableAccessControlRule.java#L94-L97));
 its Ranger plugin asks Ranger about the table 
([RangerTrinoResource.java](https://github.com/trinodb/trino/blob/6678854c112d055a2f093e61a3a219003a52a5be/plugin/trino-ranger/src/main/java/io/trino/plugin/ranger/RangerTrinoResource.java#L97-L101)).
   
   **Proposal**
   
   Under the built-in privilege model, a statement that reads rows of a table 
and none of its columns takes SELECT on the table, or SELECT on at least one 
column the table has now. That makes Doris answer the eight statements above 
the way MySQL does.
   
   What does not change:
   
   - A user who holds no column of the table is refused, as #35465 requires.
   - Every statement that reads a column takes that column's privilege: 
`COUNT(col)`, a predicate, a join condition, a grouping, an ordering.
   - A catalog governed by another authorization source (Ranger, a custom 
access controller) is asked exactly what it is asked today.
   - The HTTP row-count API (`/api/<db>/<table>/_count`) keeps asking for 
SELECT on the table.
   
   For the alias, view and subquery forms, the answer would stop depending on 
the column the planner keeps: when the check is refused and the user holds some 
column of the table, the planner looks at whether the statement reads any 
column of that table at all, and lets it through if it reads none.
   
   **Security and cost**
   
   - Nothing new is readable. Whoever passes can already read every row of the 
table through the column they hold, and a row policy on the table filters the 
counted rows as it does today.
   - A grant on a column that was dropped or renamed does not count: the check 
looks at the columns the table has now.
   - Statements that pass today take the same path as today. The extra work is 
on a refusal only, and only for a user who holds a column of the table.
   
   **One question for the maintainers**
   
   The new check ("does the user hold SELECT on some column of this table") is 
answered from the built-in model directly, in `AccessControllerManager`, and is 
not a method of the authorization plugin API: that API's surface is frozen and 
an added method is a major version change. If you would rather have it as a 
question every source can answer, I am glad to rework it that way.
   
   ### Use case
   
   Deployments that give analysts column-limited roles, with the sensitive 
columns of a table or a view left out. BI tools, JDBC clients and ORMs send 
`SELECT COUNT(*)`, `SELECT 1 FROM t LIMIT 1` and `SELECT COUNT(*) FROM 
(<query>) x` on their own, for row counts, previews and pagination, and each of 
these fails today for such a role. The workaround, counting a granted column, 
is not something a tool can be told to do.
   
   ### Related issues
   
   - #35465 introduced the table-level rule for a statement that reads no 
column. Its case, no privilege at all, stays refused.
   - #45478 added `auth_p0/test_select_count_auth.groovy`; two of its 
expectations would change.
   
   ### Are you willing to submit PR?
   
   - [x] 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]

Reply via email to