-- 8.0.18

bin/mysql -uroot -S /tmp/mysql_ushastry.sock -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> -- Create sample database/table
mysql> CREATE DATABASE bug_test;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE bug_test.test_table (test_id int, test_data varchar(50), row_is_verified bool);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO bug_test.test_table VALUES(1, 'valueA', FALSE);
Query OK, 1 row affected (0.01 sec)

mysql> -- Create role and two users
mysql> CREATE ROLE 'r_verifier';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER TestUserFails IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER TestUserWorks IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> -- Grant privileges to ROLE
mysql> GRANT SELECT ON bug_test.* TO 'r_verifier';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT UPDATE (row_is_verified) ON bug_test.test_table TO 'r_verifier';
Query OK, 0 rows affected (0.00 sec)

mysql> -- GRANT same privileges to USER
mysql> GRANT SELECT ON bug_test.* TO 'TestUserWorks';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT UPDATE (row_is_verified) ON bug_test.test_table TO 'TestUserWorks';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> -- Grant role to TestUserFails and make it a default role
mysql> GRANT 'r_verifier' TO 'TestUserFails';
Query OK, 0 rows affected (0.00 sec)

mysql> SET DEFAULT ROLE 'r_verifier' TO 'TestUserFails';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'TestUserWorks';
+------------------------------------------------------------------------------------+
| Grants for TestUserWorks@%                                                         |
+------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `TestUserWorks`@`%`                                          |
| GRANT SELECT ON `bug_test`.* TO `TestUserWorks`@`%`                                |
| GRANT UPDATE (`row_is_verified`) ON `bug_test`.`test_table` TO `TestUserWorks`@`%` |
+------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'TestUserFails';
+-----------------------------------------------+
| Grants for TestUserFails@%                    |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `TestUserFails`@`%`     |
| GRANT `r_verifier`@`%` TO `TestUserFails`@`%` |
+-----------------------------------------------+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'TestUserFails' USING 'r_verifier';
+------------------------------------------------------------------------------------+
| Grants for TestUserFails@%                                                         |
+------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `TestUserFails`@`%`                                          |
| GRANT SELECT ON `bug_test`.* TO `TestUserFails`@`%`                                |
| GRANT UPDATE (`row_is_verified`) ON `bug_test`.`test_table` TO `TestUserFails`@`%` |
| GRANT `r_verifier`@`%` TO `TestUserFails`@`%`                                      |
+------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)


- With TestUserFails

 bin/mysql -uTestUserFails -hhod03.no.oracle.com --port=3333 --protocol=tcp -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
+------------------------------------------------------------------------------------+
| Grants for TestUserFails@%                                                         |
+------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `TestUserFails`@`%`                                          |
| GRANT SELECT ON `bug_test`.* TO `TestUserFails`@`%`                                |
| GRANT UPDATE (`row_is_verified`) ON `bug_test`.`test_table` TO `TestUserFails`@`%` |
| GRANT `r_verifier`@`%` TO `TestUserFails`@`%`                                      |
+------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> SELECT CURRENT_ROLE();
+------------------+
| CURRENT_ROLE()   |
+------------------+
| `r_verifier`@`%` |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT test_id, test_data, row_is_verified FROM bug_test.test_table;
+---------+-----------+-----------------+
| test_id | test_data | row_is_verified |
+---------+-----------+-----------------+
|       1 | valueA    |               0 |
+---------+-----------+-----------------+
1 row in set (0.01 sec)

mysql> UPDATE bug_test.test_table SET row_is_verified = TRUE WHERE test_id=1;
ERROR 1143 (42000): SELECT command denied to user 'TestUserFails'@'hod03.no.oracle.com' for column 'test_id' in table 'test_table'
mysql>
mysql> show errors;
+-------+------+----------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                        |
+-------+------+----------------------------------------------------------------------------------------------------------------+
| Error | 1143 | SELECT command denied to user 'TestUserFails'@'hod03.no.oracle.com' for column 'test_id' in table 'test_table' |
+-------+------+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

- With TestUserWorks


bin/mysql -uTestUserWorks -hhod03.no.oracle.com --port=3333 --protocol=tcp -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
+------------------------------------------------------------------------------------+
| Grants for TestUserWorks@%                                                         |
+------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `TestUserWorks`@`%`                                          |
| GRANT SELECT ON `bug_test`.* TO `TestUserWorks`@`%`                                |
| GRANT UPDATE (`row_is_verified`) ON `bug_test`.`test_table` TO `TestUserWorks`@`%` |
+------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT test_id, test_data, row_is_verified FROM bug_test.test_table;
+---------+-----------+-----------------+
| test_id | test_data | row_is_verified |
+---------+-----------+-----------------+
|       1 | valueA    |               0 |
+---------+-----------+-----------------+
1 row in set (0.00 sec)

mysql> UPDATE bug_test.test_table SET row_is_verified = TRUE WHERE test_id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0