Thursday, July 31, 2014

[Security] Always parameterize/sanitize SQL queries

SQL injection is the number #1 security threat as per OWASP's top 10 threats of 2013. Even though there are tons of materials and many techniques out there on how to avoid being victims of such attacks, we still frequently see them!

Here are two funny examples of SQL injection attacks :)

(Source: http://xkcd.com/327/)

You probably may have seen the following in chain emails..

Even though the above two examples look funny, SQL injection is a serious problem and no laughing matter. Attackers have exploited SQL injection vulnerability in order to cause substantial damage to many big companies including Wall Street, LinkedIn, Yahoo, etc. (Want to see a list of victims? Check SQL injection hall of shame link :)

From the attacks we see as recently as this month, it looks like SQL injection is not preventable or is it? It is totally preventable. Before we say how, let's look at from where such attacks may most likely to originate.

1. From your application (SQL queries executed from your application)
2. From within the database itself (stored procedures, which are stored in the database, executed from within the database)

From your application, how can we mitigate SQL injection attacks?
Let me first show you a vulnerable code snippet using Java as application language:

//UNSAFE CODE
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
float sal = 0.0;
Statement stmt = null;
String query = "SELECT salary FROM emp WHERE username = " + request.getParameter("user");
try { 
  stmt = conn.createStatement();
  ResultSet rs = stmt.executeQuery(query);
  if (rs.next()) {
    sal = rs.getFloat("salary");
  }
} catch (SQLException e) {
   throw e;
} finally {
   if (stmt != null) {
     stmt.close();
   }
}

As you can see in line 3, the user input for the form variable "user" is directly appended to the SQL query. If this input was something like "'scott'; DROP TABLE emp;", the database sever would execute two queries and the latter one would simply drop the emp table! So, how can we prevent such attacks? One option is to use parameterized queries (i.e. prepared statements) :

//SAFE CODE
1
2
3
4
5
6
7
8
9
PreparedStatement pstmt = null;
String query = "SELECT salary FROM emp WHERE username = ?";
String user = request.getParameter("user");
try { 
  pstmt = conn.preparedStatement(query);
  pstmt.setString(1, user);
  ResultSet rs = pstmt.executeQuery();
  ...
}

Notice that we are using prepared statements here. That's what makes you safe from SQL injection attacks. If an attacker inputs a malicious string as the variable "user", it will fail to return any value at line 7, as the sanitized input at line 6 does not match with any user name in the emp table. It should be noted that just because you use prepared statements, you are not safe! For example, you could use the same query string from the unsafe code example above as the input to the prepared statement in line 5 of the safe code example. Such an approach is still not safe due to simple string concatenation. Always use parameterized values!

If I use prepared statement, do I still need to do validation? Yes, definitely; always validate user inputs! Prepared statements help you prevent attackers from injecting code (so that you don't need to do sanitation on your own.), but it does not help with the semantics - that is, it does not check if the user entered the data in the format or domain you expect. For example, the expected values for one of the user input is 1 - 10 which is entered from a dropdown box in the browser. It is a good practice to validate the user input to make sure it falls within this range. You may think that .. why?, we have provided a dropdown; users cannot enter their own values? True in general, but you never know that an attacker could exploit a vulnerability in the client side and input a different value.


From within databases (stored procedures), how can we mitigate SQL injection attacks?
Similar rules apply to stored procedures. If you use dynamic SQLs (For example,  execute immediate query in Oracle PL/SQL), you should parameterize the query and use bind variables.)

//SAFE PL/SQL CODE WITH NO DYNAMIC SQL
1
2
3
4
5
6
7
CREATE OR REPLACE FUNCTION get_sal_safe(emp_name IN VARCHAR2) RETURN NUMBER IS
  salary emp.sal%type;
BEGIN
  SELECT sal INTO salary FROM emp WHERE ename = emp_name;
  RETURN salary;
END;
/

//UNSAFE PL/SQL CODE WITH DYNAMIC SQL
1
2
3
4
5
6
7
8
9
CREATE OR REPLACE FUNCTION get_sal_dynamic_unsafe(emp_name IN VARCHAR2) RETURN NUMBER IS
  salary emp.sal%type;
  query VARCHAR2(500);
BEGIN
  query := 'SELECT sal FROM emp WHERE ename = ''' || emp_name || '''';
  EXECUTE IMMEDIATE query INTO salary;
  RETURN salary;
END;
/
String concatenation is not safe. You must use bind variables instead as shown below.

//SAFE PL/SQL CODE  WITH DYNAMIC SQL
1
2
3
4
5
6
7
8
9
CREATE OR REPLACE FUNCTION get_sal_dynamic_safe(emp_name IN VARCHAR2) RETURN NUMBER IS
  salary emp.sal%type;
  query VARCHAR2(500);
BEGIN
  query := 'SELECT sal FROM emp WHERE ename = :1';
  EXECUTE IMMEDIATE query INTO salary USING emp_name;
  RETURN salary;
END;
/

There could be situations where you may not be able to update your code base to prepared statements or safe stored procedures. What can we do in such cases? This is where you may use sanitation. You simply escape special characters such as commas from user input before using as part of a query. All major database systems provide functions to escape strings so that you do not have to cook up your own method. One precautionary note though: unlike prepared statements or safe stored procedures, such escaping may not prevent certain sophisticated injection attacks. As a rule of thumb, when you develop new code, always use parameterized inputs (i.e. prepared statements and dynamic SQLs with binding); that will give you a peace of mind about your code!

Tuesday, July 29, 2014

Security is risk management

Security is all about risk management! With ever increasing complexity, it is not easy to protect everything. In fact, you are wasting your resources by trying to reach the elusive goal of protecting every asset (mainly data) in your organization. So, what can we do about the vast amount of data organizations possess? Organizations should first identify the risk associated with different data items. Depending on the level of risk, different risk mitigation techniques/security controls need to be utilized. It is easier said than done. Most organizations find it difficult to quantify the risk associated with their data. In general terms, if the more sensitive is the data and the more exposure it has, the more the risk associated with it. Higher risk items should be protected with more rigorous security controls. Let's take an analogy from real life. Where do you keep gold jewelries at home? You would not store your jewelries just the way you store your shoes, would you? You would be keeping them in a safe or hidden inside some layer in a difficult to access location in your bedroom. The following matrix gives an idea how to give a risk score to organization data:

(Source: Microsoft SQL Server 2012 Unleashed)

For example, organization's PR materials have low sensitivity and are publicly available. For such content, you would probably want to have minimal control to prevent unauthorized modification to content. Organization's employee payroll information, on the other hand, is highly sensitive and has very limited internal access to it. For such content, you would deploy rigorous security controls:  encryption on the wire and at rest data, limited read/write access, auditing, intrusion detection, etc.

Two key challenges in implementing a good risk management strategy in your organization are:
1. Identify where all organization's data reside (identify data)
2. Identify who has access to each data item

These two items sound simple, but in reality they are not. Most organizations struggle to have the full answer to them. Nevertheless, once you overcome these challenges, next critical step is to quantify the risk associated with each data item: what would be the impact if a given data item is compromised? The above matrix could be the starting point.

More on quantifying risk in a future post!


Friday, July 25, 2014

[Oracle Tips] Session (Login) user vs. Current user

When you connect to an Oracle database, during your session, you may have used privileges of multiple users behind the scene. Why not we always have a single user instead of multiple? Isn't there a security hole here? This post tries to explain the reason behind multiple users in a session. There are two important users at any given time during a session: session user and current user. These two identities may refer to the same user or two different users.

Session user - the user under which you log into the database. This user remains unchanged during the life time of the session. It is sometimes called the login user. You can access it via SYS_CONTEXT('USERENV', 'SESSION_USER'). So, if you connect to the database as Scott, the session user is Scott.

Current user - the user whose privileges are currently active. This user may change during the life time of the session. You can access it via SYS_CONTEXT('USERENV', 'CURRENT_USER'). For example, if user Scott is executing a definer's rights procedure foo which is owned Tim, during the time of execution, the current user is Tim and the session user remains unchanged as Scott. (When you execute a definer's rights procedure, the privileges of the owner of the procedure become active.)

The following is a simple example showing the above two users:

SQL> CONN / as SYSDBA
Connected.

SQL> CREATE USER u1 IDENTIFIED BY p1;
User created.

SQL> CREATE USER u2 IDENTIFIED BY p2;
User created.

SQL> GRANT CREATE SESSION TO u1;
Grant succeeded.

SQL> GRANT CREATE SESSION, CREATE PROCEDURE TO u2;
Grant succeeded.

SQL> CONN u2/p2
Connected.

We create a very simple definer's rights procedure in u2's schema. It simply outputs the current user and the session user:

SQL> CREATE PROCEDURE foo AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Session user is ' || SYS_CONTEXT('USERENV', 'SESSION_USER'));
  DBMS_OUTPUT.PUT_LINE('Current user is ' || SYS_CONTEXT('USERENV', 'CURRENT_USER'));
END;
/  2    3    4    5    6  

Procedure created.

SQL> GRANT EXECUTE ON foo to u1;
Grant succeeded.

SQL> CONN u1/p1
Connected.

SQL> SET SERVEROUTPUT ON

As you can see, at the time of log on, the current user and the session user is the same u1:

SQL> EXEC DBMS_OUTPUT.PUT_LINE('Session user is ' || SYS_CONTEXT('USERENV', 'SESSION_USER'));
Session user is U1

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_OUTPUT.PUT_LINE('Current user is ' || SYS_CONTEXT('USERENV', 'CURRENT_USER'));
Current user is U1

PL/SQL procedure successfully completed.

During the time of execution of u2. foo, we have two different users for session user and current user values. As you can see, the definer's rights procedure u2.foo is executed under the privileges of u2 even though u1 was running the procedure. If used correctly, you can use definer's right procedures to grant controlled access to database objects. (For example, a user might not have select privilege on a table, but you want to allow some computation on the table or show only a  selected set of rows of the table to the user. You can write a definer's rights procedure to restrict access.) At the same time, if used incorrectly, these definer's rights procedures may give users access to database objects that they are not supposed to access and further they may potentially misuse procedures which are executed as privileged users.

SQL> EXEC u2.foo;
Session user is U1
Current user is U2

PL/SQL procedure successfully completed.

Once the execution is over, the current user and session user become the same user u1:

SQL> EXEC DBMS_OUTPUT.PUT_LINE('Session user is ' || SYS_CONTEXT('USERENV', 'SESSION_USER'));
Session user is U1

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_OUTPUT.PUT_LINE('Current user is ' || SYS_CONTEXT('USERENV', 'CURRENT_USER'));
Current user is U1

PL/SQL procedure successfully completed.

Thursday, July 24, 2014

[Oracle Tips] How to find out if a trace event is enabled or disabled?

You can use dbms_system.read_ev procedure to find the event status. If the level is zero, the event is disabled, otherwise, enabled. The following wrapper function makes it easier to use this procedure:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
create or replace function is_event_enabled(event IN INTEGER) 
return VARCHAR2 is
  level binary_integer;
begin
  dbms_system.read_ev(event, level);
  IF (level > 0) THEN
    return 'Y';
  END IF;
  return 'N';
end;
/

The following example shows a simple use case:
SQL> ALTER SYSTEM SET EVENTS '1031 trace name context forever, level 16';

System altered.

SQL> select is_event_enabled(1031) from dual;

IS_EVENT_ENABLED(1031)
----------------------
Y


SQL> ALTER SYSTEM SET EVENTS '1031 trace name context off';

System altered.

SQL> select is_event_enabled(1031) from dual;

IS_EVENT_ENABLED(1031)
----------------------
N

Wednesday, July 23, 2014

Is Encryption a Silver Bullet?

You have probably heard that "encrypt all your data so that even if an attacker gets their hands on your data, they won't be able to see your plaintext data as they cannot decrypt it". So, encryption is a silver bullet that protects your enterprise data from attackers of all walks, isn't it? Not really! Encryption do help in certain scenarios but there are many other cases where it may not fully protect your data. For example, if your goal is to protect data against direct access to data from the physical media, then encryption works if the keys are stored elsewhere.

Figure: Encryption works to prevent direct access to disk data

What most people forget is that most of the encrypted data is available in plaintext to database systems (if transparent encryption is used) and applications. If an attacker can get access to the database or application, they get access to plaintext data. The strongest encryption algorithm or the key for that matter won't help in that case.

Figure: Encryption fails as attacker hacked into the database server which has access to plaintext data

Figure: Encryption fails again as attacker gained access to the application server which sees plaintext data

So, if encryption won't fully help to protect your data, what can we do to improve protection? Obviously, we need to use defense in depth and we need to make it harder for attackers to gain access to application or database server. Application firewalls and database firewalls can help.

What if attackers penetrate the firewalls and gain access to application? One way to contain/control the damage is to always use a least privileged database user in the application so that the attacker has access only to limited objects in the database. Hence, the damage they can do is limited.

Another control mechanism is to make only the least information available for the application. For example, if the application requires two columns of a table, instead of using the full table, you can limit to the two columns only. That way, if the attacker does not have access to the database directly, they can only steal limited amount of data. One step further is to dynamically mask (redact) data before sending to the application if the full data is not necessary for the application functionality. For example, if the application just displays the credit card number, instead of sending the full 16 digit credit card number, masked number with the last four digit could be sent to the application.

Let me tell you one last, but not the least, brilliant idea. Delete or archive the data that you don't use. If there is no data, there is nothing to steal!

What about insiders who misuse the applications or databases .. and also targeted insiders (i.e. insiders whose credentials are stolen by attackers to avoid perimeter security)? Granting least privilege can help, but this where you need to have detection mechanisms (auditing and monitoring) in place. Behavioral anomaly detection could help to stop such attacks and remove the malicious or targeted insiders from the system.

The bottom line is encryption is not the cure for all your security threats. You've got to strengthen the security at the points where the plaintext is readily available. Always grant least privileges to application and database users. Deploy firewalls and detection systems. Extensively audit to make users accountable for what they do. Educate users how not be the target of phishing attacks which gives easy access to your systems to attackers. With all these sophisticated attacks we see day in and day out, defense in depth is the way to go!

Thursday, July 10, 2014

Oracle Scheduler Security - Part 1

Similar to cron jobs in Linux, you can schedule jobs in Oracle database in order to execute it once or periodically. (You can actually manage jobs, schedules, programs, credentials using dbms_scheduler package. In this post, we will focus only on jobs.) As a general security guideline, it is a best practice to grant the minimum privileges required in order to get the work done (aka principal of least privilege). Granting excessive privileges could lead to misuse/unauthorized use of the system by privileged users (aka insider attackers). Further, if an attacker gains access to an account with excessive privileges, they could do serious damages to the system. This post focuses on the risks of excessive scheduler privileges and techniques to mitigate/reduce such risks.

CREATE JOB or CREATE ANY JOB?
Users who need to schedule jobs should be granted CREATE JOB privilege whereas users who need to administer (create, start, stop, etc.) scheduler jobs should be granted CREATE ANY JOB privilege. CREATE JOB privilege allows to create jobs only in the users schema. For example, if bob is granted CREATE JOB privilege, he can create any number of jobs on his schema, but he cannot create jobs in some other schema. CREATE ANY JOB is a very powerful privilege. It allows to create jobs in any scheme other than SYS. Care should be taken when granting CREATE ANY JOB as it can potentially be misused to escalate privileges. For example, a user, who is granted CREATE ANY JOB privilege,  can create a job in the SYSTEM schema and grant excessive privilege to himself. Please note when you create a job in a schema X, it is run as user X (as Oracle does not have distinction between schema and user).

How can we exploit CREATE ANY JOB privilege? Let me show you a very basic example of how it can be exploited to escalate privileges:
We connect as SYS and create user u1:
SQL> conn / as sysdba
Connected.
SQL> create user u1 identified by p1;
User created.

Grant CREATE SESSION privilege to u1 so that it can login to the database:
SQL> grant create session to u1;
Grant succeeded.

Now let's check what privileges and roles are granted to u1:. As you can see, there are no roles currently granted to u1:

SQL> select granted_role from dba_role_privs where grantee = 'U1';
no rows selected
SQL> select privilege from dba_sys_privs where grantee = 'U1';

PRIVILEGE
----------------------------------------
CREATE SESSION

Now SYS grants excessive privilege CREATE ANY JOB to u1:
SQL> grant create any job to u1;
Grant succeeded.

Now Let's see how u1 misuses this powerful privilege to grant himself the DBA role which very powerful.
SQL> conn u1/p1
Connected.
SQL> select granted_role from user_role_privs where username = 'U1';
no rows selected

u1 creates the following job in SYSTEM schema so that he can execute the job as SYSTEM user. This job simply executes a PL/SQL block to grant DBA role to himself!
SQL> begin
  2    dbms_scheduler.create_job (
  3      job_name     => 'system.exploit',
  4      job_type     => 'PLSQL_BLOCK',
  5      job_action   => 'begin execute immediate ''grant dba to u1''; end;'
  6    );
  7  
  8    dbms_scheduler.run_job (
  9      job_name     => 'system.exploit'
 10    );
 11  end;
 12  /
PL/SQL procedure successfully completed.

Now let's check what roles u1 has. As you can see, now u1 has the DBA role!
SQL> select granted_role from user_role_privs where username = 'U1';

GRANTED_ROLE
--------------------------------------------------------------------------------
DBA

Doing the clean up for this simple test:
SQL> conn / as sysdba
Connected.
SQL> drop user u1 cascade;
User dropped.
SQL> exec dbms_scheduler.drop_job('system.exploit');
PL/SQL procedure successfully completed.

As you can see, granting CREATE ANY JOB privilege can have dire consequences. Therefore, it should be granted to only those who absolutely needs it to perform their organizational duties.

It is a good practice to audit these privileged users in order to identify misuses and also to act as deterrence to act maliciously.

CREATE ANY JOB allows to create jobs in any schema. CREATE JOB allows to create jobs only in the user's schema. What if there are many DBA's in an organization and we want to allow each DBA to administer jobs in a set of schemas (not all)? How can a security administrator enforce such a requirement? Hmm.. Oracle does not provide schema level privileges by default, but you can use Database Vault security option to do that! After granting CREATE ANY JOB privilege to users, the Database Vault administrator can authorize only a selected set of schemas on which each user can administer jobs using the API DBMS_MACADM.AUTHORIZE_SCHEDULER_USER(username, schema_name).

There is more to scheduler jobs. We will be looking at running external programs as scheduler jobs and also about scheduler object attributes in another post.