How does Oracle know who I am?

Posted on

Question :

Slightly unimportant but it’s piqued my curiousity.

I’ve just logged into an Oracle 10g database for the first time using the Oracle SQL Developer Tools.

I used a generic user login which isn’t my name (nottstest2), from a machine that is called something that isn’t my name (courgette). My name is nowhere in the database and isn’t associated with the login.

Yet, the server logs show a connection from “Jon Hopkins”.

How does it know who I am? Is it being pulled from my Windows login in some way (though I’m not using single sign in)?

Answer :

How do you log onto courgette? Would that username identify you?

You can check that by running

select sys_context('userenv', 'os_user') 
from dual;

The USERENV namespace can retrieve a lot of different information about the user and their environment. Find out more.

The Oracle client (aka driver) knows who you are in your operating system (because that code runs on your computer).

This information is transmitted as part of the login process.

Depending on your application and driver type (OCI/JDBC) it even transmits information like your computer’s name.

If you can, run a SELECT * FROM v$session WHERE sid = userenv('SID') and you’ll see all the information that the driver revealed about your environment.

You might not be able to query v$session though because that needs non-standard privileges.

Do you use an LDAP server to authenticate logins (e.g. Microsoft Active Directory, Novel eDirectory, etc.)? Oracle can interrogate that, based on your OS session login, as you suggest, to get your real name.

Leave a Reply

Your email address will not be published. Required fields are marked *