Database Security Best Practices – Simple & Worthwhile Concepts – Part II

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 organizations and their DBAs must step 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 stricter 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 will start you on the road to better security. Throughout this blog series, I will show examples using different databases, including SQL Server, Oracle, and PostgreSQL. In this first blog in the series, I used Oracle. However, all 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.

Each week I will post between four and six 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. Last week, part one of this blog series covered the first batch of security issues and recommendations. Here is the second batch of such concepts.

Database Security Best Practices: Issue #5 – Limit Access To Special Logins

Part one covered the misuse of built-in Oracle accounts (SYS and SYSTEM), Oracle connection types (as SYSDBA), and built-in Oracle roles (CONNECT, RESOURCE, and DBA). Non-Oracle examples for built-in accounts include SA for SQL Server and Sybase, ROOT for MySQL, DB2ADMIN for IBM DB2 LUW, and POSTGRES for PostgreSQL. Part one also recommended creating your own roles and logins based upon your security requirements rather than just using the built-in logins. Moreover, no matter the database platform, you also can very grant far too many or overly powerful privileges to your user own DBA type logins. The one consistent problem across all these issues is how many people have access to whatever DBA accounts you do use.

A friend of mine who is the DBA director of a SQL Server shop is enduring a security compliance audit and the first major hurdle he ran into was the proliferation of access to special logins. They had allowed every developer, manager and even some non-technical end-users access to the SA login. It was more than just for convenience, they had no other privileged login accounts and so they all shared the one in order to perform any database operation. While he readily agreed that it was very poor practice and would cause their audit to fail, this habit is too deeply embedded into the way they function and cannot be changed anytime soon.  The first step will be to create a new DBA login with all the same rights as SA and then slowly to remove privileges from that account until the correct minimum viable grants are discovered. Then they will work on reducing the number of people with access, possibly creating a second DBA type login with the same privileges and slowly removing grants from that account.

The key point is that this friend’s shop will not legitimately pass their security audit in the near future. You may not see nor agree with the critical importance of this issue, even if your shop does not follow this bad practice. But the security experts have identified this as not only a bad practice, but one that is fairly pervasive in many small to mid-sized companies’ IT departments – especially those which do not hire full-time DBA’s, but rather permit application development architects or team leads manage their databases.

Database Security Best Practices: Issue #6 – Implement Automated Resource Controls

This next recommendation probably has the least real security-related value, but it’s worth mentioning nonetheless since it builds on what we’ve discussed so far (i.e. profiles), and also leads to the next topic which will be much more clearly security oriented. Other than possibly helping to prevent or minimize the effects of certain Denial of Service (DOS) type attacks against your database, this concept is more of a computing resource fair utilization control technique. Moreover, there are no simple SQL commands per se (at least with Oracle) for managing this technique. Thus, many DBAs may not routinely implement this additional database access control since it requires writing a somewhat complex code.

Ever have some database users that you need to limit in terms of fair resource consumption, but with more granularity than a mechanism like Oracle profiles? Think about those users who love to kick off Cartesian join queries or fetch multi-million-row queries back to their PC on a regular basis. These guys are being unfair resource hogs, and simple, user-friendly tools may make it easy for users to ask for expensive operations without realizing it. Thus, it would be easy for one user’s action to have a negative impact on all other users in terms of time to run without either party knowing why the slowdown. Another way to say this is that just as desktop PC’s have increased in their speed and thus capabilities, so too has their users’ demands grown against the database servers they access. So sometimes we need to reign in certain end-user self-service tools (or users of any tool for that matter) lest they run amuck.

So let’s look at Oracle Resource Groups. Suppose we have two development projects sharing a common database server and they can’t afford one group to consume an unfair percentage of the shared servers’ resources to the detriment of the other group. Furthermore, suppose that on the production database server that business users must be allocated the majority of the resources. Figure 1 highlights these two scenarios of potentially desired resource limitation objectives.

Three other aspects make Oracle’s resource management a worthwhile feature.

First, you can create hierarchies of plans. Thus we can take the development resource plan and delve into more details on how we want it to function for different classes of users. We can define that senior developers on dev project #1 should get slightly more resources than junior developers as shown in Figure 2. Whereas dev project #2 remains the same with everyone getting the same resource limits.

Let’s now return to the idea of a DOS service security attack. Suppose that someone makes such an attack using a username and password for a junior level developer from dev project #1, the resource limits will prevent that attack from consuming more than 15% of the server’s resources (i.e. 30% of 50%). That might make the difference between a slow machine that needs a reboot and being able to work on in spite of such attacks

