Description:
The user guest@localhost with INSERT and SELECT privileges is able to do both FULL and PARTIAL INSERT into a view:
FULL: INSERT INTO view1 VALUES (10,100);
PARTIAL: INSERT INTO view1(qty) VALUES (20);
However, when I remove SELECT privilege from guest@localhost, it was still able to perform FULL INSERT successfully but PARTIAL INSERT will fail!
Bug #1: I only remove the SELECT privilege and so guest@localhost should still be able to do both FULL and PARTIAL INSERTs.
Bug #2: If, in fact, guest@localhost should NOT be able to INSERT with the removal of SELECT privilege, then FULL INSERT should also fail (which it did not).
How to repeat:
From root@localhost shell:
As you can see below, root@localhost has granted SELECT privilege to guest@localhost. Furthermore, table test1 has been truncated and so there is no data in that table.
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2013-08-27 22:36:32 |
+---------------------+
1 row in set (0.00 sec)
mysql> truncate test1;
Query OK, 0 rows affected (0.31 sec)
mysql> select * from test1;
Empty set (0.00 sec)
mysql> select * from view1;
Empty set (0.00 sec)
mysql>
mysql> show grants for guest@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for guest@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'guest'@'localhost' IDENTIFIED BY PASSWORD '*11DB58B0DD02E290377535868405F11E4CBEFF58' |
| GRANT INSERT, UPDATE, DELETE ON `my_db`.* TO 'guest'@'localhost' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> grant select on my_db.* to guest@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for guest@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for guest@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'guest'@'localhost' IDENTIFIED BY PASSWORD '*11DB58B0DD02E290377535868405F11E4CBEFF58' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `my_db`.* TO 'guest'@'localhost' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
mysql>
mysql>
mysql>
From guest@localhost shell:
The user guest@localhost was able to create tuples (10,100) and (20,NULL).
mysql>
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2013-08-27 22:37:41 |
+---------------------+
1 row in set (0.00 sec)
mysql> select * from view1;
Empty set (0.00 sec)
mysql> insert into view1 values (10,100);
Query OK, 1 row affected (0.28 sec)
mysql> insert into view1(qty) values (20);
Query OK, 1 row affected (0.28 sec)
mysql>
mysql>
mysql> exit
From root@localhost shell:
After verifying that tuples (10,100) and (20,NULL) are successfully inserted, root@localhost will remove SELECT privilege from guest@localhost.
mysql>
mysql>
mysql> select * from view1;
+------+-------+
| qty | price |
+------+-------+
| 10 | 100 |
| 20 | NULL |
+------+-------+
2 rows in set (0.00 sec)
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2013-08-27 22:38:42 |
+---------------------+
1 row in set (0.00 sec)
mysql> revoke select on my_db.* from guest@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show grants for guest@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for guest@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'guest'@'localhost' IDENTIFIED BY PASSWORD '*11DB58B0DD02E290377535868405F11E4CBEFF58' |
| GRANT INSERT, UPDATE, DELETE ON `my_db`.* TO 'guest'@'localhost' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
From guest@localhost shell:
So now guest@localhost cannot execute 'select * from view1' which is fine. However, as you can see below, guest@localhost is able to do a FULL INSERT on tuple (30,300) but failed to do PARTIAL INSERT on qty column value (40).
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2013-08-27 22:39:29 |
+---------------------+
1 row in set (0.00 sec)
mysql>
mysql> select * from view1;
ERROR 1142 (42000): SELECT command denied to user 'guest'@'localhost' for table 'view1'
mysql> insert into view1 values (30,300);
Query OK, 1 row affected (0.00 sec)
mysql> insert into view1(qty) values (40);
ERROR 1356 (HY000): View 'my_db.view1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql>
mysql>
From root@localhost shell:
As you can see, tuple (30,300) was inserted successfully but not tuple (40,NULL).
mysql>
mysql> select * from view1;
+------+-------+
| qty | price |
+------+-------+
| 10 | 100 |
| 20 | NULL |
| 30 | 300 |
+------+-------+
3 rows in set (0.00 sec)
mysql>
Both table test1 and view view1 are simple table/view which allows NULL values in all columns (note that the SQL SECURITY on the view is INVOKER which, in this case, is guest@localhost):
mysql> show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`qty` int(11) DEFAULT NULL,
`price` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create view view1\G
*************************** 1. row ***************************
View: view1
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `view1` AS select `test1`.`qty` AS `qty`,`test1`.`price` AS `price` from `test1`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
mysql>
Suggested fix:
Need to be ACID compliant in terms of Consistency.
Description: The user guest@localhost with INSERT and SELECT privileges is able to do both FULL and PARTIAL INSERT into a view: FULL: INSERT INTO view1 VALUES (10,100); PARTIAL: INSERT INTO view1(qty) VALUES (20); However, when I remove SELECT privilege from guest@localhost, it was still able to perform FULL INSERT successfully but PARTIAL INSERT will fail! Bug #1: I only remove the SELECT privilege and so guest@localhost should still be able to do both FULL and PARTIAL INSERTs. Bug #2: If, in fact, guest@localhost should NOT be able to INSERT with the removal of SELECT privilege, then FULL INSERT should also fail (which it did not). How to repeat: From root@localhost shell: As you can see below, root@localhost has granted SELECT privilege to guest@localhost. Furthermore, table test1 has been truncated and so there is no data in that table. mysql> select current_timestamp; +---------------------+ | current_timestamp | +---------------------+ | 2013-08-27 22:36:32 | +---------------------+ 1 row in set (0.00 sec) mysql> truncate test1; Query OK, 0 rows affected (0.31 sec) mysql> select * from test1; Empty set (0.00 sec) mysql> select * from view1; Empty set (0.00 sec) mysql> mysql> show grants for guest@localhost; +--------------------------------------------------------------------------------------------------------------+ | Grants for guest@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'guest'@'localhost' IDENTIFIED BY PASSWORD '*11DB58B0DD02E290377535868405F11E4CBEFF58' | | GRANT INSERT, UPDATE, DELETE ON `my_db`.* TO 'guest'@'localhost' | +--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> grant select on my_db.* to guest@localhost; Query OK, 0 rows affected (0.00 sec) mysql> show grants for guest@localhost; +--------------------------------------------------------------------------------------------------------------+ | Grants for guest@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'guest'@'localhost' IDENTIFIED BY PASSWORD '*11DB58B0DD02E290377535868405F11E4CBEFF58' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `my_db`.* TO 'guest'@'localhost' | +--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.01 sec) mysql> mysql> mysql> From guest@localhost shell: The user guest@localhost was able to create tuples (10,100) and (20,NULL). mysql> mysql> select current_timestamp; +---------------------+ | current_timestamp | +---------------------+ | 2013-08-27 22:37:41 | +---------------------+ 1 row in set (0.00 sec) mysql> select * from view1; Empty set (0.00 sec) mysql> insert into view1 values (10,100); Query OK, 1 row affected (0.28 sec) mysql> insert into view1(qty) values (20); Query OK, 1 row affected (0.28 sec) mysql> mysql> mysql> exit From root@localhost shell: After verifying that tuples (10,100) and (20,NULL) are successfully inserted, root@localhost will remove SELECT privilege from guest@localhost. mysql> mysql> mysql> select * from view1; +------+-------+ | qty | price | +------+-------+ | 10 | 100 | | 20 | NULL | +------+-------+ 2 rows in set (0.00 sec) mysql> select current_timestamp; +---------------------+ | current_timestamp | +---------------------+ | 2013-08-27 22:38:42 | +---------------------+ 1 row in set (0.00 sec) mysql> revoke select on my_db.* from guest@localhost; Query OK, 0 rows affected (0.00 sec) mysql> mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> mysql> show grants for guest@localhost; +--------------------------------------------------------------------------------------------------------------+ | Grants for guest@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'guest'@'localhost' IDENTIFIED BY PASSWORD '*11DB58B0DD02E290377535868405F11E4CBEFF58' | | GRANT INSERT, UPDATE, DELETE ON `my_db`.* TO 'guest'@'localhost' | +--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> From guest@localhost shell: So now guest@localhost cannot execute 'select * from view1' which is fine. However, as you can see below, guest@localhost is able to do a FULL INSERT on tuple (30,300) but failed to do PARTIAL INSERT on qty column value (40). mysql> select current_timestamp; +---------------------+ | current_timestamp | +---------------------+ | 2013-08-27 22:39:29 | +---------------------+ 1 row in set (0.00 sec) mysql> mysql> select * from view1; ERROR 1142 (42000): SELECT command denied to user 'guest'@'localhost' for table 'view1' mysql> insert into view1 values (30,300); Query OK, 1 row affected (0.00 sec) mysql> insert into view1(qty) values (40); ERROR 1356 (HY000): View 'my_db.view1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysql> mysql> From root@localhost shell: As you can see, tuple (30,300) was inserted successfully but not tuple (40,NULL). mysql> mysql> select * from view1; +------+-------+ | qty | price | +------+-------+ | 10 | 100 | | 20 | NULL | | 30 | 300 | +------+-------+ 3 rows in set (0.00 sec) mysql> Both table test1 and view view1 are simple table/view which allows NULL values in all columns (note that the SQL SECURITY on the view is INVOKER which, in this case, is guest@localhost): mysql> show create table test1\G *************************** 1. row *************************** Table: test1 Create Table: CREATE TABLE `test1` ( `qty` int(11) DEFAULT NULL, `price` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> show create view view1\G *************************** 1. row *************************** View: view1 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `view1` AS select `test1`.`qty` AS `qty`,`test1`.`price` AS `price` from `test1` character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set (0.00 sec) mysql> Suggested fix: Need to be ACID compliant in terms of Consistency.