Securing xDBC Connections with ServerInitCode

The ideal number of table permissions to assign for your users is zero. Permissions should be granted upon sign-in based on the application used for access. For web applications, we have a simple way of doing this by appointing application roles, matching roles, and required resources in the System Management Portal.

ODBC and JDBC connections present a different problem, however, especially when third-party applications are involved. As providers of an ERP system, our customers often wish to be able to employ various software packages to integrate with or report on their data. Many of these programs are capable of running any kind of query. Yet, letting them do that can be devastating to a customer’s data.

Fortunately, InterSystems gives us the tools to handle such situations too. By setting the ServerInitCode option, we can make a routine run any time a user tries to establish a connection with the help of ODBC or JDBC. To do that, we will need to utilize $SYSTEM.SQL.Util.SetOption. Issuing the following command will make the routine Init^SecureXDBC run whenever a user attempts to initiate an XDBC connection:

do $SYSTEM.SQL.Util.SetOption("ServerInitCode","Init^SecureXDBC")

If we set the $ECODE variable during this routine, the user’s login will fail. It is an error code variable, and it must start and end with a comma. We can use our routine to decide when and how to set things up to secure our XDBC connections in a better way. (Note that on older versions of Cache, the method to do this is $SYSTEM.SQL.SetServerInitCode. In current IRIS, this method is deprecated.)

We can also use this routine to set roles via the $ROLES variable. This peculiar variable contains all the roles granted to the current process but works differently from most other variables. Setting it to a comma-separated list of roles awards those roles in addition to any other roles assigned to the user. You can also concatenate roles onto $ROLES. However, setting $ROLES to “” or initializing a new $ROLES will only remove the additional roles you have granted during this process. It means that roles that are given to the user can not be dismissed, yet extra roles can be added. 

You can use it the way you like, but you will need to determine the criteria for allowing and rejecting connections. For demonstration purposes, this article will focus on making XDBC connections have application roles, matching roles, and required resources to help us secure them like a web application. Since we need to store those settings somewhere, we will begin by making a new persistent class that I will call User.ApprovedEXEs. It will have three properties (all strings). I will call them Name, Resource, and MatchRoles because those names match the way these things are defined in the Security.Applications class.

The name you select should be the name of the client executable. We can see the executable name associated with the current process by using %SYS.ProcessQuery as follows:

set exename = ##class(%SYS.ProcessQuery).%OpenId($J).ClientExecutableName

Although I do not plan to use them today, some other values from the ProcessQuery class might also be practical for your specific needs. For instance, if you wanted to limit access based on a client's IP address or username, you could account for that as well.

Resource will be a resource name. It should include a resource name, a colon, and permission. For example, MyResource:U would require the user to have the Use permission on the resource called MyResource. If the user does not have that resource, they will not be allowed to connect.

MatchRoles will be the list of application and matching roles. In order to be consistent, we will utilize the same format the Security.Applications class uses. It is a list of roles separated by colons. If the user has the first role, the rest of them will be granted automatically. For instance, TestRole:MatchRole1:MatchRole2 will grant the user MatchRole1 and MatchRole2 if they already have TestRole. If the string starts with a colon, the roles that follow it will always be granted. So if we set MatchRoles to :SomeRole, the user will always be granted SomeRole when they log into the application. We can separate multiple strings with commas if needed. For example, if we set it to be TestRole:MatchRole1:MatchRole2,:SomeRole, then users who already have TestRole will be granted MatchRole1 and MatchRole2, yet all users will be given SomeRole.

I started this article by saying that users should not have any table permissions assigned to them, and this table will not be an exception. We will create a role that gives the user SELECT permission on this table, and we will utilize the $ROLES variable to grant that permission during the ServerInitCode process. We will then remove that permission before assigning other roles based on the entry in ApprovedEXEs. We will call this role Init, and it will grant Read permission on the %DB_USER resource and SELECT permission on the SQLUser.ApprovedEXEs table.

There is a small matter we should address right away, though. If a user has %All, we cannot interfere with them using any other XDBC connection they wish. It is the purpose of %All. Yet, if we set $ECODE during the server initialization code, it would cause the connection to fail even if the user had %All! Due to that, our first line will be simple. If we find %All in the list of roles, we will not go any further:

