Fundamental Security Part Four – Permissions Checking

03/07/2023 3 Comments

July 2023 – Oracle 19C

So in the previous post we talked about identifying unused and historic accounts. Old accounts should be removed, but what are the capabilities of the existing users who are accessing the systems?

A surprisingly large amount of applications request that the schema owner, and maybe the primary application connection, has DBA-level rights. This is always excessive and should be avoided.

Most DBA’s don’t need DBA rights most of the time. It makes a lot of sense to have a company-specific “DBA” role with minimal permissions. Most day-to-day checking tasks are read only. It is sensible to have a fairly low privilege account for day-to-day access and an admin account (preferable accessed via raising a ticket in response to an incident or change request) for when high privilege change is needed.

Your system permissions should be allocated on the principle of least privilege. Only grant those privileges and resources explicitly and legitimately required to perform the function.

Who Has DBA?

SELECT * FROM dba_role_privs 
WHERE granted_role = 'DBA'
ORDER BY grantee;

GRANTEE                        GRANTED_ROLE       ADM DEL DEF COM INH
------------------------------ ------------------ --- --- --- --- ---
APP_SCHEMA                     DBA                NO  NO  YES NO  NO
ERIK                           DBA                NO  NO  YES NO  NO
MARTIN                         DBA                NO  NO  YES NO  NO
NEIL                           DBA                NO  NO  YES NO  NO
TIM                            DBA                NO  NO  YES NO  NO
SYS                            DBA                YES NO  YES YES YES
SYSTEM                         DBA                NO  NO  YES YES YES

Identify who really needs DBA privs? NEIL the DBA does, but does ERIK the Developer need such high level privs? This is unlikely, even in a dedicated Development database.

However, you do need to be aware of other privileges. A CIS-level audit will pick up on DBA privs, but will not necessarily spot who has the IMP_FULL_DATABASE role granted to them (or, more powerfully, DATAPUMP_IMP_FULL_DATABASE). These roles contain very high level privileges such as ALTER DATABASE, DELETE ANY TABLE, and BECOME USER which should be highly restricted.

SELECT * FROM dba_role_privs 
WHERE granted_role = 'IMP_FULL_DATABASE'
ORDER BY grantee;

GRANTEE                        GRANTED_ROLE       ADM DEL DEF COM INH
------------------------------ ------------------ --- --- --- --- ---
DATAPUMP_IMP_FULL_DATABASE     IMP_FULL_DATABASE  NO  NO  YES YES YES
DBA                            IMP_FULL_DATABASE  NO  NO  YES YES YES
SCOTT                          IMP_FULL_DATABASE  NO  NO  YES NO  NO
SYS                            IMP_FULL_DATABASE  YES NO  YES YES YES

PUBLIC Grants

The above roles are fairly well known. A lesser known risk is the amount of procedures with EXECUTE permissions granted to PUBLIC. Anyone with access to the database can use these. However, there are over 2,500 such permissions in Oracle 19.13. The CIS Security standard helps to identify some of the higher risk grants, to packages such as UTL_TCP (which allows access to [corrupt] the TCP stream), UTL_HTTP (which could send information to external websites), DBMS_SQL (which, if used incorrectly, allows SQL Injection attacks), and DBMS_RANDOM (because it’s not very random and shouldn’t be used for any serious work!)

Sample of potentially insecure packages granted to public:

Network Security
DBMS_LDAP 
UTL_INADDR 
UTL_TCP 
UTL_MAIL 
UTL_SMTP 
UTL_DBWS 
UTL_ORAMTS 
UTL_HTTP 
HTTPURITYPE 

File Security
DBMS_ADVISOR
DBMS_LOB
UTL_FILE

Encryption
DBMS_CRYPTO
DBMS_OBFUSCATION_TOOLKIT
DBMS_RANDOM

Java
DBMS_JAVA
DBMS_JAVA_TEST

Scheduler
DBMS_SCHEDULER
DBMS_JOB

SQL Injection Helpers
DBMS_SQL
DBMS_XMLGEN
DBMS_XMLQUERY
DBMS_XLMSTORE
DBMS_XLMSAVE
DBMS_REDACT

(please review the CIS Standards for a list of the packages in your database release)

