In Oracle Database 19c managing user accounts is very important for database security. Checking how the database is working. As a Database Administrator you need to check locked and expired accounts all the time to make sure everything is okay find users who’re not active and avoid problems with logging in to applications.
This article will show you how to find locked and expired accounts using views in the database and give you useful SQL scripts to check the database every day.
1. Key Concepts
In Oracle Database 19c Oracle Database 19c has:
Locked Accounts
A user account gets locked when:
- The Database Administrator locks it manually
- The user tries to log in many times with the wrong password
- Security rules say the account should be locked
Expired Accounts
- The password has been used for long (because of PROFILE settings)
- The Database Administrator expires it manually
- The user logs in for the time after the account was created (in some cases)
2. Main View Used for Audit
Oracle Database 19c stores information about user accounts in:
DBA_USERS
This view has all the information:
- ACCOUNT_STATUS
- LOCK_DATE
- EXPIRY_DATE
- PROFILE
- LAST_LOGIN
3. Query: List All Locked Accounts
You can use this SQL query to find all accounts:
SELECT username, account_status,lock_date expiry_date FROM dba_users WHERE account_status LIKE '%LOCKED%' ORDER BY username;
You might see these results:
- LOCKED
- LOCKED(TIMED)
- EXPIRED & LOCKED
4. Query: List All Expired Accounts
You can use this SQL query to find all accounts:
SELECT username, account_status expiry_date, lock_date FROM dba_users WHERE account_status LIKE '%EXPIRED%' ORDER BY username;
5. Query: Locked OR Expired Accounts
This is an useful query for checking the database:
SELECT username, account_status lock_date, expiry_date, default_tablespace temporary_tablespace FROM dba_users
WHERE account_status LIKE '%LOCKED%' or account_status LIKE '%EXPIRED%' ORDER BY account_status ;
6. Count Summary
You can use this query to see how many accounts have each status:
SELECT account_status, COUNT(*) AS total_users FROM dba_users GROUP BY account_status ORDER BY total_users DESC;
7. Identify Users About to Expire
To manage password expiration you can use this query:
SELECT username, expiry_date account_status FROM dba_users WHERE expiry_date IS NOT NULL AND expiry_date <= SYSDATE + 7 ORDER BY expiry_date;
8. Unlock a User Account
If an account is locked you can unlock it with this command:
ALTER USER wadhah ACCOUNT UNLOCK;
9. Reset Password and Unlock
You can reset the password and unlock the account with this command:
ALTER USER wadhah IDENTIFIED BY NewPassword ACCOUNT UNLOCK;
10. Important Database Administrator Recommendations
For security you should:
- Check FAILED_LOGIN_ATTEMPTS settings
- Not leave accounts locked for a time without checking
- Use profiles to control password expiration
- Check DBA_USERS every week in production
For monitoring you should:
- Add queries to health-check scripts
- Export results to monitoring dashboards
11. Advanced Audit Tip
To find users who are not locked you can use this query:
SELECT username, last_login FROM dba_users WHERE last_login < SYSDATE- 90 OR last_login IS NULL;
Checking locked and expired accounts in Oracle Database 19c is necessary, for security and making sure the database is working well. By using DBA_USERS Database Administrators can quickly find accounts, automate checks and prevent login issues before they affect production systems.
Google+
Facebook
