X-Received: by 10.224.217.195 with SMTP id hn3mr23915809qab.5.1368643862252; Wed, 15 May 2013 11:51:02 -0700 (PDT) X-Received: by 10.49.85.65 with SMTP id f1mr212578qez.36.1368643862225; Wed, 15 May 2013 11:51:02 -0700 (PDT) Path: news.cambrium.nl!textnews.cambrium.nl!feeder2.cambriumusenet.nl!feed.tweaknews.nl!87.79.20.101.MISMATCH!newsreader4.netcologne.de!news.netcologne.de!news.glorb.com!l3no7777766qak.0!news-out.google.com!y6ni43806qax.0!nntp.google.com!m7no7871813qam.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.oracle.server Date: Wed, 15 May 2013 11:51:02 -0700 (PDT) In-Reply-To: <3475641d-d996-4c58-93a1-e40706a7d194@l5g2000vbn.googlegroups.com> Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=69.4.5.254; posting-account=KXUmygkAAABvBFmgDBe4RBLFwhTRAMZC NNTP-Posting-Host: 69.4.5.254 References: <349e6937-b7fa-45ce-bc86-15ecacefd5f3@googlegroups.com> <3475641d-d996-4c58-93a1-e40706a7d194@l5g2000vbn.googlegroups.com> User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <202628f4-4340-4e09-b403-e161ae40a54d@googlegroups.com> Subject: Re: Certain dynamic sql statements make stored procedures "forget" transaction? From: ddf Injection-Date: Wed, 15 May 2013 18:51:02 +0000 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Xref: news.cambrium.nl On Wednesday, May 15, 2013 9:34:38 AM UTC-6, neilsolent wrote: > > You have that backwards -- look at the correctly functioning example ag= ain and you will see the insert is AFTER the execute immediate, not before.= =A0YOUR example does it the way you stated and it won't rollback the inser= t on error. >=20 > > >=20 >=20 >=20 > Actually, I think I do want the audit log insert before the execute .. >=20 > If the execute of the grant fails, then there is no commit, and >=20 > exception is raised and everything is rolled back (including the >=20 > insert into the audit log), isn't it? That's what my tests seem to >=20 > show. >=20 >=20 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 s= ttempted to be executed. >=20 > If I put the audit log insert after the update I fear I risk the >=20 > following (highly unlikely) scenario: >=20 >=20 >=20 > 1. execute of grant succeeds (and therefore commits - can't be rolled >=20 > back) Certainly it can by using REVOKE. >=20 > 2. insert into audit log fails (some real-time issue with Oracle >=20 > server - out of disk space let's say). >=20 > We then have a change made to the database without any accompanying >=20 > 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 :=3D '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 :=3D 'revoke ssf_admin from ssf_user1'; 20 execute immediate sql_v; 21 delete from example where example=3D2; 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 =3D 'SSF_USER1' 4 and granted_role =3D 'SSF_ADMIN'; no rows selected SQL> The example is rigged to emulate a failed insert but I think you get the id= ea. The grant is gone by virtue of the exception handler. David Fitzjarrell