How to Prove That Your SmartDB App Is Secure

If you are guarding your data behind a hard shell PL/SQL API as Bryn Llewellyn, Toon Koppelaars and others recommend, then it should be quite easy to prove, that your PL/SQL application is secured against SQL injection attacks. The basic idea is 1) that you do not expose data via tables nor views to Oracle users used in the middle-tier, by end-users and in the GUI; and 2) that you use only static SQL within PL/SQL packages. By following these two rules, you ensure that only SQL statements with bind variables are used in your application, making the injection of unwanted SQL fragments impossible. In this blog post I show how to check if an application is complying to these two rules.

Demo Applications

I’ve prepared three tiny demo applications to visualise what guarding data behind a hard shell PL/SQL API means and how static and dynamic SQL can be used within Oracle Database 12c Release 2 (12.2). You may install these applications using this script.

 

Rule 1) Do not expose data via tables nor views

The idea behind a hard shell PL/SQL is to expose data through PL/SQL units only. Direct access to tables or views are unwanted. The Oracle users configured in the connection pool of the middle tier, need the CONNECT role and EXECUTE rights on the PL/SQL API only. These rights may be granted directly or indirectly via various levels of Oracle roles.

The following query shows if an Oracle database user is ready to be used in the middle tier application. Oracle users maintained by Oracle itself, such as SYS, SYSTEM, SYSAUX, etc. are excluded along with some other users which grant objects to PUBLIC (see line 35 to 38). To execute this query you need the SELECT_CATALOG_ROLE.

Just THE_GOOD_USER and THE_BAD_USER are ready to be used in the middle tier. To see the issues of all other users you may simply change the main part of the query as follows:

THE_UGLY_USER has access to table T owned by THE_UGLY_DATA. This violates clearly rule 1.

However, it is important to note, that we have excluded all Oracle maintained users from the analysis. So let’s have a look at all the tables and views granted to PUBLIC by the Oracle maintained users.

Even THE_GOOD_USER has access to 2317 views and tables. To reduce this number we have to uninstall some components, but that’s just a drop in the ocean. There is currently no way to create on Oracle user without access to views and tables. Hence we just have to focus on our application and our data.

 

Rule 2) Use only static SQL within PL/SQL

If you use just static SQL in your PL/SQL units, then no SQL injection is possible. The absence of dynamic SQL proves that your application is secured against SQL injection attacks. Of course, there are good reasons for dynamic SQL. But proving that a dynamic SQL is not injectable is difficult. Checking for the absence of dynamic SQL is the simpler approach, even if it is not that easy as I’ve initially thought.

In How to write SQL injection proof PL/SQL the following ways are mentioned to implement dynamic SQL:

  • native dynamic SQL (EXECUTE IMMEDIATE)
  • DBMS_SQL.EXECUTE
  • DBMS_SQL.PARSE
  • DBMS_UTILITY.EXEC_DDL_STATEMENT
  • DBMS_DDL.CREATE_WRAPPED
  • DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE
  • DBMS_HS_PASSTHROUGH.PARSE
  • OWA_UTIL.BIND_VARIABLES
  • OWA_UTIL.LISTPRINT
  • OWA_UTIL.TABLEPRINT

But there are more ways to execute dynamic SQL, such as:

  • Open-For Statement
  • Java Stored Procedure
  • DBMS_SYS_SQL.EXECUTE
  • DBMS_SYS_SQL.PARSE
  • DBMS_SYS_SQL.PARSE_AS_USER

It’s difficult to get a complete list, due to the fact the the Oracle-supplied subprograms are wrapped and often the SQL statement is hidden behind a C API. That leaves us basically two options

  1. Use a list of Oracle-supplied packages and/or subprograms to identify dynamic SQL, even if the list might be incomplete
  2. Suspect that any Oracle-supplied subprogram may contain dynamic SQL, except some trusted packages such as “DBMS_STANDARD” and “STANDARD”

Both options are not very appealing. But I’m in favour of option 1. At a certain point I have to focus on my application code and assume/trust, that the Oracle supplied packages are doing their part to reduce the risk of SQL injection.

The following object types may contain PL/SQL and have to be checked for dynamic SQL:

  • FUNCTION
  • PROCEDURE
  • PACKAGE BODY
  • TYPE BODY
  • TRIGGER

What if we call services outside of the database via REST calls or via AQ messages? I think we may ignore these cases. They are not part of this application anymore and even if the services call this database, they have to go through the hard shell, and these are PL/SQL units already covered.

We need PL/Scope metadata for some checks. The following anonymous PL/SQL block produces these data. Be aware that the application code and some SYS objects are compiled. Invalid, dependent objects will be recompiled at the end. Nonetheless, you should not run this code in your production environment.

Here’s the query to check application users.

To see the issues of THE_BAD_API you may simply change the main part of the query as follows:

To suspect that every JAVA CLASS uses SQL is not a very differentiated analysis result. Further Java specific code analysis are necessary. However, the other results are reasonable.

 

Conclusion

In this blog post I showed how to prove, that a PL/SQL application does not use dynamic SQL and therefore is secured against SQL injection attacks.

The use of dynamic SQL is automatically reduced in PL/SQL due to the fact that it is easier and more efficient for a developer to deal with compile errors than runtime errors. But there are cases when static SQL is not possible or not efficient enough. In those cases proper input validation is a necessity to mitigate the SQL injection risk (see also “Ensuring the safety of a SQL literal” in How to write SQL injection proof PL/SQL).

Views are often part of the API in applications I’m involved in. I like the power and the flexibility of these views. In fact I’m very grateful that the Oracle database provides a view API for its data dictionary, which simplified the analysis for this blog post. But views come with a SQL injection risk. Moreover the risk and responsibility is delegated to a certain extend to the developers using the database API. Hence, in the future I will think twice before making views part of the API, but I will for sure not rule them out per se.

2 Comments

  1. Apoorv Jain says:

    Hi This is quite interesting and informative…but I am not sure how it will work with MVC architecture for ADF or other applications?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.