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.