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 tighter security across the board – including development and all test databases (e.g. unit testing, stress testing, and acceptance testing). This blog series will cover 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. Whether you implement all or even just some of these ideas you will effectively harden your database. That’s always a decent first step in improving overall database security. Here is the first batch of ideas.
Issue #1 – Misuse or Overuse of SYS
The SYS schema owns the data dictionary (i.e. the catalog tables and views). Basically, you should never connect as SYS unless performing special tasks such as Oracle patches, version upgrades or running $ORACLE_HOME/rdbms/admin scripts which specifically document that they require SYS access. In fact, Oracle’s Database Administrators Guide very clearly states: “Ensure that most database users are never able to connect to Oracle Database using the SYS account.” Yet many people treat SYS as some sort of general purpose, super DBA or root account and routinely connect as SYS as shown in Figure 1 and Figure 2. Connecting as SYS should be the exception and occur so rarely as to be a non-issue. In short, do not connect as SYS unless you have a legitimate requirement to do so.
Figure 1: Connecting to the database as SYS (command line)
Figure 2: Connecting to the database as SYS (GUI tool)
Issue #2 – Misunderstanding the SYSDBA Privilege
The SYSDBA system privilege permits granted users to perform some very basic DBA commands related to fundamental database instance management – namely:
- Perform STARTUP and SHUTDOWN operations
- ALTER DATABASE: open, mount, back up, or change character set
- CREATE DATABASE
- DROP DATABASE
- CREATE SPFILE
- ALTER DATABASE ARCHIVELOG
- ALTER DATABASE RECOVER
- Includes the RESTRICTED SESSION privilege
Yet once again many people seem to think that connecting as SYSDBA provides comprehensive general purpose DBA rights and routinely connect as SYSDBA as shown in Figure 3 and Figure 4. If I really want to connect as the “DEMO” user and have general purpose DBA type privileges, then I really need to take some time to grant whatever roles and/or privileges I desire the “DEMO” user to have. And I don’t mean just granting the DBA role, but that’s another issue covered next. Remember, just connecting as SYSDBA does not infer general purpose DBA type rights – just the subset required for basic database instance management. So you could connect as SYSDBA and find that you cannot do the things you expected – and that’s by design.
Figure 3: Connecting to the database as SYSDBA (command line)
Figure 4: Connecting to the database as SYSDBA (GUI tool)
Issue #3 – Over-reliance on Predefined DBA Role
There are three pre-defined roles for Oracle 7.X backward compatibility: CONNECT, RESOURCE, and DBA. Academically speaking all these roles should simply be avoided. However, people have become too accustomed to using them, especially DBA, even though Oracle’s Database Security Guide states “Oracle recommends that you design your own roles for database security rather than relying on this role. This role may not be created automatically by future releases of Oracle Database.” Ideally, you should create your own DBA type of roles such as JUNIOR_DBA, MASTER_DBA, and DEVELOPER_DBA. That way you can better control who gets what privileges, because you really don't want all DBA connections using the DBA role and thus having all 237 system privileges and 18 system roles as shown in Figure 5.
Figure 5: DBA Role Has Too Many Grants
Issue #4 – Implement Oracle Profiles
Aqua Data Studio is a great tool, and it’s not uncommon for people to use it from sun-up to sun-down. But leaving any software running on your desktop when you might temporarily step away is inherently unsafe. Even with screensavers requiring a password to unlock, it’s still not entirely safe. Furthermore, we routinely have people who leave apps like Aqua Data Studio running unattended overnight and over entire weekends – again, this is a highly insecure but common practice. There is no mechanism within most database tools to force idle people to log out, but Oracle provides this capability and DBAs should strongly consider implementing it via profiles. With a profile, the DBA can limit their idle time, the number of concurrent connections, password management policies, and basic database resource limits.
Let’s assume that we want to limit SQL*Plus command line users (e.g. DEMO) to no more than eight concurrent database sessions (i.e. simultaneous logins) and to timeout their session when idle for 60 minutes or longer. We simply need to create a profile like that shown here which can log out idle uses as shown in Figure 6.
CREATE PROFILE SQLPLUS_USER
LIMIT SESSIONS_PER_USER 8
IDLE_TIME 60;
ALTER USER "DEMO" PROFILE SQLPLUS_USER;
Figure 6: Automatic Session Idle Timeout via Profile
NOTE – this requires setting the following spfile (init.ora) parameter:
- RESOURCE_LIMIT = TRUE (default is FALSE)
This was just the first part of a series which I expect to run for several weeks as there are many more database security best practices to share. So look for the upcoming parts of this series for a complete security checklist.