Thursday, 9 June 2011

Configuring Oracle Binary Permissions

Oracle Database uses several binary files. The most important is the executable oracle in UNIX and Linux flavors and oracle.exe in Windows.
Note the permission on these files. For instance, in UNIX, you may see something like this.
# cd $ORACLE_HOME/bin
# ls -l oracle
-rwsr-s--x 1 oracle oinstall 69344968 Jun 10 14:05 oracle

The permissions (the same in all relevant Oracle versions) are the default. Let's see what they mean. (If you are familiar with the UNIX permissions, you can skip this subsection and proceed to the subsection "Two-Task Architecture.")

The first position indicates the type of the file. In UNIX, everything—regular files, directories, and devices—is considered a file. This is a true file, hence the first position shows "-." Had it been a directory, this position would have shown "d"; in the case of a character special device, it would have shown "c," and so on.
The second position onward shows the permissions given on the file. The permissions are shown in blocks of three, indicating the status for the Read, Write, and Execute respectively. The first three positions show the permissions for the owner, the next three show the permissions given to the group the file belongs to, and the last three show the permissions provided to all others.

In each permission set, the permissions are shown as either a value or "-." If a "-" appears in the place, it indicates that the permission is not granted on that privilege. For instance, in the above case, note the sixth position, indicating that the Write permission for the Group is set to "-," which indicates that the group "dba" (the group the file belongs to) cannot write to this file. If the permission is granted, then the value is set to the corresponding letter. Again, in the above example, the Read permission for the Group (denoted by the fifth position) shows "r," indicating that the group "dba" can read this file.

Note the last three positions, which indicate the permissions for the Others (not the owner, oracle, or the users belonging to the group dba). From the permissions, you can see that Others can simply execute this file but not read it or write to it.
This explains "r," "w," and "x"—for Read, Write and Execute, respectively—but what about the character "s" in the place where there should have been an "x"? This is an interesting twist to the Execute privileges. The presence of this "s" on the permission above indicates that this program is setuid enabled. When the program runs, regardless of who runs it, it will run as the user who owns it, i.e. oracleThis is one way in which the program can be owned by Oracle software but run by anyone who would connect to it. Thus, the program can operate under the privileges of oracle and not the user who runs it, which makes it possible to open database files and so on.
Two-Task Architecture. Recall how Oracle Database processes operate, by decoupling the user process from the server process. If you don't remember this completely, I highly recommend rereading the first few chapters of the Oracle Database 10g Concepts Manual. Below is a highly distilled version of the interaction, which merely lays the foundation for understanding the permissions; it's not a substitute for reviewing the contents of the manual.
When a user connects to an Oracle database—say, with SQL*Plus—Oracle creates a new process to service this user's program. This new process is called the Oracle server process, which differs from the user's process (sqlplus, sqlplus.exe, TOAD.exe, or whatever it else it may be). This server process interacts with the memory structures such as the System Global Area (SGA) and reads from the datafiles; if the data is not found in the buffer cache in the SGA, and so on. Under no circumstances is the user's process (sqlplus) allowed to directly interact with the Oracle database datafiles. Because there are two processes (the user process and the server process) working in tandem to get the work done, this is sometimes known as two-task architecture. If a user process does something potentially disruptive, such as violating the memory management in the host machine, the Oracle database itself is not affected and the damage is limited to the user's process.
(Note that the above applies to Oracle connections in a dedicated server environment. In a multithreaded server environment, this model is a little different in the sense that a single server process can service more than one user process. It's still two-task, but instead of a 1:1 relation between the server and user processes, it's 1:many.)
The server processes are run under the user who owns the Oracle software. Here's an example. Suppose the user logs into the database using SQL*Plus:
$ sqlplus arup/arup

After this, if you search for this process:

$ ps -aef|grep sqlplus

it shows this:

oracle 6339 6185 0 13:06 pts/0 00:00:00 sqlplus

This, of course, assumes that no other SQL*Plus sessions have been running on the server.

