Insert command test cases for denies

2 files changed, 151 insertions, 0 deletions

diff --git a/mysql-test/suite/deny/insert.result b/mysql-test/suite/deny/insert.result
new file mode 100644
index 00000000000..943341e2e54
--- /dev/null
+++ b/mysql-test/suite/deny/insert.result
@@ -0,0 +1,63 @@
+create user foo;
+create database deny_db;
+create table deny_db.t1 (a int, b int, secret int);
+insert into deny_db.t1 values (1, 2, 3);
+create table deny_db.t2 (a int, b int, secret int);
+insert into deny_db.t2 values (10, 20, 30);
+grant insert (a) on deny_db.t1 to foo;
+connect  con1,localhost,foo,,;
+insert into deny_db.t1 (a) values (1), (2), (3);
+disconnect con1;
+connection default;
+deny insert on deny_db.* to foo;
+connect  con1,localhost,foo,,;
+insert into deny_db.t1 (a) values (1), (2), (3);
+ERROR 42000: INSERT command denied to user 'foo'@'localhost' for table 't1'
+disconnect con1;
+connection default;
+revoke deny insert on deny_db.* from foo;
+connect  con1,localhost,foo,,;
+insert into deny_db.t1 (a) values (1), (2), (3);
+disconnect con1;
+connect  con1,localhost,foo,,;
+insert into deny_db.t1 (a) values (1), (2), (3);
+insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
+ERROR 42000: INSERT command denied to user 'foo'@'localhost' for column 'b' in table 't1'
+disconnect con1;
+connection default;
+grant insert on deny_db.t1 to foo;
+connect  con1,localhost,foo,,;
+insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
+disconnect con1;
+connection default;
+deny insert on deny_db.* to foo;
+connect  con1,localhost,foo,,;
+insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
+ERROR 42000: INSERT command denied to user 'foo'@'localhost' for table 't1'
+disconnect con1;
+connection default;
+grant insert on deny_db.* to foo;
+connect  con1,localhost,foo,,;
+insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
+ERROR 42000: INSERT command denied to user 'foo'@'localhost' for table 't1'
+disconnect con1;
+connection default;
+grant insert on *.* to foo;
+connect  con1,localhost,foo,,;
+insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
+ERROR 42000: INSERT command denied to user 'foo'@'localhost' for table 't1'
+disconnect con1;
+connection default;
+revoke deny insert on deny_db.* from foo;
+connect  con1,localhost,foo,,;
+insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
+disconnect con1;
+connection default;
+deny insert on *.* to foo;
+connect  con1,localhost,foo,,;
+insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
+ERROR 42000: INSERT command denied to user 'foo'@'localhost' for table 't1'
+disconnect con1;
+connection default;
+drop database deny_db;
+drop user foo;
diff --git a/mysql-test/suite/deny/insert.test b/mysql-test/suite/deny/insert.test
new file mode 100644
index 00000000000..92a30807217
--- /dev/null
+++ b/mysql-test/suite/deny/insert.test
@@ -0,0 +1,88 @@
+--source include/not_embedded.inc
+
+create user foo;
+
+create database deny_db;
+create table deny_db.t1 (a int, b int, secret int);
+insert into deny_db.t1 values (1, 2, 3);
+
+create table deny_db.t2 (a int, b int, secret int);
+insert into deny_db.t2 values (10, 20, 30);
+
+grant insert (a) on deny_db.t1 to foo;
+
+--connect (con1,localhost,foo,,)
+insert into deny_db.t1 (a) values (1), (2), (3);
+disconnect con1;
+
+connection default;
+deny insert on deny_db.* to foo;
+
+--connect (con1,localhost,foo,,)
+--error ER_TABLEACCESS_DENIED_ERROR
+insert into deny_db.t1 (a) values (1), (2), (3);
+disconnect con1;
+
+connection default;
+revoke deny insert on deny_db.* from foo;
+
+--connect (con1,localhost,foo,,)
+insert into deny_db.t1 (a) values (1), (2), (3);
+disconnect con1;
+
+--connect (con1,localhost,foo,,)
+insert into deny_db.t1 (a) values (1), (2), (3);
+
+--error ER_COLUMNACCESS_DENIED_ERROR
+insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
+disconnect con1;
+
+connection default;
+grant insert on deny_db.t1 to foo;
+
+--connect (con1,localhost,foo,,)
+insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
+disconnect con1;
+
+connection default;
+deny insert on deny_db.* to foo;
+
+--connect (con1,localhost,foo,,)
+--error ER_TABLEACCESS_DENIED_ERROR
+insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
+disconnect con1;
+
+connection default;
+grant insert on deny_db.* to foo;
+
+--connect (con1,localhost,foo,,)
+--error ER_TABLEACCESS_DENIED_ERROR
+insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
+disconnect con1;
+
+connection default;
+grant insert on *.* to foo;
+
+--connect (con1,localhost,foo,,)
+--error ER_TABLEACCESS_DENIED_ERROR
+insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
+disconnect con1;
+
+connection default;
+revoke deny insert on deny_db.* from foo;
+
+--connect (con1,localhost,foo,,)
+insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
+disconnect con1;
+
+connection default;
+deny insert on *.* to foo;
+
+--connect (con1,localhost,foo,,)
+--error ER_TABLEACCESS_DENIED_ERROR
+insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
+disconnect con1;
+
+connection default;
+drop database deny_db;
+drop user foo;