Happy Friday! I thought I would jumpstart your creative juices with this little, really simple quiz. While it's trivial, it may not be that obvious to many. See if you can catch it. Time yourself exactly 1 minute to get the answer. Tweet answer to me @arupnanda
Here it goes. Database is 11.2.0.3. 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.
Have fun.
Update at the end of the Day. Here is the answer:
