Here it goes. Database is 220.127.116.11. Tool is SQL*Plus.
The user ARUP owns a procedure that accepts an input string and executes it. Here is the procedure.
create or replace procedure manipulate_arup_schema ( p_input_string varchar2 ) is begin execute immediate p_input_string; end; /
The user ARUP have granted EXECUTE privileges on this to user SCOTT. The idea is simple: SCOTT can create and drop tables and other objects in ARUP's schema without requiring the dangerous create any table system privilege.
With this, SCOTT tries to create a table in the ARUP schema:
SQL> exec arup.manipulate_arup_schema ('create table abc (col1 number)') PL/SQL procedure successfully completed.The table creation was successul. Now SCOTT tries to create the table in a slightly different manner:
SQL> exec arup.manipulate_arup_schema ('create table abc1 as select * from dual');It fails with an error:
BEGIN bus_schema.manipulate_bus_schema ('create table abc as select * from dual'); END; * ERROR at line 1: ORA-01536: space quota exceeded for tablespace 'USERS' ORA-06512: at "ARUP.MANIPULATE_ARUP_SCHEMA", line 18 ORA-06512: at line 1
Huh? After checking you did confirm that the user indeed doesn't have the quota on tablespace USERS, so the error is genuine; but how did the first table creation command go through successfully?
Tweet me the answer @arupnanda. Aren't on Twitter? Just post the answer here as a comment. I will post the answer right here in the evening. Let's see who posts the first answer. It shouldn't take more than 5 minutes to get the answer.
Update at the end of the Day. Here is the answer: