Database security is a hot topic these days, especially with all the new and seemingly never-ending security compliance requirements being imposed such as GDPR. This means that DBAs must up their game when it comes to database security. Some DBAs may think these new requirements apply only to production, but depending on the situation DBAs may well need to apply tauter security across the board – including development and all test databases (e.g. unit testing, stress testing, and acceptance testing). This blog covers some very simple database security checks which are easy to deploy and which at least start you on the road to better security. While the examples shown are for Oracle, the security concepts and thus techniques should apply equally well across all other database platforms. You may just need to translate the concepts and example scripts shown here to your specific database platform.
Note that for many examples I will be using the powerful, multi-database GUI tool: Aqua Data Studio.
Each week I will post four simple, yet worthwhile database security ideas. Part one and Part two posted the last couple of weeks covered the first eight security ideas. This blog now picks up with the next set of four security ideas.
Issue #9 – Lockdown Built-in User Accounts
Often when you create a database instance the process will often also create from several to several dozen built-in accounts with default passwords which are well known by many people, and especially hackers. Moreover deploying third-party applications on your database can also create many built-in accounts with well-known default passwords. These built-in accounts may or may not have elevated privileges; nonetheless, you should change the default passwords and/or lock all these user accounts. Here is a short list of just a few well known, built-in accounts in an Oracle database:
- Predefined Administrative Accounts
- ANONYMOUS
- CTXSYS
- DBSNMP
- EXFSYS
- LBACSYS
- MDSYS
- MGMT_VIEW
- OLAPSYS
- OWBSYS
- ORDPLUGINS
- ORDSYS
- OUTLN
- SI_INFORMTN_SCHEMA
- SYS
- SYSMAN
- SYSTEM
- TSMSYS
- WK_TEST
- WKSYS
- WKPROXY
- WMSYS
- XDB
- Predefined Non-Administrative User Accounts
- APEX_PUBLIC_USER
- DIP
- FLOWS_30000
- FLOWS_FILES
- MDDATA
- ORACLE_OCM
- SPATIAL_CSW_ADMIN_USR
- SPATIAL_WFS_ADMIN_USR
- XS$NULL
So checking my database user accounts with Aqua Data Studio shown below it appears that most of my built-in accounts are properly locked down. However, I can see that both SYS and SYSTEM show up in this list. However, I know that I’ve already changed their default passwords from CHANGE_ON_INSTALL and MANAGER, respectively. Therefore I can mark this database security issue as passed for this database.
NOTE: Here is the SQL Statement for those wishing to copy it.
select USER_ID, USERNAME, CREATED, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE, PROFILE
fromdba_users where account_status not in ('EXPIRED', 'LOCKED', 'EXPIRED & LOCKED')
Issue #10 – Locate and Disable Idle User Accounts
There could be many reasons that a user account could become idle or inactive. Here are just a couple of possible reasons:
- The user has never, ever connected to the database
- The user has not connected in some long time period
In order to test for these types of security conditions your database auditing must be enabled, you must have chosen to audit connections, and you must have access to the auditing tables. Below is a SQL*Plus session where I’ve set the proper security configuration parameters for this to work:
Now I have access to the audit tables which are collecting the history of session connections. So I can now query to find user accounts which have never logged in as shown below. Note one exceptionally good finding, no one has ever logged on as SYS. So it appears that I just have one DBA and two USER type accounts not being used. I could now choose to lock them or drop them as I see fit. I leave it to the reader the tweaks necessary to find user accounts which have not connected in a long time (hint – it’s just one extra where condition in the sub-select).
NOTE: Here is the SQL Statement for those wishing to copy it.
select USER_ID, USERNAME, CREATED, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE, PROFILE
fromdba_users where account_status not in ('EXPIRED', 'LOCKED', 'EXPIRED & LOCKED')
and username not in (select username from dba_audit_trail
where action_name = 'LOGON')
order by username
Issue #11 – Locate and Disable Cracked User Accounts
There could be many reasons that a user account could be considered having been hacked. For example, maybe we define an Oracle profile for managing user accounts which specifies to lock any account which experiences three failed login attempts. Below is an example of trying to unsuccessfully login to DBA_04 which we just previously identified as an idle account. Note after the third failed login attempt that the account becomes locked. These failed attempts could have been a denial of service (DOS) type attack or just someone trying to break into this database.
So now looking for locked accounts which appear to have been possibly hacked due to both a status of “LOCKED(TIMED)” and having had a recent series of connection attempts is shown below. We can now clearly see that DBA_04 is suspect – and should probably be locked.
NOTE: Here is the SQL Statement for those wishing to copy it.
select USER_ID, USERNAME, CREATED, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE, PROFILE
fromdba_users where account_status = 'LOCKED(TIMED)'
and username in (select username from dba_audit_trail
where action_name = 'LOGON')
order by username
Issue #12 – Look for Unknown Domains or Unsupported OS
While security violations can always occur within your company, you also must protect against external sources of attack. In my case the Windows database server is on a virtual machine within my home lab’s WORKGROUP domain. Furthermore, I know that all the physical and virtual machines in my home lab are running Windows 7, Windows 10, CentOS Linux 6.x, or CentOS 7.x. So here I can tell that the USER_12 account is being accessed from an unknown domain called “HACKNET” plus it also looks all those PC’s are running Windows XP based upon their machine names. So once again it appears that some of my database accounts have been compromised.
NOTE: Here is the SQL Statement for those wishing to copy it.
select * from dba_audit_trail
where userhost not like 'WORKGROUP%' and userhost <> 'winvm'
Until next week when then, when we will delve even deeper into basic security techniques to harden your databases against attack. Hopefully, by now you’ve begun to see that the net or cumulative effects of all these ideas will shore up your database security. And best of all, all these recommendations are all very easy to implement.