Fundamental Security Part One – User Profiles
15/06/2023 3 Comments
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