On More-Secure Applications

Our technologist shows how to build security into application design.

May/June 2015

I’m worried about the security of my application—things like SQL injection, for example. What can I do to minimize the chances that my application will be hacked?

This is a great question, because not a day seems to go by without news of yet another hack. Whether it be someone stealing identities, credit card information, personal information, or whatever, new security incidents seem to happen often. Too often.

There are a few things you can do in your application design to eliminate or reduce your exposure. Securing an application is something that needs to be done as the application is being developed—it is very hard to retrofit security into an existing application. Trying to fix an existing application to be secure is sort of like trying to patch a leaky foundation of a house rather than building a waterproof foundation in the first place.

Here are some of the most important things you can do for your application design architecturally:

• Make sure you have read the Database 2 Day + Security Guide and the Database Security Guide. They will give you an overview of what you need to be thinking about security-wise and an excellent look into the capabilities Oracle Database offers in the area of security.
• Employ the concept of least privilege.
• Use multiple schemas—many more than one—to separate objects and help enforce the concept of least privilege.
• Use bind variables! They are not only a scalability and performance feature; they also help secure your application from SQL injection attacks.
• Employ multiple levels of defense. Do not put security only in the application code; repeat it as many times as you can within the database, using different techniques. In that way, a bug in one layer of defense won’t leave your database exposed.

Read on for details of some of these security strategies.

This is a key tenet of database security: grant the fewest (least) privileges possible to everyone—from your DBAs down to the application schemas and out to the schemas used to connect to the database from the middle tier.

All too often, application developers request a privilege in the database simply to make their lives easier. For example, if they are working on an application that requires data from other application schemas—from many tables in many other schemas—they might request the SELECT ANY TABLE privilege. With that privilege, no matter what table they need from those other schemas, they will have it. The application developers might feel that it makes them more “agile”—able to pump out code faster—because they never have to ask for a SELECT grant again.

If attackers can find a SQL injection flaw in the developed application, they will almost certainly be able to gain at least read access to everything in the database—not just the tables the application accesses but every single table in the entire database.

The SELECT ANY TABLE privilege will also make it very hard to survive a true security audit. There will be no way to justify why the application truly needs SELECT ANY TABLE privileges. Additionally, there will be no documentation for the tables the application truly needs.

No ANY grant should ever be given to an application schema. The power of a grant with the ANY keyword in it—such as CREATE ANY CONTEXT, SELECT ANY TABLE, DROP ANY TABLE—is beyond what any application needs. There is always another way for developers to achieve what they need to do.

For example, I’ve seen DROP ANY TABLE granted to an application schema with the reasoning that the application developers needed to truncate a table in another schema. In reference to truncating a table, the Database SQL Language Reference, states: “To truncate a table, the table must be in your schema or you must have the DROP ANY TABLE system privilege.”

That is true, but you do not need to have the DROP ANY TABLE privilege to achieve the goal of truncating a table in another schema. That is what’s important—the goal is to truncate table T in schema X. There are at least two ways to achieve that:

1. Use the powerful and dangerous DROP ANY TABLE privilege.
2. Implement a stored procedure that executes as schema X (the owner of the table) and performs the truncate. And then grant EXECUTE privileges on this procedure.

If you were to grant DROP ANY TABLE to the application schema and an attacker discovered a SQL injection flaw in the application, the attacker would have the DROP ANY TABLE privilege. Think about how damaging that would be!

The other approach, achieving the goal with the minimum privileges—with the least privileges—is the right way to go. Consider the following:

SQL> create user a identified by a;
User created.
SQL> create user b identified by b
  2  default tablespace users
  3  quota 5m on users;
User created.
SQL> grant create session to a;
Grant succeeded.
SQL> grant create session,
  2        create table,
  3        create procedure
  4  to b;
Grant succeeded.

I now have two schemas—A and B. A has just the privilege to log in, and B can log in and create tables and procedures. Now I’ll log in as B and create my objects:

