Read-only SQL92 connections to an OpenEdge database - Forum - OpenEdge RDBMS - Progress Community

Read-only SQL92 connections to an OpenEdge database

 Forum

Read-only SQL92 connections to an OpenEdge database

This question has suggested answer(s)

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

--
Tom Bascom
tom@wss.com

All Replies
  • 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.

  • 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;
    
  • 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.

    --
    Tom Bascom
    tom@wss.com

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

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

  • 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)

    --
    Tom Bascom
    tom@wss.com

  • 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 ]) ]

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

  • SELECT * FROM PUB.CUSTOMER WITH(NOLOCK)