And second, you can define resource consumption limits within a group that automatically cause the users’ processes to switch from one resource group to another. Thus in the example above, we could define dev project #1’s senior developers resource group such that any process that consumes more than some maximum threshold downgrades to a junior status job – and thus gets far less CPU time for that point forward.

Finally, almost everything you could do above with Oracle Profiles you can do much better with Oracle Resource Plans and Groups. For example, both execution and idle time limits can be best managed via this technique – and with additional options and features.

Unfortunately, you’ll need to learn some overly-complex PL/SQL package like shown below to implement the development resource controls shown in Figure-2.

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager.create_consumer_group (
      consumer_group               => 'PROJ_1_SENIOR_DEV'
     ,comment                      => 'Group for senior database developers on project #1.'
     ,mgmt_mth                     => 'ROUND-ROBIN'
     ,category                     => 'INTERACTIVE');
  sys.dbms_resource_manager.submit_pending_area();
end;
/

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager_privs.grant_switch_consumer_group (
      grantee_name                 => 'PROJ_1_DEV_1'
     ,consumer_group               => 'PROJ_1_SENIOR_DEV'
     ,grant_option                 => FALSE);
  sys.dbms_resource_manager.submit_pending_area();
end;
/

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager.create_consumer_group (
      consumer_group               => 'PROJ_1_JUNIOR_DEV'
     ,comment                      => 'Group for junior database developers on project #1.'
     ,mgmt_mth                     => 'ROUND-ROBIN'
     ,category                     => 'INTERACTIVE');
  sys.dbms_resource_manager.submit_pending_area();
end;
/

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager_privs.grant_switch_consumer_group (
      grantee_name                 => 'PROJ_1_DEV_2'
     ,consumer_group               => 'PROJ_1_JUNIOR_DEV'
     ,grant_option                 => FALSE);
  sys.dbms_resource_manager.submit_pending_area();
end;
/

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager.create_consumer_group (
      consumer_group               => 'PROJ_2_ALL_DEV'
     ,comment                      => 'Group for all database developers on project #2.'
     ,mgmt_mth                     => 'ROUND-ROBIN'
     ,category                     => 'INTERACTIVE');
  sys.dbms_resource_manager.submit_pending_area();
end;
/

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager_privs.grant_switch_consumer_group (
      grantee_name                 => 'PROJ_2_DEV_2'
     ,consumer_group               => 'PROJ_2_ALL_DEV'
     ,grant_option                 => FALSE);
  sys.dbms_resource_manager.submit_pending_area();
end;
/


begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager_privs.grant_switch_consumer_group (
      grantee_name                 => 'PROJ_2_DEV_1'
     ,consumer_group               => 'PROJ_2_ALL_DEV'
     ,grant_option                 => FALSE);
  sys.dbms_resource_manager.submit_pending_area();
end;
/

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager.create_plan (
      plan                         => 'PROJ_1'
     ,mgmt_mth                     => 'EMPHASIS'
     ,active_sess_pool_mth         => 'ACTIVE_SESS_POOL_ABSOLUTE'
     ,parallel_degree_limit_mth    => 'PARALLEL_DEGREE_LIMIT_ABSOLUTE'
     ,queueing_mth                 => 'FIFO_TIMEOUT'
     ,comment                      => null
     ,sub_plan                      => FALSE);
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'PROJ_1'
     ,group_or_subplan             => 'OTHER_GROUPS'
     ,switch_estimate              => FALSE
     ,switch_for_call              => FALSE
     ,comment                      => NULL  );
  sys.dbms_resource_manager.create_consumer_group (
      consumer_group               => 'PROJ_1_SENIOR_DEV'
     ,comment                      => 'Group for senior database developers on project #1.');
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'PROJ_1'
     ,group_or_subplan             => 'PROJ_1_SENIOR_DEV'
     ,switch_estimate              => FALSE
     ,max_idle_time                => 60
     ,mgmt_p1                      => 1
     ,switch_for_call              => FALSE
     ,utilization_limit            => 70
     ,comment                      => NULL  );
  sys.dbms_resource_manager.create_consumer_group (
      consumer_group               => 'PROJ_1_JUNIOR_DEV'
     ,comment                      => 'Group for junior database developers on project #1.');
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'PROJ_1'
     ,group_or_subplan             => 'PROJ_1_JUNIOR_DEV'
     ,switch_estimate              => FALSE
     ,max_idle_time                => 60
     ,mgmt_p1                      => 1
     ,switch_for_call              => FALSE
     ,utilization_limit            => 30
     ,comment                      => NULL  );
  sys.dbms_resource_manager.submit_pending_area();
