Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Question Unanswered: Oracle listener.log

    Hello,

    I am quite inexperienced in the ways of oracle, but am trying to learn for a project I'm doing at work. I've made a small script that parses Oracle's listener.log file to extract information that could be used for security purposes (e.g. finding who the most logged in users are, listing when people log in from outside our domain, etc.) But I've hit a fundamental stumbling block in the fact that listener.log appears to be logging the remote OS user name of clients. For example, many logins are from user administrator at a certain IP address, even though we don't have an oracle user named administrator. My question is, does anyone know why that's happening and how to correct it?

    Thanks,
    Carl

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: Oracle listener.log

    Originally posted by brasic
    Hello,

    I am quite inexperienced in the ways of oracle, but am trying to learn for a project I'm doing at work. I've made a small script that parses Oracle's listener.log file to extract information that could be used for security purposes (e.g. finding who the most logged in users are, listing when people log in from outside our domain, etc.) But I've hit a fundamental stumbling block in the fact that listener.log appears to be logging the remote OS user name of clients. For example, many logins are from user administrator at a certain IP address, even though we don't have an oracle user named administrator. My question is, does anyone know why that's happening and how to correct it?

    Thanks,
    Carl
    HUH?
    "OS user name" <> "Oracle username".
    client? as in business client? PC name ("client name)?
    "correct it"? what is broken that needs to be fixed?
    What is happening is that somebody from "certian IP address"
    is waking up the Oracle listener; which is dutifully logging the request.
    If you REALLY want to find out who is behind this,
    simply configure in the listener.ora file to reject all connection requests from the "offending IP#s" and sit back & wait for the phone to ring.
    When "they" call asking why they no longer can get into Oracle,
    you can ask them who they are.

  3. #3
    Join Date
    Sep 2003
    Posts
    3

    Re: Oracle listener.log

    Originally posted by anacedent
    HUH?
    "OS user name" <> "Oracle username".
    client? as in business client? PC name ("client name)?
    "correct it"? what is broken that needs to be fixed?
    What is happening is that somebody from "certian IP address"
    is waking up the Oracle listener; which is dutifully logging the request.
    If you REALLY want to find out who is behind this,
    simply configure in the listener.ora file to reject all connection requests from the "offending IP#s" and sit back & wait for the phone to ring.
    When "they" call asking why they no longer can get into Oracle,
    you can ask them who they are.
    OK, sorry for not being clear. As I said, I'm new to this. By the OS user name, I mean the user name of the person logged onto the remote PC, such as 'administrator', the name you enter in when you log into windows, for example. When I said 'oracle username,' I meant the account that oracle uses to authenticate remote users. The listener log is not logging the oracle username, meaning, say I were to log into the oracle server from my windows PC, with the username 'oracle.' Listener.log would log this connection something like the following:

    25-SEP-2003 15:49:54 * (CONNECT_DATA=(SERVICE_NAME=LIB1)(PROGRAM=)(HOST=S POTLIGHT)
    (USER=Administrator))) * ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.43.77)(PORT=14 40)) * establish * LIB1 * 0

    It logs the username on my pc, Administrator, instead of the oracle username, 'oracle.' This is the thing that is broken and needs fixing. If anyone has any idea why this is happening, I'd appreciate your help.

  4. #4
    Join Date
    Jan 2003
    Location
    Vienna, Austria
    Posts
    102

    Re: Oracle listener.log

    If I'm right you want te LISTENER to log the Oracle-User instead of the OS-User of the Client?
    Don't blame the LISTENER, it's not his duty. He doesn't know anything about the Login at the DB-Level.
    The Listener only recieves a connect-request, tries to find a fitting DB and delegates the connection to the DB-process.
    Afterwards it isn't involved in any Client-Server communication and doesn't even get any information of this connection.
    You have to check the Database to get the information about the Oracle-User.
    ^/\x

  5. #5
    Join Date
    Sep 2003
    Posts
    3

    Re: Oracle listener.log

    Originally posted by berxh3g
    If I'm right you want te LISTENER to log the Oracle-User instead of the OS-User of the Client?
    Don't blame the LISTENER, it's not his duty. He doesn't know anything about the Login at the DB-Level.
    The Listener only recieves a connect-request, tries to find a fitting DB and delegates the connection to the DB-process.
    Afterwards it isn't involved in any Client-Server communication and doesn't even get any information of this connection.
    You have to check the Database to get the information about the Oracle-User.
    Is there anywhere where that information would be logged?

  6. #6
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    Use auditing...FGA
    Hope that helped...
    Visit My Website : http://www.oraflame.com
    _____________________________
    Tarry Singh

    OCP DBA 8i
    Currently: SQL Server DBA 7,2000
    Oracle, PHP Programmer

  7. #7
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    Runnig a query like this would help but note building a simple trigger around user_env will also give you the users login's and that you can happlily log into a table.


    Running this will get you the current logon's....
    Code:
    select a.spid pid,
           b.sid sid,
           b.serial# ser#,
           b.machine box,
           b.username username,
           b.osuser os_user,
           b.program program
    from   v$session b, 
           v$process a
    where  b.paddr = a.addr
    and    type='USER'
    order  by spid;
    Hope that helped...
    Visit My Website : http://www.oraflame.com
    _____________________________
    Tarry Singh

    OCP DBA 8i
    Currently: SQL Server DBA 7,2000
    Oracle, PHP Programmer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •