Bug #97971 Roles not handling column level privileges correctly; Can SELECT, but not UPDATE

Description:
When assigning column level privileges using Roles, a schema level SELECT privilege is not being considered when making a column level UPDATE statement with a WHERE clause such that the UPDATE statement will fail with the error:

Error Code: 1143. SELECT command denied to user 'TestUserFails'@'localhost' for column 'test_id' in table 'test_table'

However, this works as expected for Users, so it seems to be a bug with how Roles are handled.

How to repeat:
Set up a sample database/table as follows with these roles and privileges:

We'll have one role, and two users.  One user (TestUserFails) is granted the role (r_verifier).  Both the role and the other user (TestUserWorks) are assigned the same SELECT and UPDATE privileges.

-- -------------------------------------CREATE CODE-----------------------------------------------------
-- Create sample database/table
CREATE DATABASE bug_test;
CREATE TABLE bug_test.test_table (test_id int, test_data varchar(50), row_is_verified bool);
INSERT INTO bug_test.test_table VALUES(1, 'valueA', FALSE);

-- Create role and two users
CREATE ROLE 'r_verifier';
CREATE USER TestUserFails IDENTIFIED BY 'test';
CREATE USER TestUserWorks IDENTIFIED BY 'test';

-- Grant privileges to ROLE
GRANT SELECT ON bug_test.* TO 'r_verifier';
GRANT UPDATE (row_is_verified) ON bug_test.test_table TO 'r_verifier';

-- GRANT same privileges to USER
GRANT SELECT ON bug_test.* TO 'TestUserWorks';
GRANT UPDATE (row_is_verified) ON bug_test.test_table TO 'TestUserWorks';

-- Grant role to TestUserFails and make it a default role
GRANT 'r_verifier' TO 'TestUserFails';
SET DEFAULT ROLE 'r_verifier' TO 'TestUserFails';
-- -------------------------------------CODE-----------------------------------------------------

Then, as the 'TestUserFails' user, you execute the following commands:

SELECT CURRENT_ROLE();
SELECT test_id, test_data, row_is_verified FROM bug_test.test_table;
UPDATE bug_test.test_table SET row_is_verified = TRUE WHERE test_id=1;

This shows that
1) The appropriate role is active as expected
2) You can SELECT the test_table as expected
3) You CANNOT update the row_is_verified column in the because of the aforementioned error.
   (If you do a SELECT with no WHERE clause, it works)
4) The schema level SELECT privileges for the role are NOT being used for a column specific UPDATE statement's WHERE clause

Also, as 'TestUserWorks', you execute the same SELECT and UPDATE commands:

SELECT test_id, test_data, row_is_verified FROM bug_test.test_table;
UPDATE bug_test.test_table SET row_is_verified = TRUE WHERE test_id=1;

This shows that
1) You can SELECT the test_table as expected
2) You CAN update the row_is_verified column as expected
   (The schema level select privileges are being used in the UPDATE statement's WHERE clause.

I know this also affects DELETE statements with WHERE clauses similarly.

Suggested fix:
Make user's with active Roles behave the same way as normal user with the same privileges as the Role.

As a workaround, I believe/hope (but haven't tried yet) you can assign column level SELECT privileges as needed, but those will be directly redundant with the schema level SELECT privileges outside of this issue.

Hello Travis Bement,

Thank you for the bug report.

regards,
Umesh

Test results - 8.0.18

Attachment: 97971.results (application/octet-stream, text), 8.23 KiB.

A few more details:

If I add column level select privileges for the role, it works (confirming the workaround).

Also, this may not actually be an issue with column level privileges specifically, but more of a general issue where privileges can only be used from one 'role' at a time.

I had a case where I was working with table level permissions in a similar fashion where it required one role with update table permissions and then another role that had select schema privileges and I couldn't do a table update with a where clause for the same reason.

Posted by developer:
 
Fixed in 8.0.22.

Privileges granted using roles could be mishandled at the
column-privilege level.