DBA role and roles needed to perform backups

[JtheRipper]

Hi there,

I am busy doing a security audit and have quite a few users with the DBA role granted to them. My question is this:
1. Do SYS and SYSTEM need the DBA role to perform any background activities?
2. What role does a user need to perform backups if it is not the DBA role?

Thanks,
J.

 

[SantaMufasa]

J,

I, too, would be very concerned if a crowd of "regular" (read non-DBA) Oracle users had DBA privileges. That opens up an auditing nightmare and probably exposes your organization to a truckload of liability if your database is a fiduciary environment for other organizations.

Now, to your specific questions:
Q1) Do SYS and SYSTEM need the DBA role to perform any background activities?
A1) You can successfuly "REVOKE DBA FROM SYS, SYSTEM;". But if a user logs into your operating system with a userid that is a member of the operating system's known DBA group, then s/he still has full capabilities as a DBA by connecting to your Oracle-interface environment (SQL*Plus or svrmgrl) with the "connect / as sysdba" command. Once they connect in this manner, they not only have all the privileges in the DBA role, but they have "Super-DBA" privileges and can startup and shutdown the database and any other command that requires "specially gifted" super-DBA privileges.

Q2) What role does a user need to perform backups if it is not the DBA role?
A2) Keep in mind that "to perform backups", we typically use operating system "copy/cp/ftp" commands to "backup" database files. For the backup to be usable, either the database must be shutdown (cold backup) or the database must be in "archivelog mode" and the "backer-upper" must have issued the command "ALTER TABLESPACE <ts_name> BEGIN BACKUP;", following which the backer-upper issues o/s copy commands (hot backup). So, the backer-upper must have at least the "ALTER TABLESPACE" privilege; but they certainly do not need to have a full bestowal of the DBA role.

Keep in mind that the DBA role is just a bunch of individual grants of system privileges. If you do not wish to grant full DBA privileges to someone, but they need some form of "higher-than-usual" database system privilege, then just grant them the specific privilege rather than the DBA role.

Did this answer your questions satisfactorily?

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:53 (03Mar04) UTC (aka "GMT" and "Zulu"), 09:53 (03Mar04) Mountain Time)

 

[DBAwhosaysNIE]

if you're doing hot backups you also need alter database to backup the controlfile and alter system to force a logswitch when you finish copying the datafiles so at that point you're pretty close to the DBA role anyway. of course backups can (re: should) be run automatically by cron (or whatever scheduler), not by a person at a terminal (unless you're fixing to apply a patch or something like that).

 

[JtheRipper]

Hi guys,

Sorry for the delay in my reply, but good feedback.

Thanks,
J