Subject Re: [IBO] REVOKE and GRANT
Author Helen Borrie
At 08:59 PM 28/05/2004 +0000, you wrote:
>This command:
>ExecuteDDL('REVOKE ALL ON ' + CurrentTable.Text + ' FROM ' +
>lvPermissions.Selected.Caption, error);
>is not returning an error when the current user does not have
>reference permission, but
>DataAcc.ExecuteDDL('GRANT ALL ON ' + CurrentTable.Text + ' to ' +
>lvPermissions.Selected.Caption, error);
>acts correctly and generates an error. Can anyone explain this? I
>should note that the permissions are NOT revoked in this case, but
>there is no error which is causing program flow problems.
>Here is my ExecuteDDL proc
>function TDataAcc.ExecuteDDL(DDL:String; var error:string):boolean;
> result := false;
> error:='';
> try
> IB_DSQL.SQL.Text := DDL;
> IB_DSQL.Execute;
> result := true;
> except
> on e:Exception do
> begin
> error := E.Message;
> end;
> end;
>I have also tried putting the DSQL in manually in the revoke statement
>to make sure it wasn't my procedures faults but again, no error.

SQL permissions are subject to strange rules. GRANT and REVOKE are not
symmetrical, even though the language elements are.

Any attempt to GRANT privs that the user doesn't own results in an
error. However, an attempt to revoke privs that the user doesn't own is
just "swallowed". Internally, it's just a query that returns no result and
that is never an exception in SQL.

Something to note about TIB_DSQL: you should include this before your
Execute call:

if not Prepared then Prepare;

It's not really an issue with this particular function, since you are
completely destroying the state of the statement each time (by replacing
the SQL.text); but, in parameterised statements, you'd get an exception if
you tried to apply a parameter value to an unprepared statement.