Pessimistic locking with approles.

From: Paul Ritchie (pritchie@xtraREMOVE.coREMOVE.nz)
Date: 06/12/02 

From: "Paul Ritchie" <pritchie@xtraREMOVE.coREMOVE.nz>
Date: Thu, 13 Jun 2002 09:00:19 +1200

<I posted this in the ADO NG yesterday but received no replies.>

I have rewritten our ISAM based application which uses pessimistic
record locking. It now uses ADO, server side cursors, application roles,
and SQL Server. Any of those individually and most combinations thereof
seem to work fine, but not the combination I'm using!

My problem is this: say you have an ADO recordset open on a table Employee
with a pessimistic record lock on a connection that is using an AppRole.
(Pessimistic locking requires a server side cursor.) Before you post
the changes to Employee, maybe you wish to validate whether one of the
fields you hope to save to the Employee table is valid, and for this you use
a SQL query on another Command or recordset object.

To paraphrase another post last year, it seems that "when you have a
server-side cursor open with results pending & you are trying to create
another recordset, ADO will automatically open a new connection".

However when it does this, it does not apply the AppRole to the newly
opened connection which therefore seems to become subject to the rights of
the original login. Needless to say, these are minimal.

I would be really keen on any suggestions on how to get around this.
Pessimistic locking is fundamental to the way the application works, and
pessimistic locking requires a server side cursor.

I understand using AppRoles makes security management extremely simple where
their use is appropriate, however maybe there is another security
arrangement that is nearly as good for users of my application?

BTW One suggestion was that I create a db user with a hidden password (the
same as my application & AppRole hides it now) and attach that user to the
db_owner database role? Are there any major downsides to that?

It seems that I would be forced to use SQL Server authentication, but apart
from that it's almost the same as setting an AppRole which is assigned to
the db_owner role?

TIA,

Paul Ritchie

Relevant Pages

  • Re: DAO to ADO Recordset Options
    ... DAO was really fast when dealing with Access data (faster than ADO) but was ... Recordset. ... For client-sided cursors, there is only Static cursor type regardless as ... determines how often data is fetched from the server. ...
    (microsoft.public.data.ado)
  • Re: ADO Data Control Concurrency Problem
    ... >> Editing of row in table is done with the help of ADO Data Control. ... Cursor location does not bare on whether the ... Client or Server memory. ... reflected in the open recordset. ...
    (microsoft.public.vb.general.discussion)
  • Re: Slow Stored Procedure when run via ado, fast from query analyzer
    ... > about 2 seconds from query analyzer, and takes 50+ seconds run from ADO ... If a Recordset - have you checked to see CursorLocation, CursorType, ... Server vs Client, it should be the same ballpark in magnitude. ...
    (microsoft.public.data.ado)
  • Really disconnect a recordset?
    ... Is there a way to completely disconnect a recordset so that it has no memory ... it was created via OLE DB for SQL Server? ... ADO is not allowing me to "update" the these fields. ... Set con = New ADODB.Connection ...
    (microsoft.public.vb.database.ado)
  • Re: Is ADO Dead (3)?
    ... The Design of ADO ... ADO uses a single object, the Recordset, as a common representation for ... a forward-only stream of results from a database, ... where data is updated at the data source or cached locally as with the ...
    (comp.databases.ms-access)