RE: allowing developers to create guaranteed restore point
- From: "Stephens, Chris" <Chris.Stephens@xxxxxxx>
- To: Toon Koppelaars <toon@xxxxxxxxxxx>
- Date: Tue, 3 Dec 2013 13:15:46 -0600
I tried that but it doesn't appear to work.
SQL> @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@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@xxxxxxxxxxx]
Sent: Tuesday, December 03, 2013 1:09 PM
To: Stephens, Chris
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxx<mailto:Chris.Stephens@xxxxxxx>> 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@xxxxxxxxxxx<mailto:Toon.Koppelaars@xxxxxxxxxxx>
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.
Other related posts:
