Re: Certain dynamic sql statements make stored procedures "forget" transaction?
Date: Wed, 15 May 2013 11:51:02 -0700 (PDT)
Message-ID: <202628f4-4340-4e09-b403-e161ae40a54d_at_googlegroups.com>
On Wednesday, May 15, 2013 9:34:38 AM UTC-6, neilsolent wrote:
> > You have that backwards -- look at the correctly functioning example again and you will see the insert is AFTER the execute immediate, not before. YOUR example does it the way you stated and it won't rollback the insert on error.
>
> >
>
>
>
> Actually, I think I do want the audit log insert before the execute ..
>
> If the execute of the grant fails, then there is no commit, and
>
> exception is raised and everything is rolled back (including the
>
> insert into the audit log), isn't it? That's what my tests seem to
>
> show.
>
>
You're not testing properly:
SQL> exec sp_example2;
BEGIN sp_example2; END;
*
ERROR at line 1:
ORA-01919: role 'SSF_ADMIN_T' does not exist ORA-06512: at "GRIBNAUT.SP_EXAMPLE2", line 9 ORA-06512: at line 1
SQL>
SQL> select * from example;
EXAMPLE
2
SQL> The grant failed but the commit BEFORE the grant was executed prevents the rollback. The only commit that failed was the commit after the grant was sttempted to be executed.
>
> If I put the audit log insert after the update I fear I risk the
>
> following (highly unlikely) scenario:
>
>
>
> 1. execute of grant succeeds (and therefore commits - can't be rolled
>
> back)
Certainly it can by using REVOKE.
>
> 2. insert into audit log fails (some real-time issue with Oracle
>
> server - out of disk space let's say).
>
> We then have a change made to the database without any accompanying
>
> audit log record.
No, you code this to revoke the grant if an error interrupts the insert:
SQL> CREATE OR REPLACE PROCEDURE sp_example2
2 AS
3 BEGIN
4 declare 5 sql_v varchar(100); 6 bad_storage exception; 7 pragma exception_init(bad_storage, -20001); 8 BEGIN 9 insert into example(example) values (2); 10 11 sql_v := 'grant ssf_admin to ssf_user1'; 12 execute immediate sql_v; 13 14 RAISE_APPLICATION_ERROR(-20001, 'Abort 2'); 15 16 COMMIT; 17 18 exception when bad_storage then 19 sql_v := 'revoke ssf_admin from ssf_user1'; 20 execute immediate sql_v; 21 delete from example where example=2; 22 23 END;
24 END;
25 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> exec sp_example2
PL/SQL procedure successfully completed.
SQL>
SQL> select * From example;
no rows selected
SQL>
SQL> select granted_role
2 from user_role_privs
3 where username = 'SSF_USER1'
4 and granted_role = 'SSF_ADMIN';
no rows selected
SQL> The example is rigged to emulate a failed insert but I think you get the idea. The grant is gone by virtue of the exception handler.
David Fitzjarrell Received on Wed May 15 2013 - 20:51:02 CEST