Read-only SQL92 connections to an OpenEdge database

Posted by ChUIMonster on 13-Apr-2018 14:14

If you want to ensure that SQL92 connections are "read-only" what should you do?

All Replies

Posted by Rob Fitzpatrick on 13-Apr-2018 14:17

First, make sure the user isn't a SQL DBA. ;)  Check sysprogress.sysdbauth.

Second, check the user's table-level permissions in sysprogress.systabauth.  They should only be granted SELECT permission on the tables they need to read.  They shouldn't have any other permissions.

Posted by Rob Fitzpatrick on 13-Apr-2018 14:23

SELECT * from Sysdbauth;

SELECT
Systabauth.Grantor,
Systabauth.Grantee,
Systabauth.Tbl,
Systabauth.Ins,
Systabauth.Del,
Systabauth.Upd,
Systabauth.Sel,
Systabauth.Exe,
Systabauth.Ndx,
Systabauth.Alt,
Systabauth.Ref
FROM Systabauth
WHERE (Systabauth.Tblowner='PUB') AND (Systabauth.Tbl<'ZZZ')
ORDER BY Systabauth.Tbl;

Posted by ChUIMonster on 13-Apr-2018 14:29

That is excellent advice.

I said "users" but I meant "connections".  I have edited the original post to reflect what I really meant.

I'm trying to put together a checklist of things beyond "don't permit sql connections", "apply the Linux patch" and "ensure that the DSN is set to read uncommitted" to help people avoid SQL sessions locking up lots of records for no good reason.

Posted by Rob Fitzpatrick on 13-Apr-2018 14:35

Good question.  You can't specify -RO on a SQL broker.  I'm not sure if there is a connection-string parameter you can use to make a connection read-only.

Posted by jmls on 13-Apr-2018 14:47

require a user / password for a connection. Make the tables select only for that user .. then you have a read only connection :)

It's the only way to be sure. Apart from nuking it from orbit .

Posted by ChUIMonster on 13-Apr-2018 14:58

Are there any things that can be done on the client side of a connection that will at least help?

Do SQL queries support any handy syntax like "no-lock" that can be liberally sprinkled throughout the user code?

(As you may have guessed i don't write SQL)

Posted by Rob Fitzpatrick on 13-Apr-2018 15:08

I don't do much with SQL either.  But the docs for SELECT show a WITH clause that lets the coder override the defaults for lock wait timeout and session transaction isolation level.

[ WITH ( READPAST NOLOCK [ WAIT timeout | NOWAIT ]) ]

Posted by gus bjorklund on 15-Apr-2018 15:08

> On Apr 13, 2018, at 3:59 PM, ChUIMonster wrote:

>

> Do SQL queries support any handy syntax like "no-lock" that can be liberally sprinkled throughout the user code?

>

>

you can set the “isolation level” to the exact equivalent of no-lock.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

all other isolation levels require the use of locks to work properly.

Posted by Dinesh on 15-Apr-2018 22:10

SELECT * FROM PUB.CUSTOMER WITH(NOLOCK)

This thread is closed