Re: Delete UNIX files/tblsp owned by Oracle

In article <3ovkub$a7n_at_news1.databank.com>, Compuserve ID: 74171.1353_at_compuserve.com wrote:

> gapinski_at_lerc.nasa.gov (Gary Gapinski) wrote:
>
> >Place the data files that you allow users to delete in a directory whose
> >owner is the oracle account and whose group includes those users allowed
> >to create and delete files. chmod g+rwx the directory. The files will be
> >owned by the oracle account but will be deletable by members of the group.
> >Note that they are not coerced into dropping the tablespace before they
> >can delete associated data files. Use as many such directories as there
> >are distinct groups.
 

> >BTW, if you're allowing users to drop tablespaces, perhaps they will be
> >astute enough to manipulate the initSID.ora file, but, if not, place
> >that file elsewhere.
>
> Thanks -- I realize there are *big* security issues, but these people
> are contractors developing DSS application. The problem is that I
> need to execise some "change control".... I want them to be able to
> delete the files but not change "system settings" etc. Unfortunately
> I cannot move these files since we have them spread across many
> devices. I was trying to see if some script executing rm $1, owned by
> the Oracle UNIX id and belonging to UNIX dba group, having some
> "sticky-bit" permissions would allow a user in the dba group to
> execute it and be able to delete Oracle owned file. Is this possible?
> Thanks in advance.
> Regards,
> Perry A. McGrew (perrygrw_at_future.dreamscape.com)

Perry,

        Allowing users to arbitrarily delete tablespace datafiles is not necessarily
 a good idea ! Also, in order to use the SETUID bit to prevent wide open access
to the database's datafiles you need to place this on an executable not a shell script.
The most sensible (and secure?) way to solve this problem seems to be to create a Pro*C executable which is owned and SETUID oracle. This executable would have execute limited to users in a specific group, maybe not the default DBA group, but a more restrictive one. If your unix operating system supports ACLs then you could use these to further restrict execute access to the executable. This
executable could have further checks built into it, e.g.: 1) User MUST supply valid DBA authentication information (i.e. a valid DBA username and password) to signon to the database. 2) The executable could then drop the tablespace using SQL. 3) The executable could then delete the dropped tablespace's datafiles, which it could determine from the view DBA_DATA_FILES. 4) The application could then provide whatever audit information is required in an application specific audit table. Received on Thu May 18 1995 - 00:00:00 CEST