###### Change OSDBA and OSOPER Role after Installation

by Jeff Hunter, Sr. Database Administrator

# Introduction

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 powerfulSYSDBA 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 likeasmadmin, 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 OSOPERgroup.  # 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

 $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 ... ... 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

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.

#### Oracle 11g R2+RAC+ASM+OracleLinux6.4安装详解（图）

2016-06-10 13:39:28

#### Oracle Linux 6.4安装Oracle 11g R2+RAC+ASM图文详解

2017-09-06 10:00:21

#### Oracle 11g R2+RAC+ASM+OracleLinux6.4安装详解（图）

2017-09-06 10:01:41

#### linux 下安装oracle文档

2016-05-29 23:08:10

#### Oracle 11g R2 RAC with Raw Disk on CentOS5.7_x64 by VMware Server 2.0

2012-05-10 09:02:02

#### 使用yum快速部署Oracle安装环境(11g)

2016-01-17 19:36:09

#### Oracle 11g R2(11.2.0.4.0)+udev搭建RAC

2017-09-12 08:33:14

#### linux 上oracle安装

2009-07-01 16:04:00

#### Oracle数据库各版本区别+字符集+OSDBA组+全局数据库名+管理(SYS)口令

2017-11-12 17:10:27

#### linux下安装oracle 10g说明

2012-12-13 17:54:39

## 不良信息举报

Change OSDBA and OSOPER Role after Installation