I mentioned when grant statements take into effect in Sql Server, MySql, and Oracle here.
I found out recently that there are some implementation differences when you grant only delete permission on a table to a user. MySql and Sql Server do this the same way, whereas Oracle is different.
Suppose you have:
1. Table t1: create table t1 (c1 int);
2. User TestLogin. The only permission of this TestLogin is delete on t1.
In all 3 database platforms, TestLogin can find out what columns t1 has by default, using either
In both Sql Server and MySql, the only thing you can do is:
delete from t1;
which essentially wipes out the whole table. You can do the same thing in Oracle.
However, if you do:
delete from t1 where c1 = 1;
you will get a select permission denied in both Sql Server and MySql, but Oracle will allow you to do it.
Personally, I think Oracle’s implementation is wrong on this one, because this gives TestLogin select permissions on this table. For example, suppose the table is a salary table, TestLogin can find out columns using desc, then it can do something like:
delete from SalaryTable where FirstName = ‘John’ and LastName = ‘Doe’ and SalaryAmount >= 50000 and SalaryAmount <= 50050;
If the statement returns “one row affected”, then the person would know John Doe’s salary. This person can then issue
If “no row affected” is returned, the person can continue until s/he can find it out.
I did a quick search on ANSI SQL 92 standard, but didn’t find anything, so I am not sure which way is consistent with ANSI 92. My check was brief, though, so I may have overlooked it.