This month, we’re going to talk about security. Maintaining good security in our applications is extremely important. If you’ve read any of the recent reports of credit card theft, identify theft, or just plain data theft, then you have a glimmer of why it is so important. But keeping things secure is a lot of work and sometimes a major pain. There are a lot of things to worry about so rather than make a list I’d like to take an idea and use it as a theme.
In this article, the key idea you should come away with is that you can’t trust me. The me in this case is any person using your applications or databases. Here are some ways that we’ve been known to trust our users and the security holes that result from that trust.
Production Job User
In mainframe environments, it is fairly common to have a job scheduling subsystem. To ensure the production jobs are able to execute all programs, there was a time when the userid assigned to those jobs might have been granted SYSADM privileges. Hopefully, we all know that SYSADM privileges allow a userid to execute all packages making it a very useful privilege level for executing our batch jobs. Unfortunately, it also gives that userid a whole lot of other access privileges along with it. In a system like this, I can (as a malicious user) effectively execute any SQL statement I’d like. The only requirement is that I be able to implement a production job and have the scheduling system run it. That’s a relatively low bar to hurdle in order to gain a lot of access. I just have to get a job as an application developer or production control analyst.
Are you trusting your programmers to implement only the things they are supposed to? Are you expecting a production control analyst to catch them if they do more than they should? Data is valuable. I could implement a job, run a report to unload the data, then replace the job with something innocuous to reduce the risk of being caught. If no damage was done, I’ve probably gotten away with data theft. If I’m feeling ambitious, I might even mess with the auditing logs and erase any traces.
I should mention that this isn’t strictly a mainframe issue. I’ve seen plenty of DB2 for Linux, UNIX and Windows implementations that allow everyone to access the database with DBADM privileges.
Who can bind a package in your production environment? How do they do it? Do you use a privileged system userid running as a production job? Do you trust your clerical staff to do the appropriate binds by giving them full table access to every table in production?
Hopefully, you’ve set up your package owners properly so that programmers in development environments can get their jobs done simply by specifying their appropriate package owner. This only requires them to be connected to the security group that is the owner. You grant the appropriate table privileges to that group and your programmers can bind their packages as well as double check their work against the database using query tools. Assuming the privileges include insert and update access, your programmers can set up test cases in the database. All very good stuff.
When you move into a higher more controlled environment you want to tighten that down a bit. Programmers shouldn’t be compiling directly into user acceptance test and production environments. For these, you probably have someone with the designated clerical authority to issue the binds on request as long as the developers have complied with the required paperwork and signatures. Did you connect that production control clerk to your production package owners? If you did, I want to work in that department. I’d have full access to all of your databases. I could even insert my code into the programs that someone else developed.
How do you fix that little hole of trust? Grant the source control team BINDAGENT from the package owner instead of real database access. That way they can only bind the packages rather than perform all of the SQL statements they contain. Someone else in your organization is left with fixing the other potential holes outside of DB2.
Applications that run on desktop computers are one of the earlier forms of rich client computing. The advent of the client/server programming model placed the data on a server and the application logic on the users’ desktops. This allowed development of complex applications that could be very responsive to the users and provided sharing of data from a remote database. The people using these applications were able to perform shared business functions.
When a client/server application connects to a remote resource, the user’s login information is generally passed to that resource for authentication and to determine if the user has the required privileges. When the resource is a database, we have a two-tier implementation. The user has to be granted all of the database access privileges directly. This requires that we give a lot of trust to the users of the application. We are trusting them to use only the intended application to access and change the data in the database. There is no way to stop a user from creating their own programs or query tools and bypassing the controls that were built into the programs.
In this type of situation I, as a malicious user, have the opportunity to wreak havoc with the application and its other users. At the very least, I could log into the database, unload all of the data, and sell it. I could subtly change the data and the only way to trace the activity would be in database audit logs (if you have them). If I am lucky, you’d have granted full database administrator access to the users (because it was easier) and then I could even cover up my tracks.
How did we solve this problem? We stopped trusting the users and started writing stored procedures. The use of stored procedures provided a level of control at the database server. This control point reduced the amount of trust we had to place in individual user’s hands.
Later, we reduced the amount of work that occurred on the client platform by moving it to another intermediate server.
One of the cures for the problems of the original client/server architecture was the move toward a 3-tier architecture. We solved the basic security problems by placing the application logic and business rules on an application server placed between the user’s desktop and the database. Now we could write more complicated business logic, use multiple data sources, and still have a secure application. Or so we’d hope.
For user interaction, these 3-tier applications work with HTML forms. The forms accept user input and pass it on to the programs on the server side. The user logs into the application and their privileges are verified within the application programs. The user no longer requires any database access privileges, removing that level of trust. The application layer takes the user input, updates the database and returns an updated display to the user. So now, instead of granting database access to the users we grant the access to a non-person userid assigned to the application. The application uses that userid when connecting to the database, but we now lose database-level auditing for the specific users.
As an additional security layer, we can and should put the database behind a firewall so that the users cannot access it directly.
But many programmers continue to exhibit trust in their users. In this case, they trusted the users to enter proper data in the forms giving me a new attack vector called SQL Injection.
With the implementation of web-based application techniques, we’ve also seen a lot of growth in the number of interactive web sites. Companies have taken their 3-tier applications and exposed them to the outside world. Now, our users are outside the company as well as inside. Trusting the users to enter valid content submitted to a scripting language sets the scene for an SQL injection attack.
An SQL injection attack takes advantage of common programming techniques used in many languages. With this common technique the program takes the user input and embeds it in SQL statements. Unlike the standard embedding of SQL in programs that we’d been using with DB2 for years, this embedding simply builds the whole SQL statement into a variable and passes it on to the DBMS. Take the following code-like snippet for example:
# Input form variables: empno, salary
# Set up our SQL statement
sql_stmt = ‘UPDATE SALARY_TABLE SET SALARY = ‘ + salary + ‘ WHERE EMPNO = ‘ + empno + ‘;’
# Run our SQL statement
dbstmt = dbconn.execute(sql_stmt)
# Continue with our program
This type of code gives me, the malicious user, a big opening. I can try putting anything I want into the input field and see what happens. If I were to pass the following as an EMPNO, what do you suppose would happen?
‘; DELETE FROM SALARY_TABLE WHERE EMPNO = ‘
The apostrophe and semi-colon in the embedded EMPNO content effectively complete the SQL statement and after that we add a second SQL statement. Since the second statement is also a valid SQL statement, I’ve turned the update into a delete even though I didn’t have delete privileges within the application. I could do many other things up to the limits of the privileges granted to the application’s non-person userid.
How do we fix this? Simple, we don’t trust the user input fields; we validate the input and if it seems to be something bad then we throw it away. Cleansing the input fields is critical to security in any web-based application, especially one on the Internet.
Now, we’re on the verge of another evolution in application design.
With these frameworks, we can be responsive to the user’s requests. We can pull together data from multiple data sources and display an integrated view. We can compare two complex database hierarchies and show just the changes, saving the user hours of time. And we can do it using more client machine resources and fewer server resources. Everyone wins.
A developer may take advantage of the rich client framework to pass more data to the client-side code and implement display filtering rules there. If the user isn’t allowed to see the fields, one way of hiding them is simply setting the field attributes so that it doesn’t display. This is, of course, a mistake. I’m smart enough to figure out how to display them with a browser-based debugger. This is another mistake of trust, trusting the user to only use the application as written.
What Should We Do?
Database administrators need to ensure the correct access is granted to both person and non-person userids.
Non-person userid privileges should be limited to DML operations that make sense for the application. Don’t give SELECT, INSERT, UPDATE, DELETE privileges to an inquiry-only application. Certainly don’t give them DBADM privileges.
Be careful with implicit privileges. The schema (owner) of our database objects has a lot of implicit access. Make sure you don’t create your objects using the same userid (schema-name) as the application’s non-person userid.
For people in development environments, go ahead and give them more privileges than they need. The more they learn, the better their implementations will be in the future. But lock down the acceptance test and production environments and make sure the application userids have only the access they need. And make sure the database access isn’t being granted to end users.
Consider whether stored procedures may improve security in your environment. If they will, treat them like application layers and make sure they have only the access privileges they need. Don’t implement your stored procedures using administrator privileges if you can avoid it.
Building a truly secure system is hard work. There are so many things to worry about, but in the end just remember… don’t trust me… I’m your user and I’m smart enough to be dangerous.