Skip to content

Administrative Privileges and Job Role Separation in Oracle 12c

    With Job role separation in Oracle, each Oracle Database installation has separate operating system groups to provide authorization for system privileges on that Oracle Database. Multiple databases can, therefore, be installed on the cluster without sharing operating system authorization for system privileges. In addition, each Oracle software installation is owned by a separate installation owner, to provide operating system user authorization for modifications to Oracle Database binaries.

    OS Users and Groups

    The documentation discusses the following groups.

    Generic Name          OS Group    Admin Privilege   Description
    ====================  ==========  ================  =================================
    OraInventory Owner    oinstall                      (Mandatory)
    OSDBA                 dba         SYSDBA            Full admin privileges (Mandatory)
    OSOPER                oper        SYSOPER           Subset of admin privileges
    
    OSDBA (for ASM)       asmdba
    OSASM                 asmadmin    SYSASM            ASM management
    OSOPER (for ASM)      asmoper     
    
    OSBACKUPDBA           backupdba   SYSBACKUP         RMAN management
    OSDGDBA               dgdba       SYSDG             Data Guard management
    OSKMDBA               kmdba       SYSKM             Encryption key management
    
    OSRACDBA              racdba      SYSRAC            Real Application Clusters management

    Remember, if DBAs are the only people in your organisation that are allowed to manage Oracle functionality (databases, ASM, grid infrastructure etc.), these admin privileges are not needed. The only mandatory OS groups are “oinstall” and “dba”.

    Creating OS Groups and Users

    If you have used a preinstall package, like “oracle-rdbms-server-12cR1-preinstall”, to perform the prerequisites on Oracle Linux, the “oinstall”, “dba” and “oper” groups will be created already. The other groups can be created manually as follows.

    groupadd -g 54321 oinstall
    groupadd -g 54322 dba
    groupadd -g 54323 oper
    
    groupadd -g 54327 asmdba
    groupadd -g 54328 asmoper
    groupadd -g 54329 asmadmin
    
    groupadd -g 54324 backupdba
    groupadd -g 54325 dgdba
    groupadd -g 54326 kmdba
    
    # 12.2 only.
    groupadd -g 54330 racdba

    With the groups in place, you can create the “oracle” user with the useradd command.

    useradd -u 54321 -g oinstall -G dba,oper,asmdba,backupdba,dgdba,kmdba oracle

    If the “oracle” user already exists, it can be amended using the usermod command.

    usermod -g oinstall -G dba,oper,asmdba,backupdba,dgdba,kmdba oracle

    The id command shows the current settings for the user.

    id oracle
    uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54327(asmdba)

    Using Administrative Privileges

    When you install the database software the “Privileged Operating System groups” screen gives you the ability to associate these groups withe the relevant privilege.

    Privileged OS Groups

    Remember, this is optional. There is nothing wrong with using something like the following if it suits your organisation.

    Privileged OS Groups

    To allow a database user to connect using these admin privileges, you need to grant the relevant admin privilege to them. You can’t grant sysrac to a database user.

    GRANT sysdba    TO my_dba_user;
    GRANT sysoper   TO my_oper_user;
    GRANT sysasm    TO my_asm_user;
    GRANT sysbackup TO my_backup_user;
    GRANT sysdg     TO my_dg_user;
    GRANT syskm     TO my_km_user;

    The users will then be able to connect using the their admin privileges.

    $ sqlplus my_dba_user as sysdba
    $ sqlplus my_oper_user as sysoper
    $ sqlplus my_asm_user as sysasm
    $ sqlplus my_backup_user as sysbackup
    $ sqlplus my_dg_user as sysdg
    $ sqlplus my_km_user as syskm
    $
    $ # 12.2 only.
    $ sqlplus / as sysrac

    Identify Users with Administrative Privileges (V$PWFILE_USERS)

    The V$PWFILE_USERS view allows you to quickly identify users with with admin privileges.

    SELECT * FROM v$pwfile_users;
    
    USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
    ------------------------------ ----- ----- ----- ----- ----- ----- ----------
    SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
    SYSDG                          FALSE FALSE FALSE FALSE TRUE  FALSE          1
    SYSBACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          1
    SYSKM                          FALSE FALSE FALSE FALSE FALSE TRUE           1
    
    SQL>

    For More Information check Oracle Help Center:

    Oracle Job Role Separation