These grants can be seen in DBA_TAB_PRIVS (with the privilege of EXECUTE – the view isn’t only for tables)
It is a good practice to remove the grant of all of these packages from PUBLIC
[REVOKE EXECUTE ON <pkg> FROM PUBLIC; ]

WARNING: If you revoke these privileges from PUBLIC, you may find that your application stops working. You need to identify if any of the above packages are used and grant them explicitly to only the accounts which need them. The most common grant where you would need to do this would be for DBMS_LOB, but any of the above packages may be used by your apps or users. You need to discover which are being used (running checks against the application source code and view DBA_SOURCE, as well as monitoring the contents of GV$SQL and DBA_HIST_SQLTEXT are good starting points for this.)

Dangerous Grants

There are a number of very powerful and sensitive tables and procedures within Oracle where we need to monitor the grants carefully. Access to these is not granted by default, and we need to ensure this does not happen by accident!

Powerful Procedures
DBMS_BACKUP_RESTORE
DBMS_FILE_TRANSFER
DBMS_SYS_SQL
DBMS_REPCAT_SQL_UTL
INITJVMAUX
DBMS_AQADM_SYS
DBMS_STREAMS_RPC
DBMS_PRVTAQIM
LTADM
DBMS_IJOB
DBMS_PDB_EXEC_SQL

Sensitive Tables
CDB_LOCAL_ADMINAUTH$
DEFAULT_PWD$
ENC$
HISTGRM$
HIST_HEAD$
LINK$
PDB_SYNC$
SCHEDULER$_CREDENTIAL
USER$
USER_HISTORY$
XS$VERIFIERS

You may be wondering what is so sensitive about these tables and procedures. The DBMS_SYS_SQL package could allow a user to run code as a different user without entering valid credentials. Table HISTGRM$ contains samples of (or maybe all) data from table columns. Access to these objects is a notable security risk which needs to be taken seriously.

Here’s a useful bit of SQL which will look for access to these objects

SELECT owner, table_name, grantee, privilege, type FROM dba_tab_privs
WHERE table_name IN ('DBMS_LDAP', 'UTL_INADDR', 'UTL_TCP', 'UTL_MAIL', 'UTL_SMTP', 
'UTL_DBWS', 'UTL_ORAMTS', 'UTL_HTTP', 'HTTPURITYPE', 'DBMS_ADVISOR', 'DBMS_LOB', 
'UTL_FILE', 'DBMS_CRYPTO', 'DBMS_OBFUSCATION_TOOLKIT', 'DBMS_RANDOM', 'DBMS_JAVA',
'DBMS_JAVA_TEST', 'DBMS_SCHEDULER', 'DBMS_JOB', 'DBMS_SQL', 'DBMS_XMLGEN', 
'DBMS_XMLQUERY', 'DBMS_XLMSTORE', 'DBMS_XLMSAVE', 'DBMS_REDACT', 
'CDB_LOCAL_ADMINAUTH$', 'DEFAULT_PWD$', 'ENC$', 'HISTGRM$', 'HIST_HEAD$', 'LINK$', 
'PDB_SYNC$', 'SCHEDULER$_CREDENTIAL', 'USER$', 'USER_HISTORY$', 'XS$VERIFIERS', 'DBMS_BACKUP_RESTORE', 'DBMS_FILE_TRANSFER', 'DBMS_SYS_SQL', 'DBMS_REPCAT_SQL_UTL', 'INITJVMAUX', 'DBMS_AQADM_SYS', 'DBMS_STREAMS_RPC', 'DBMS_PRVTAQIM','LTADM', 'DBMS_IJOB', 'DBMS_PDB_EXEC_SQL')
ORDER BY owner,table_name;


