0

I tried this -

revoke drop on table emp from user1; 

but it is not working, error is

Error report -
SQL Error: ORA-00990: missing or invalid privilege
00990. 00000 -  "missing or invalid privilege"

i looked up on oracle documentation and it had only 4 or 5 privileges like select, insert, delete, update but not drop.

  • where did you look? – miracle173 May 25 '17 at 12:44
  • 1
    May be you could check in the manual what privileges are required to execute DROP TABLE and revoke those, instead of trying to guess. – mustaccio May 25 '17 at 12:48
  • 1
    You cannot restrict the permissions the Schema owner user has over DDL, this is one of the reasons why not to use a DBO user at runtime. – eckes May 25 '17 at 13:33
  • DROP TABLEand REVOKE – miracle173 May 25 '17 at 14:02
2

There is no drop table privilege.

Users always have full permissions on their own objects, even if they didn't create those objects.

sqlplus kjohnston

SQL> create user testuser;
SQL> grant create session to testuser;
SQL> create table testuser.testtab (x varchar2(1));
SQL> exit;

sqlplus testuser
SQL> drop table testtab;
table dropped

However, there is drop any table privilege for dropping tables in other schemas:

sqlplus kjohnston 
SQL> grant create any table to testuser;
SQL> exit;

sqlplus testuser
SQL> create table kjohnston.testtab (x varchar2(1));
table created    
SQL> drop table kjohnston.testtab;
ORA-01031: insufficient privileges
SQL> exit;

sqlplus kjohnston
SQL> grant drop any table to testuser;
SQL> exit;

sqlplus testuser;
SQL> drop table kjohnston.testtab;
table dropped

For this reason, it is considered best practice to separate accounts that will be logging in to the database (actual users, website logins, etc...) from users that own the objects (in which case, consider not even granting create session privilege to these users so that they can't login to the database).

Not the answer you're looking for? Browse other questions tagged or ask your own question.