What Can You Do With GRANT ALL ON?

I saw somebody using the GRANT ALL ON command recently and decided to see what I could do with it in an Oracle 11.2.0.2.7 database. First I logged in as OPS$ORACLE:

 

SQL> conn /

Connected.

SQL> show user

USER is "OPS$ORACLE"

SQL>

 

Then I created a test table called CLAIMS:

 

SQL> create table claims (col1 number)

  2  /

 

Table created.

 

SQL>

 

… and did GRANT ALL ON it to another user:

 

SQL> grant all on claims to judy

  2  /

 

Grant succeeded.

 

SQL>

 

I logged in as the other user and found that she could run DML on the table:

 

SQL> conn judy/garland

Connected.

SQL> insert into ops$oracle.claims values (1)

  2  /

 

1 row created.

 

SQL> update ops$oracle.claims set col1 = 2

  2  /

 

1 row updated.

 

SQL> select * from ops$oracle.claims

  2  /

 

      COL1

----------

         2

 

SQL> delete ops$oracle.claims

  2  /

 

1 row deleted.

 

SQL>

 

She could also modify its structure:

 

SQL> alter table ops$oracle.claims

  2  add (col2 varchar2(1))

  3  /

 

Table altered.

 

SQL> desc ops$oracle.claims

Name                       Null?    Type

-------------------------- -------- ------------------

COL1                                NUMBER

COL2                                VARCHAR2(1)

 

SQL>

 

… but she could not DROP the table:

 

SQL> drop table ops$oracle.claims

  2  /

drop table ops$oracle.claims

                      *

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL>

 

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.