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
ORACLEASM_UID=grid
ORACLEASM_GID=asmadmin

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.

13 comments:

  1. Hi,

    Thanks for publishing. Please clarify the following:

    "The database OS user has to be in the software install group (oinstall), OSDBA group (dba) and OSDBA for ASM group"

    Shouldn't that read "OSASM for ASM group"?

    Regards,
    Geof.

    ReplyDelete
    Replies
    1. Hi Geof,
      OSASM group is granted the SYSASM privilege. OSDBA group (for ASM) is granted the SYSDBA privilege on the Oracle ASM instance.
      I guess the group naming is not ideal, but it's correct in the article.
      Cheers,
      Bane

      Delete
  2. Hi ,
    I have question.

    users are as below.
    uid=502(oracle) gid=500(oinstall) groups=500(oinstall),502(dba),506(asmdba)
    uid=1100(grid) gid=500(oinstall) groups=500(oinstall),502(dba),506(asmdba),1000(asmadmin),1301(asmoper)

    /etc/init.d/oracleasm configure has been done as GRID/ASMADMIN.

    ASM disks permission has been set as GRID/ASMADMIN and chmod 660.
    as below.
    brw-rw---- 1 grid asmadmin 8, 5 Mar 1 15:05 DISK1

    But How oracle db can read/write on ASM disks which it did not own or neither has permission.
    this disk dont have R/W permission for ORACLE user.
    brw-rw---- 1 grid asmadmin 8, 5 Mar 1 15:05 DISK1

    Thanks .

    ReplyDelete
    Replies
    1. Hi Jignesh,

      The key is in the correct ownership and permissions for oracle binary in the database home. The ownership should be oracle:asmadmin, and the permissions should be -r-xr-s--x, as in the example above:

      $ 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

      Cheers,
      Bane

      Delete
    2. This comment has been removed by the author.

      Delete
  3. Thanks a lot sir for answer.

    It was very informative post you wrote related to role separation.

    ReplyDelete
  4. What is the difference between connect '/as sysasm' and connect '/as sysdba' to ASM instance

    ReplyDelete
    Replies
    1. As SYSASM you have the full ASM privileges - you can create and drop disk groups, add/drop/resize disks, etc. As SYSDBA you are limited to:
      ALTER DISKGROUP ... ADD DIRECTORY
      ALTER DISKGROUP ... ADD/ALTER/DROP TEMPLATE (non-system templates only)
      ALTER DISKGROUP ... ADD USERGROUP
      SELECT
      SHOW PARAMETER

      Cheers,
      Bane

      Delete
  5. But in 10gR2 with connect '/as sysdba' to ASM instance I am able to do add disks to disk group, create new disk group.
    Is this not supported in 11gR2 now? Do I have to essentially connect as sysasm only to do these operations.
    Also can I do startup and shutdown of ASM instance with connect '/as sysdba' in 11gR2?

    ReplyDelete
    Replies
    1. Yes, this is new in 11g. For admin tasks you need to connect as SYSASM in 11g.
      I don't think you can startup and shutdown an ASM instance as SYSDBA. You set up OSOPER role in 11g and connect as SYSOPER. That would allow startup, shutdown, mount, dismount and alter diskgroup check.

      Delete
  6. Hi Bane,

    But do we need the dba group for grid user as well

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

    Thanks
    Zunain Ahmed

    ReplyDelete