This discussion is archived
9 Replies Latest reply: May 9, 2013 2:23 PM by Denis Sun RSS

Oracle database files permission group attribute

Denis Sun Newbie
Currently Being Moderated
Hi,

I changed Oracle user primary group from "dba" to "dba2", I expect the files like newly generated archived logs, trace file will have "dba2" group, but they still be dba, am I missing sth?


SQL> ho id
uid=500(oracle) gid=502(dba2) groups=502(dba2),54322(dba)

SQL> alter database backup controlfile to trace;

Database altered.

SQL> ho date
Thu May 9 10:41:07 PDT 2013

SQL> ho ls -lhtr
total 76K
-rw-rw---- 1 oracle dba 59 May 9 09:49 orcl_ckpt_2460.trm
...
-rw-r----- 1 oracle dba 79 May 9 10:41 orcl_dbrm_3496.trm
-rw-r----- 1 oracle dba 1.3K May 9 10:41 orcl_dbrm_3496.trc ===> expect to see oracle dba2

SQL>
SQL> ho touch hello.txt
SQL> ho ls -l hello.txt
-rw-r--r-- 1 oracle dba2 0 May 9 10:45 hello.txt




this test in a VM machine with linux, i already reset the machine, bounced the oracle instance, still the file group is "dba"


oracle@localhost trace]$ uname -a
Linux localhost.localdomain 2.6.18-194.17.1.0.1.el5 #1 SMP Wed Sep 29 15:40:03 EDT 2010 i686 i686 i386 GNU/Linux


