Introduction
Introduced in DB2 v9.7 Fix Pack 3, the CONNECT_PROC database configuration parameter allows you to specify a stored procedure that will be executed when a user attempts to connect to the database. This functionality affords the DBA a wide range of powerful capabilities including setting special registers, changing settings per-user and auditing or, as we’ll cover here, restricting logins.
With the recent focus on data protection due to GDPR and some high profile security breaches specifically we’ll look at:-
- Restricting clients from which a connection can be made
- Defining times a connection is allowed
- Stopping excessive concurrent logons
What does a Basic Connect Procedure Look Like?
With a few restrictions, the connect procedure is the same as any valid stored procedure. There is a limitation that no parameters are allowed, and there cannot be another procedure with the same two part name in the database (so no overloading).
A basic procedure that changes a user’s CURRENT SCHEMA register on logon might look like this:
How to Implement a Connect Procedure
Once you have created a stored procedure that you want to use as the connect procedure, the first thing to do is grant EXECUTE to PUBLIC.
This might seem counter-intuitive when we’re trying to improve database security, but when a user attempts to connect and they have passed the username/password verification stage the procedure will be executed by that user. If the user attempting a connection doesn’t have execute permission the call will fail – and a failed execution means the connection attempt itself will fail.
Once the procedure has been created and the permissions granted, update the database configuration:
Note you must be connected to the database to set this parameter (SQL6112N RC11).
Updating or Removing a Connect Procedure
Once the database configuration is updated to use a connect procedure no changes may be made to that procedure. In order to update the code first unset the CONNECT_PROC parameter:
This parameter can be unset without being connected to the database provided it is deactivated. This can provide a way out of a sticky situation if you’ve managed to secure your database a little too well.
Once the parameter is unset the procedure can be recreated or dropped normally. If you drop and create the procedure remember to grant EXECUTE to PUBLIC again – replacing the procedure will maintain the grants.
Adding Security Functionality to the Procedure
In order to apply login restrictions to the correct users and clients we first need to identify them – for this we can use the SESSION_USER special register and CLIENT_IPADDR global variable. Building on the previous example we can set CURRENT SCHEMA to a value of SCHEMA1 if user APPUSER1 connects from a specific IP address:
Connecting from any other address, or as any other user, will result in CURRENT SCHEMA being set to SCHEMA2.
Restricting Clients from Which a Connection Can Be Made
Now we have the code to identify the user and source of a connection it’s time to put some restrictions in place. One of the most common scenarios for this functionality is to stop the user created for an application being used for connections from other machines. To implement this we’ll use the SIGNAL statement to return an error with message explaining why the connection was not allowed. To trigger the error message the IF statement will be changed to only allow user APPUSER1 to connect from a specific range of IP addresses. Now the example procedure would look like this:
The procedure first declares a variable to hold an error message – the variable becomes useful when the code is expanded to reject different types of connections and the message aids in troubleshooting if any connections are unexpectedly disallowed. The SIGNAL command throws an SQLSTATE of 42502 with the defined error message. An unsuccessful connection would look like this:
Defining Times a Connection is Allowed
Although 24×7 operations are becoming more and more the standard there are still scenarios when certain users might have a defined window to run queries, and for these we can use the connect procedure as a time lock. Note that the procedure only affects a user as they are connecting – users who already have a connection will be able to carry on working.
Building on the previous example we’ll add functionality to stop the customer support user CUSTSUP1 from connecting outside of support hours:
Here we’ve tweaked the error message that is assigned to the variable that holds the error message and reused the signal statement. The message returned to a user for a refused login looks like this:
Stopping Excessive Concurrent Logins
There are several reasons this requirement might come up, but they usually come down to a security edict to restrict the number of admin users that may be logged into a system at one time. There is occasionally a suggestion this could be used as a simple workload management feature (how much trouble can an MI user cause with just one connection…?) but the built in WLM functionality is much better suited if you can get it set up.
Adding the last component to our example we query MON_GET_CONNECTION to count the number of users connected with our username, excluding the current connection which is found via MON_GET_APPLICATION_HANDLE:
The procedure now limits the number of connections using the APPADMN1 user to no more than 2. This limit applies to both local and remote connections:
A Note on Performance
Although all environments will be different, the performance cost of implementing any reasonable connect procedure should be negligible – some unscientific testing on a laptop revealed an average execution time of ~0.002 seconds for the example above. Bear in mind the characteristics of the applications that will connect however – if they are configured to instantly attempt to spawn many thousands of connections adding additional workload could result in problems so thorough testing would be required.
In order to minimise overhead from the connect procedure keep the code as simple and lightweight as possible – avoid expensive SQL or function calls where possible. Also try to provide the quickest path out of the logic by putting the most restrictive predicates such as session_user first.
HADR Considerations
Implementing a connect procedure on a HADR database may require a failover – the stored procedure and grant will replicate to the standby nodes but the CONNECT_PROC database configuration will need to be updated manually for each node, with a database connection. A controlled failover and connection tests on each node should be part of the implementation plan.
Conclusion
In this simple example we’ve shown three scenarios where using a connect procedure can enhance database security. The same techniques can be extended further depending on requirements and the environment – using stored procedures allows a lot of flexibility, but it is important to remember speed, reliability and security of the code is paramount to avoid unplanned outages.
This functionality should be just one of many measures taken to protect data. Hopefully it’s safe to assume that physical, network, operating system and application security measures are in place for any important system. Database authorities and permissions should already have been applied using the principle of least privilege as far as possible – though I suspect most of us have had to grant more authority than we would like for some apps, revoking CONNECT from public is a bit of a no-brainer, and using a connect procedure to enhance security for the remaining users with connect privileges can provide an extra layer of protection.
About: James Cockayne – Principal Consultant
Favourite topics – Database security, performance & availability. And Guinness.
Mostly Guinness.
In over 15 years of experience as a DBA James has worked across government, retail, insurance and banking industries designing, building and maintaining databases that sit behind some of the UKs busiest online systems and the warehouses that support them.
Now working for UK based Triton Consulting, James focuses on extending the use of DevOps and cloud tooling and techniques with DB2 both on mainframe and LUW platforms.
View James’ blogs and tech tips.