The programmatic nature of Oracle’s Recovery Manager tool provides great flexibility but also creates a common problem of insecure scripts being left running on production servers allowing anyone to read critical password details with ease.
The power of the Oracle Database lies in it’s ability to seemingly be able to handle anything that gets thrown at it. While Oracle is well able to handle large and complex workloads no one would say that it’s particularly easy to get Oracle to do it, or to do anything else for that matter, due largely to how Oracle interfaces with the world around it. Unlike systems like SQL Server where the first port of call for doing regular tasks will be the GUI tools, Oracle’s cross-platform nature, coupled with its pedigree and size, means that the best interface for regular jobs will usually be the command line, though to really leverage Oracle’s ability to do complex tasks you are better off approaching it as a developer and writing small programs in scripts.
A very common and important task regarding any Oracle system is backing up a database. There are several tools and approaches but the best, in terms of flexibility of options available, is the Oracle Recovery Manager, better known as RMAN. While RMAN can be accessed via the command line and can be used to run backups on an ad hoc basis, most administrators will want to schedule their backup jobs to happen during quieter parts of the day, normally outside of regular business hours when most of the systems users will be elsewhere and not using the database. The catch with doing out of hours backups is that the administrator should be out of the office as well, so it’s necessary to automate the process.
The approach most DBA’s take when running RMAN backups automatically is to create a series of scripts that can be run via a scheduling tool like Task Scheduler on Windows or CRON on Linux. Firstly an RMAN script containing the actual RMAN commands to be executed is prepared. This is followed by an Operating System script (e.g. a batch file on Windows or Shell script on Linux) that connects to RMAN and runs the RMAN script. Finally, the execution of the OS script is scheduled to occur at certain times via the scheduling tool on the OS. This is all pretty standard stuff but there is a problem with this approach, and that’s security.
Running RMAN from a script usually entails passing a username and password to RMAN in order to be able to log on and backup the database. When writing the OS script administrators can simply type in a username and password into the script without thinking and leave them there, in plain text format, for anyone to see. But, what’s the alternative?
Oracle actually has a couple of different solutions for this problem, ranging from the quite simple to the clever and complex solution that Oracle excels at. At the top end of the solution spectrum lies the Secure External Password Store, a wallet based system for securely storing usernames and passwords and associating aliases with those stored credentials. On the simpler end of things is OS Authentication. OS Authentication allows for an Oracle user to be authenticated via the Operating System’s authentication method, so on Windows the Oracle user would actually be verified as an Active Directory (or local system) user.
Configuring a scheduled RMAN backup routine that utilises OS Authentication is surprisingly straightforward and only requires a couple of additional, relatively minor steps.
Firstly, create an OS user. In this example I’m using a local user on the server running Oracle, but the same principles hold for Active Directory users also.
Add the newly created user into the ora_dba group on the server.
Give the user the ability to logon as a batch job, this is needed in order to be able to run batch scripts via the task scheduler. A step by step overview of how to give a user this ability can be found here: http://www.brooksnet.com/faq/117-02.html
In Oracle, create a user identified externally. For Windows it is necessary to include the domain name, or hostname of the server, where the user account is configured. It is also necessary to prefix the user account name with the OS_Authent_Prefix – which is by default set to OPS$ – this setting is part of the verification process for OS users connecting to the database.
create user "ops$server01orabackup" identified externally;
On Windows it is also necessary to ensure that SQLNET.ora is configured to include the following parameter:
SQLNET.AUTHENTICATION_SERVICES = (NTS)
Like so
The next step is to create the RMAN script as normal, for example here is a simple RMAN script:
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG NOT BACKED UP 2 TIMES; DELETE FORCE OBSOLETE; DELETE FORCE ARCHIVELOG ALL BACKED UP 2 TIMES TO DISK; RESTORE DATABASE VALIDATE;
Then create the OS script, but instead of entering the username and password just use “/” (without quotations)
set oracle_sid=orcl D:oracleproduct11.2.0dbhome_1BINrman target / nocatalog @D:ScriptsRMAN.RCV
Finally, schedule the job via the task scheduler as the newly created OS user.
This is a common problem with an easy fix, but for some reason it gets overlooked far too often. As well as elegantly dealing with this issue, the OS Authentication solution to script security goes to show what can be achieved if administrators allow the lines between OS and Database to blur just a little. You’ve got to wonder what can be achieved if Oracle is allowed to completely interface with the OS, and beyond.
For a great overview of OS Authentication and its settings, check out: http://www.oracle-base.com/articles/misc/os-authentication.php
Martin Minnock 