OWN TABLE_NAME                GRANTEE              PRIVILEGE  TYPE
SYS DBMS_ADVISOR              PUBLIC               EXECUTE    PACKAGE
SYS DBMS_BACKUP_RESTORE       SYSBACKUP            EXECUTE    PACKAGE
SYS DBMS_FILE_TRANSFER        EXECUTE_CATALOG_ROLE EXECUTE    PACKAGE
SYS DBMS_IJOB                 IMP_FULL_DATABASE    EXECUTE    PACKAGE
SYS DBMS_JAVA                 PUBLIC               EXECUTE    PACKAGE
SYS DBMS_JOB                  PUBLIC               EXECUTE    PACKAGE
SYS DBMS_LDAP                 PUBLIC               EXECUTE    PACKAGE
SYS DBMS_LOB                  WMSYS                EXECUTE    PACKAGE
SYS DBMS_LOB                  PUBLIC               EXECUTE    PACKAGE
SYS DBMS_LOB                  ORDPLUGINS           EXECUTE    PACKAGE
SYS DBMS_LOB                  ORDSYS               EXECUTE    PACKAGE
SYS DBMS_OBFUSCATION_TOOLKIT  PUBLIC               EXECUTE    PACKAGE
SYS DBMS_PDB_EXEC_SQL         XDB                  EXECUTE    PROCEDURE
SYS DBMS_PDB_EXEC_SQL         AUDSYS               EXECUTE    PROCEDURE
SYS DBMS_RANDOM               ORDSYS               EXECUTE    PACKAGE
SYS DBMS_RANDOM               PUBLIC               EXECUTE    PACKAGE
SYS DBMS_REDACT               IMP_FULL_DATABASE    EXECUTE    PACKAGE
SYS DBMS_REDACT               EXECUTE_CATALOG_ROLE EXECUTE    PACKAGE
SYS DBMS_SCHEDULER            PUBLIC               EXECUTE    PACKAGE
SYS DBMS_SCHEDULER            MDSYS                EXECUTE    PACKAGE
SYS DBMS_SCHEDULER            AUDSYS               EXECUTE    PACKAGE
SYS DBMS_SQL                  AUDSYS               EXECUTE    PACKAGE
SYS DBMS_SQL                  PUBLIC               EXECUTE    PACKAGE
SYS DBMS_SQL                  DVSYS                EXECUTE    PACKAGE
SYS DBMS_SQL                  ORDSYS               EXECUTE    PACKAGE
SYS DBMS_STREAMS_RPC          EXECUTE_CATALOG_ROLE EXECUTE    PACKAGE
SYS DBMS_XMLGEN               PUBLIC               EXECUTE    PACKAGE
SYS DBMS_XMLQUERY             PUBLIC               EXECUTE    PACKAGE
SYS HISTGRM$                  NEIL                 SELECT     TABLE
SYS HTTPURITYPE               PUBLIC               EXECUTE    TYPE
SYS UTL_FILE                  WMSYS                EXECUTE    PACKAGE
SYS UTL_FILE                  PUBLIC               EXECUTE    PACKAGE
SYS UTL_FILE                  ORDPLUGINS           EXECUTE    PACKAGE
SYS UTL_FILE                  ORDSYS               EXECUTE    PACKAGE
SYS UTL_HTTP                  ORDPLUGINS           EXECUTE    PACKAGE
SYS UTL_HTTP                  PUBLIC               EXECUTE    PACKAGE
SYS UTL_INADDR                PUBLIC               EXECUTE    PACKAGE
SYS UTL_INADDR                DVSYS                EXECUTE    PACKAGE
SYS UTL_SMTP                  PUBLIC               EXECUTE    PACKAGE
SYS UTL_TCP                   PUBLIC               EXECUTE    PACKAGE

High Level Permissions

Another source of risk are various high level permissions allowing access to objects outside of your own user. The obvious ones are the “ANY” privileges, such as SELECT ANY TABLE, READ ANY TABLE and EXECUTE ANY PROCEDURE. and GRANT ANY PRIVILEGE.

You need to identify which users have these privileges and consider if a focussed role granting the necessary levels of permissions is a viable solution.

SELECT dsp.grantee
      ,dsp.privilege
      ,dsp.admin_option
      ,decode(dr.role,null,'USER','ROLE') user_or_role
      ,LISTAGG(DISTINCT drp.grantee,', ' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY drp.grantee) GRANTED_TO
  FROM dba_sys_privs dsp LEFT OUTER JOIN dba_roles dr ON (dsp.grantee=dr.role)
                         LEFT OUTER JOIN dba_role_privs drp ON (dsp.grantee=drp.granted_role)
 WHERE dsp.privilege LIKE '%ANY%'
    -- exclude Oracle users
   AND dsp.grantee NOT IN (SELECT du.username FROM dba_users du WHERE oracle_maintained = 'Y')
 GROUP BY dsp.grantee,dsp.privilege,dsp.admin_option,decode(dr.role,null,'USER','ROLE')
 ORDER BY user_or_role,dsp.grantee,dsp.privilege
