This content has been marked as final. Show 8 replies
You can lock or unlock an user using the following
ALTER USER user_name ACCOUNT LOCK; ALTER USER user_name ACCOUNT UNLOCK; /*To get th list of users that didnot login in last 2 months.*/ SELECT userid FROM sys.aud$ GROUP BY userid HAVING MAX(TIMESTAMP#)<ADD_MONTHS(SYSDATE,-2) /* To get the list of users locked before 4 months. */ SELECT * FROM dba_users WHERE account_status = 'LOCKED' AND lock_date < ADD_MONTHS (SYSDATE, -4)
As per profiles settings it might not possible. Why can't your test the list of users last password reset time against the last login details as you are maintaining at non-peak hours once in day and confirm that any difference of 2 months exists - then go head lock the account as per requirements.
- Pavan Kumar N
For 1, key information missing is, oracle does not capture/ maintain the time when a user last logged in. You could create a logon trigger and capture this information. Based in this info, you could run a scheduled job at regular interval and lock a user based on set criteria.
For 2, again a scheduled job based on information in lock_date column of dba_users should do the trick.
Hope that helps.
Why oracle doesn't provide a simple way to lock the users who are not really using the DB. (As availble with all OS including Oracle solaris etc;)
If oracle support a feature just like it does in expiring the passwords, that would be great !
So as per your explaination following steps has to be followed sequentially.
1. Have to identify the users
2. Lock the users
3. Delete the users
Still it's much manual. Even to lock 10 users I have to run lock user statement 10 times.
Can all this be automated ?
You can certainly automate the process. You can audit connections to the database to record when users log in. Less cleanly, you could also use a login trigger. You can use either the DBMS_JOB or the DBMS_SCHEDULER package to regularly compare the audit trail against your rules to determine whether to lock or delete a user.
That being said, at the organizations I've encountered, this sort of thing would be disasterous-- DBAs would be spending gobs of time unlocking accounts and creating new accounts. It's relatively common, for example, to give BAs and developers read-only accounts to various systems so that they can debug problems. It's not uncommon for developers to go a few months without accessing a particular database but when they need access, they generally need it immediately. If the developer had to put in a ticket to get a DBA to unlock an account or, even worse, to ask a manager to authorize a new account for the developer, in order to troubleshoot an urgent production issue, that's going to create a pretty serious backlash.