Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to audit password change of users

Posted on 2011-03-17
18
Medium Priority
?
1,949 Views
Last Modified: 2012-05-11
i want to audit password change of a user.if any user changes their password With privileges or without privileges .i am using 11g client.can any one  tell me how to do this?
0
Comment
Question by:Cha1tu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 35160186
>>>if any user changes their password With privileges or without privileges


what do you mean by this?  how can you change passwords without having privileges to do so?

everyone has implicit  privilege to change their own password
0
 
LVL 35

Expert Comment

by:johnsone
ID: 35160274
I'm not sure what you mean by capturing password changes without privileges.

You could capture users and when they change their passwords with a password verify function.  An example is below.  If you have users where you wouldn't want to log their changes, leave them out of the anonymous block at the end that alters the profile.
--Create a table to hold the log
CREATE TABLE PW_CHANGE_LOG
( USERNAME VARCHAR2(100),
  CHANGE_DATE DATE)
TABLESPACE USERS;

--Create a function to log the change
CREATE OR REPLACE FUNCTION password_change_log (
  username varchar2,
  password varchar2,
  old_password varchar2
) RETURN boolean
IS
BEGIN
  INSERT INTO PW_CHANGE_LOG VALUES (USERNAME, SYSDATE);
  RETURN(TRUE);
END;
/

--Create a profile that will call the function
CREATE PROFILE LOG_PW_CHANGE;
ALTER PROFILE LOG_PW_CHANGE LIMIT PASSWORD_VERIFY_FUNCTION PASSWORD_CHANGE_LOG;

--Assign all users to the profile
BEGIN
  FOR C1 IN (SELECT USERNAME FROM DBA_USERS) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ALTER USER ' || C1.USERNAME || ' PROFILE LOG_PW_CHANGE';
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/

Open in new window

0
 
LVL 2

Author Comment

by:Cha1tu
ID: 35160317
Actually you know what nobody can’t change password of others without privileges I agree with you.

But here I want to audit the password changes.

Like if I created one user I will give temporary password for that user. Once the user is login then it will ask for password reset.

I want to know which user is changing their password and what is the time of that .Generally if I put password expiration for 90 days. It will prompt to change password then the user can change their password.

I want to see how many users in the database changing their passwords. And also if the user has DBA privileges they can change the password of any user right? I want to see that password change also.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35160439
I'm on mobile and can't confirm this but there is a change date.  I don't think it care 'who' changed it.

Going from memory see if it is the CDATE column in the user$ view.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35160956
as a privilege user "audit alter user"


you can see alter user commands in the audit view  dba_audit_trail
you won't see the exact command though
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35160970
combine the audit user results from dba_audit_trail with the PTIME column from sys.user$
0
 
LVL 2

Author Comment

by:Cha1tu
ID: 35161143

@sdstuber:

"combine the audit user results from dba_audit_trail with the PTIME column from sys.user$"


can you clarify that with statements.i am not able to get it
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 35161494
something like this...

SELECT username,
       timestamp,
       obj_name,
       action_name
  FROM dba_audit_trail dat, sys.user$ u
 WHERE dat.action_name = 'ALTER USER'
   AND u.name = dat.obj_name
   AND u.ptime = dat.timestamp;
0
 
LVL 35

Expert Comment

by:johnsone
ID: 35161621
You could also modify the verification function to trap the logged in user.  Then it would be kept in one place.
0
 
LVL 2

Author Comment

by:Cha1tu
ID: 35208964
@sdstuber: when i query these are the results

SQL> SELECT username,
  2         timestamp,
  3         obj_name,
  4         action_name
  5    FROM dba_audit_trail dat, sys.user$ u
  6   WHERE dat.action_name = 'ALTER USER'
  7     AND u.name = dat.obj_name
  8     AND u.ptime = dat.timestamp;

no rows selected


what it means ,can you calrify this ,Thank You
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35209031
did you enable auditting for the alter user command?

did you alter any users after enabling auditting?

check your audit_trail value in v$parameter,  are your audits written to an OS file instead of the DB?
0
 
LVL 2

Author Comment

by:Cha1tu
ID: 35209194
I set the parameters like this

SQL> show parameter audit;

NAME                                        TYPE                VALUE
----------------------------------- ----------- ------------------------------
audit_file_dest                      string         F:\ORACLE_11G\RDBMS\AUDIT
audit_sys_operations          boolean      TRUE
audit_trail                             string          DB, EXTENDED


i am not sure about the alter user command.weather i use this r not.can you tell how to check that?
0
 
LVL 2

Author Comment

by:Cha1tu
ID: 35209249


i try this ,is this correct?
SQL> audit alter user;

Audit succeeded.

SQL> audit alter user WHENEVER SUCCESSFUL;

Audit succeeded.

SQL> audit alter user WHENEVER NOT SUCCESSFUL;

Audit succeeded.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 35209278
look in  DBA_PRIV_AUDIT_OPTS

you should see something like this

<null>  <null>   ALTER USER      BY ACCESS      BY ACCESS

if you didn't then you didn't run "audit alter user"
0
 
LVL 2

Author Comment

by:Cha1tu
ID: 35209414
yes i am able to see

<null>  <null>   ALTER USER      BY ACCESS      BY ACCESS
still it is giving results like this

SQL> SELECT username,
  2         timestamp,
  3         obj_name,
  4         action_name
  5    FROM dba_audit_trail dat, sys.user$ u
  6   WHERE dat.action_name = 'ALTER USER'
  7     AND u.name = dat.obj_name
  8     AND u.ptime = dat.timestamp;

no rows selected


0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35209453
have you altered any user since you turned auditting on?

0
 
LVL 2

Author Comment

by:Cha1tu
ID: 35209646
i don;t know that.right now i can see that.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35209696
>>> i don;t know that.right now i can see that.


auditting can only track actions that occur after you turn auditting on.

if you alter a user now, you should see something show up in dba_audit_trail  
if nobody ever issues an alter user command the auditting is irrelevant because there will be nothing to audit.


if you feel the query I gave you is giving the wrong results
then don't use the join, just query the dba_audit_trail view itself with only the action_name filter and see what is returned.  If nothing, then nobody has tried an alter.


0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question