Bug #97971 Roles not handling column level privileges correctly; Can SELECT, but not UPDATE
Submitted: 12 Dec 2019 17:30 Modified: 24 Nov 2020 17:51
Reporter: Travis Bement Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:8.0.18 OS:Any
Assigned to: CPU Architecture:Any
Tags: Column Privileges, roles

[12 Dec 2019 17:30] Travis Bement
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.
[13 Dec 2019 10:22] MySQL Verification Team
Hello Travis Bement,

Thank you for the bug report.

regards,
Umesh
[13 Dec 2019 10:23] MySQL Verification Team
Test results - 8.0.18

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

[13 Dec 2019 20:43] Travis Bement
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.
[24 Nov 2020 17:51] Paul DuBois
Posted by developer:
 
Fixed in 8.0.22.

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