Fundamental Security Part Three – Unused and Historic Accounts

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