Restrict ODBC Access Based On Application

Question

Question

David Hockenbroch · Jun 8, 2022

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

Vitaliy Serdtsev · Jun 23, 2022

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

Simple example:

Class dc.test
{

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

  i 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.

1 0

score 0 · Answer 1 · 2022-06-08T13:37:30-04:00

Based on the ODBC source program maybe you can get it with Delegated Authentication. Depending on the origin, the user would receive additional Roles or not with (Using Privileged Routine Applications).

To create additional or specific Roles for each user or user group see RBAC documentation.

score 0 · Answer 2 · 2022-06-13T10:02:50-04:00

Eduard Lebedyuk · Jun 13, 2022

Each app should use it's own user with a minimal set of roles and permissions.

0 0

score 0 · Answer 3 · 2022-06-22T11:32:28-04:00

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?

score 0 · Answer 4 · 2022-06-22T11:44:25-04:00

Where these third-party apps are mostly reporting tools, it could make sense to set up a Reporting Async mirror with read-only databases. That would handle the "clients shouldn't be able to insert/update/delete" issue and protect your main instance from rogue queries. (And these clients would only be allowed to connect to the reporting async.)

score 0 · Answer 5 · 2022-06-23T14:57:45-04:00

David Hockenbroch · Jun 23, 2022

Thanks, Vitaliy! This seems to be the approach that gives me what I want.

0 0