Question
· Jun 8, 2022

Restrict ODBC Access Based On Application

Is there a way for us to restrict user's ODBC permissions based on what program they're running on a client?

For example, we have some older Windows apps (.exe) that are a regular part of our software package which require the user to be able to select, insert, update, and delete. Some of our users are also using other third-party apps to connect (mostly reporting tools) but we only want them to be able to select unless we've approved the exe. Is there a way to do that?

These are not applications that were developed using CacheDirect.

Product version: Caché 2018.1
$ZV: Cache for UNIX (Red Hat Enterprise Linux for x86-64) 2018.1.2 (Build 309U) Mon Mar 4 2019 15:07:46 EST
Discussion (6)2
Log in or sign up to continue

Ideally, that would be the case, but some of these aren't apps we've written and are forcing to log in with a safe user account. They're 3rd-party apps that our users are logging into using their usual credentials to create an ODBC connection. Most of them are reporting tools, but a few are also capable of running queries other than selects.

I think what we'd like to be able to do is determine roles similarly to how you can set up application roles so that the user gets those roles when they log in, we set permissions based on the program being used. Can we do that somehow in ZSTART?

See $SYSTEM.SQL.SetServerInitCode() (there are differences for IRIS)

Simple example:

Class dc.test
{

ClassMethod Test()
{
  
  programname=$zcvt(##class(%SYS.ProcessQuery).%OpenId($j).ClientExecutableName,"L")

  programname="blablabla.exe" {

    ;useful work

    s $EC="ERROR"
    ;or
    s $ROLES="r1"
  }
}

}

USER>d $SYSTEM.SQL.SetServerInitCode("d ##class(dc.test).Test()")

Now, when connecting from a specific program via ODBC/JDBC to namespace "USER", an error will occur. You can configure something another.