1

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?

  • 1
    The Oracle installer creates a Linux user named oracle which is used to run the database instance. – a_horse_with_no_name Sep 18 '17 at 6:16
  • This does not explain why different processes run under different users. – Rene Sep 18 '17 at 7:08
  • Of course they run under different users. A process started in a "user session" obviously uses that user. A process started by the Oracle scheduler runs under the user that started the Oracle instance - which is (typically) oracle – a_horse_with_no_name Sep 18 '17 at 7:10
  • Maybe I did not explain the problem clearly enough. It's the Linux system that denies access to some of the Oracle processes trying to read/write on a directory on the Linux file system. I can give the Linux user "oracle" access to the Linux directory but not all Oracle processes seem to run under the Linux "oracle" account. So what other Linux accounts are used for running Oracle processes other than the "oracle" account? – Rene Sep 18 '17 at 7:16
  • Is the type of scheduler job - job_type='EXECUTABLE' ? – Eduard Okhvat Sep 18 '17 at 8:12
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.