SQL> connect b/b
Connected.
SQL> create table t
  2  as
  3  select *
  4    from all_users;
Table created.
SQL> create or replace
  2  procedure truncate_table_t
  3  authid DEFINER
  4  as
  5  begin
  6      execute immediate
  7        'truncate table B.T';
  8  end;
  9  /
Procedure created.
SQL> grant select on t to a;
Grant succeeded.
SQL> grant execute
  2    on truncate_table_t
  3  to a;
Grant succeeded.

Schema B now has a table T with some data in it and also a definer’s rights procedure that truncates table B.T. A definer’s rights routine (the default type of stored procedure) runs with the privileges granted directly to the owner of the procedure—that is, all the privileges of schema B minus any privileges granted to B via a role. Schema B allows schema A to read table T and to execute the stored procedure B.TRUNCATE_TABLE_T.

I’ll log in as A and see what I can do:

SQL> connect a/a
Connected.
SQL> select count(*) from b.t;
  COUNT(*)
------------
       55

I can see that table B.T exists, I can query it, and it has data. Now I’ll try to truncate table B.T as user A:

SQL> truncate table b.t;
truncate table b.t
                 *
ERROR at line 1:
ORA-01031: insufficient privileges

I am not privileged enough to truncate this table. For that truncate to succeed as executed by A, I would need the DROP ANY TABLE privilege. But that doesn’t mean I need to have the DROP ANY TABLE privilege in order to truncate B.T! I can just execute that stored procedure:

SQL> exec b.truncate_table_t;
PL/SQL procedure successfully completed.
SQL> select count(*) from b.t;
  COUNT(*)
--------------
        0

I have achieved the goal—to truncate B.T—but did not require the DROP ANY TABLE privilege. I have greatly limited the exposure to risk, but I have not eliminated it. An attacker finding a SQL injection bug in code executed by schema A would likely be able to execute the B.TRUNCATE_TABLE_T procedure, but I’ve still achieved a huge reduction in exposure. I’ve gone from risking the loss of every table in the database to the loss of data in one table, a table that is truncated on a recurring basis already.

Using stored procedures is a great way to reduce the strength of a grant you need to give across schemas. They definitely help achieve the least privileges concept. Here schema A needs the EXECUTE privilege only on a procedure that can truncate exactly the one table that A needs.

NOTE: Oracle Database 12c includes a new privilege analysis tool to help enforce the concept of least privileges. See the Database Vault Administrator’s Guide, for details.

This idea probably gets more pushback from developers than any other security idea I suggest. I’m going to reproduce a question from a previous Ask Tom column:

A data architect at work has proposed that we start using separate database accounts to hold the code (packages, procedures, views, and so on) and the data (tables, materialized views, indexes, and so on) for an application. I’ve never come across this idea before, and it seems to be contrary to the concepts of encapsulation, in that the application will be spread across at least two schemas and require more administrative overhead to maintain the necessary grants between them.

Are there any situations you can think of where this would be a recommended approach? And if you did this, how would you recommend referencing objects in the data schema from the application schema? Finally, would you put any views into the code or data schema?

You can see my original response to this question at bit.ly/asktommultischema, but in looking at this question again, I can see that the questioner is trying to find reasons to not do something that would be greatly beneficial to security. Developers may throw out words such as encapsulation (although having multiple schemas actually promotes encapsulation) and claim that it will require more administrative overhead to maintain the necessary grants, while missing the point that the production application will need to have the concept of least privileges in place. What some developers view as drawbacks, I see as positives.

My approach would be to have at least one schema that contains table data, and maybe more than one—probably more than one—but at least one schema that owns just the table data and, if need be, a few procedures like the one described in the last section. There would be a second schema, and this schema would own code (PL/SQL, Java stored procedures, and so on) that accesses these tables. It would also contain views of the various tables as needed. The first schema, the one that contains table data, would grant just the privileges needed on a table to the second, “code” schema. (There would be no GRANT ALL ON T TO another_schema.) The data schema would grant just the access necessary: INSERT, UPDATE, DELETE, and/or SELECT.

