Fundamental Security Part One – User Profiles

June 2023 – Oracle 19C

Stage 1 with our Fundamental Oracle Security… are you enforcing password rules with your database users and administrators? Do you have a standard to which you are adhering? Are the users forced to change their passwords regularly? Can they change it back again, maybe to something simple? What happens to the account when someone leaves the organisation?

One of the fundamental issues with Oracle is that it provides a lot of tools to enforce user compliance to standards, and a lot of it is turned off or has default settings which don’t comply with best practice. A good place to start is to use the Centre for Internet Security standards – CIS – to which many organisations are audited. It’s not perfect, but it’s a good place to approach to ensure a reasonable level of compliance and security.

Oracle provides USER PROFILES to help to manage user accounts. There is a DEFAULT profile, to which all users are allocated in the absence of other controls. However, the defaults are fairly loose and do not provide a great deal of protection when measured against the CIS standards.

---

DBA_PROFILES

Lets have a look at the defaults.

SELECT
    profile
  , resource_name
  , limit
FROM
    dba_profiles
WHERE
    resource_type = 'PASSWORD' and PROFILE = 'DEFAULT'
ORDER BY
    profile
  , resource_type
  , resource_name;


PROFILE  RESOURCE_NAME             LIMIT     CIS Recommendation
-------- ------------------------- --------- -----------------------------------
DEFAULT  FAILED_LOGIN_ATTEMPTS     10        FAIL <=   5
DEFAULT  INACTIVE_ACCOUNT_TIME     UNLIMITED FAIL <= 120 days(then lock)
DEFAULT  PASSWORD_GRACE_TIME       7         FAIL <=   5 days
DEFAULT  PASSWORD_LIFE_TIME        180       FAIL <=  90 days(enforced chg)
DEFAULT  PASSWORD_LOCK_TIME        1         PASS >=   1 day (duration locked)
DEFAULT  PASSWORD_REUSE_MAX        UNLIMITED FAIL >=  20     (pwd hist num)
DEFAULT  PASSWORD_REUSE_TIME       UNLIMITED FAIL >= 365 days(pwd hist days)
DEFAULT  PASSWORD_ROLLOVER_TIME    -1
DEFAULT  PASSWORD_VERIFY_FUNCTION  NULL      FAIL >= Password Complexity

As we can see, the DEFAULT profile assigned to users is far from meeting the CIS benchmark. What do these options mean?

Resource Name	Description
FAILED_LOGIN_ATTEMPTS	How many times can I get my password wrong before the account locks?
PASSWORD_LOCK_TIME	How long should the password be locked following FAILED_LOGIN_ATTEMPTS ?
INACTIVE_ACCOUNT_TIME	How long should I wait before locking an unused account automatically?
PASSWORD_GRACE_TIME	When a password expires, for how long should I give the user a warning message and still let them into the database?
PASSWORD_LIFE_TIME	How long before the password needs to be changes?
PASSWORD_REUSE_MAX	How many historic passwords will I remember to prevent password reuse?
PASSWORD_REUSE_TIME	How long will I remember passwords to prevent password reuse?
PASSWORD_ROLLOVER_TIME	New Feature from 19.12 – allow an account to remember and allow the previous password so I don’t need to change every client at exactly the same time. The account has 2 passwords for a while! This is extremely useful for application users where we may have hundreds of client application servers connecting to the database.
PASSWORD_VERIFY_FUNCTION	How complex should the password be? The default is NULL meaning there is no password complexity enforced! It can be “X”.

How do I enforce better user control?

We need to create one or more profiles which comply with better standards than the defaults supplied. We will probably want more than one profile, as we will generally have more than one type of user – typical examples would be end users directly connecting, application accounts and database administrator accounts.

CREATE PROFILE cis_user_account LIMIT
       FAILED_LOGIN_ATTEMPTS     5
       INACTIVE_ACCOUNT_TIME     60
       PASSWORD_GRACE_TIME       5
       PASSWORD_LIFE_TIME        90
       PASSWORD_LOCK_TIME        1
       PASSWORD_REUSE_MAX        20
       PASSWORD_REUSE_TIME       365
       PASSWORD_ROLLOVER_TIME    0
       PASSWORD_VERIFY_FUNCTION  null];

ALTER USER neil_reporting   PROFILE cis_user_account ;
ALTER USER erik_reporting   PROFILE cis_user_account ;
ALTER USER martin_reporting PROFILE cis_user_account ;


CREATE PROFILE cis_app_account LIMIT
       FAILED_LOGIN_ATTEMPTS     5
       INACTIVE_ACCOUNT_TIME     120
       PASSWORD_GRACE_TIME       5
       PASSWORD_LIFE_TIME        365
       PASSWORD_LOCK_TIME        1
       PASSWORD_REUSE_MAX        20
       PASSWORD_REUSE_TIME       365
       PASSWORD_ROLLOVER_TIME    2
       PASSWORD_VERIFY_FUNCTION  null];

ALTER USER hr_app_user PROFILE cis_app_account ;

You many note that the cis_app_account profile has a longer PASSWORD_LIFE_TIME (365). This is not a good practice but it may be impractical to change the application account credentials on every application server every few months, like a user can. An exemption to the standard might be needed until it is possible to use automated tools such as Ansible and Liquibase to deploy new passwords to the application servers and database. The cis_app_account also has a PASSWORD_ROLLOVER_TIME of 2 days to allow a more controlled way to change the application password without stopping all of the application servers at the same time.

WARNING #1

Changing the PROFILE of a user may have unexpected consequences. A user who has not connected to the database for longer then the INACTIVE_ACCOUNT_TIME may suddenly lock and need to be unlocked. A user who has not changed their password for longer than PASSWORD_LIFE_TIME may suddenly lock and need to be unlocked. However, this does not happen immediately. There is a background processs which periodically check users against their PROFILE settings and takes action, so the account may lock several hour after you have changed the profile. If that is an application user, you may cause a system outage just when you do not expect it.

WARNING #2

Users who connect with system privileges, such as SYSDBA or SYSBACKUP have the ability to connect to the database even when the database is not available. As such, they do not update the LAST_LOGIN column in DBA_USERS. This means they are susceptible to being locked out by INACTIVE_ACCOUNT_TIME as the background process uses DBA_USERS.LAST_LOGIN to determine is the account should be locked. At the time of writing (in 19.15) , these accounts do not up the field in V$PWFILE_USERS.LAST_LOGIN either. The SYS account does not lock, so you can always access the database via SYS.

Password Complexity

But what about password complexity? We don’t want users to be able to use “X” as their password. We should all know that long passwords containing a combinations of elements – upper case, lower case, numeric and special characters – are the hardest to decrypt or brute-force. You have left the PASSWORD_VERIFY_FUNCTION as null! That is not compliant with any security standard, so what should we do here? This will take a bit more to explain…

Next, check out: Fundamental Security Part Two – Password Complexity and Defaults