Database security has always been important and now, with the compliance requirements of new regulations such as GDPR and the California Consumer Privacy Act (CCPA), it’s an issue that reaches across the organization, into the board room, and to customers. This attention is putting new pressure on DBAs to secure production data and development and testing databases.
Here are some relatively simple database security best practices and security checks that are easily executed, and can help organizations better understand and strengthen their defensive security posture. While various examples will use different databases, including SQL Server, Oracle, and PostgreSQL, the security concepts apply across platforms, and the techniques are easily adaptable with just a bit of translation.
Issue #1: Misuse or Overuse of SYS
For Oracle, the SYS schema owns the data dictionary (i.e., the catalog tables and views. No one should connect to Oracle using “SYS” unless they’re performing special tasks such as patching, version upgrades or running $ORACLE_HOME/rdbms/admin scripts that specifically require SYS access. Oracle’s Database Administrators Guide makes this clear: “Ensure that most database users are never able to connect to Oracle Database using the SYS account.”
Intelligent database security step 1: Do not enable anyone to connect as "SYS" unless they have a legitimate requirement to do so.
Issue #2: Failing to Leverage the SYSDBA Privilege
The Oracle SYSDBA system privilege allows users to perform some basic DBA commands related to fundamental database instance management—without conferring what should be closely-held general purpose DBA rights. SYSDBA privileges include:
- Performing 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
Intelligent database security step 2: Take advantage of the intermediate levels that major databases offer.
Issue #3: Over-Reliance on the Predefined DBA Role
There are three predefined Oracle roles for version 7.X-backward compatibility: CONNECT, RESOURCE, and DBA. In many organizations, people have become far too accustomed to using them, especially the DBA role, despite the Oracle Database Security Guide warning: “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.”
A better approach is for administrators to create their own DBA-type roles such as JUNIOR_DBA, MASTER_DBA, and DEVELOPER_DBA to better control who gets certain privileges. Most organizations don’t want or need all DBA users and their connections using the predefined DBA role, which comes with 237 system privileges and 18 system roles for Oracle 12c. For Oracle 18c, those numbers are even higher—much higher.
Intelligent database security step 3: Define the organization’s DBA-type roles, both to more closely protect organizational security around privileges and to prevent reliance on pre-defined roles that may not be supported in the future.
Issue #4: Idle Accounts
There is no mechanism within some end-user database tools to force idle people to log out, but in Oracle, for example, this capability is available via a mechanism known as profiles. The reality is that some database user tools are so useful that people like to have them running constantly, but leaving any software running on a desktop when the user steps away is inherently unsafe, even if the system is password-protected. Administrators may routinely see instances where such useful apps are regularly left running unattended over nights, weekends, and even vacations. That’s why security- minded DBAs should strongly consider implementing measures such as Oracle profiles, which lets them limit user idle times and the number of concurrent connections, and invoke password management policies and basic database resource limits.
For example, it makes sense to limit SQL*Plus command-line users (e.g., DEMO) to no more than eight concurrent database sessions (i.e., simultaneous logins) and to time out their sessions when they have been idle for 60 minutes or longer.
Intelligent database security step 4: Consider logging out idle users, limiting concurrent connections, and invoking password management.
Issue #5: Broad Access to Special Logins
The one consistent problem across all of the issues covered so far is how many people have access to whatever DBA accounts are used. A DBA director of a SQL Server shop went through a security compliance audit and the first big hurdle he ran into was the proliferation of access to special logins. The organization had given every developer, manager, and even some non-technical end users access to the SA login. They had no other privileged login accounts, and so they all shared the one in order to perform any database operation. It was understood that this was a very poor practice and would cause their audit to fail.
One approach to fixing this scenario is to create a new DBA login with all the same rights as SA and then slowly start to remove privileges from each account until the correct levels of minimum viable grants are identified for each user. From there, an organization can work on reducing the number of people with such access, possibly creating a second DBA type login with the same or a subset of privileges and slowly removing grants from that account.
Intelligent database security step 5: Remember that in today’s compliance-sensitive environment, audits are a real possibility. Curtail access to special logins, don’t share log-ins among multiple users, and look to define and limit privileges.
Issue #6: User Tools and 'Save Password'
This security recommendation may incur some negative user opposition (similar to the prior issue of forced timeout of idle sessions). Many end-user and database-developer tools are great productivity enhancers and, as previously mentioned, are often left running. It’s not uncommon for people to choose a “save password” option, which means that these tools’ connection screens take just a glance and double click to connect to the desired database. And that’s the problem. Anyone lurking and finding an unattended PC with such tools installed could potentially gain unfettered access to the databases with saved passwords. This actually happens, so a forced session timeout is a great first defense. Even better, if database tools permit it, turn off (preferably centrally rather than per desktop) the ability to save passwords. That, combined with idle timeouts, would be far better security-wise although people may complain about having to retype their passwords. In fact, if the database tools in use allow connecting to the database through Microsoft Active Directory or LDAP managed logins, then this issue is entirely eliminated. That’s even better.
Intelligent database security step 6: Don’t allow “auto” saved passwords for user tools.
Issue #7: Use Password Aging and Complexity Verification
Even if using passwords and even not permitting them to be saved by desktop tools, one more password-related security step should be considered. Security experts agree that all passwords, no matter how good, should have a short “shelf-life” (i.e., they are safe only for a short duration of time), and should be changed regularly. At many government sites, password changes are forced every 30 days. While it’s not a popular policy, it’s a wise one where sensitive data is concerned, and a sound strategy.
Database passwords should forcibly be required to be updated at regular intervals, and additional logic should specify the required complexity and any disallowed criteria (e.g., cannot reuse prior passwords, or use their names or other content found on social media). Especially in environments that choose not to disable password-saving, this limits the window of opportunity to use stolen passwords. Similar to many earlier security recommendations, this one is achievable using inherent database features or capabilities.
Intelligent database security step 7: Force database password updates on a regular basis, with mandated complexity, and disallow password components that can be easily "socially engineered."
Database Security Best Practices
With the implementation of new data-handling regulations and the knowledge that additional mandates will certainly be added, DBAs need to focus attention on securing production data as well as development and testing databases. These simple database best practices and checks can be easily executed, and can help DBAs and their organizations better understand and strengthen security.