关闭

Change OSDBA and OSOPER Role after Installation

标签: dbachange group
160人阅读 评论(0) 收藏 举报
分类:

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.

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:146752次
    • 积分:2830
    • 等级:
    • 排名:第13436名
    • 原创:99篇
    • 转载:244篇
    • 译文:0篇
    • 评论:0条