Oracle Binairy Permissions

Background
Oracle Database uses several binary files. The most important, of course, 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.

-rwsr-s--x  1 oracle oinstall      69344968 Jun 10 14:05 oracle
 * 1) cd $ORACLE_HOME/bin
 * 2) ls -l oracle

The permissions (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 section and go straight to "Two-Task Architecture.")

The first position indicates the type of the file. In UNIX, everything—regular files, directories, devices—are considered files. 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 a "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 Read, Write, and Execute respectively. The first three positions show that for the owner, the next three for the permissions given to the group that the file belongs to, and finally, the last three specifies the permissions provided to all others.

Position 1 2  3  4  5  6  7  8  9  10 Value - r  w  s  r  - s - - x     Owner Group Other

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 the Write permission to Group is set to "-", which indicates that the group "dba" (the group the file belongs to) cannot write to the file. If the permission is granted, then the value is set to the corresponding letter. Again, in the above example, the permission for Read by the Group (denoted by position 5) 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.

Well, that 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. "oracle". This is one way 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. Remember how the Oracle Database processes operate—by decoupling the user process from the server process. If you don't remember it completely, I highly recommend re-reading the first few chapters of the Oracle Database 10g Concepts Manual. In the interests of time, here is a highly distilled version of the interaction, which merely lays the foundation for understanding the permissions; it's not a substitute for the contents in 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 data files; if the data is not found in the data block buffers in the SGA, and so on. Under no circumstances is the user's process (sqlplus) allowed to directly interact with the Oracle database. As 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 multi-threaded 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:

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 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

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 the user connects through a slightly modified manner. Instead of connecting directly on the server, she uses the TNS string. Assume your TNS string looks like this (on the server oradba).

DBA102 = (DESCRIPTION =  (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oradba)(PORT = 1521)) )  (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DBA102) ) )

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 /

SPID

6428

The process ID is 6428. Searching 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 the same way you see the SQL*Plus session in bequeath connections. In some cases, even through 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 the whole 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 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:

$ 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).

Strategy
Since 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:

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

Implications
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 to connect a local connection, the executable "oracle" is run on his behalf as if the user "orasoft" is running it. This is important; because the server process will open the data files (owned by "orasoft"), it must either run as "orasoft" or the user must have permissions to open the data files.

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.

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 since 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 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 Operating System IDs will not be able to shutdown or startup 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 SYS password; but that's a better practice anyway 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
Preparation See if any other user on the system makes a bequeath connection. You can do this by: Simply asking Searching for processes on the server and see if you see something as obvious as SQL*Plus Checking the column MACHINE of V$SESSION select program from v$session where machine = '';

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

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