/

GRANTEE                    PRIVILEGE            ADM USER GRANTED_TO
-------------------------- -------------------- --- ---- ------------------------------
AQ_ADMINISTRATOR_ROLE      DEQUEUE ANY QUEUE    YES ROLE GSMADMIN_ROLE, GSMCATUSER, MDS
AQ_ADMINISTRATOR_ROLE      ENQUEUE ANY QUEUE    YES ROLE GSMADMIN_ROLE, GSMCATUSER, MDS
AQ_ADMINISTRATOR_ROLE      MANAGE ANY QUEUE     YES ROLE GSMADMIN_ROLE, GSMCATUSER, MDS
AUDIT_ADMIN                AUDIT ANY            NO  ROLE SYS
...
DATAPUMP_IMP_FULL_DATABASE GRANT ANY ROLE       NO  ROLE DBA, GSMADMIN_INTERNAL, SYS
DATAPUMP_IMP_FULL_DATABASE SELECT ANY TABLE     NO  ROLE DBA, GSMADMIN_INTERNAL, SYS
DBA                        ALTER ANY ASSEMBLY   NO  ROLE APP_SCHEMA, DODGY_DBA, NEIL,
...
SCHEDULER_ADMIN            EXECUTE ANY CLASS    YES ROLE DBA, SYS
SCHEDULER_ADMIN            EXECUTE ANY PROGRAM  YES ROLE DBA, SYS
APP_USER                   DELETE ANY TABLE     NO  USER 
APP_USER                   INSERT ANY TABLE     NO  USER 
APP_USER                   SELECT ANY TABLE     NO  USER 
APP_USER                   UPDATE ANY TABLE     NO  USER 
NEIL2                      SELECT ANY TABLE     NO  USER

Please note that this is just the ‘%ANY%’ privileges. You need to be aware of users with other very high level privileges such as ALTER DATABASE, ALTER SYSTEM, AUDIT SYSTEM, ALTER USER, DROP USER and DROP PUBLIC SYNONYM. Hint: a good starting point is to remove the ‘dsp.privilege LIKE ‘%ANY%’‘ predicate and look to see what permissions SYS has granted to it. Privileges are added and removed with every Oracle release and you need to understand what they are doing.

Understand who has access to what, and if that access is justified. If not, determine how to reduce the access safely, without compromising your system availability whilst tightening your security.

Next we will talk about Observability. Things change. You need to be watching, regularly, to catch the change and ensure it’s valid.

Filed under Administration, Security Tagged with , , , , , , , , , , , , , ,

Fundamental Security Part Three – Unused and Historic Accounts

19/06/2023 3 Comments

June 2023 – Oracle 19C

So in the previous post we talked about implementing password complexity. Complex passwords are fine, but what when you experience change. For example, someone leaves the company to become a professional skydiver. Do you remove their database accounts? Are you even informed by HR that someone with database access has left?

If you have implemented user profiles appropriately, the account – left unused – will lock eventually, which is a good start. Pretty much every organization I investigate has old accounts hanging around in every database. You need to identify these accounts and remove them.

Fortunately, within recent versions of Oracle (12C+), account access is recorded in LAST_LOGIN column in the DBA_USERS view.

Users with SYSDBA/SYSBACKUP/etc privs do not update DBA_USERS (well, the underlying table SYS.USER$) as they must be able to connect to a database which is not available. They should update V$PWFILE_USERS.LAST_LOGIN instead of DBA_USERS, but as of my last test on 19.15, they do not.

If we run a query against DBA_USERS, it can be instructive to see what it going on. Reviewing the ACCOUNT_STATUS, CREATED data and LAST_LOGIN, we should be able to make some preliminary decisions about which accounts should continue to exist, and which accounts should be deleted.

DBA_USERS

SELECT username
      ,oracle_maintained
      ,account_status status
      ,created
      ,nvl(last_login,'never') last_login
  FROM dba_users ORDER BY 2, 1;

