Home » RDBMS Server » Security » audit change password (9.2.0.8 on OEL 4.7)
audit change password [message #494428] Mon, 14 February 2011 11:06 Go to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
I want to get information "who ,when change his own or other user password."
I enable audit "alter user"
But It was useless.... If some one have an idea pls share with me.
Re: audit change password [message #494432 is a reply to message #494428] Mon, 14 February 2011 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Log Miner.

Regards
Michel
Re: audit change password [message #494434 is a reply to message #494432] Mon, 14 February 2011 11:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But I can answer to the question who: the user himself. Smile
When can be found in sys.user$.ptime.

Regards
Michel


Re: audit change password [message #494436 is a reply to message #494432] Mon, 14 February 2011 11:39 Go to previous messageGo to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
Please explain it with example to track change passwords
Re: audit change password [message #494438 is a reply to message #494436] Mon, 14 February 2011 11:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
please Read The Fine Manual

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_logmnr.htm#ARPLS022
Re: audit change password [message #494439 is a reply to message #494438] Mon, 14 February 2011 11:58 Go to previous messageGo to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
I think Log Miner is little hard to setup....
Moreover I have no idea about keeping archived log for how much time.
Currently I have I month archived logs...
What will happen if i want to generate report for last six months of change passwords.....
Re: audit change password [message #494440 is a reply to message #494439] Mon, 14 February 2011 12:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I think Log Miner is little hard to setup....
Setup only needs to occur once.

>Moreover I have no idea about keeping archived log for how much time.
>Currently I have I month archived logs...
>What will happen if i want to generate report for last six months of change passwords.....
Mine logs daily & save results in own/custom table for reporting later
Re: audit change password [message #494441 is a reply to message #494440] Mon, 14 February 2011 12:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
is application 3-tier?
user<=>browser<=>WebServer<=>ApplicationServer<=>DatabaseServer
Re: audit change password [message #494442 is a reply to message #494441] Mon, 14 February 2011 12:12 Go to previous messageGo to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
Yes Application is 3-tier?
user<=>browser<=>WebServer<=>ApplicationServer<=>DatabaseServer
Re: audit change password [message #494443 is a reply to message #494442] Mon, 14 February 2011 12:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Yes Application is 3-tier
Then it is application passwords that are changing; not Oracle's.
Application code itself must implement any required audit capability.
From Oracle's RDBMS perspective, application password is just another application DML.
You could place TRIGGER on application password table.
Re: audit change password [message #494685 is a reply to message #494443] Wed, 16 February 2011 10:14 Go to previous messageGo to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
No users are not Application users.
Even I have application roles and database users are granted these application roles as per requirement.
Re: audit change password [message #494696 is a reply to message #494685] Wed, 16 February 2011 11:33 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Perhaps you could use the password verify function? Include an autonomous transaction that would write out details of the change. I haven't tested this, I would be interested to know if it works.
Re: audit change password [message #494703 is a reply to message #494696] Wed, 16 February 2011 11:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it works. Didn't think about wrapping verify function.

SYS> create table michel.log (msg varchar2(1000));

Table created.

SYS> create or replace procedure michel.p (who in varchar2, what in varchar2)
  2  is
  3    pragma autonomous_transaction;
  4  begin
  5    insert into michel.log values (who||' modifies '||what||'''s password at '||systimestamp);
  6    commit;
  7  end;
  8  /

Procedure created.

SYS> CREATE OR REPLACE FUNCTION verify_function
  2  (username varchar2,
  3    password varchar2,
  4    old_password varchar2)
  5    RETURN boolean IS 
  6  BEGIN 
  7     michel.p (user, username);
  8     RETURN(TRUE);
  9  END;
 10  /

Function created.

SYS> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function;

Profile altered.

SYS> alter user michel identified by michel;

User altered.

SYS> select * from michel.log;
MSG
--------------------------------------------------------------------------------------------------------------------
SYS modifies MICHEL's password at 16/02/2011 18:54:34.390 +01:00

1 row selected.

SYS> connect michel/michel
Connected.
MICHEL> password
Password changed
MICHEL> select * from michel.log;
MSG
--------------------------------------------------------------------------------------------------------------------
SYS modifies MICHEL's password at 16/02/2011 18:54:34.390 +01:00
MICHEL modifies MICHEL's password at 16/02/2011 18:55:04.093 +01:00

2 rows selected.

Regards
Michel
Re: audit change password [message #494705 is a reply to message #494703] Wed, 16 February 2011 12:08 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Interesting. Actually, I think this is horrific: in effect, we have a trigger that can capture everyone's password as it is changed. I suppose it becomes necessary to audit ALTER PROFILE as well as all access to the verify function and anything it calls. I hope the security police know about this.
Re: audit change password [message #494707 is a reply to message #494705] Wed, 16 February 2011 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can create the verify function ONLY in SYS schema, so only those who already have the privilege to change account password or connect without knowing it can capture this password.
But yes, I agree, Oracle should not allow autonmous transaction in verify function; it could be (very) dangerous.

Regards
Michel

[Updated on: Wed, 16 February 2011 12:17]

Report message to a moderator

Re: audit change password [message #495524 is a reply to message #494707] Sun, 20 February 2011 10:48 Go to previous messageGo to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
What you mean by
"autonmous transaction in verify function; it could be (very) dangerous"
And how we can protect databases against such transactions.
Re: audit change password [message #495525 is a reply to message #495524] Sun, 20 February 2011 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ I will not explain how to make it dangerous, just know it could be
2/ Only SYS can do it, and you cannot protect against SYS (at least with the standard version and tool)

Regards
Michel
Re: audit change password [message #495526 is a reply to message #495525] Sun, 20 February 2011 11:03 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Smunir, one has to be responsible when discussing techniques that could be used to hack the database. Michel's example of an autonomous transaction in the password verify function is OK: it does exactly what you asked for in your original post, and no more. But many DBAs will immediately have seen how a variation could be used to hack the database, either through a SQL injection or by compromising password secrecy. Such a variation might well be illegal in some applications in some jurisdictions.

One way of explaining the issue is to talk about triggers. All triggers have dreadful security implications, because they cause events to occur that execute outside the control (or even the knowledge) of the person executing the original command.

Does that help?
Re: audit change password [message #495527 is a reply to message #495526] Sun, 20 February 2011 11:16 Go to previous messageGo to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
Sorry I did not want that...
But Someone have to know about harmful transactions.....(autonomous) Then he can develop best solution for that...
Re: audit change password [message #495528 is a reply to message #495527] Sun, 20 February 2011 11:29 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>But Someone have to know about harmful transactions.....(autonomous) Then he can develop best solution for that...
Solution is simply, an actual code review before every procedure is released into Production
You can not automate Best Practices which are procedural in nature.
Previous Topic: To Find All The 'Failed Log Ins' Through Audit Report
Next Topic: Autotrace for user account
Goto Forum:
  


Current Time: Thu Mar 28 07:15:09 CDT 2024