Oracle Security and Backups (EMC SRDF Replication)

Hi gurus,

We use BCV Backups to take backups of our Production Environments. We take inconsistent (hot backups) everyday.

We have an os user (unix sun solaris) that sudo's onto the oracle database user (w/o using a password) and then issues commands like:

alter database start backup;

alter database end backup;

alter  system archive log current;

This makes the sudo os user a very powerful user and a potential threat.

What other methods can we use to perform the same task. Can we have an Oracle user which has limited rights (e.g. only to issue the 3 commands )


Who is Participating?
mrjoltcolaConnect With a Mentor Commented:
Oops, sorry mate, I misread your message the first time. :)

When you say you currently use the "oracle database user" please specify exactly what you mean:
  a) The system "oracle" OS user / owner of the software
  b) A user with dba group
  c) SYS or SYSTEM Oracle level user

If it is (a), then my initial reply was just to switch to using (b).

If not, I'll clarify more after I understand your specifics.
slightwv (䄆 Netminder) Commented:
You can look at creating just a backup user.

Check out the SYSOPER role:

It's still a pretty powerful role but pretty much just in the BACKUP world.

I would also suggest looking into RMAN for backup/recovery.  Then you can create localized cron or database jobs to do the backups.
judejamesAuthor Commented:
Your ideas are good but no good for me.
You can't compare BCV with RMAN. its horse vs tortoise.
BCV completes backup at lightening speed (within a minute), with next to zero headache.
I know i can selectively give the following privileges to the backup user and I am still better than sysoper, I am looking for something more restrictive!


The Lifecycle Approach to Managing Security Policy

Managing application connectivity and security policies can be achieved more effectively when following a framework that automates repeatable processes and ensures that the right activities are performed in the right order.

You could restrict it with several alternative approaches.

1) Another user with dba group, but not the actual oracle owner. This should allow connecting to do the operations you mention, without full ability (such as recover database) or overwriting the actual Oracle owned files (assuming your file mask does not include group write).

2) You can also limit sudo users by command, so you can write a specific script, add it to the sudoers file, and make sure that script is not editable by the user that executes it. That gives very limited access to the user and is enough to execute a backup.

Lastly, backup and recovery, with or without BCV, assumes the user is a very privileged user in the first place. The timefinder / backup script probably should only be "originating" from a root or oracle crontab in the first place. What other users would have access?
Actually, minor correction, if the user has dba group, he would be able to connect as sysdba, which is required for recover database (which should be in this user's privilege set anyway).
judejamesAuthor Commented:
I liked your 2nd point very much . Can you tell me more about the first point?
I don't want the recover database privilege.
I just want enough privileges to issue the following commands:
alter database begin backup;
alter database end backup;
alter  system archive log current;
Sure. Since you have sudo on the system, you need to edit the sudoers file (visudo, etc.)

Add an entry like this.

myuser   ALL=/full/path/to/backupscript

That ensures the user can ONLY run that backupscript (as root). Inside the script you can use "su -c oracle" to call another Oracle script, etc. or you could call timefinder scripts.

Key is not to give edit access, or the user could insert his own commands to subvert it.

Look in your /etc/sudoers file, there is a stock example of command level access control.
judejamesAuthor Commented:
Like i said, i understood your 2nd point. Thanks for explaining it further. But I wanted to know more about the 1st point i.e. another user w/o dba group.

I am already satisfied with the 2nd point and will have no problems implementing it.

Another good suggestion somebody provided me was about definers in oracle in a pl/sql block.

the bcv script part is already covered the bcv server connects to remote databases to change the backup mode and log switching and then initiates the the sync.
judejamesAuthor Commented:
Oh well, never mind, i liked your 2nd option. If nobody replies for a while, you can be awarded full points.
If you'll clarify by answering my question in post http:#31281839 I may be able to add some more detail.

I think option (2) is realistically all you need be concerned about, because the backup and recovery user really is a powerful, system user. Access to backup and recovery privileges and resources implies that user has full control over a database, so as long as you ensure only root or oracle can initiate the scripts (and usually in the case of EMC / Oracle systems I've configured it is, my timefinder scripts run as root, my hot backup or RMAN scripts run as Oracle) then you are fine.
judejamesAuthor Commented:
I understand your scenario. Our slightly different.
we have a separate bcv server and all scripts initiate from the server, we have dozen databases; so we created another script that has a flat text database where we enter the dbhostname, oracle os user name, etc... This makes maintenace very easy. if a production box hostname etc changes, all we have to do is edit the environment info file.
Currently we had a password less login onto the db hosts and then sudo as dba os user and sqlplus / as sysdba and run backup commands. This made the bcv user very powerful.
Now we will either opt for the option 2 you recommended or there is another option; output attached. This will make the bcv user very restrictive and we don't have to sudo on dbhosts.

creating procedures in sys schema using authid definer clause. This involves: 
Creating a procedure in sys schema. 
Creating a backup user 
Granting execute privileges on the sys procedure to backup user. 
                                                               i.      I tested this on TEST instance and it worked fine. It also eradicates the need to sudo onto production boxes. We can install Oracle Client on the bcv server box and add tns entries to the databases. The bcv script will login as backup user onto Oracle from bcv box.



SQL*Plus: Release - Production on Tue Apr 20 10:19:21 2010


Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.



Connected to:

Oracle Database 10g Enterprise Edition Release - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> show user;





  1  create or replace procedure switch_archive_log

  2  authid definer

  3  as

  4  begin

  5  execute immediate 'alter  system archive log current';

  6* end;

SQL> /


Procedure created.


SQL> show err

No errors.

SQL> grant execute on switch_archive_log to backup;



SQL> conn backup/abc;


SQL> exec sys.switch_archive_log;


SQL> !tail -10 /gbev01/app/oracle/admin/TEST/bdump/alert*.log

Thread 1 advanced to log sequence 421 (LGWR switch)

  Current log# 1 seq# 421 mem# 0: /gbev01/app/oracle/oradata/TEST/redo01.log

Tue Apr 20 05:30:47 2010

Thread 1 advanced to log sequence 422 (LGWR switch)

  Current log# 2 seq# 422 mem# 0: /gbev01/app/oracle/oradata/TEST/redo02.log

Tue Apr 20 10:27:36 2010


Tue Apr 20 10:27:36 2010

Thread 1 advanced to log sequence 423 (LGWR switch)

  Current log# 3 seq# 423 mem# 0: /gbev01/app/oracle/oradata/TEST/redo03.log

Open in new window

judejamesAuthor Commented:
good job.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.