end;
/

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager.create_plan (
      plan                         => 'PROJ_2'
     ,mgmt_mth                     => 'EMPHASIS'
     ,active_sess_pool_mth         => 'ACTIVE_SESS_POOL_ABSOLUTE'
     ,parallel_degree_limit_mth    => 'PARALLEL_DEGREE_LIMIT_ABSOLUTE'
     ,queueing_mth                 => 'FIFO_TIMEOUT'
     ,comment                      => null
     ,sub_plan                      => FALSE);
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'PROJ_2'
     ,group_or_subplan             => 'OTHER_GROUPS'
     ,switch_estimate              => FALSE
     ,switch_for_call              => FALSE
     ,comment                      => NULL  );
  sys.dbms_resource_manager.create_consumer_group (
      consumer_group               => 'PROJ_2_ALL_DEV'
     ,comment                      => 'Group for all database developers on project #2.');
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'PROJ_2'
     ,group_or_subplan             => 'PROJ_2_ALL_DEV'
     ,switch_estimate              => FALSE
     ,max_idle_time                => 60
     ,switch_for_call              => FALSE
     ,utilization_limit            => 100
     ,comment                      => NULL  );
  sys.dbms_resource_manager.submit_pending_area();
end;
/

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager.create_plan (
      plan                         => 'DEV_PLAN'
     ,mgmt_mth                     => 'RATIO'
     ,active_sess_pool_mth         => 'ACTIVE_SESS_POOL_ABSOLUTE'
     ,parallel_degree_limit_mth    => 'PARALLEL_DEGREE_LIMIT_ABSOLUTE'
     ,queueing_mth                 => 'FIFO_TIMEOUT'
     ,comment                      => null
     ,sub_plan                      => FALSE);
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'DEV_PLAN'
     ,group_or_subplan             => 'OTHER_GROUPS'
     ,switch_estimate              => FALSE
     ,switch_for_call              => FALSE
     ,comment                      => NULL  );
  sys.dbms_resource_manager.create_consumer_group (
      consumer_group               => 'PROJ_1_SENIOR_DEV'
     ,comment                      => 'Group for senior database developers on project #1.');
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'DEV_PLAN'
     ,group_or_subplan             => 'PROJ_1_SENIOR_DEV'
     ,switch_estimate              => FALSE
     ,switch_for_call              => FALSE
     ,utilization_limit            => 35
     ,comment                      => NULL  );
  sys.dbms_resource_manager.create_consumer_group (
      consumer_group               => 'PROJ_1_JUNIOR_DEV'
     ,comment                      => 'Group for junior database developers on project #1.');
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'DEV_PLAN'
     ,group_or_subplan             => 'PROJ_1_JUNIOR_DEV'
     ,switch_estimate              => FALSE
     ,switch_for_call              => FALSE
     ,utilization_limit            => 15
     ,comment                      => NULL  );
  sys.dbms_resource_manager.create_consumer_group (
      consumer_group               => 'PROJ_2_ALL_DEV'
     ,comment                      => 'Group for all database developers on project #2.');
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'DEV_PLAN'
     ,group_or_subplan             => 'PROJ_2_ALL_DEV'
     ,switch_estimate              => FALSE
     ,switch_for_call              => FALSE
     ,utilization_limit            => 50
     ,comment                      => NULL  );
  sys.dbms_resource_manager.submit_pending_area();
end;
/

Database Security Best Practices: Issue #7 – Don’t Permit User Tools Saving Passwords

This security recommendation will probably incur the 2nd most negative user opposition (much like the prior issues about using Oracle profiles or resource groups to timeout idle sessions). Many end-user and developer database tools are great productivity enhancers (and often left running all the time as was mentioned in part one), so it’s not uncommon for people to choose to “save password” option such that these tools’ connection screens take but a quick look and double click in order to connect to the desired database. That’s exactly why it’s unsafe, because anyone finding an unattended PC with such tools installed could possibly gain unfettered access to your databases with saved passwords. This actually happens more than you think, hence while the idle session timeout is a great first defense. Better yet if your 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, even though people would complain about having to retype their passwords all the time. In fact if your database tools allow connecting to the database through MS Active Directory (AD) or LDAP managed logins, then this issue is entirely eliminated. That may be the safest bet.

Database Security Best Practices: Issue #8 – Use Password Aging and Complexity Verification