USERNAME   O STATUS  CREATED    LAST_LOGIN  my comments
APP_SCHEMA N OPEN    2019-04-18 2022-01-01  <- a schema owner
APP_USER   N OPEN    2019-04-18 2023-06-15  <- the main application user
BKP_USR    N OPEN    2019-04-18             <- no last login. WHY?
MARTIN     N OPEN    2020-11-16 2021-04-20  <- He retired
NEIL       N OPEN    2021-11-15 2023-06-15  <- me!
NIALL      N OPEN    2019-10-02 2020-12-12  <- off skydiving?
SCOTT      N LOCKED  2019-11-15 never       <- should this test schema exist?
TIM        N OPEN    2019-11-17 never       <- old and unused! Delete!
.
AUDSYS     Y LOCKED  2019-04-17 never
CTXSYS     Y LOCKED  2019-04-17 never
.
SYSRAC     Y LOCKED  2019-04-17 never
SYSTEM     Y OPEN    2019-04-17 2021-11-16  <- Do we use this? Should it be locked?
WMSYS      Y LOCKED  2019-04-17 never
XDB        Y LOCKED  2019-04-17 never
XS$NULL    Y LOCKED  2019-04-17 never

Take note of the ORACLE_MAINTAINED flag. You should not delete any accounts with this set to "Y", except under the supervision of Oracle Support, as this may cause system instability or even break the database.

Lets look at V$PWFILE_USERS too

$ sqlplus bkp_usr/oracle as sysbackup
Connected.
SQL > show user
USER is "SYSBACKUP"


SELECT username
      ,sysdba
      ,sysoper
      ,sysasm
      ,sysbackup
      ,sysdg
      ,syskm
      ,account_status status
      ,last_login
--    ,lock_date
--    ,expiry_date
--    ,authentication_type
--    ,common
      ,con_id
FROM  V$PWFILE_USERS
order by 1
/

USERNAME   SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM STATUS  LAST_LOGIN         CON_ID
BKP_USR    FALSE FALSE FALSE TRUE  FALSE FALSE OPEN                            3           
SYS        TRUE  TRUE  FALSE FALSE FALSE FALSE OPEN                            0

We can see from the above that BKP_USR is not registering logins as a sys privileged accounts (sysbackup), although it is used daily… We can see that it is indeed SYSBACKUP privs, but there’s no entry in LAST_LOGIN in either view.

We need to periodically manually review accounts in the database for anomalies and confirm that they are all correct. Create a regular report for this – there’s a good chance Security may like a copy.

Of course, there is an alternative to managing user populations in every database individually. From 18C, Oracle introduced Centrally Managed Users – CMU. This allows a database to directly authenticate accounts against Microsoft AD, without using an intermediary service such as OID or Oracle Enterprise User Security. I would recommend using MOS Document: 2462012.1 as a starting point, and initially integrating administrative users before opening up to the wider user population. Maybe not for schemas, though?

Next, we will check who has what permissions, and if there are any security surprises in there: Fundamental Security Part Four – Permissions Checking

Filed under Administration, Security Tagged with , , , , , , , , , ,

Fundamental Security Part Two – Password Complexity and Defaults

16/06/2023 5 Comments

June 2023 – Oracle 19C

So in the previous post we saw the creation of a PROFILE to add controls on user passwords, but we did not specify password complexity. By default, this is set to NULL in the profile, meaning no password complexity is enforced. This is a bad practice and must be changed.

PROFILE          RESOURCE_NAME             LIMIT
---------------- ------------------------- ---------
DEFAULT          PASSWORD_VERIFY_FUNCTION  NULL

Oracle provides a number of built-in password verification functions to set minimum length, complexity and the amount of character change required when entering a new password. Their details are as follows (Note that in 19C, VERIFY_FUNCTION_11G  and ORA12C_VERIFY_FUNCTION  are deprecated but still available)

Function NameLengthUpper Lower Numeric Special Differs-By
ORA12C_STIG_VERIFY_FUNCTION1511118
ORA12C_STRONG_VERIFY_FUNCTION922224
ORA12C_VERIFY_FUNCTION 811103
VERIFY_FUNCTION_11G 101103

