In a previous post I have noted that ultimately SQL Injection is a database attack through a web application proxy, and that the Database and the associated Database Administrators need to play a larger part in the defense of data and applications. I recommended a couple steps to assist in combating attacks through the use of stored procedures to help in input parameter validation. I also want to make additional recommendations in the areas of separation of duties and compartmentalization of functions.
Most of the relational database platforms now provide the ability to have more than one DBA role. This is typically accomplished by removal of the single all-powerful DBA user, and separating the DBA functions into specific accounts, with each assigned a distinct role like backup & recovery or user setup. The goal obviously is to limit the scope of damage should any single account be compromised, promote more granular auditing, and help prevent the type of abuse that happened with FIS. I find many large corporations are in fact moving to this model. Which leads me to my first point- that I have not seen this change within the application development community, to use databases to compartmentalize functions and users.
I was reading a post on SQL Injection Attacks over on the Vulnerability Research and Defense blog a couple days back. On their continuing thread of advice on how to address SQL Injection, they recommend IT and Database Administrators take steps to help prevent SQL Injection. Specifically, review IIS logs for signs of attack, consult your ISV on potential vulnerabilities of your 3rd party code, and validate that the accounts have the ‘least privilege’ needed to perform the work. While I have no disagreement with any of these items per se, I think it misses the point. I want to use this to illustrate the issue of perspective, and suggest a change in thinking that needs to happen here.
Most applications perform all database activities under a single database user.
This is a problem in that a database administrator is supposed to apply the concept of least privilege to the database user and group, but that single generic database user performs every application function. Application of the least privilege concept in this context is almost meaningless. Limiting the features or the scope of access available is just as important. Think about this as separation of duties, so that the scope of what is possible through the web is restricted.
The application developer must take some steps to assist in this area by reducing functional scope for individual users. Any web application that uses a database establishes a trusted connection to that database regardless of whether it is ASP or JSP or whatever. Ultimately, a SQL Injection attack is the user of the web application, exploiting that trust relationship between the application and the database to their advantage by piggy-backing code onto the legitimate access. I don’t want to say that if you are considering ‘least privilege’ to assess risk you have already lost the battle, but this really should be done in the design phase as well as with periodic reviews of the system.
Collaborate with Database Administrators and Architects (Or stop treating the database like a black box)
They say if your only tool is a hammer, everything begins to look like a nail. That accurately describes many of the web application developers I have worked with in the last 10 years. They attempt to provide all of the functionality for their application within their application and use the database as a simple repository to store, sort and report data. In reality database engines like Oracle, MS SQL Server, and DB2 are extraordinarily feature rich applications and, in data processing related activities, provide more advanced processing capabilities. Yet I still find application developers writing tools, functions and utilities that would be better served being in the database itself.
So separation of duties in the processing environment is a good idea, where different programs or different roles within those programs provide different pieces of functionality. Siloed, if you will. So is constant collaboration between application developers and database administrators, designers and programmers. Smaller, dedicated pieces of code are easier to review. And this is being driven not just by PCI, but also by more modern development processes and QA strategies.
In the next post I want to comment on trust relationships and distributed application use of databases.
Reader interactions
2 Replies to “Separation of Duties/Functions & SQL Injection”
I have been wishing for some time that we would not have these generic ids. I have one system, I designed but I do not know how it works, in which the user enters a password that is the DB password. The DB is aware of who is updating it. And the DB restricts their access. I understand how it is easier for the app to have a generic id, my one app proves that it does not have to be that way … but why is this so difficult and what is the best way to link the web user to a db account?
[…] Lane writes in his blog entry about separation of duties on the application level. While I agree with his sentiments I also know […]