-Denis
  • 1. Re: Oracle database files permission group attribute
    EdStevens Guru
    Currently Being Moderated
    Denis Sun wrote:
    Hi,

    I changed Oracle user primary group from "dba" to "dba2",
    WHY??

    I expect the files like newly generated archived logs, trace file will have "dba2" group, but they still be dba, am I missing sth?
    >
    >
    SQL> ho id
    uid=500(oracle) gid=502(dba2) groups=502(dba2),54322(dba)

    SQL> alter database backup controlfile to trace;

    Database altered.

    SQL> ho date
    Thu May 9 10:41:07 PDT 2013

    SQL> ho ls -lhtr
    total 76K
    -rw-rw---- 1 oracle dba 59 May 9 09:49 orcl_ckpt_2460.trm
    ...
    -rw-r----- 1 oracle dba 79 May 9 10:41 orcl_dbrm_3496.trm
    -rw-r----- 1 oracle dba 1.3K May 9 10:41 orcl_dbrm_3496.trc ===> expect to see oracle dba2

    SQL>
    SQL> ho touch hello.txt
    SQL> ho ls -l hello.txt
    -rw-r--r-- 1 oracle dba2 0 May 9 10:45 hello.txt




    this test in a VM machine with linux, i already reset the machine, bounced the oracle instance, still the file group is "dba"


    oracle@localhost trace]$ uname -a
    Linux localhost.localdomain 2.6.18-194.17.1.0.1.el5 #1 SMP Wed Sep 29 15:40:03 EDT 2010 i686 i686 i386 GNU/Linux


    -Denis
  • 2. Re: Oracle database files permission group attribute
    sybrand_b Guru
    Currently Being Moderated
    What are you trying to accomplish? Or are you just hacking?

    -------------
    Sybrand Bakker
    Senior Oracle DBA
  • 3. Re: Oracle database files permission group attribute
    Alvaro Pro
    Currently Being Moderated
    Please tell us why is that relevant.
  • 4. Re: Oracle database files permission group attribute
    Denis Sun Newbie
    Currently Being Moderated
    from various political reasons, we want to have a separate group of DBAs, who cannot view applicationd data, but they should be able to move archived log, delete trace file etc i.e any operations don't need see user data.

    I am thinking assinging oracle (db software owner) and them the same group (i.e. DBA2) to simply files permission managment. Our OSDBA is dba, so I cannot give them dba group as they can do login as sysdba.

    - Denis
  • 5. Re: Oracle database files permission group attribute
    Alvaro Pro
    Currently Being Moderated
    Helo Denis,

    So, you want to restrict the access of OS authentication to the database (sqlplus / as sysdba). Is that correct?

    If so, I would recommend you do the following:

    1. Create a separate user for the DBAs who whould be able to log in to the database through the OS. You can create them with the identified externally clause , which will leave their authentication to the OS, read: http://www.oracle-base.com/articles/misc/os-authentication.php

    2. Grant them sysdba privilege.

    3. Set sqlnet.authentication_services=NONE in your sqlnet.ora file, which will disable automatic OS authentication for users within the OS dba group.

    4. Now the user oracle can't automatically log in as sysdba. You can set an audit or trigger to log any access as SYS after that. (That way you will know if someone messed with your sqlnet.ora to gain entry and then changed it back)

    Changing the dba group of the OS owner of the database is not a best practice and has bad idea written all over it, IMO.
  • 6. Re: Oracle database files permission group attribute
    sybrand_b Guru
    Currently Being Moderated
    As you are using 11g, you can use RMAN to automatically delete archivelogs.
    Also 11g comes with a tool called ADRCI which can be use to automate trace file house keeping (and much more).
    What you are trying to do is not going to work, the only thing which can be done is give access to the trace files to everyone (using the trace_files_public parameter).
    Your DBAs, or you, would better spend some time in learning ADRCI, compared to trying to hack yourself out by experimenting, and implementing something Oracle will not support at all.

    --------
    Sybrand Bakker
    Senior Oracle DBA
  • 7. Re: Oracle database files permission group attribute
    Denis Sun Newbie
    Currently Being Moderated
    @Alvaro - thank you for mentioning point 3 about slqnet.ora, this is sth new to me and I will explore.

    @sybrand - thank you for your inputs, I will denfinitely explore ADRCI capability. Archive log and trace are just two examples.
    What we want to achive is to enable a group of speical DBAs to manage db as efficitently as possible without being able to see user data.
    ( for example these dba will have sysoper privs not sysdba)

    I don't quite understand why change the db software owner - oracle 's primary unix group to sth other than "dba" is a "hacking" behavior.


    I did play a little bit more, find after relink as follows, seems it worked, the newly generated files will have "dba2" group attribute:
    cd $ORACLE_HOME/rdbms/lib/
    make -f oracle.mk ioracle



    [oracle@localhost trace]$ id
    uid=500(oracle) gid=502(dba2) groups=502(dba2),54322(dba)
    [oracle@localhost trace]$ sqlplus "/ as sysdba"

    SQL*Plus: Release 11.2.0.2.0 Production on Thu May 9 12:16:57 2013

    Copyright (c) 1982, 2010, Oracle. All rights reserved.

    Connected to an idle instance.

    SQL> startup
    ORACLE instance started.

    Total System Global Area 456146944 bytes
    Fixed Size 1344840 bytes
    Variable Size 390073016 bytes
    Database Buffers 58720256 bytes
    Redo Buffers 6008832 bytes
    Database mounted.
    Database opened.
    SQL> alter database backup controlfile to trace;

    Database altered.

    SQL> ho ls -lhtr
    total 164K
    ...

    -rw-r----- 1 oracle dba2 80 May 9 12:17 orcl_ora_3492.trm
    -rw-r----- 1 oracle dba2 7.6K May 9 12:17 orcl_ora_3492.trc
    -rw-r----- 1 oracle dba2 5.2K May 9 12:17 alert_orcl.log
  • 8. Re: Oracle database files permission group attribute
    Alvaro Pro
    Currently Being Moderated
    You're very welcome Denis,

    I think what Sybrand pointed out is that these practices are not recommended by Oracle, therefore it may lead to other issues in the feature or some anomalous behavior by the oracle instance.

    We should refrain from experimenting with things that Oracle states as not supported. :)

    Best of luck,
  • 9. Re: Oracle database files permission group attribute
    Denis Sun Newbie
    Currently Being Moderated
    change OSDBA group after oracle installation is docuemented in: SYSDBA and SYSOPER Privileges in Oracle [ID 50507.1]


    Let's say we'll keep oracle:dba for all the filies ( forget about change file group to oracle:dba2)

    now I change OSDBA group to dba2 in config.c per above MOS note. all os DBA user will still have dba group but only oracle user will have secondoary group "dba2" in addition to primary group "dba" ; now only oracle can login as sysdba -- do you think this is not supported?

    What I try to do is essentially no difference from above IMO.

    Thanks,

    Denis

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points