A Case of Changing or Creating OSDBA and OSOPER Groups

Goal

Created an Unix user (e.g. tier1 ) in the Oracle database server that can perform some database administration tasks but cannot be used to view or modify any application data.


Current typical Unix/Liux Environment:

- OS user: "oracle" is the Oracle software owner and its primary group is "dba" (i.e. Oracle database files have ownership oracle:dba )

- Assuming OSDBA group is "dba" (i.e. every os user that belongs to "dba" group can login as sysdba and view any data)

Suggested changes


- add a Unix group "oper" if not exist
# /usr/sbin/groupadd -g 503 oper

- add a Unix group "dbax" if not exist
# /usr/sbin/groupadd -g 504 dbax

- Change OSDBA group to "dbax" and define OSOPER group in $ORACLE_HOME/rdbms/lib/config.c, edit and ensure the following:
#define SS_DBA_GRP "dbax"
#define SS_OPER_GRP "oper"
shutdown instance and relink:
  cd $ORACLE_HOME/rdbms/lib/
   mv config.o config.o.orig
    make -f ins_rdbms.mk ioracle

- assign "dbax" as secondary unix group to "oracle"
# /usr/sbin/usermod -g dba -G dbax oracle

- create tier1 user with priamry group as "dba" and secondry group as "oper" # /usr/sbin/useradd -g dba -G oper tier1

Test cases


Test case 1 - "oracle" user can login as sysdba as it belongs to OSDBA group:dbax

[oracle@localhost ~]$ id
uid=500(oracle) gid=54322(dba) groups=504(dbax),54322(dba)
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Thu May 9 13:37:51 2013

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

SQL> conn / as sysdba
Connected.
SQL> shutdown 
Database closed.
Database dismounted.
ORACLE instance shut down.
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>

Test case 2 - "tier1" user can login as sysoper but not as sysdba

[tier1@localhost ~]$ id
uid=502(tier1) gid=54322(dba) groups=54322(dba),54324(oper)
[tier1@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Thu May 9 13:50:41 2013

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

SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges


SQL> conn / as sysoper
Connected to an idle instance.
SQL> startup 
ORACLE instance started.
Database mounted.
Database opened.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Test case 3 - "tier1" and "oracle" belong to same primary OS group "dba", so "tier1" can operate on various Oracle files based on group privillege:

[tier1@localhost 2013_05_09]$ id
uid=502(tier1) gid=54322(dba) groups=54322(dba),54324(oper)
[tier1@localhost 2013_05_09]$ ls -lh o1_mf_1_401_8rr62tq0_.arc
-rw-rw---- 1 oracle dba 7.2M May  9 14:42 o1_mf_1_401_8rr62tq0_.arc
[tier1@localhost 2013_05_09]$ mv o1_mf_1_401_8rr62tq0_.arc /tmp
[tier1@localhost 2013_05_09]$ ls -lh o1_mf_1_401_8rr62tq0_.arc
ls: o1_mf_1_401_8rr62tq0_.arc: No such file or directory