Let’s assume that you are using passwords and even not permitting them to be saved by desktop tools, you still have one more password-related security step to consider implementing. Many people hold the following premise to be true: all passwords, no matter how good, have a short “shelf-life” (i.e. are safe only for a short duration of time) and thus should be changed regularly. I’ve worked at government sites where we had to change the password every 30 days. Yes, I hated it – but it was a requirement to work there since we dealt with extremely sensitive data.

Thus, the next recommendation sure to generate some hate mail: database passwords should forcibly be required to change at regular intervals with additional logic specifying the required complexity, plus any disallowed criteria (e.g. cannot reuse prior passwords). Following such advice will help to further mitigate the security risks in those shops that choose not to disable password saving by limiting the window of opportunity to use stolen passwords. Plus like many earlier security recommendations, this one too is best achieved via the Oracle (or other) database itself via inherent features or capabilities.

Here’s an example of a very basic password verification Oracle PL/SQL function. You may freely use this as example code as your starting point or the Oracle provided example code contained in the DBA script $ORACLE_HOME/RDBMS/admin/utlpwdmg.sql.

CREATE OR REPLACE FUNCTION verify_password
(username VARCHAR2,
new_password VARCHAR2,
old_password VARCHAR2)
RETURN BOOLEAN
IS
i INTEGER;
c CHAR;
isdigit BOOLEAN := false;
isletter BOOLEAN := false;
ispunct BOOLEAN := false;
differ INTEGER;
m INTEGER;
BEGIN
--Check if the password is same as the username
IF (new_password = username) THEN
raise_application_error(-20001, 'New password same as username');
END IF;
--Check for the new password = old password
IF (new_password = old_password) THEN
raise_application_error(-20002, 'New password same as old password');
END IF;
--Check for the minimum length of the password
IF length(new_password) < 8 THEN
raise_application_error(-20003, 'New password length less than 8 chars');
END IF;
--Check if the password is too common or simple
IF NLS_LOWER(new_password) IN ('welcome', 'database', 'account', 'user',
'password', 'oracle', 'computer', 'abcd','mgr', 'manager', 'tiger')
THEN raise_application_error(-20004, 'New password too common or simple');
END IF;
--Check if the password contains at least one
--letter, one digit, and one punctuation mark
FOR i IN 1 .. length(new_password) LOOP
c := substr(new_password,i,1);
IF (NOT isdigit) THEN
isdigit := (INSTR('0123456789',c) > 0);
END IF;
IF (NOT isletter) THEN
isletter := (INSTR
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',c)
> 0);
END IF;
IF (NOT ispunct) THEN
ispunct := (INSTR('!"#$%&()''*+,-/:;<=>?_',c) > 0);
END IF;
EXIT WHEN isdigit AND isletter AND ispunct;
END LOOP;
IF (NOT isletter) OR (NOT isdigit) OR (NOT ispunct) THEN
raise_application_error(-20005, 'Password should contain at least one digit, one character and one punctuation');
END IF;
--Check if the password differs from the previous password by at least 3 letters
differ := length(old_password) - length(new_password);
IF abs(differ) < 3 THEN
IF length(new_password) < length(old_password) THEN
m := length(new_password);
ELSE
m:= length(old_password);
END IF;
differ := abs(differ);
FOR i IN 1..m LOOP
IF substr(new_password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;
IF differ < 3 THEN
raise_application_error(-20006, 'Password should differ by at least 3 characters');
END IF;
END IF;
--Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/

Note – The Oracle password verify function must be owned by SYS.

Then to enact this password security verification plus all of the above-mentioned additional password security checks, one simply uses Oracle profiles as follows:

ALTER PROFILE AQUA_DATA_STUDIO_DEV_SENIOR
LIMIT
SESSIONS_PER_USER 8
IDLE_TIME 60
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 30
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 1
PASSWORD_GRACE_TIME 1
PASSWORD_VERIFY_FUNCTION verify_password;

Thus, if a user tries to log in their database tool and their password has expired, they will be prompted to replace it as shown below:

And if the user provides a new Oracle password that fails verification, an error like the one below will display:

Until next week when then, when we will delve even deeper to basic security techniques to harden your databases against attack.

Other Blogs in This Series

• Part 1 – https://stealthbits.com/blog/database-security-best-practices-part-1/
• Part 3 – https://stealthbits.com/blog/database-security-best-practices-simple-worthwhile-concepts-part-iii/
• Part 4 – https://stealthbits.com/blog/database-security-best-practices-simple-worthwhile-concepts-part-iv/

Don’t miss a post! Subscribe to The Insider Threat Security Blog here:

Name

Email*