Secure Coding and Design

Secure Coding and Design You need to design your system to minimize the paths to get to data, keeping only the paths that are needed. As the number of paths to your data increases, your attack surface increases. You also need to separate your data from your code. People have been putting all their code and data in one common schema for more than 30 years now, which is a problem. Additionally, a common problem is when application servers connect to the database as the schema owner. Your secure design and coding practices should always separate data from code and use the PL/SQL features that enforce secure access paths to the data. In 1988, Oracle made PL/SQL available in Oracle version 6. Yes, some of us still remember using the first version of PL/SQL. In 1992, Oracle provided the ability to create stored PL/SQL procedures and triggers in Oracle version 7. The problem is, many people are still designing database systems like it was 1992. The designs we see in our day-to-day work typically fall under one of three paradigms, all of which have some security concerns. 198 Problematic Designs Figure 6-1 is by far the most common design paradigm and problem we see in our work. The data and PL/SQL code coexists in one schema, and the user is granted execute privileges on the PL/SQL, along with select, update, insert, and delete privileges on data objects. By default, when you create a procedure, package, or function, it is created with definer’s rights, meaning the code executes with the same privileges as the schema it resides in. Because the code resides in the same schema as the tables, the code has full access to all the data in the schema. Do you see the problem here? All it takes is one SQL injection bug, and the bad guy owns your database. Data and Code User Figure 6-1. All data and code are stored in a common database schema Figure 6-2 shows the next most common design paradigm and problem that we see in our work. The data is stored in the database, and all the application code including SQL Data Manipulation Language (DML) and Data Definition Language (DML) resides outside of the database in Java, PHP, or Python. The application server connects to the database as the schema owner, so the user does not need to be granted access to the underlying data objects. Again, all it takes is one SQL injection bug, and the bad guy owns your databases. Another problem with this configuration is that the security of the application is left up to the application code. Chapter 6 SeCure Coding and deSign 199 As we all know, code has bugs, and this configuration does not leverage the security mechanisms built into the database. In addition, this configuration increases the size of the attack surface. The SQL code that is stored in the application code is frequently generated using frameworks that do not optimize the amount of data that is transmitted over the network. USER Data App Server. Business Logic Figure 6-2. All application logic including DDL and DML is stored outside the database Figure 6-3 shows another common problem we see. Data and code are stored in a database schema, and some SQL is stored outside of the database in Java, PHP, or Python. The SQL is either generated by a framework or handwritten into the code. This configuration has a few problems with it. Let’s start with SQL that is generated by a framework. You will suffer performance issues, because the SQL generated by the framework will cause excessive network traffic, and you’ll likely also be dealing with the SQL having to recompile every time it sends a request to the database. These are two big performance issues. Then we’re back to the SQL injection issue; if the bad guy finds a SQL injection bug, he owns your database. Because there is more network traffic than required, the attack surface has been increased. Chapter 6 SeCure Coding and deSign 200 Improved Design Now we’ll discuss what we need to do to implement a trusted path to the data. The first thing we need to do is separate the data and PL/SQL code into different schemas. Your business logic code calls the API that performs the required DML. This is all done inside the database, minimizing network round-trips and thereby reducing the attack surface and improving application performance. In Figure 6-4 we’ve separated the code from the data, executed the code using code-based access control (CBAC), and granted roles to the code. By doing these simple steps, if we have a SQL injection bug in one API, we’re not exposing all of the data to the bad guys. This reduces the attack surface quite a bit. Additional advantages of this design paradigm are improved performance and ease of maintenance.