Cha1tu
asked on
How to audit password change of users
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?
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.
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;
/
ASKER
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.
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.
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.
Going from memory see if it is the CDATE column in the user$ view.
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
you can see alter user commands in the audit view dba_audit_trail
you won't see the exact command though
combine the audit user results from dba_audit_trail with the PTIME column from sys.user$
ASKER
@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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You could also modify the verification function to trap the logged in user. Then it would be kept in one place.
ASKER
@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
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
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?
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?
ASKER
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?
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?
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
<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
have you altered any user since you turned auditting on?
ASKER
i don;t know that.right now i can see that.
>>> 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.
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.
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