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]
