Don’t Let Your Stored Procedures Lack Integrity

Image

Unfamiliar territory

As a security analyst, engineer, or CISO, there are so many aspects of the field that require immediate attention that one cannot possibly know everything.  Some of the common areas of security knowledge include topics such as where to place a firewall, configuration and patch management, physical and logical security, and legal and regulatory concerns. 

However, learning about an unfamiliar topic can not only serve to enhance your awareness, giving you another tool towards better protecting your organization, but it can also keep the job fresh and interesting. In an effort to fulfill this goal, let’s examine a topic that is often overlooked in InfoSec study guides: Stored Procedures.

A Crash Course

Structured Query Language (SQL) is found in almost every organization. It's been around for years and will be around for decades to come. It's the indigenous language of the resident database and the most effective way to define and manipulate relational data within it.

A stored procedure is a subprogram/routine available to applications that access a relational Database Management System (DBMS). You may also see references to stored procedures under the following abbreviations: proc, storp, sproc, StoPro, StoredProc, StoreProc, sp, or SP. 

Server stored procedures are used to group one or more Transact-SQL statements into logical units. The stored procedures are then stored as named objects in the SQL Server Database Server. A major advantage of stored procedures is that they can run directly within the database engine. These procedures are typically stored in the database data dictionary.

The Why and How

There are definite advantages to using stored procedures. First and foremost, they are the most efficient way to communicate with a database. They are also a great way to build robust services with unrivaled performance, adding a whole new level of productivity.

Uses for stored procedures include data validation, which are integrated into the database or access-control mechanisms. Additionally, stored procedures can merge and integrate logic that was originally employed in applications.

These techniques save time as well as system memory. Extensive or complex processing that requires execution of several SQL statements can be saved into stored procedures, and applications then “call” the procedures. Remember, businesses run on applications, and any changes to applications that cause downtime can be significant to a business. To expand on this idea, imagine the consequences if stored procedures are changed on financial applications; this can be ruinous to an organization.

Summarizing Business Reasoning

Stored procedures allow programmers to embed business logic as an Application Programming Interface (API) in the database, which can simplify data administration and reduce the need to encode the logic elsewhere in client programs.

This can result in a less significant likelihood of data exploitation by faulty client programs. The database system can ensure data integrity and consistency with the help of stored procedures. In many systems, stored procedures can be granted access rights to the database, allowing execution of those procedures while not granting access rights directly to the user. This mechanism protects the database by preventing direct access to it.

In some systems, stored procedures can be used to control transaction management. In others, stored procedures run inside a transaction process such that the transactions are effectively transparent to them. Stored procedures can also be invoked from a database trigger or a condition handler.  For example, a stored procedure may be triggered by an “insert” command on a specific table or an update of a specific field in a table, and the code inside the stored procedure would be executed.

Stored Procedures Can Be Used As Protection Tools

Stored procedures can be used to protect against SQL injection attacks. Stored procedure parameters will be treated as data even if an attacker inserts SQL commands. Also, some DBMS will check the parameter's type. However, a stored procedure that in turn generates Dynamic SQL using the input is still vulnerable to SQL injections unless proper precautions are taken. 

Difficulties

One of the problems with stored procedure languages is that they are often vendor- specific. This means that if a company changes database vendors, it usually requires rewriting existing stored procedures. This is why it is important to think strategically when selecting a database product.

Another challenge is that changes to stored procedures are harder to keep track of within a version control system than other code. Changes must be replicated as scripts to be stored in the development record, and differences in methods can be harder to combine and trace appropriately. Errors in stored procedures cannot be spotted as part of a collection or construct action in an application integrated development environment. The same would be true true if a stored procedure went missing or was accidentally erased.

Fortunately, there are some solutions to the change control conundrum.

The Role of FIM

File Integrity Monitoring (FIM) solutions watch for changes to files associated with the servers, databases, routers, applications, and other devices and elements in an enterprise IT infrastructure. The list of monitored files is vast, including registry files, configuration files, executables, file and directory permissions, tables, indexes, and stored procedures. In fact, the reality is today’s IT infrastructure is far too complex to be monitored manually, even in smaller organizations.

Stored procedures offer a place where application logic can reside. You can put application logic to process data or make sure that only certain kinds of interactions happen, controlling the flow of an application from within the database. And so, it's full of code that you write to serve those purposes within a database. You can also write code to do the same things within your application itself.

Here's the thing, there are two separate points, and both of them need integrity monitoring. The first is the actual data protection of the data being entered into the database, and the second is the the stored procedures must have integrity checking to ensure that no one put malicious code into the stored procedures, as well.

Making sure that that you understand what's changing in that code is critical.

The point, from a Tripwire perspective, is that regardless of what the stored procedures are doing or what kind of logic is built into them, monitoring them for changes is going to be extremely helpful. Whether it's break/fix or simply ensuring the integrity of your application, Tripwire’s integrity checking is there to help.

Tripwire can show you line by line differentials, spotlighting where there are changes. This is accomplished by an agent that is installed on the operating system that hosts the database management system on which the database runs. That agent logs into the database with appropriate credentials and gathers the desired information whether it's table definitions, stored procedures, and triggers.

Triggers

A trigger is another aspect that's important because a trigger can cause something to happen when something else happens. A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server. For example, Data Manipulation Language (DML) triggers will run when a user tries to modify data through a specific event such as INSERT, UPDATE, or DELETE statements on a table or view.

Since many applications are written with application code and decision points stored in data in tables, this allows the application to be dynamic, and the code itself can query a table to get its next command or to get a series of commands. The ability to monitor that application foundation for change is also very important. The Tripwire solution is not a transaction-level auditing tool. It is more about monitoring the structure of the database and schema objects.

Innocent Progress

Very often, the skill sets of your Database Administrators (DBA) change over time. In many cases, they improve with experience and training. If a DBA learns a better way to perform a task or if the application changes, then change is good.  After all, skills and application maturity are not static. The way that Tripwire keeps a record of changes is through a scoring system that would reveal whether malicious code was entered. It's an accepted versus non accepted change of a stored procedure or a notification of change in a stored procedure.  This allows for following up and examining the code change to make sure that it was appropriate, enabling a manual reconciliation of that change.

All This and Compliance, Too

Compliance policy management ensures the integrity of your IT configurations by proactively comparing them against internal policies or external policies for standards, regulations, and security best practices. By proactively identifying misconfiguration risks and providing prescriptive remediation guidance, policy compliance management enables a rapid return to a known and trusted state.

When compliance policy management and file integrity monitoring capabilities are combined, you gain complete configuration control and continuous compliance. You get the initial confidence that systems are configured in a known and trusted state and confidence that they'll maintain that state by monitoring for and detecting any improper change.

File Integrity Monitoring that includes compliance policy management requires not only the detection and reporting of unauthorized changes, specific types of changes, changes made under certain conditions, and user-specified severity of changes. It must also perform an assessment of how an existing, or recently changed, configuration compares with established organizational and regulatory guidelines.

Concluding Thoughts

When working with databases, stored procedures are an important consideration for any IT security practice, as they go a long way in protecting an organization. 

While this can offer a level of protection, this process needs to be automated so that it can scale appropriately. One way to ease the burden of tracking changes while staying within organizational and regulatory compliance is through using a tool such as Tripwire Enterprise.