BorlandTalk.com

Jeremy · Guest

I have a stored proc on ms sql server which I am opening with a
tadostoredproc. The proc does this:

select * from tbl1 where tbl1.fld1 in (select fld2 from tbl2 where
tbl2.fld3=@myparm)

The program loops through the table, assigning values to certain fields:

...
sp.edit
sp.fieldvalues['fldxxx'] := myvar; <== error happens here.
sp.post;
sp.next;
...

Some users can run the program just fine, but others can't. The error these
unfortunates get is "select permission denied on tbl1 for user dbo ..". I
gave these folks select permission on the tbl1, and that fixed the problem.

But I really don't want to give anyone direct table permissions, just to the
stored procs.

An interesting side effect seems to be that giving users direct rights to
the table seems to have sped up the process a bunch. What on earth is
Delphi doing inside it's tadostoredproc?

Jeremy

Matt Jacobs · Guest

"Jeremy" <jeremy> wrote:

Del M · Guest

Delphi isn't doing anything inside the stored procedure, SQL Server is doing
it. Do you have a primary key on the table?
Since the stored proc is returning an adorecordset, then you should change
the tadostoredproc to a tadodataset. Set the commandtype to stored
procedure, set the lock type to batchoptimistic, be sure after completion of
your loop that you do "updatebatch" to actually apply the changes to the
table.

If you are looking for speed, you could just use a tadodataset and do a
"update table1 set x=y, z=q, where ... in (select ... "
That would probably go much faster since the database engine will update the
database for you and your delphi program will never even see the data or
send it back and forth between the client and the database.

Jeremy · Guest

Del, I eventually got around to reading the delphi help on this, and it says
that after you issue a tadostoredproc.edit, the component retrieves the
record. At this point it isn't a "stored proc" anymore as far as the sql
connection is concerned, so it needs to check for permissions again to do
this implicit select.

What you say about using a tadorecordset is intriguing. I'll check it out.
However, it sounds like it still is going to need select permission.

Thanks.

Jeremy

"Del M" <Del.Murray (AT) CreditHawk (DOT) Net> wrote

Del M · Guest

you should be using a sql userid that has the correct permissions for the
application. If one user logged in under that userid can perform the
operation then all users under that id can. Go with the tadodataset, your
life will be simpler. Tadostoredproc is for executing stored procedures that
dont return data, to use it any other way is probably going to get you a lot
of grief, IIRC it is a little buggy.

Jeremy · Guest

Del, pardon me if I disagree with your statements.

First, people who are concerned with security prefer NOT giving end users
direct table permissions, but only giving them exec rights to stored
procedures.

I think you're also suggesting that there be a single userid that everyone
uses. Sorry, that would be a very serious security problem that we could
not tolerate. If you mean using an application role to control access, that
would be ok (but each user still should have to authenticate with their own
unique login). This app doesn't happen to use an application role, but I
have other apps that do use them successfully.

As for tadostoredproc being buggy, I don't think so. I have hundreds of
them in use in various apps, and they work extremely well, and very, very
fast -- even across the internet. In fact I have several that return
multiple recordsets. Saves a vast amount of latency which can be an
app-killer across the net.

In the specific case I raised, the behavior doesn't qualify as a bug. True,
I didn't understand how it worked, but now I know.

Jeremy

"Del M" <Del.Murray (AT) CreditHawk (DOT) Net> wrote

Jeremy · Guest

Brian, thanks for reminding me about this. However, it does seem that both
tadostoredproc and tadodataset inherit the same edit method from tdataset,
which actually retrieves the record, thereby requiring the currently
logged-in user to have a rights I don't want them to have.

At some point I'll revise the code to use a stored proc to do the update,
and banish Edit forever.

Thanks

Jeremy

"Brian Bushay TeamB" <BBushay (AT) Nmpls (DOT) com> wrote