Row-level Security Error

[jtyow]

I followed the documentation on row-level security but running the following DDL statements grants access to both column_value_1 and column_value_2 to user_1. this shouldn't happen. what am I doing wrong?

CREATE ROW ACCESS POLICY policy_1

ON `project.dataset.table_1`

GRANT TO ('user:user_1@google.com')

FILTER USING (column IN ('column_value_1'));

CREATE ROW ACCESS POLICY policy_2

ON `project.dataset.table_1`

GRANT TO ('user:user_2@google.com')

FILTER USING (column IN ('column_value_2'));

[kolban]

It sure does feel like user_1 should only be able to return the rows that have column set to "column_value_1".  What if we try an experiment .... drop all the ROW ACCESS POLICIES and try a filter that is:

FILTER USING (column = "column_value_1")

Let's also see the query you are running.  I'd also be interested in seeing a dump of the row access policies on the table.  Are these the ONLY row policies on the table?

[jtyow]

I agree that's what should be happening as well. These are the only row-level policies on the table. The workaround, which I don't like because it won't scale efficiently with a large, diverse user pool, was the creation of a provisioning table and the addition of an array column (user_ids) and the following:

FILTER

USING

(SESSION_USER() IN UNNEST(user_ids));

[jtyow]

Also, I tried both "IN" and "=" for the FILTER USING modifier and both returned the same results. To validate my policies, I created a separate user_id with working credentials, logged into a different Chrome session, and ran SQL queries in the BQ workspace to mimic the real-world environment.