Seeing commands run by user in SQL


I have a user that keeps complaining about their password changing and I'm almost certain that it's something they're doing. I'd like to investigate the issue but I'm not really sure where to start.

I apologize I'm fairly new to being a DBA so my knowledge is limited. I was wondering if SQL commands he has run can be dug up similar to a log in unix or bash history.


Best Answer

The SQL Command they can use would be Alter User UserName Identified by Password;

This will tell you when an account locks and when their password will expire: select username, account_status, lock_date, expiry_date from dba_users;

This will tell you the last time the password was changed: SELECT name, ctime, ptime FROM sys.user$; (ctime is account creation, ptime is password change).

To audit these events, audit alter user; to tell Oracle to track alter user commands, then

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

That should report out the audit trail results.