Summary

I receive a Client question as below, in this article I try to collect all details relevant to their concerns and answers future such questions:
Context: I noticed that some of our DB Schema User passwords are expiring soon. I know how to change the password, so this in itself does not present a problem and I can take care of the immediate situation as it affects only lower environments.

But the fact that the passwords are expiring poses the question of how long such application related passwords are allowed to be used before a change is required. 

According to the guidelines these passwords are allowed to be kept for a maximum of 2 years.  

The questions

How can we review and if required update the security profile of the DB schema users?

What are your recommendations for the expiration timeframe of DB schema users and general security profiles on the DB?

Answer: first recommendation is to master the usage of security profiles🥸

Oracle Database is widely used in enterprise environments, and securing it is crucial to prevent unauthorized access, data breaches, and compliance issues. One key aspect of database security is managing security profiles for schema users:

These profiles define password policies, resource limits, and other security parameters that control user behavior and access.

What is an Oracle DB Security Profile?

In Oracle, a security profile is an object typically associated with a database user or schema and is implemented through profiles. These profiles allow administrators to:

• Control password policies – enforce strong passwords, password expiration, and reuse restrictions.
• Set resource limits – prevent users from over-consuming system resources.
• Enable auditing and account lockout – track failed logins and lock accounts after multiple failed attempts.
• Define session and system-level limits – limit the number of concurrent sessions, CPU usage, or idle time.

Profiles are assigned to users at the schema level, and every user must have a profile.

Oracle provides a default profile (DEFAULT), which can be modified or new custom profiles can be created.

Oracle Profiles: Key Security Parameters

Relevant to profiles, some common parameters in Oracle database include:

Parameter	Description
FAILED_LOGIN_ATTEMPTS	Maximum number of failed login attempts before the account is locked.
PASSWORD_LIFE_TIME	How long a password is valid before the user must change it.
PASSWORD_REUSE_MAX / PASSWORD_REUSE_TIME	Prevents reuse of old passwords within certain limits.
PASSWORD_VERIFY_FUNCTION	A PL/SQL function to enforce password complexity rules.
SESSIONS_PER_USER	Maximum concurrent sessions allowed for the user.
CPU_PER_SESSION / CPU_PER_CALL	Limits CPU time per session or per call.
CONNECT_TIME / IDLE_TIME	Limits session duration or idle session time.
PRIVATE_SGA / LOGICAL_READS_PER_SESSION	Controls memory usage or read operations per session.

How to Review Security Profiles

To review a schema user’s security profile and settings:

1. Check the profile assigned to a user:
   • SELECT username, profile
FROM dba_users
WHERE username = 'YOUR_SCHEMA_USER';
   • or, in an OCI Autonomous database:
     • 
2. List parameters of a specific profile:
   • SELECT *
FROM dba_profiles
WHERE profile = 'YOUR_PROFILE_NAME';
   • or for the default profile:
   • SELECT *
FROM dba_profiles
WHERE profile = 'DEFAULT';
   • 
3. –

How to Update Security Profiles

1. Modify an Existing Profile
   • To change a parameter in an existing profile:
     • ALTER PROFILE secure_profile
LIMIT PASSWORD_LIFE_TIME 90
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 5;
     • Example:
     • 
       • Be cautious: Changing a profile affects all users assigned to it.
2. Create a New Custom Profile
   • If you want to implement stricter security rules without impacting existing users:
     • CREATE PROFILE high_security_profile
LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 5
PASSWORD_VERIFY_FUNCTION ora_complexity_check
SESSIONS_PER_USER 2
CONNECT_TIME 120
IDLE_TIME 15;
   • Then, assign it to a user:
     • ALTER USER my_schema_user PROFILE high_security_profile;
3. Auditing and Monitoring
   • Use DBA_AUDIT_TRAIL to monitor login attempts.
   • Regularly check DBA_USERS for expired or locked accounts:
     • SELECT username, account_status, lock_date, expiry_date
FROM dba_users;
   • Implement automated scripts to alert when users approach password expiry or resource limits.

Best Practices for Oracle User Security Profiles

1. Apply the principle of least privilege – users should only have the minimum required privileges.
2. Enforce strong password policies – use a “PASSWORD_VERIFY_FUNCTION” to prevent weak passwords (this will be the topic of a future article)
3. Monitor account activity – regularly review failed login attempts and resource usage.
4. Segment high-risk users – use stricter profiles for administrative or privileged accounts.
5. Review and update profiles regularly – compliance standards like GDPR, SOX, or HIPAA require periodic review.
6. which is super practical for DBAs: create a step-by-step checklist specifically for auditing and updating all schema users’ security profiles in Oracle.

Info: all the examples and use cases displayed in this articles were reproduced / tested using OCI’s Autonomous Database

Conclusion

Oracle Database security profiles are a powerful tool to enforce consistent security policies across schema users. Regularly reviewing and updating these profiles ensures that user accounts remain secure, reduces the risk of data breaches, and helps maintain compliance. By combining password policies, resource limits, and auditing, administrators can effectively manage database security at the schema level.