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
Pingback: Fundamental Oracle Security | Neil Chandler's DB Blog
Pingback: Fundamental Security Part Two – Password Complexity and Defaults | Neil Chandler's DB Blog
Pingback: Fundamental Security Part Four – Permissions Checking | Neil Chandler's DB Blog