DBA Tips Archive for Oracle


[an error occurred while processing this directive]

No Title

[an error occurred while processing this directive]

by Michael New, MichaelNew@earthlink.net, Gradation LLC



Over the years, I've inherited a significant number of Oracle installations on Unix/Linux that did not take advantage of creating separate operating system groups for OSDBA, and OSOPER. These are OS groups defined in /etc/group which get mapped to the SYSDBA and SYSOPER Oracle roles respectively and are defined during the Oracle installation. For example, many DBAs accept the default of dba for all three group classes during the Oracle installation as shown below.

# groupadd dba # useradd -m -g dba -c "Oracle Software Owner" oracle # id oracle uid=501(oracle) gid=502(dba) groups=502(dba) # passwd oracle Changing password for user oracle. New password: xxxxxxxxxxx Retype new password: xxxxxxxxxxx passwd: all authentication tokens updated successfully.

During the Oracle installation, the single dba group gets mapped to the Oracle Inventory Group and both Oracle OS roles for SYSDBA and SYSOPER.

Figure 1: Oracle Inventory Directory (Oracle Software Owner)

Figure 2: SYSDBA and SYSOPER Privileges

The above configuration works of course, but does not allow administrative tasks to be divided between different classes of users to the database. The oracle user account is left as the only Unix account in the dba group and the OSOPER group is not being used at all. The dba group is not only the OS group that gets mapped to the powerful SYSDBA Oracle role, but is also defined as the Oracle Inventory Group (the owner of the Oracle software).

# cat /etc/oraInst.loc inventory_loc=/u01/app/oraInventory inst_group=dba

Without the OSDBA and OSOPER groups defined different than the Oracle Inventory Group, this means that operators whose sole responsibility may be to only backup the Oracle database are logging in to the system as the oracle user account which is the Oracle software owner and has access to the excessive privileges offered through the SYSDBA role.

One option is to simply re-install the Oracle software while specifying separate OS groups for the Oracle Inventory Group, OSDBA, and OSOPER. However, if the Oracle database is already in production and you have a limited outage window, another option is to change the current Oracle installation by modifying a simple configuration file and relinking the Oracle binaries which is the subject of this guide.


The Oracle configuration described in this guide will not make use of Automatic Storage Management (ASM) and therefore will not create or assign the ASM related OS groups like asmadmin, asmdba, and asmoper.

Change OSDBA and OSOPER Role

In this section, let's first create the additional OS users and groups that will get mapped to the SYSDBA and SYSOPER Oracle roles. Here I am assuming that the Oracle software owner (oracle) was a member of the dba OS group and that group was configured as the Oracle Inventory Group (which should have been oinstall) and both Oracle OS roles for SYSDBA and SYSOPER during the Oracle install. It is too late to change the Oracle Inventory Group from dba (as far as I know), but we can easily modify the Oracle configuration to allow different groups for the SYSDBA and SYSOPER Oracle roles.

Create new OSDBA and OSOPER OS groups.

# groupadd dbadmin # groupadd dboper

Next, create two new example OS users that will be assigned the SYSDBA and SYSOPER Oracle roles. These two OS users will be able to log in to the database using OS authentication as SYSDBA and SYSOPER.

# useradd -G dbadmin -c "Database Administrator" dbauser # passwd dbauser # cp ~oracle/.bash_profile ~dbauser/.bash_profile # id dbauser uid=502(dbauser) gid=506(dbauser) groups=506(dbauser),505(dbadmin) ----------------------------------------------------------------------------------------------- # useradd -G dboper -c "Database Operations Administrator" operuser # passwd operuser # cp ~oracle/.bash_profile ~operuser/.bash_profile # id operuser uid=503(operuser) gid=503(operuser) groups=503(operuser),504(dboper)

Assign the new OS roles to the current Oracle software owner (oracle). Remember to use the append option (-a) to the usermod command so that the existing user will not be removed from groups not specified.

# usermod -a -G dbadmin,dboper oracle # id oracle uid=501(oracle) gid=502(dba) groups=502(dba),504(dboper),505(dbadmin)

As the Oracle software owner, modify the file $ORACLE_HOME/rdbms/lib/config.c (after taking a backup of the file of course) and specify the new OSDBA and OSOPER group.

# su - oracle $ cp $ORACLE_HOME/rdbms/lib/config.c $ORACLE_HOME/rdbms/lib/config.c.bak $ vi $ORACLE_HOME/rdbms/lib/config.c ------------------------------------------------------------------------------- /* SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access. */ /* Refer to the Installation and User's Guide for further information. */ /* IMPORTANT: this file needs to be in sync with rdbms/src/server/osds/config.c, specifically regarding the number of elements in the ss_dba_grp array. */ #define SS_DBA_GRP "dbadmin" #define SS_OPER_GRP "dboper" #define SS_ASM_GRP "" char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP}; -------------------------------------------------------------------------------

Shut down all Oracle services.

$ sqlplus / as sysdba SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. $ lsnrctl stop

Relink Oracle.

$ cd $ORACLE_HOME/rdbms/lib $ mv config.o config.bak $ make -f ins_rdbms.mk ioracle chmod 755 /u01/app/oracle/product/11.2.0/dbhome_1/bin - Linking Oracle rm -f /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/oracle gcc -o /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/oracle ... <SNIP> ... mv -f /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracleO mv /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/oracle /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle chmod 6751 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

Restart all Oracle services.

$ lsnrctl start $ sqlplus / as sysdba SQL> startup Database closed. Database dismounted. ORACLE instance shut down.

Test the new OS users.

$ su - dbauser Password:******** $ id uid=502(dbauser) gid=506(dbauser) groups=506(dbauser),505(dbadmin) $ sqlplus /nolog SQL> connect / as sysdba Connected. SQL> select user from dual; USER ------------------------------ SYS SQL> connect / as sysoper ERROR: ORA-01031: insufficient privileges Warning: You are no longer connected to ORACLE. SQL> exit

$ su - operuser Password:******** $ id uid=503(operuser) gid=503(operuser) groups=503(operuser),504(dboper) $ sqlplus /nolog SQL> connect / as sysdba ERROR: ORA-01031: insufficient privileges SQL> connect / as sysoper Connected. SQL> select user from dual; USER ------------------------------ PUBLIC SQL> exit

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.

Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Tuesday, 26-Mar-2013 15:34:36 EDT
Page Count: 10556