Spoofing V$SESSION.OSUSER java code

In my previous post I discussed using OSUSER for auditing user access. To spoof OSUSER I wrote a short program in Java, but I didn’t publish a source code. I was planning to do it for quite some time now, especially that Pete Finnigan expressed interest in seeing it. Martin also hassled me for the past few months, and here is the Java code I used.

To compile it I used java 1.6 and Oracle jdbc driver:

javac -cp ../lib/ojdbc6.jar:. OraAccess.java

OraAccess.java source file

import java.util.Properties;
import java.sql.*;
import java.io.Console;
import java.io.IOException;

public class OraAccess{
   public static Connection getConnection(String db_address, String db_name, String db_username,
   String db_pass, String new_osuser){
   Properties props = new Properties();
   props.put("user", db_username);
   props.put("password", db_pass);
   props.put("v$session.osuser", new_osuser);
   props.put("v$session.program", "UserSpoofTest");
   Connection con = null;
   try {
          con = DriverManager.getConnection("jdbc:oracle:thin:@"+ db_address +":" + db_name +"",props);
       } catch(SQLException ex) {
          System.err.println("SQLException: " + ex.getMessage());
   return con;

public static void main(String[] args) throws SQLException, IOException {
   Console c = System.console();
   if (c == null) {
      System.err.println("No console.");
   String username;
   username = System.getProperty("user.name");

   System.out.print("Your current operating system username is: ");

   String database_address = c.readLine("Enter database server IP and port number (IP:port):  ");
   String database_name = c.readLine("Enter database name:  ");

   String db_username = c.readLine("Enter database username:  ");
   String db_password = c.readLine("Enter password for user \"" + db_username +"\":  ");

   String fake_osuser = c.readLine("Enter fake osuser name:  ");

   Connection conn = getConnection(database_address, database_name, db_username, db_password, fake_osuser);
   String end_connection = c.readLine("Enter something to disconnect.");


In order to audit users activity in a database we need the ability to uniquely identify every individual user. The easiest way of course is to create every user and individual user account and use it for auditing purposes. But what if for whatever reason using individual database user accounts is difficult to implement, or impractical ? If we can’t use unique database accounts maybe a combination of a (shared) database account and a unique operating system user ID could be a way to go ?

The information about operating system account can be found in V$SESSION view column OSUSER, and when auditing is enabled it is written to AUD$ table, and can be viewed via related views. It is important to understand that it’s a client side operating system ID and database relies on the client software to provide it.

If we connect using SQLPLUS and database user “HR” from the OS user “pawel”, and query V$SESSION, we can see that OS user “pawel” connected to the database as user “HR”:

SQL> select username,osuser,program from v$session where username='HR';

---------- ---------------- ----------------------------------------
HR         pawel            sqlplus@arantorga (TNS V1-V3)


It looks OK because the client software provided the database with a correct information about OS user ID. It is possible to write a client software that will send false OS user ID to the database instead of a real one, and it isn’t even difficult. It took me less them 5 minutes and Google to find how to do it, and it doesn’t require a lot of programming skills. I used Oracle JDBC driver ( and Sun JDK6 U16 to write a small piece of code that connects to the database ( with false OSUSER. The application doesn’t really do anything except connecting to the specified database and waiting for user input before terminating the connection:

uid=1000(pawel) gid=1000(pawel) groups=122(kvm),1000(pawel)
pawel:~/workspace/java_sandbox/src$java -cp ../../java_test/lib/ojdbc6.jar:. OraAccess
Your current operating system username is: pawel
Enter database server IP and port number (IP:port):
Enter database name:  sample
Enter database username:  hr
Enter password for user "hr":  hr
Enter fake osuser name:  false_osuser
Enter anything to disconnect: 

Once the connection is established we can check V$SESSION view again:

SQL> select username,osuser,program from v$session where username='HR';

---------- ---------------- ----------------------------------------
HR         pawel            sqlplus@arantorga (TNS V1-V3)
HR         false_osuser     UserSpoofTest


I was able to set not only OSUSER column but also PROGRAM and a few other columns from V$SESSION view can be set in exactly the same way.

I can’t give a definite answer if using OSUSER for auditing is acceptable or not because it probably depends on more then one thing. However spoofing OSUSER isn’t exactly difficult, and if security is important to you, individual database accounts should be your first choice, even if it’s not easy it implement.

Purging Audit Data

When looking to implement something to purge the audit data after 90 days from some Oracle databases I found a nice blog post from Alex Gorbachev. The post provides a procedure for keeping X days of records in SYS.AUD$ and deleting anything older. It also provides an example for submitting a scheduled job via DBMS_SCHEDULER so that the purging of the data can be set up, and hopefully largely forgotten about.

I wanted to take this a little further and create a user specifically for the purpose of purging the SYS.AUD$ and most importantly I wanted the user to be locked down as much as possible. Here is what I came up with…


create user purge_aud identified by {appropriately complex password};
grant create session, create job, create procedure to purge_aud;


grant delete on sys.aud$ to purge_aud;
grant execute on sys.dbms_system to purge_aud;

I feel that I should explain why I didn’t just run all the above as SYS. Well, the logic is that if I have the parameter AUDIT_SYS_OPERATIONS set to TRUE, I believe is is wise to do this, then every command that is run as SYS will be logged in an audit file in AUDIT_FILE_DEST. I don’t want to create more audit data than I need to, so therefore try to limit the commands that I run as SYS. In this case the creation of a user and the granting of system privileges can be handled by SYSTEM – I can also see value in either having a user specifically to create new users or having individual accounts for the DBAs so that they can create users using an account that should only ever be used by a named individual.

Maybe I don’t need to point this out, but performing the object level grants as a user other than SYS would require me giving the same privilege I want to grant to PURGE_AUD to another user with the “WITH GRANT” option, which to me is not appropriate and doesn’t actually help. I would say that as a general rule I want the object owner to be the user granting the privileges on that object.

As I final note on this section, I have not named my user “PURGE_AUD” in reality on the basis that it would feel a little too obvious what the purpose of the user is and would potentially make it a target for the bad guys/girls!

The next step was to create the PURGE_AUDIT_TRAIL procedure in the schema of the PURGE_AUD user. I won’t repoduce Alex’s code here, but note that I used fully qualified object names, i.e. sys.aud$ and sys.dbms_system. Once the procedure was created I then scheduled the job as specified by Alex, but with the addition of “SYS.” before the schedule_name parameter and using 90 as the parameter value for PURGE_AUDIT_TRAIL. There would also be the option of creating synonyms, but I prefer to keep it simple and to me more objects in increased complexity.

At this point we have a user with pretty tight privileges and a scheduled job to run each day and clear out any audit data older than 90 days. I wanted to lock this user right down and remove any privileges that were no longer needed…


alter user purge_aud account lock;
revoke create session, create job, create procedure from purge_aud;

The above highlights something that I have the impression is often overlooked: A user might need a certain set of privileges when being “setup”, but it is highly likely that the user will not need that same set of privileges for its entire life.

The scheduled job is not impeded by revoking any of the above privileges and the job will continue to run each day in the “maintenance window”. In the unlikely event that anything needs to be changed it will simply be a case of granting the required privilege(s) to PURGE_AUD, making the change and then revoke the privilege(s) previously granted specifically for the change… A little extra work I admit, but I’d say a fair price for improved security.

It has been commented that auditors might not like the granting of execute on DBMS_SYSTEM to the PURGE_AUD user, but if that is the case for you then there is the option of not writing to the alert log and cutting this bit out of the PURGE_AUDIT_TRAIL procedure.

Finally, this maybe obvious to most readers, but the text will only be written to the alert log of the instance running the scheduled job in a RAC environment.