Under what user does my process run on Oracle Linux?

Question

We experience problems with directory access on some Oracle databases running on Linux.

We have two kinds of processes that need to read/write files from a directory: 1 started in a user session, 2 started by Oracle scheduled job.

It seems that on some systems these processes run under different Linux users and that permissions on the Linux file system are only granted to one of them.

Is this assumption correct and what users are these?

Answer 1

This is such a common problem when the database was installed with user separation (grid + oracle user) and DBAs tend to overlook this.

When you use RAC or even just Oracle Restart (with or without ASM), you need to install Grid Infrastructure. Grid Infrastructure can be installed as a different user (typically grid).

When you have Grid Infrastructure, the proper way to handle listeners is through Grid Infrastructure. If Grid Infrastructure was installed with grid user, then the listener runs as grid user.

In Oracle architecture, by design, remote connections log in through the listener, and the database server process is forked by the listener. On Linux/UNIX platforms, the oracle binary is owned by oracle user, and it has the setuid bit enabled. grid and oracle users share a common group, and the oracle binary can be executed by the members of this group.

Given the above information, remote connections coming through the listener running as grid user can spawn processes whose UID and EUID is the same as the UID of oracle.

So far this is what usually everyone knows. The difference is however the RUID and the inherited privileges because of it.

On a machine with users as (this is the default configuration taken from an Exadata X5-2 compute node, so this is how Oracle officially deploys its configuration):

$ id oracle
uid=1001(oracle) gid=1001(oinstall) groups=1001(oinstall),1004(asmdba),1002(dba),1003(dboper)
$ id grid
uid=1000(grid) gid=1001(oinstall) groups=1001(oinstall),1004(asmdba),1005(asmoper),1006(asmadmin)

When you log in locally, all user IDs are 1001 (=oracle):

sqlplus user/password

SQL>select spid from v$process where addr = (select paddr from v$session where sid = sys_context('userenv', 'sid'));

SPID
------------------------
85510

SQL>!ps -o uid,euid,ruid -p 85510
  UID  EUID  RUID
 1001  1001  1001

But when you log in through the listener:

sqlplus user/password@orcl

SQL>select spid from v$process where addr = (select paddr from v$session where sid = sys_context('userenv', 'sid'));

SPID
------------------------
90372

SQL>!ps -o uid,euid,ruid -p 90372
  UID  EUID  RUID
 1001  1001  1000

Notice that the RUID is different, it is the UID of grid user.

For example, this can lead to a situation, where file generation from the database with UTL_FILE is successful when executed in a scheduled job, but fails when executed from a remote session.

With the above setup, server processes forked by the listener inherit the privileges of grid user. Scheduled jobs spawned by the database itself inherit the privileges of oracle user. If they do not have the same privileges, they will behave differently.