Of the 4 build-in options, the ORA12C_STIG_VERIFY_FUNCTION is the “best” due to the level of complexity but mainly the length. Passwords need to be long to make them difficult to decrypt or crack. Having a guaranteed mix of upper case, lower case, numeric and special characters mean that there are a lot of possible options for each character position (over 70).

It is possible to write your own password complexity function, and Oracle provide the code for the above options in clear text in the file : $ORACLE_HOME/rdbms/admin/catpvf.sql


Simple example of creating your own custom password verification function based upon the built-in Oracle functions ora_complexity_check and ora_string_distance:


CREATE OR REPLACE FUNCTION custom_password_verify (
    username     VARCHAR2
  , password     VARCHAR2
  , old_password VARCHAR2
) RETURN BOOLEAN IS
    differ  INTEGER;
BEGIN
    IF NOT ora_complexity_check(
                               password
                             , chars => 15
                             , uppercase => 1
                             , lowercase => 1
                             , digit => 1
                             , special => 1
           ) THEN
        RETURN ( false );
    END IF;
    -- Check if the password differs from the previous password by n characters
    IF old_password IS NOT NULL THEN
        differ := ora_string_distance(old_password, password);
        IF differ < 8 THEN
            raise_application_error(-20000, 'password is too similar to previous password');
        END IF;

    END IF;
RETURN ( true );
END;
/

ALTER PROFILE default LIMIT PASSWORD_VERIFY_FUNCTION custom_password_verify;

NOTE: The function ORA_STRING_DISTANCE computes the Levenshtein distance between 2 words, which is the minimum number of single character edits required to change one word into the other.

It can be a good idea to match the complexity required within the Oracle database passwords with the complexity required with your corporate AD or LDAP password validation.

More on using AD or LDAP to authenticate within Oracle in another blog post.

WARNING

The PASSWORD_VERIFY_FUNCTION is a very powerful function which needs to be protected. Given that passwords are passed to it in clear text, and it is possible to subvert the procedure to perform illicit actions (such as keeping a backdoor permanently unlocked or recording everyones password) with the function, care should be taken to review and monitor the function for change and unexpected code.

Default Passwords

There are many standard Oracle accounts in existence, some of which are intrinsic to the running of the system such as SYS and SYSRAC. Some are automatically installed in the database, such as OUTLN or ORDDATA, and some are are simply well know, such as SCOTT or HR. These accounts have default passwords, which are commonly known to every hacker. Oracle provides as view which will display any accounts with a default password, called: DBA_USERS_WITH_DEFPWD

Installing a vanilla Oracle19.3 database and running a select yields the following results:


SQL > SELECT * FROM dba_users_with_defpwd;

USERNAME                       PRODUCT
------------------------------ ------------------------------
SYS
SYSTEM
CTXSYS

SQL > conn CTXSYS/CTXSYS
ERROR:
ORA-28000: The account is locked.

As we can see, the system believed SYS and SYSTEM have the default passwords, and CTXSYS definitely does have the default password! Fortunately, it is also locked by default, but it might be a good idea to change the password immediately.

However, SYS and SYSTEM force you to change the password on installation. There’s a minor bug which means the SHA1 hash which is used to identify default passwords is not updated at installation time. It is a good idea to re-change the SYS and SYSTEM password after install to update this and demonstrate to any audit or compliance officer running the above SELECT that the password is, indeed, not set to the default.

For more details about the underlying mechanisms which are used to allow this view to work, please check out Pete Finnegans blog post here

Now, lets look at old users…

Filed under Administration, Security Tagged with , , , , , , , , ,

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 NameDescription
FAILED_LOGIN_ATTEMPTSHow many times can I get my password wrong before the account locks?
PASSWORD_LOCK_TIMEHow long should the password be locked following FAILED_LOGIN_ATTEMPTS ?
INACTIVE_ACCOUNT_TIMEHow long should I wait before locking an unused account automatically?
PASSWORD_GRACE_TIMEWhen 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_MAXHow many historic passwords will I remember to prevent password reuse?
PASSWORD_REUSE_TIMEHow long will I remember passwords to prevent password reuse?
PASSWORD_ROLLOVER_TIMENew 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_FUNCTIONHow 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

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

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

Filed under Administration, Security Tagged with , , , , , , , ,