Question
· 6 hr ago

Using /api/atellier/v1 to make REST SQL Calls - Locking it Down

#REST API #Security #SQL #HealthShare #InterSystems IRIS for Health #Health Connect #InterSystems IRIS

There is a Master Table within IRIS that I am populating from Epic but want to share it with our Enterprise Application Development Team (Web). As a test I was able to use _SYSTEM from postman to execute the following.

POST /api/atelier/v1/xxxx/action/query HTTP/1.1
Host: xxxxxxxx
Content-Type: application/json
Authorization: ••••••
Cookie: CSPSESSIONID-SP-443-UP-api-atelier-=00f0000000000AKyLjBfUvU$MpFD8UT8y$EoNKNw1ixZeXN4_Q; CSPWSERVERID=hzZAT5rb
Content-Length: 86

{"query": "SELECT * FROM osuwmc_Epic_Clarity.DepartmentMaster WHERE ID = '300000000'"}

if I create another user specific for this, how can I lock it down in a way that they could only query specific tables through the REST POST call?

Product version: IRIS 2025.1
$ZV: IRIS for UNIX (Red Hat Enterprise Linux 8 for x86-64) 2025.1 (Build 230_2U) Wed Jun 4 2025 18:50:44 EDT [HealthConnect:8.2.2]
Discussion (7)2
Log in or sign up to continue
Brett Saviano · 5 hr ago

A REST implementation method can do anything, including Dynamic SQL queries. Do you need to allow arbitrary SQL execution though? If all you need to expose is the data in one table with some filtering you can have an endpoint like GET /epic/master (path is arbitrary) and have query parameters for filtering. The implementation can use SQL to popular the results under the hood without needing the end user to write SQL.

0 0
Stephen Canzano · 4 hr ago

Interesting.. but if the user understands SQL and understands the db schema why dont they connect via ODBC/JDBC.  If they dont understand SQL and the database schema why not make a specific REST class that has endpoints for each type of query they might want to run.  Allow for arbitrary SQL statement here including INSERT, UPDATE, DELETE is probably not a good idea.. although it looks like /query may only allow Statement Type = 1 SELECT and Type=45 CALL.

0 0
Scott Roth · 1 hr ago

The Enterprise Application Developers (Web Team) that develop our External Patient facing Applications are not used to trying to connect to something other than Microsoft SQL from .Net. So, trying to find a way to expose this via a REST application would be helpful and less time consuming then trying to figure out the InterSystems Drivers to connect via ODBC/JDBC from .Net.

0 0
David Hockenbroch · 3 hr ago

I'll echo the idea that you don't really want to be using the /api/atelier web app this way because of the %Development requirement and the arbitrary SQL execution, and because it includes a lot of other functionality that you probably don't want people to have access to.

BUT if you're really intent on using that API, then you could make your own web application in the management portal that uses the same dispatch class that /api/atelier does and create a new resource to be the required resource to access it. Make sure your users have permission to use that resource, and then assign the appropriate table and view permissions in a role that you either give to those users or assign to the Web Application as an application role.

0 0