by Robert Buda | Jun 9, 2011 | Best Practices, Database, Database Security
It has been a relatively short time since I wrote my last post on database security but so many breaches have occurred since then that it seems like much longer.
In just the past few months, Sony’s gaming system was shut down for two weeks, a nuclear facility in Iran was physically damaged by nefarious code introduced into the system, the SecureId system at Lockeed Martin was compromised, and google’s mail system was hacked again. The list goes on and on.
Two things are becoming clear; With the current state of network security, no computer systems are completely safe from attack. And the reasons for attacks are becoming more varied.
Suspected reasons for recent attacks have ranged from personal vengeance against a company, to likely state-sponsored espionage, to the more common stealing information for personal or corporate gain.
Some recent attacks came from the outside. But others, notably, the attack on the Iranian nuclear facility, is believed to have been introduced inside the network, possible via an infected flash drive.
This tells us we must use a defense in depth approach whereby we secure the individual components of our information technology infrastructure in addition to the overall network and physical environment — if we want to protect data that is stored in a database, then we must secure the database directly!
Databases from every database software vendor are subject to database vulnerabilities caused by misconfiguration, poor security procedures, or bugs in the database software.
The first step in eliminating these vulnerabilities is to identify them. This can be done using database vulnerability scanning tools such as Application Security Inc’s AppDetective Pro, which we use at Buda Consulting, to help secure our client’s databases.
After the vulnerabilities are identified, they should be resolved or designated as acceptable risks, and then another scan should be performed to ensure that all of the vulnerabilities have been addressed.
Finally, we must audit database activity and monitor the audit logs on a regular basis to identify potential attacks. Database security is not a one-time activity. It is an ongoing process that must be performed on a regular basis.
Download our Database Security Roadmap to help guide you through the process of securing your database.
by Robert Buda | Jan 3, 2011 | Best Practices, Database, Database Security
As the New Year begins, many of us take the beginning of a new calendar year as a cue to to clean out and shred our old files and reorganize our storage spaces so that we have room for all the new supplies and equipment we will need in the new year.
We do this because we know that if we did not do this each year, the task would get overwhelming and would likely not get done at all.
We know that clutter is bad. Clutter slows us down when we need to find something. Clutter can cause injuries as we step over old boxes. Clutter can cause us to lose track of important files and may prevent us from securing sensitive files properly.
As we do this with our physical files in our file cabinets and with the supplies in our closets, we should take the same steps with our database systems.
A thorough database security audit should be performed each year and one deliverable of the audit should be a list of potential items to be removed.
Clutter in our database system takes various forms. This includes user clutter and object clutter.
- Obsolete user accounts (accounts for terminated employees or contractors).
- Unused test accounts.
- Obsolete tables and columns.
- Unused stored procedures and functions
- Unused database links
Of course, these items should be cleaned out more often than once per year. In a perfect world, we would never have obsolete user accounts or unused tables in any database. But performing an annual cleanup ensures that nothing gets missed.
This cleanup should be performed on both Production and Development databases. There will likely be more object clutter (unused tables or stored procedures) in the development database than in the production database. The production database will likely have more user clutter (obsolete user accounts).
Obviously, extreme care must be taken when performing such a cleanup. Appropriate backups should be performed beforehand as with any maintenance task.
When cleaning up obsolete users, you can start by asking your HR department for a list of employees that have been terminated during the year. Check to see if their corresponding accounts have been removed.
It is common to find that the account of a terminated employee is still in use by employees that have assumed his duties. In this case, the privileges (and any objects) of the terminated user should be transferred to the user that remains and the account should be removed.
When identifying obsolete objects, look for the following as potential candidates for removal:
- Tables with names indicating a temporary nature. Tables with Test or Temp as part of the name
- Tables with a persons name attached.
- Tables with a date attached.
- Tables that do not follow the naming convention for the system.
- Empty tables
Before removing any table, check with the development team and the application owners to see what they know about it. Of course backup any table or other object individually before removing it. Also check for any foreign key constraints involving the table.
Keeping your database clean in this fashion will make it more secure and easier to maintain and understand as the years go by. A fifteen or twenty year old system can accumulate a surprising amount of clutter if left unchecked. Just like that storage space in the basement.
Happy New Year!
by Robert Buda | Jul 28, 2010 | Best Practices, Database
I know that some will disagree with me on this but I have never been a fan of database query hints.
Most database vendors give you the ability to override the query optimizer with hints that you specify in a query. Each query can be executed in a variety of ways by the database engine. Normally, the optimizer evaluates the statistics about the data in each of the tables involved in the query and makes a decision about the best way to execute that query. Hints enable you to direct the database engine to use a specific approach, even if it prefers a different one.
Here are five reasons that I avoid hints.
Reason Number 1. I view hints as a workaround to a problem that should be solved in other ways. There are a few problems that may lead to the need to use a hint:
One is that the database statistics may not be up to date which prevents the optimizer from making the best choices. The obvious solution to this problem is to ensure that all appropriate statistics are collected frequently.
Another problem may be with the database or application design. If the query optimizer cannot choose a path that will execute the query quickly enough, we may need to look at the use of indexes or even the data model itself. Does the data model actually represent the true relationship between data or is the model causing the application to do more work than it should?
Finally, all major database vendors upgrade and improve their optimizers over time. For example, Oracle’s optimizer has improved very significantly over the past few major releases. If you are not on a recent release of you database software, Upgrade! When you do, rip out your hints and see how the new optimizer does, you may be surprised.
Reason Number 2: Database vendors change their optimizers with new releases. Hints that have good results in one release can have poor results or even cause a query to break in later releases.
Reason Number 3: As data volumes grow and statistics are updated in the database, hints prevent the optimizer from selecting new and better execution paths as the result of the change in the characteristics of the data.
Reason number 4: As much as we would like to think that we are smarter than Microsoft or Oracle, most of us have not put millions of dollars and many years into query optimization technology as they have. Since we are paying for this through our license fees, we should take advantage of that investment.
Reason number 5: Now the reason that led me to write this post: This excellent blog post describes situations where a hint can cause a query to succeed when one set of criteria is given and to fail when other criteria is given. This type of problem is very difficult to diagnose because it is sporadic and depends on specific criteria. This problem occurs on SQL Server and in that environment, this issue alone is enough to rule out the use of query hints.
Bottom line: use query hints only as a last resort. Getting to and fixing the root cause of the problem will ensure that the query will continue to perform well into the future.
by Robert Buda | Jul 9, 2010 | Database, MySQL
We are currently working on an application that uses a mysql database. The application produces a large number of csv files that must be kept around for a few weeks, and then removed after being used in various ways by the users of the system.
We need to keep track of all of these files and present a list of the files to the user so they can download them to their local machine, or send them to other systems as needed. We need to track when the file was created, who created it, and other information.
We considered a few alternatives for managing these files; We could keep then on the file system and have scripts that keep the database synchoronized with the file system. Or we could load each file into a blob type and keep it in the mysql database. Either of these options will work fine if we are careful. But we are seeking something more.
Our research led us to MongoDB. An open source document oriented database.
MongoDB claims to bridge the gap between key-value stores which are typically used by high volume web applications that have simple data storage requirements, and traditional RDBMS database systems.
In addition to simple queries based on key-value pairs, you can use boolean expressions, regular expressions, set operations, and sorting and limiting. MongoDB also has the ability to grab documents directly from the file system using its gridFS functionality.
We have not implemented MongoDB for this project yet but it looks very promising.
I will send updates as we get closer.