Note the process id (6339). Now if you search that process ID:
$ ps -aef|grep 6339

You will get two processes:

oracle 6339 6185 0 13:06 pts/0 00:00:00 sqlplus
oracle 6340 6339 0 13:06 ? 00:00:00 oracleDBA102

The first one you've seen before (the process for the SQL*Plus session). The second one—process ID 6340—is the server process that Oracle creates for the user. Note the Parent Process ID of the process; it's 6339, which is the process ID of the SQL*Plus session.

The process name is oracleDBA102 (DESCRIPTION=(LOCAL=YES) (ADDRESS=(PROTOCOL=beq))), which tells you several things. First, the presence of the clause LOCAL=YES indicates that this process started due to another process that is running locally, on the same server as the database itself. It also shows PROTOCOL=beq, which means that the connection was made through a bequeath connection.
You can also find the information about the server process from the dynamic views:
select spid
from v$session s, v$process p
where s.sid = (select sid from v$mystat where rownum <2)
and p.addr = s.paddr;

The value returned by the above query is the process ID of the server process. This is the only way to get the process ID if the client process is on a different server, such as someone running SQL*Plus on a laptop connecting to the database.

Now, assume that the user connects through a slightly modified manner. Instead of connecting directly on the server, she uses the TNS string. Assume that your TNS string looks like this (on the server oradba):
DBA102 =
(ADDRESS = (PROTOCOL = TCP)(HOST = oradba)(PORT = 1521))

Now the user connects (on the same server, oradba) as follows:

sqlplus arup/arup@dba102

Check the process ID from the dynamic views:

SQL> select spid
2 from v$session s, v$process p
3 where s.sid = (select sid from v$mystat where rownum <2)
4 and p.addr = s.paddr
5 /




The process ID is 6428. Search for this on the server:

$ ps -aef|grep sqlplus | grep -v grep
oracle 6426 6185 0 13:20 pts/0 00:00:00 sqlplus

Now when you search for the server process on the database server:

$ ps -aef|grep 6426 | grep -v grep
oracle 6426 6185 0 13:20 pts/0 00:00:00 sqlplus

you don't see the server process. There is no child process of the user process 6426. But you know from the dynamic performance views that the server process is 6428, so what is the parent process of that?

$ ps -aef|grep 6428 | grep -v grep
oracle 6428 1 0 13:20 ? 00:00:00 oracleDBA102 (LOCAL=NO)

The parent process is 1. But why isn't it 6426?

