Fine-Grained Access Control for Stored Procedures

Synopsis

Modern enterprise databases have comprehensive security mechanisms to enforce fine-grained access control to data in tables and views.

However, when it comes to stored procedures, access control is coarse: either you can execute a stored procedure, or you can't.

In this article, we show a more flexible approach, using a programmable proxy, that allows precise control of stored procedure invocations based on all available information, such as parameter values, returned values, and more. This approach may be of interest to anyone who needs more granularity and flexibility in managing access to their enterprise databases.

Fine-Grained Access Control

Most enterprise databases provide elaborate security mechanisms to control who can do what to which data, down to the level of individual rows and columns. For instance:

• Microsoft SQL Server has row-level security and column-level grants
• IBM DB2 has Row and Column Access Control and a fairly elaborate Label-Based Access Control
• Oracle has Fine-Grained Access Control, Oracle Label Security, and a few other mechanisms

In all cases, the notion is the same: you should be able to specify, to a very granular level, which operations can be performed on what data by which users.

When it comes to stored procedures, though, all databases have a simple access/no access mechanism. Either you can execute a given stored procedure, or you can't.

Do We Need Fine-Grained Access Control for Stored Procedures?

Many organizations use stored procedures heavily, sometimes to the point where direct access to the tables and views may be completely blocked, and all data access must go through stored procedures.

The stored procedures themselves often have to provide complex access control as part of their implementation to make sure that the invocation is valid, even if the underlying data is itself protected by fine-grained access control. This makes the stored procedures more complex, more expensive to invoke, and more onerous to change.

For many people, that's just the cost of doing business, but in some cases, a proxy-based approach is useful when:

• The constraints depend on external data which is difficult to access from the SQL
• The stored procedures cannot easily be modified to reflect the security requirements, for instance, because they are part of a third-party package that you don't control
• You don't have privileged access to the database
• As an architect, you prefer to externalize fine-grained access control rather than embed it in the stored procedures
• You need to change some parameter values or redirect the invocation to a different stored procedure

Even if none of these apply to you, you may want to read on and see how this approach may open new avenues for you.

What Can We Control With a Proxy?

A programmable database proxy can control just about anything that goes between database servers and clients, but in this article, we'll focus on stored procedures.

With the introduction of a programmable proxy, we can control three critical aspects of stored procedure invocations:

• The invocation itself
• The values of the parameters being passed to the stored procedure
• The values or result set(s) being returned by the stored procedure

Controlling the Invocation

The proxy can reject or modify the invocation of the stored procedure. This can be for a number of reasons:

• The parameter values are not acceptable
• The context is not right: the invocation comes from an unexpected address, the proxy has detected an unexpected pattern of behavior or any other number of factors
• The invocation can be redirected to a different stored procedure, and the parameters can be adjusted accordingly
• The invocation can call more than one stored procedure, and combine the results

Controlling Parameter Values

The proxy can also execute logic on the parameters being passed by the client:

• Parameter values can be logged or recorded
• The logic can verify that the parameters have acceptable values
• The logic can modify the values of these parameters
• The logic can reject the parameters

Controlling Returned Values and Result Sets

Once a stored procedure has been executed, it may return some data, either as individual values or as one or more result sets.

Based on these values or result sets, a proxy can then:

• Let everything flow back to the client
• Stop the invocation and return an error to the client, for instance, if the proxy determines that the client is not authorized to see a specific piece of data
• Modify the values or result sets by modifying them, hiding them, removing rows and column values from result sets, etc...
• Return null values or empty result sets if it determines that the client should not have access, but also should not be aware that it does not have access

What Does It Look Like?

Adding a proxy to a database is typically a simple matter of spinning up one or more proxies, and directing the clients to the proxies. So instead of the usual connection:

We add the proxy in the middle, and start adding whatever logic we need in the proxy:

Let's look at a simple example. We'll use SQL Server as the database, and Gallium Data as the proxy.

Given a simplistic stored procedure:

CREATE PROCEDURE DEMO.CREATE_PRODUCT ( 
   IN NAME VARCHAR(50), 
   IN PRICE DECIMAL(10,2),
   IN TYPEID INT)

We want to implement the following requirements:

1. Only users in the MGMT group can create a product of type 98 or 99
2. Only products with type > 100 can have a price greater than $5,000
3. Products of type 16 and 17 must actually be created using the CREATE_SPECIAL_PRODUCT procedure

The first requirement is easy to satisfy with an RPC filter in the proxy:

let typeId = context.packet.parameters[2].value;
if (typeId === 98 || typeId === 99) {
   let rs = context.mssqlutils.executeQuery("select is_member('MGMT') as res");
   let isMember = rs.rows[0].res;
   if ( ! isMember) {
      context.result.errorMessage = "User is not a member of the MGMT group";
      return;
   }
}

This will cause the client to receive an error if the requirement is not satisfied.

The second requirement is a straightforward extension:

let price = context.packet.parameters[1].value;
if (price > 5000 && typeId <= 100) {
   context.result.errorMessage = "Price is too high for this type of product";
   return;
}

The third requirement is even easier:

if (typeId === 16 || typeId === 17) {
   context.packet.procName = "CREATE_SPECIAL_PRODUCT";
}

In this last example, we assume that the CREATE_SPECIAL_PRODUCT procedure takes the same parameters as CREATE_PRODUCT, but of course our logic could change the parameters as required if that is not the case.

These are simplistic examples, but you get the picture: it's fairly straightforward to secure stored procedures with a programmable database proxy, and it's especially useful if you cannot (or don't want to) change your stored procedures.