Change OSDBA and OSOPER Role after Installation

转载 2015年11月21日 10:47:55

by Jeff Hunter, Sr. Database Administrator

Contents

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 likeasmadminasmdba, 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

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.

MySql之ALTER命令用法详细解读

本文详细解读了MySql语法中Alter命令的用法,这是一个用法比较多的语法,而且功能还是很强大的。...
  • Evankaka
  • Evankaka
  • 2015年06月27日 22:45
  • 6274

HIVE 随谈(hive操作create,alter等)

Hive 的官方文档中对查询语言有了很详细的描述,请参考:http://wiki.apache.org/hadoop/Hive/LanguageManual ,本文的内容大部分翻译自该页面,期间加入了...
  • IQuicksandI
  • IQuicksandI
  • 2013年01月17日 22:43
  • 16215

Oracle安装小记

比照官网文档安装Oracle,虽然也不难,但是毕竟是英文的,而且内容太详细了,常常装完后,就忘了前面是什么步骤了,此文主要是将其中关键点梳理下来,算是帮助自己记忆吧。 基本步骤如下: 以root用户登...
  • ppp00
  • ppp00
  • 2014年05月11日 01:24
  • 238

app installation failed 的问题的解决过程

今天遇到了一个 app installation failed 的问题 ,提示 A valid provisioning profile for this executable was not f...
  • chenli532
  • chenli532
  • 2017年04月14日 13:46
  • 2007

Wise Installation System 常用问题及使用过程简述

Wise Installation System 制作安装包过程中遇到了许多问题,现把它整理出来,以备后用: 1.首先.建了个最普通的安装程序.运行时出现GLJ*.tmp的运行时库报错. 是O...
  • suxinpingtao51
  • suxinpingtao51
  • 2015年09月20日 17:38
  • 1104

2、Websphere学习笔记之二安装Installation Manager篇

2、Websphere学习笔记之一安装Installation Manager篇          继续来看下如何安装Websphere吧。 关键字:Websphere 安装  IBM Install...
  • notbaron
  • notbaron
  • 2015年03月30日 23:06
  • 31547

bootstrap中<li role="presentation">表示真实含义是什么?

最近在学习bootstrap3.3.5,遇到一个问题搞不清楚到底什么含义? 网上到处查国内资料很少,没有表达最贴切含义,在GitHub 查到了相关资料结合“图灵计算机词汇” 在图灵计算机词汇里也可以查...
  • sinat_34719507
  • sinat_34719507
  • 2016年12月10日 01:38
  • 3481

jQuery的change函数,以及on函数的change事件,使用和区别

jQuery的change函数,以及on函数的change事件,使用和区别 1change函数 change是什么意思? 仅仅适用于,、、三种标签。 但是碰到了一个很奇特的情况,单独用c...
  • Ideality_hunter
  • Ideality_hunter
  • 2017年12月01日 13:25
  • 209

MVC中重写RoleProvider角色管理

/* 数据表SQL脚本 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_UsersInRoles_Ro...
  • smartsmile2012
  • smartsmile2012
  • 2016年07月01日 17:56
  • 1063

Domain,project,user,role,token 的概念理解

OpenStack 的 Keystone V3 中引入了 Domain 的概念。引入这个概念后,关于 admin 这个role 的定义就变得复杂了起来。  1. Domain,project,u...
  • u010305706
  • u010305706
  • 2017年01月13日 11:43
  • 853
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Change OSDBA and OSOPER Role after Installation
举报原因:
原因补充:

(最多只允许输入30个字)