To understand the answer, you have to understand how different Oracle server processes are created. In the first case, when the user did not use a TNS connect string, the connection was routed directly to the database without going to the listener first. The database created a user process and then handed the control of the process to the process owner, a process known as bequeathing—hence the term bequeath process, which showed up in the process name.
In the second case, when the user was still on the same server but connected through the listener, the listener created the process for the user—which is known as forking. Similarly, if the user process were running on a different machine (such as a laptop), the connection would have to be made to the listener and the listener would have created the process. The process was created by a remote server, hence the process name contains the clause LOCAL=NO. Even if the SQL*Plus session was running on the same server, the fact that it was a non-bequeath connection made it a non-LOCAL one.
(Note: Depending on the OS, you may not see the parent ID of the server process in the same way that you see the SQL*Plus session in bequeath connections. In some cases, even though the connection is bequeath, the parent ID will show as 1. Therefore, don't rely on the parent ID to determine what type of server process it is; use the process name instead.)
Now that you understand the two-task model, let's see if you get the salient point in this discussion. The database creates and runs the server process, not the user who started the client process such as SQL*Plus. The server process uses the executable oracle or oracle.exe, so only the Oracle software owner, named orasoft (named so as to avoid confusion with the term "oracle," which is the name of the executable), should have privileges to execute them—no one else. So why do you need permissions for the others?
The short answer is, you don't. You can remove the unnecessary permissions by issuing this command:
$ chmod 4700 $ORACLE_HOME/bin/oracle

After executing the command, the permissions will look like this:

-rws------ 1 orasoft oinstall 248754168 Oct 8 07:11 oracle

Now we can move on to strategy—via the SUID bit. In this case the SUID bit is set to ON (indicated by rws permissions for the owner).

Because you don't need anyone other than the Oracle software owner (orasoft, in this case) to run the Oracle executable, you should remove the SUID bit from the executable and make it accessible by only the owner—no one else:

$ chmod 0700 $ORACLE_HOME/bin/oracle

The permissions now look like this:

-rwx------ 1 orasoft oinstall 248754168 Oct 8 07:11 oracle


This is a major change, and it's important that you understand its impact. When a user (not the Oracle software owner) on the server tries a local connection, the executable oracle is run on his behalf as if the user orasoft is running it. Because the server process will open the datafiles (owned by orasoft), either it must run as orasoft or the user must have permissions to open the datafiles.

For example, suppose the UNIX user ananda logs in to the same server the database is on and connects locally:
$ sqlplus arup/arup

The user will immediately get an error:

ORA-12546: TNS:permission denied


Enter user-name:

The reason why is very simple: you removed the SUID permission on the file oracle. When the user executes a local connection, he essentially tries to run the executable oracle, but because the SUID is not set, it's not tried as user orasoft but rather as ananda. As user ananda does not have permission to run this file, it will not be executed—hence the ORA-12546 error.

So, how can ananda connect to the database? There are two options. One is to make all the user processes run on a different server than the database server itself—thus there are no bequeath connections to the database, only non-LOCAL ones. Because the non-LOCAL connections go through the listener process and the listener spawns a server process for them, the server process is owned by orasoft (the Oracle software owner) and not by the user who is running the client process. There is no permission to issue.
Alternatively, if you must run some user processes on the database server itself, you can connect through the listener with
$ sqlplus arup/arup@dba102

which has the same effect as a user connecting from outside the server. Now only the user who owns the Oracle software (in this case, orasoft) can connect to the database through a bequeath connection.

DBAs with individual OS IDs will not be able to shut down or start up the database using the command connect / as sysdba, even if they belong to group dba. They can do so with
$ sqlplus /nolog
SQL> connect sys/Password_of_SYS@dba102 as sysdba

Yes, this approach makes use of the SYS password, but that's a better practice compared to / as sysdba. A much better practice yet is to create Oracle userids for individual DBAs: 

connect ANANDA/Password_of_ANANDA@dba102 as sysdba

A favorite hacker trick is to get into the server using any account and then try to force into the database. (A typical "loose door" is the user "nobody.") Even if the hacker does not get into the database, he can create a denial-of-service attack by buffer overflow of the oracle executable. If the ability to execute the file is removed, then the effectiveness of the attack is severely limited. At the same time, as you saw, you have not removed any functionality from legitimate users. Most users connect to the database using the listener anyway, and they will not be affected much.

Action Plan
See if any other user on the system makes a bequeath connection. You can accomplish this by

• Simply asking
• Searching for processes on the server to see if you find something as obvious as SQL*Plus
• Checking the column MACHINE of V$SESSION:
select program
from v$session
where machine = '<machine_name>';

If something comes up, you can identify the exact program running by turning on auditing (which you will learn about in the subsequent phases of this article series)and capturing any program coming from the server.


IF no programs connect from the server, THEN
  Change the permissions of the oracle executable($O_H/bin/oracle)
chmod 0700 $ORACLE_HOME/bin/oracle
ELSIF some program connects from the server
  Change the connection from UserID/Password to UserID/Password@Connect_String
IF you frequently connect from shell scripts as sqlplus / as sysdba THEN
  Change it to use DBAUser/Password@Connect_String


Anonymous said...

Nice post

Erman Arslan said...


It is an interesting subject.
I just come accross your blog, while I was analyzing the role seperations in RAC environments from OS perspective.
I was thinking about the need for suids and guids set in oracle binaries.
Very good post.

Erman Arslan said...
This comment has been removed by the author.
Blogger said...

Bluehost is definitely one of the best web-hosting provider with plans for any hosting requirements.