9 Replies Latest reply: May 9, 2013 4:23 PM by Denis Sun RSS

    Oracle database files permission group attribute

    Denis Sun
      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
          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
            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
              Please tell us why is that relevant.
              • 4. Re: Oracle database files permission group attribute
                Denis Sun
                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
                  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
                    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
                      @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
                        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
                          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