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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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!


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?
Defend Against the Q2 Top Security Threats

Were you aware that overall malware worldwide was down a surprising 42% from Q1'18? Every quarter, the WatchGuard Threat Lab releases an Internet Security Report that analyzes the top threat trends impacting companies worldwide. Learn more by viewing our on-demand webinar today!

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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.