Dne 24.5.2010 15:30, Michael Ludwig napsal(a):

> PenWin schrieb am 24.05.2010 um 14:25:45 (+0200):
>
>> Interesting. To get a more controllable environment, I created a new
>> database with just the two tables, one procedure and one trigger. I
>> granted the rights I needed and tried the sequence above. And to my
>> great surprise, it worked: user INTERNET can call stored procedure
>> INTERNET_INSERT_ROW, which will insert a record into SOMETABLE, which
>> fires the trigger which inserts another record to LOGTABLE.
>>
>> But I just can't get the same process to work on my "real" database.
>
> If it doesn't work, how does it fail?

From my first post:

Now with the loggins tables, I have this sequence:

1) User INTERNET calls procedure INTERNET_INSERT_ROW.
2) Procedure INTERNET_INSERT_ROW validates data and writes it to SOMETABLE.
3) AFTER INSERT trigger LOGTRIGGER_SOMETABLE fires.
4) LOGTRIGGER_SOMETABLE writes the new record from SOMETABLE to LOGTABLE.

Unfortunately, at step #4 I am getting an error no permission for
insert/write access to TABLE LOGTABLE, and after a number of failed
attempts, I am at a loss what to do. I tried granting INSERT ON LOGTABLE
to either INTERNET (the user) or INTERNET_INSERT_ROW (the procedure that
does the inserting), but unfortunately it didn't change anything.

>> What would be the best way of debugging the cause?
>
> I would write a script that drops and creates all the objects you need
> for your setup so that you only need to call it using INPUT from ISQL to
> have everything created. You could then call that on your real database,
> taking care, of course, not to delete real data. :-)
>
> Things to consider:
>
> * SET AUTODDL and COMMIT

That only applies to ISQL, I believe. Since I need to run my scripts
through a custom utility, which has autocommit on, it won't help me much
I am afraid. I'll see about adding explicit COMMITs, to be sure.

> * dependencies and correct sequence of DROP/CREATE

Verified: those are correct.

> * SHOW GRANT output

That is also just an ISQL command. I used it to make sure the grants are
applied correctly, and sure enough, they are - all objects where I would
expect a right do have it.

Pepak