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
