Implementing Security Policies Using Oracle Profiles

database security Hits: 98

Implementing Security Policies Using Oracle Profiles

In Oracle Database user management, Profiles are one of the key tools for enforcing security policies. With a profile, you can define specific restrictions such as password complexity, the number of failed login attempts, password expiration time, and resource usage limits for users.

In this article, we classify users into three main categories, and define different settings for each to ensure both security and system stability:

1. Application Users

These are service-based users that connect to the database via applications or API interfaces.

Goals:

  • Prevent application disruption caused by account lockouts

  • Allow manual password management by the DBA team

Parameter Recommended Value Description
PASSWORD_REUSE_TIME 365 Prevent reuse of recently used passwords
PASSWORD_REUSE_MAX 3 Require multiple password changes before reuse is allowed
PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION or custom Ensure strong password enforcement

For more details on password complexity, refer to this article:

https://vahiddb.com/en/oracle/database-security/customizing-and-enforcing-password-complexity-policies-in-oracle-en

 

Create profile for application users:

CREATE PROFILE app_profile LIMIT
  PASSWORD_VERIFY_FUNCTION       vahiddb_19c_verify_function
  PASSWORD_REUSE_TIME            365
  PASSWORD_REUSE_MAX             5
  FAILED_LOGIN_ATTEMPTS          UNLIMITED
  PASSWORD_LIFE_TIME             UNLIMITED
  PASSWORD_GRACE_TIME            UNLIMITED
  PASSWORD_LOCK_TIME             UNLIMITED
  INACTIVE_ACCOUNT_TIME          UNLIMITED
  IDLE_TIME                      UNLIMITED
  SESSIONS_PER_USER              UNLIMITED;

 

Note:

  • FAILED_LOGIN_ATTEMPTS and PASSWORD_LIFE_TIME are intentionally set to UNLIMITED to avoid application disruptions. Password rotations should be handled manually in coordination with the DBA team.

  • These values are left unset for valid reasons:

    • PASSWORD_LIFE_TIME: Requires coordination with the app team and might cause outages.

    • FAILED_LOGIN_ATTEMPTS: Could lock out the entire application. Instead, auditing is used to track failed attempts.

2. Normal / Human Users

These include analysts, developers, report users, and others who connect to the database interactively.

Goals:

  • Enforce strong, renewable passwords

  • Manage inactive or error-prone users

Parameter Value Description
PASSWORD_LIFE_TIME 90 Password must be changed every 90 days
PASSWORD_GRACE_TIME 5 5-day grace period after password expiration
PASSWORD_REUSE_TIME 365 Prevent reuse of the same password within a year
PASSWORD_REUSE_MAX 10 Require at least 10 changes before password reuse is allowed
PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION or custom Enforce password complexity
FAILED_LOGIN_ATTEMPTS 5 Account locks after 5 failed login attempts
PASSWORD_LOCK_TIME 1 Locked accounts stay locked for 1 day
INACTIVE_ACCOUNT_TIME 60 Accounts inactive for 60 days are disabled
IDLE_TIME 30 Sessions idle for 30 minutes are disconnected
SESSIONS_PER_USER 3 Limit concurrent sessions to avoid excessive connections

Create profile for normal users:

 

CREATE PROFILE user_profile LIMIT
  PASSWORD_VERIFY_FUNCTION       vahiddb_19c_verify_function
  PASSWORD_REUSE_TIME            365
  PASSWORD_REUSE_MAX             10
  PASSWORD_LIFE_TIME             90
  PASSWORD_GRACE_TIME            5
  FAILED_LOGIN_ATTEMPTS          5
  PASSWORD_LOCK_TIME             1
  INACTIVE_ACCOUNT_TIME          60
  IDLE_TIME                      30
  SESSIONS_PER_USER              3;

This profile is suitable for developers, analysts, or anyone connecting directly to the database.

3. Admin / DBA Users

These are highly privileged users (e.g., DBA, SEC_ADMIN, SYSOPER) who require tighter security controls.

Goals:

  • Stronger and more frequent password rotations

  • Protection against targeted attacks

Parameter Value Description
PASSWORD_LIFE_TIME 30 Password must be changed every 30 days
PASSWORD_GRACE_TIME 5 5-day grace period after expiration
PASSWORD_REUSE_TIME 365 Prevent reuse for one year
PASSWORD_REUSE_MAX 15 Require 15 changes before reuse is allowed
PASSWORD_VERIFY_FUNCTION ORA12C_STIG_VERIFY_FUNCTION More stringent complexity check
FAILED_LOGIN_ATTEMPTS 3 Lock account after 3 failed attempts
PASSWORD_LOCK_TIME 1 Account stays locked for 1 day
INACTIVE_ACCOUNT_TIME 30 Accounts inactive for 30 days are disabled
IDLE_TIME UNLIMITED Not limited
SESSIONS_PER_USER UNLIMITED Not limited

Create profile for admin users:

CREATE PROFILE admin_profile LIMIT
  PASSWORD_VERIFY_FUNCTION       ORA12C_STIG_VERIFY_FUNCTION
  PASSWORD_REUSE_TIME            365
  PASSWORD_REUSE_MAX             15
  PASSWORD_LIFE_TIME             60
  PASSWORD_GRACE_TIME            5
  FAILED_LOGIN_ATTEMPTS          3
  PASSWORD_LOCK_TIME             1
  INACTIVE_ACCOUNT_TIME          30
  IDLE_TIME                      UNLIMITED
  SESSIONS_PER_USER              UNLIMITED;

In this case, a STIG-based password verification function is used for stricter enforcement. This profile is suitable for DBA and SYS-level accounts.

Conclusion

Oracle Profiles are a powerful mechanism to implement role-based security policies. By classifying users into three groups—application users, human users, and admin users—we can:

  • Strengthen database security

  • Standardize user management

  • Minimize human errors or potential breaches