I saw somebody using the GRANT ALL ON command recently and decided to see what I could do with it in an Oracle 188.8.131.52.7 database. First I logged in as OPS$ORACLE:
SQL> conn /
SQL> show user
USER is "OPS$ORACLE"
Then I created a test table called CLAIMS:
SQL> create table claims (col1 number)
… and did GRANT ALL ON it to another user:
SQL> grant all on claims to judy
I logged in as the other user and found that she could run DML on the table:
SQL> conn judy/garland
SQL> insert into ops$oracle.claims values (1)
1 row created.
SQL> update ops$oracle.claims set col1 = 2
1 row updated.
SQL> select * from ops$oracle.claims
SQL> delete ops$oracle.claims
1 row deleted.
She could also modify its structure:
SQL> alter table ops$oracle.claims
2 add (col2 varchar2(1))
SQL> desc ops$oracle.claims
Name Null? Type
-------------------------- -------- ------------------
… but she could not DROP the table:
SQL> drop table ops$oracle.claims
drop table ops$oracle.claims
ERROR at line 1:
ORA-01031: insufficient privileges
So I was a little surprised to see that, according to Donald Burleson, the GRANT ALL ON command does allow a user to DROP a table (as usual, click on the image to enlarge it and bring it into focus):
I have written to them to ask for clarification . As soon as I hear back I will update this post accordingly. In the meantime, there is an advertisement for one of their books below. I have one myself and it has provided material for several of the posts on this blog.
If you have an Oracle book on Amazon, which you would like to advertise here for free, please write to me at firstname.lastname@example.org.