Thursday, April 18, 2013

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 database. First I logged in as OPS$ORACLE:
SQL> conn /
SQL> show user
Then I created a test table called CLAIMS:
SQL> create table claims (col1 number)
  2  /
Table created.
… and did GRANT ALL ON it to another user:
SQL> grant all on claims to judy
  2  /
Grant succeeded.
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$ values (1)
  2  /
1 row created.
SQL> update ops$ set col1 = 2
  2  /
1 row updated.
SQL> select * from ops$
  2  /
SQL> delete ops$
  2  /
1 row deleted.
She could also modify its structure:
SQL> alter table ops$
  2  add (col2 varchar2(1))
  3  /
Table altered.
SQL> desc ops$
Name                       Null?    Type
-------------------------- -------- ------------------
COL1                                NUMBER
COL2                                VARCHAR2(1)
… but she could not DROP the table:
SQL> drop table ops$
  2  /
drop table ops$
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

No comments: