How to block a user from starting a transaction

Question

Question

Hugh Hamilton · Nov 16, 2023

We have a service account that is running queries to pull some data. It has read only access to the tables, but, unfortunately, the tool is starting a transaction before it reads the database. The query takes more than 20 minutes (21 - 23 minutes), so we're getting the console message that the transaction is open longer than 20 mins. Is there a way to prevent a user/service account from being able to start a transaction since it only has read access to all of the tables in the database?

Product version: Caché 2018.1

Discussion (10)4

Log in or sign up to continue

score 0 · Answer 1 · 2023-11-16T09:55:17-05:00

Could you please give additional information on how the data is being pulled?
You say "tables" so I assume you run a SQL: Is this done by "select" or do you run a SP?
Is this is a local task on the server (running a COS code) or externally with ODBC/JDBC connection?

score 0 · Answer 2 · 2023-12-01T11:43:29-05:00

we have an external system that is querying the data via SQL (ODBC/JDBC). The tool that's being used on the other server was built to start a transaction (TSTART) before the query runs. The query only reads data and the service account has read only access to the tables in the database. the problem is that it keeps starting the transaction.

I can't stop the other process from starting a transaction, but i need its request to start a transaction to fail.

score 0 · Answer 3 · 2023-12-01T11:59:20-05:00

I'm not sure you can "revoke start transaction" in some form, but the risk is that if the transaction start fail the tool will exit/fail the entire query.

Do you know by chance what tool is used?
Is it using JDBC or ODBC?

Enrico

score 0 · Answer 4 · 2024-01-02T14:48:12-05:00

Yeah, that's exactly what I'm looking for. I don't know what tool the other team is using, I do believe that it's an ODBC connection. I just know that the tables that the service account has access to are all read only. If they have a process that is starting a transaction, i want it to fail/return an error to them. They cannot write to any tables, so they shouldn't be able to start a transaction.

score 0 · Answer 5 · 2023-12-02T16:07:06-05:00

Check whether the tstart command is issued with a post conditional parameter if so then you may be able to make the ts fail and therefore the ts would not start the transaction.

For instace if you had:

Tstart:$g(^startts)=1

Then killing ^startts would cause the transaction not to be created.

#longshot

score 1 · Answer 6 · 2023-12-13T11:53:14-05:00

I think it's really important that you find out why the tool is written to start a transaction before every query.

score 0 · Answer 7 · 2024-01-02T14:53:33-05:00

Though I don't agree that the why is relevant (since none of the tables that the service account has access to are able to be written to), i would suspect that it's a system setting or a default value somewhere in their tool that tells it to automatically send a TSTART.

I've been told that the 3rd party system that's querying our InterSystems database does NOT start a transaction, but clearly it is, evidenced by the console message that the transaction for that process has been open for more than 20 minutes. I don't have access to their system to help them find it. And they'd be fine if we can lock out their ability to start a transaction (ie, make everything read only for that service account). Unfortunately, I've only been able to make the tables read only and not everything. Make sense?

score 0 · Answer 8 · 2024-01-02T16:46:21-05:00

You say you've been able make the tables read only and not "everything". What are the accessing besides tables?

score 0 · Answer 9 · 2024-01-03T01:51:28-05:00

If you want an account with only read access to be unable to initiate transactions, you can do so through the following methods:

1. * * Database level restrictions * *:

*Most database management systems (such as MySQL, PostgreSQL, SQL Server, etc.) allow you to set permissions for accounts or roles. You can set permissions for the service account to prevent it from initiating transactions.

*For example, in MySQL, you can use the 'SET TRANSACTION' statement to set the isolation level of transactions. However, if the account does not have the permission to 'SET TRANSACTION', it will not be able to change the isolation level of the transaction and therefore cannot start the transaction.

2. * * Application level restrictions * *:

*If the tool allows changing its behavior through configuration files or settings, you can check if there is an option to disable the automatic start of transactions.

*Some tools provide command-line parameters or configuration options that allow you to specify whether to automatically start transactions. Check the documentation of the tool to see if there is such an option.

3. * * Code level limitations * *:

*If the tool allows you to interact with it through code or scripts, you can try manually starting a transaction before the query and committing it immediately after the query. In this way, transactions will only exist for a short period of time and will not trigger long running transaction warnings.

4. * * Database monitoring and alerts * *:

*Although you cannot prevent the account from initiating transactions, you can set up database monitoring tools to detect long running transactions and send alerts for you. In this way, if the account accidentally initiates a long running transaction, you can receive notification as soon as possible and take action.

5. * * Review and Training * *:

*Train the personnel using this account to ensure they understand how to use it correctly and not attempt any operations that they do not have permission to perform.

*Consider regularly reviewing the usage of this account to ensure it has not been abused.

6. * * Consider other tools or methods * *:

*If existing tools cannot meet your needs, consider finding or developing a new tool that can meet your needs and will not automatically initiate long running transactions.

Overall, you need to combine multiple methods to ensure that accounts with only read access do not inadvertently initiate long running transactions.

score 2 · Answer 10 · 2024-01-03T15:20:34-05:00

Some more thoughts on this:

If the 3rd party system claims that they're not starting a transaction, but you have evidence that a transaction is starting, it would be good to try to work together to get to the bottom of that. Of course that's up to you. It's best to get the WRC involved.
Transactions are an application thing, not a user-oriented thing. There is no way to say "prevent user X or role Y from starting a transaction."
There is a potential reason for the 3rd party system to be using transactions even when simply reading data with SELECT, known as Isolation Level. Read about it here. To establish Isolation Level, you can use SET TRANSACTION (which doesn't start a transaction), or START TRANSACTION (which obviously does start a transaction). By default, a SELECT shows you all the matching rows, even rows that are uncommitted (changes to those rows could be rolled back). ISOLATION LEVEL READ COMMITTED is used when you want to guarantee that a SELECT shows you only committed data, although this may result in the SELECT failing to complete when it reaches a row that has not been committed yet. So maybe the 3rd party system is using START TRANSACTION for that. Maybe they could switch to SET TRANSACTION instead. Edit: Unfortunately, IRIS does not provide the ability to GRANT or REVOKE the ability to use SET/START TRANSACTION.
You wrote "If they have a process that is starting a transaction, i want it to fail/return an error to them." But wouldn't that prevent any of their queries from running? In other words, since the 3rd party system always starts a transaction, and that behavior can't be turned off, IRIS would return an error every time, and the query wouldn't run.