quit:($LISTFIND($LISTFROMSTRING($ROLES),"%All")>0)

You will notice that throughout this process, we will convert $ROLES to a list and use $LISTFIND rather than $FIND on the $ROLES string. We should do this because when we use $FIND, there is a chance that the name of a role we are looking for will also be a part of the name of another role. In that case, utilizing $FIND would create a false positive. For instance, if, for some reason, I had a role that was named “MyRole%All”, using $FIND($ROLES,”%All”) would not return a 0. Converting $ROLES to a list and utilizing $LISTFIND prevents this from happening.

Next we should check the ApprovedEXEs table for an entry. In this case, we will deny the login if there is no entry for the executable. We will also capture a few pieces of information by simply storing them in globals. Doing this will help us answer a phone call later when the user asks why they can not log in! Also note that we must add the role we need to access the ApprovedEXEs table just before we query it, and then we should get rid of it once we are done. We do not want to leave that role there to prevent the user from querying this table later in their process:

set exename = ##class(%SYS.ProcessQuery).%OpenId($J).ClientExecutableName

set $ROLES = "Init"

&sql(SELECT NVL(Resource,''), NVL(MatchRoles,'') INTO :Resource, :MatchRoles FROM SQLUser.ApprovedEXEs WHERE Name = :exename)

set $ROLES = ""

if SQLCODE = 100{

set i = $I(^LogInitError)

set ^LogInitError(i) = $ZDT($H,3,2)

set ^LogInitError(i,"Executable") = exename

set ^LogInitError(i,"User") = $USERNAME

set ^LogInitError(i,"Reason") = "Application Undefined"

set $ECODE = ",Application undefined,"

quit

}

Next, we should iterate through the MatchRoles as a list containing sublists separated by colons. If the user has the role stated before the first colon, or if there is nothing there, we must add the following roles to the current set of roles by concatenating it to the $ROLES string.

set matchlist = $LISTFROMSTRING(MatchRoles)

for x=1:1:$LISTLENGTH(matchlist){

set rolelist = $LISTFROMSTRING($LISTGET(matchlist,x),":")

if $LISTFIND($LISTFROMSTRING($ROLES),$LISTGET(rolelist,1)) > 0 || $LISTGET(rolelist,1) = ""{

for y=2:1:$LISTLENGTH(rolelist){

set $ROLES = $ROLES_","_$LISTGET(rolelist,y)

}

}

}

The final inspection we will do is for the resource if one is defined. It is a simple security assessment via $SYSTEM.Security.Check:

if Resource '= "", $SYSTEM.Security.Check($PIECE(Resource,":",1),$PIECE(Resource,":",2)) = 0{

set i = $I(^LogInitError)

set ^LogInitError(i) = $ZDT($H,3,2)

set ^LogInitError(i,"Executable") = exename

set ^LogInitError(i,"User") = $USERNAME

set ^LogInitError(i,"Reason") = "Missing Required Resource"

set $ECODE = ",Missing Required Resource,"

quit

}

Once we have written and compiled this code, we have to run the following command mentioned earlier in this article:

do $SYSTEM.SQL.Util.SetOption("ServerInitCode","Init^SecureXDBC")

It will execute this piece of code whenever an XDBC connection is initialized. Now, in each namespace, we can use the SQLUser.ApprovedEXEs table to secure those connections better.

There will probably be some other executables that you will want to add right away. For example, odbcad32.exe is the executable that Windows users can run to set up ODBC connections. It should be in the table since if it is not there any time users click the Test Connection button, it will always fail.

It is a good start, but not perfect, though. Since all we have to go on is the executable name, there is always a chance that more than one program will use the same executable name. The executable crw.exe is usually utilized in Crystal Reports. However, there is always the possibility that another program could operate a crw.exe to make an ODBC connection. In case that happens, there is no way to distinguish between them, unfortunately. Additionally, some suites of programs like Microsoft Office use shared components to make data connections. It means that you cannot set different permissions for connections from, let's say, Word and Excel.

Still this is a reasonable starting point. I hope that once you have read this, you will be able to write your routine on your own and use ServerInitCode to meet your specific needs.