Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

How can I find the logonname of a ora-01017 logon denied?

Hello,

I'm a database admin of a oracle 11g. I have a application without any support and this application give me a ora-01017 (since the new server).
The logon on the oracle database is in background of this application.
Now I need the information which oracle logonname the application use to logon?
Have you any Idea how I can find the logonname of the logon that failed with ora-01017?

Thank you
Reiner
0
seffer
Asked:
seffer
2 Solutions
 
johnsoneSenior Oracle DBACommented:
You need to look in the listener.log file.  That should have the information you are looking for.

If you want to log this in an ongoing manner, you can create a trigger that will log it to a table.  However, since it seems like this already happened, that isn't going to help you now.
0
 
slightwv (䄆 Netminder) Commented:
Check the listener.log file.

If that doesn't have it check the OS logs.

If neither of those have the info you'll likely need to turn on auditing to track it.
0
 
sefferAuthor Commented:
Thank you, but I don't need the os user. I need the logonname of oracle which the application try to connect and fail with ora-01017

in the listener.log I see only the os user
0
 
johnsoneSenior Oracle DBACommented:
Then I believe your only other option is auditing.  Either Oracle auditing or a custom trigger.

If you decide to go custom, we used to have them.  I pulled this from an old trigger I have.  You want something like this:
create or replace trigger logon_audit
after logon
on database
begin

   if (is_servererror(1017)) then
    --  Insert a record with information into table
    --  sys_context('USERENV', 'SESSION_USER') should give you the attempted username that was trying to be logged into.
  end if;
end;
/

Open in new window

0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
If you have auditing enabled, maybe you take a look at this one:

select *
  from dba_audit_trail a
 where a.returncode = 1017;

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now