Then there would be a third schema. This schema would be granted nothing more than CREATE SESSION to log in and the bare privileges on the second schema the application needs in order to execute the procedures and access the views. This third schema, the database account, is the one your application server would use to connect to the database.

Think about the benefits this would bring you. If hackers get into the application schema, the damage they can do will be very limited. They won’t be able to read every table—they’ll be able to read only a few. And if you use stored procedures as a data access layer, they may not be able to access any tables at all! All they’ll be able to do is run your application. They won’t be able to drop any tables, which they would be able to do if you used a single schema for everything, or update anything they choose, as they would be able to if you used a single schema. And so on. Hackers will be very restricted in what they can and cannot do.

Let’s make this a bit more concrete. Suppose your application has an application audit trail (as it and every application should). Your typical application user needs to be able to insert into this audit trail, but that user should never be able to read it, delete it, or modify it. You might also have an administrative application that needs to read the audit trail, but it doesn’t ever need to insert into it, update it, or delete from it. If you go with a single schema, both the application and the administrative application users will have full READ/WRITE access on this table. You might say, “Our application enforces security—don’t worry.” But that does worry me, because you will have a bug in your application—somewhere, someday. And then the audit trail will be 100 percent exposed to tampering.

If instead you put the audit trail into its own schema and create two code schemas—one for the typical application user and the other for the typical administrative application user, you’ll be able to grant INSERT privileges on the audit trail table to the first code schema and SELECT privileges on the audit trail to the second code schema. Now the first schema can create the code that inserts into the audit trail. The second schema can create some views for reporting or use stored procedures that return ref cursors instead.

Last, you’ll create a schema that has CREATE SESSION and EXECUTE privileges on the code in the first application schema and then create an administrative login that has CREATE SESSION and EXECUTE privileges on the code in the second schema. This is the concept of least privileges put into action to the fullest. The administrative schema will use code in the application schema to audit itself and will be able to report on—but not modify—the audit trail. The application schema will also be able to audit itself but not read the audit trail (because it has no reason to).

To witness this multischema architecture idea in action—with all the details, code, and more—see the Database 2 Day Developer’s Guide, Chapter 9, “Developing a Simple Oracle Database Application.”

Did you know that if your SQL uses bind variables for all variables that can change from execution to execution, your code cannot be SQL-injected? On the other hand, if you use string concatenation to put these variables into your SQL, your code can be SQL-injected!

That is, if you issue SQL such as SELECT * FROM EMP WHERE ENAME LIKE ? and you bind in a value for the ?, no one will be able to change the meaning of your SQL, regardless of what they send you. On the other hand, if you build your SQL statement by using string concatenation like this:

SELECT * FROM EMP WHERE ENAME 
LIKE '" + some_variable +"'

it will be far too easy for your code to be SQL-injected.

In my experience, many, if not most, database attacks are performed by SQL injection, whereby the attacker sends you input that makes your resulting SQL different from what you intended. There are programmatic ways to combat this. For example, you can use the DBMS_ASSERT package in PL/SQL when building SQL, write your own “sanitizer” routines to verify that the inputs are safe to concatenate, and write lots of code. You’ll still have to worry about attack vectors you haven’t thought of (see bit.ly/tkbinject for an interesting example of a SQL injection attack most people would not see coming). So whatever programmatic strategy you use, there will still be concern that your code is not as secure as you think it is.

Or you can use bind variables. If you use a bind variable, it will be impossible—repeat, impossible—for an attacker to change SELECT * FROM EMP WHERE ENAME LIKE ? into any other SQL. On the other hand, it would be relatively easy for an attacker to try to change

SELECT * FROM EMP WHERE ENAME 
LIKE '" + some_variable +"' 

into

SELECT * FROM EMP WHERE ENAME 
LIKE '' or 1=1 – ' 

by providing the input

' or 1=1 – 

