Overview
This document explains the permissions model that ASR uses to secure the API over TMWSuite.
Integration Design
First, it’s fundamentally important to understand that under the hood, ASR's integration is essentially a custom API over TMWSuite.
We perform all the read and upsert operations through stored procedures that execute the business logic to modify records. There’s a lot of security considerations covered in this document, but the main takeaway is that at some level, an API call must turn into a Database call.
This is ultimately true of any API with a database back-end, even native TMW tools like SystemsLink, so the real question comes down to how we secure the layers in between.
Demilitarized Zone
When we talk about a demilitarized zone (DMZ), the primary idea is that we want to put layers between untrusted external traffic and secure internal network layers.
For some content (such as static websites that just display data) you can fully partition the traffic off and keep everything at the DMZ partitioned away from your internal network. The goal is that a compromise of that external layer significantly limits exposure and requires further compromise for additional damage to occur.
However, for other tools you need to expose them to customers, or drivers, or outside users that are untrusted. In that model, you cannot fully separate the DMZ and the internal network, because data that lives on your internal network needs to be synchronized or communicated back and forth to the systems residing in the DMZ.
Most customers do this by opening limited access between the DMZ and that backend. This could be as small as a single port for communication to the SQL Server (but opens SQL up to compromise, so that access must be controlled), or it could be that you proxy all traffic to a DIFFERENT Web API server on the internal network, and THAT server communicates with the database.
Ultimately though, you’re still balancing the layers. You can have multiple firewalls and multiple Proxy Web Servers before you talk to the database, but if an attacker can compromise the external layer, they may be able to compromise the additional proxies and still reach the internal network.
Protecting Database
Therefore, the real key in the solution lies in thoroughly protecting and limiting the actions you can take in the database, so that even if every layer was compromised, the attacker cannot perform damaging activity in the database itself with the compromised access.
This is a difficult problem in TMWSuite. Ideally a database is designed with zero permissions, then you add permissions as necessary for access. However, TMWSuite has an inherently insecure database structure where every table is granted to PUBLIC. This means any user accessing the database automatically has access to create, read, edit and delete data throughout the database.
The only way to restrict this default access is to DENY explicit core permissions to the user, so that the DENY restrictions override the default PUBLIC access. To set up such a model, ASR does the following:
1. We create a ROLE called RESTRICTED.
- We deny all SELECT, INSERT, UPDATE, and DELETE operations on the whole database to this role.
- We deny EXECUTE on DBO and SYS schemas in the database.
- The result is that the restricted role cannot do ANYTHING in the database, except run procedures that exist on a specifically granted SCHEMA.
2. We then create a SCHEMA in TMWSuite. Think of a SCHEMA like a folder to contain our database objects separately and keep them partitioned off in the TMWSuite database.
3. We add all our objects and code to the SCHEMA, so that our RESTRICTED role users can call them, but nothing else in the database, because of the DENY permissions.
4. We create three users within this model.
- WEB_ELEOS is added to RESTRICTED. The only thing possible with this user is calling our procedures.
- ELEOS is added but disabled. You cannot log in with it. However, it gets default permissions to TMWSuite.
- ADMIN_ELEOS is created and given CONTROL over our schema, allowing us to make changes to it, but nothing else in the database.
Impersonation
Normally, this would be enough to secure the database, because in SQL Server any declared procedure is considered Trusted Code. If you have permission to create a procedure, and you grant a user permission to Execute that procedure, then even if they don’t have explicit permission to perform the activities in the procedure, they can still run when they call the procedure.
The classic simple example for this “Stops”. In this example the user wants to update the stop but the WEB_ELEOS user is DENIED permission to directly update the Stops table. By making a procedure that updates a stop to “Arrived”, the WEB_ELEOS user can call that procedure, and it will allow the Stop to be updated to “Arrived”.
However, the problem is Triggers. Triggers in SQL Server execute in the context of the caller for the procedure. That means that if there is a Trigger on Stops that Audits who marked it Arrived (which there is, it sets the Last Updated By field whenever a user edits a stop) then the Trigger runs as WEB_ELEOS. Because WEB_ELEOS doesn’t have permission to update Stops themselves, the entire operation fails and rolls back.
The answer to solve this is impersonation. ALL our procedures use EXECUTE AS to IMPERSONATE the ELEOS user. This means that in the context of the procedure (and most importantly, anything in a TRIGGER that runs after it) inherit the default TMW user permissions, but nothing is granted below.
Cross Database Restrictions
This model works perfectly up until you need to leave the TMWSuite database. SQL Server automatically sandboxes any impersonated context to just the original database. This means you CANNOT leave the database without special configuration if you are running a procedure in an impersonated context.
If you need to go outside the TMWSuite database in your integration (not every customer does), the two main options for this are “TRUSTWORTHY” and “CROSS-DATABASE OWNERSHIP CHAINING”. Typically, we have found Trustworthy to be easier to configure and have less exposure overall. Trustworthy is often written off as a security concern for two main reasons.
- It makes any external assemblies added to the database automatically run as UNSAFE, giving them the maximum access. If a poorly performing or suspicious assembly was attached to the database, this could allow excessive permissions for it, creating a risk. However, we’ve never yet seen anyone attach Assemblies (which are external DLLs that you link to SQL functions through a special setup) to a TMWSuite database (excepting some specialized projects we did ourselves). As such, there are no assemblies in most normal TMWSuite databases that would have this as a risk.
- It allows a user who is a DB_OWNER but NOT a SYSADMIN to create a procedure that runs as a SYSADMIN, temporarily giving themselves excessive permissions. This is a legitimate risk, but is wholly managed by users. If the company reviews and avoids granting DB_OWNER to any users who they do not want to be full SYSADMIN role, there is no opportunity for this escalation.
As such, while TRUSTWORTHY does have risks, the risks are managed by administrative effort and can be overseen. Comparatively, having no security model on the WEB user connecting to the database is a risk that cannot be overseen, because if that external access was compromised without the restrictions we apply, it would allow them to manipulate data in the TMWSuite database without any controls, and could cause significant damage.
End Result
The ultimate result of this design is that we have a partitioning of only our objects to a separate, tracked schema.
We block permissions so that stolen credentials or a hijacked session cannot do anything directly in the database, not even a SELECT * FROM table or trying to read the SYS schema to learn about the database.
We then use the IMPERSONATION model to grant required permissions inside our trusted code, so we can still update TMWSuite.
This creates a model where communication from an outside source (such as the internet) can reach inside the TMWSuite database, and still be protected such that any compromise of this access does not expose data or access in TMWSuite beyond the predefined Stored Procedures used by the integration to perform API calls.