The views expressed on this blog are my own and do not necessarily reflect the views of Oracle

April 17, 2010

Oracle ASM Job Role Separation Option with SYSASM

The SYSASM privilege (introduced in 11.1) is fully separated from the SYSDBA privilege in 11.2. If you choose to use this optional feature, and designate different operating system groups as the OSASM and the OSDBA groups, then the SYSASM administrative privilege is available only to members of the OSASM group. [From Oracle® Grid Infrastructure Installation Guide 11g Release 2].

To set up ASM admin and DBA job role separation, you need at least two OS users – one for the database, typically called oracle, and another one for the Grid Infrastructure, typically called grid.

The database OS user has to be in the software install group (oinstall), OSDBA group (dba) and OSDBA for ASM group (asmdba). [OSDBA group is designated at the installation time, which makes it SS_DBA_GRP in $ORACLE_HOME/rdbms/lib/config.c].

In my case that OS user is called oracle and the OSDBA group is called dba:

$ id oracle
uid=502(oracle) gid=500(oinstall) groups=500(oinstall),502(dba),506(asmdba)

$ grep "define SS_DBA_GRP" $ORACLE_HOME/rdbms/lib/config.c
#define SS_DBA_GRP "dba"

The Grid Infrastructure OS user has to be in the software install group (oinstall), OSASM group (asmadmin) and OSDBA for ASM group (asmdba). [OSASM and OSDBA for ASM groups are designated at the Grid Infrastructure installation time, which makes them SS_ASM_GRP and SS_DBA_GRP in $GRID_HOME/rdbms/lib/config.c].

In my case the OS user is called grid, the OSASM group is called asmadmin and the OSDBA for ASM group is called asmdba:

$ id grid
uid=1100(grid) gid=500(oinstall) groups=500(oinstall),502(dba),506(asmdba),1000(asmadmin),1301(asmoper)

$ egrep "define SS_DBA_GRP|define SS_ASM_GRP" $ORACLE_HOME/rdbms/lib/config.c
#define SS_DBA_GRP "asmdba"
#define SS_ASM_GRP "asmadmin"

To administer ASM the OS user grid should connect to ASM instance as SYSASM, as follows:

$ sqlplus / as sysasm

Given my OS user names and groups, the ownership of ASM disks has to be grid:asmadmin. In my Linux environment, with ASMLIB, my disk ownership is as follows:

$ ls -l /dev/oracleasm/disks/
total 0
brw-rw---- 1 grid asmadmin 8, 5 Mar 1 15:05 DISK1
brw-rw---- 1 grid asmadmin 8, 6 Mar 1 15:05 DISK2
brw-rw---- 1 grid asmadmin 8, 7 Mar 1 15:05 DISK3

The ownership is correct, as I specified the correct user and group at the time ASMLIB was installed. That can be verified as follows (note that this is ASMLIB specific):

$ egrep "^ORACLEASM_UID|^ORACLEASM_GID" /etc/sysconfig/oracleasm

Finally, and this is very important, the correct ownership of the oracle binary – in my database home – has to be oracle:asmadmin:

$ ls -l $ORACLE_HOME/bin/oracle
-r-xr-s--x 1 oracle asmadmin 173515991 Apr 8 12:10 /u01/app/oracle/product/11.2.0/dbhome_2/bin/oracle

With all this in place we have the correct set up for Oracle ASM job role separation feature.