That input would change the meaning of your query entirely. Additionally, attackers might instead try to input

'UNION ALL SELECT… FROM T – ' 

Think about what that would do to your query. Instead of querying the EMP table, your attackers would now be querying some other table T (a SQL injection bug, once found, typically gives at least READ access to every object the schema has read access to).

If you do not use bind variables in your application for inputs into your query, I firmly believe you’ll have to

• Write lots of additional procedural code to sanitize inputs (and lose sleep every night wondering if you did it perfectly every time and everywhere).
• Submit your code to be reviewed by at least five people who do not like you. The reason for the “do not like you” part is that they must be motivated to search long and hard for any mistakes you might have made. If they like you—or even worse, respect you—they might not look hard enough.

But following these steps will not guarantee security. Your code may still be SQL-injectable, because it might not be perfect and the reviewers might not find everything.

Remember: bugs happen to everyone. Bugs, including ones that allow for SQL injection, happen to me more times than I can count. Consider the article I wrote years ago on SQL injection. After you read the section on SQL injection in that article, I encourage you to read on and look at the last section. There I used a stored procedure to do “selective granting”—similar to the truncate example earlier in this article. But note the “note” there about revised content. My original stored procedure—the one that was printed in the hard-copy magazine, never to be fixed—had a SQL injection flaw in it! Yes, in an article on SQL injection, I supplied some code that was SQL-injectable. It can happen to anyone—highly experienced programmers, novice programmers . . . everyone.

Having multiple levels of defense is another basic security tenet, right up there with the least privileges concept. You want to have security in depth—security at multiple levels.

Suppose you put all your security logic in the application, so the folks at the network/database/storage level don’t have to worry about anything. Someone will find a way around that security. It is not if but a matter of when attackers will find a way around it.

If, on the other hand, you have multiple layers of defense—multiple repetitive layers of defense—a hole in any one defense level won’t mean that your data will be compromised. For example, suppose for some reason that your application uses string concatenation and does not use bind variables. In that case, I would suggest that you procedurally sanitize your application inputs to validate them. Have your string concatenation code reviewed so that multiple eyes look at it to validate it.

• Employ Oracle Database Firewall to catch SQL injection flaws when they inevitably occur (from not using bind variables!).
• Use the concept of least privileges so that if all other defenses fail, you’ll minimize your risk.
• Use multiple schemas to further mitigate the security risk (and take least privileges to the farthest point possible).

• Employ auditing at the application level, firewall level, and database level; consider using Oracle Audit Vault to consolidate all that information; and set up real-time audit policies that look for suspicious activity as it happens.

There are at least six levels of defense right there, but each of those layers might have a flaw in it somewhere—a hole to be exploited. Use multiple layers of defense in case one—or more—of them is defeated.

Security is a #1 concern these days. In the past—before the internet—security was a bit easier. Our databases were not exposed to billions of potential attackers and didn’t have as much sensitive information in them. Today a child could attack your database just for fun (search for “sql injection toolkit,” and you might be surprised at what is out there). Attacking a website is not hard.

Fortunately, protecting yourself and minimizing your exposure is not that hard either.

Employ least privileges—yes, that seems like more work for the development team, but look at what you get out of it: minimized exposure, better documentation, and a solid understanding of who uses what objects and why. It is not only a security feature; it also makes your entire code base better, easier to maintain, and easier to understand.

Use encapsulation and modularization via multiple schemas to set up “walls” between various components. Again, use least privileges to put the pieces together.

Avoid the major attack vectors such as SQL injection entirely by using bind variables. Bind variables are not only good for performance and scalability but also excellent for security.

Employ as many layers of defense as you can come up with. They are not redundant, so do not consider them redundant. They each add to your security footprint, in a positive fashion.

And perhaps most importantly, remember to design this all into your application from day 1. Trying to retrofit least privileges and multiple schemas—and fixing code that doesn’t use bind variables so that it does use bind variables—is not only hard but also error-prone. It would be like working on a leaky foundation.