RE: allowing developers to create guaranteed restore point

SQL> _at_conn_oract1db
Connected.
SQL> show user
USER is "SYS"
SQL> create procedure test_restore_point as   2 begin
  3 EXECUTE IMMEDIATE 'create restore point nightly_restore_pt guarantee flashback database';   4 end;
  5 /

Procedure created.

SQL> grant execute on test_restore_point to adm_dba;

Grant succeeded.

SQL> conn adm_dba_at_oract1db
Enter password:
Connected.
SQL> exec sys.test_restore_point;
BEGIN sys.test_restore_point; END;

*
ERROR at line 1:

ORA-01031: insufficient privileges
ORA-06512: at "SYS.TEST_RESTORE_POINT", line 3
ORA-06512: at line 1

SQL> From: Toon Koppelaars [mailto:toon_at_rulegen.com] Sent: Tuesday, December 03, 2013 1:09 PM To: Stephens, Chris
Cc: oracle-l_at_freelists.org
Subject: Re: allowing developers to create guaranteed restore point

A definers rights stored procedure in the SYS schema (that performs the restore point stuff using NDS) + one execute grant to a dev-schema?

On Tue, Dec 3, 2013 at 8:02 PM, Stephens, Chris <Chris.Stephens_at_adm.com<mailto:Chris.Stephens_at_adm.com>> wrote: 11.2.0.4 RAC on Oracle Linux 6

Yesterday I was asked how to handle the following:

The developers are frantically working to debug a number of issues with a nightly batch process that frequently fails and leaves the database in an inconsistent state that require hours of manual backing out of partially loaded data. While they work to fix those issues and to create a process that allows them to gracefully restart batch loads, they are asking for the ability to restore the database to the point immediately prior to the batch load that failed. This is the only application currently running in the database and I think Guaranteed Restore points are the best fit. I would like to create a procedure they can (synchronously) call as a pre-exec step immediately prior to the batch load and once that load completes and is successfully verified, allow another procedure call to drop the restore point.

The problem is that SYSDBA is required to create a restore point. I'm trying to figure out the most secure way to implement this. My initial though is to create a shell script on the server that performs the create/drop of restore point. Implement that shell script as a preprocessor script for an external table. Create a procedure that selects from that table and then grant execute on that procedure to the developer role. In the shell script I would implement a check to ensure it hasn't been executed in the last 30 minutes or something.

That seems like a pretty round about way of doing it but I don't really see any other way without giving the developers the ability to log on as SYSDBA. Am I missing a much easier way to do this?

Thanks for any suggestions.

I'd also like to automate the restore back to the restore point but I plan to wait on that until we've had to do this a few times. I don't want to get called several times / week in the middle of the night for the foreseeable future. Any suggestions on how to handle this would also be greatly appreciated.

Chris

CONFIDENTIALITY NOTICE:
This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply.

--

Toon Koppelaars
RuleGen BV
Toon.Koppelaars_at_RuleGen.com<mailto:Toon.Koppelaars_at_RuleGen.com> www.RuleGen.com<http://www.RuleGen.com>
TheHelsinkiDeclaration.blogspot.com<http://TheHelsinkiDeclaration.blogspot.com>

(co)Author: "Applied Mathematics for Database Professionals" www.RuleGen.com/pls/apex/f?p=14265:13<http://www.RuleGen.com/pls/apex/f?p=14265:13>

CONFIDENTIALITY NOTICE:
This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply.

--

http://www.freelists.org/webpage/oracle-l Received on Tue Dec 03 2013 - 20:15:46 CET