At 02:08 PM 28/02/2008, you wrote:

>We have the application that uses Firebird 2.0.3 Server and accesses
>to is through ODBC (Firebird driver 1.2).
>When application creates stored procedures under non-SYSDBA user and
>then tries to execute some of them
>under restricted user, function that called execution hangs with no
>result.

The application should not create stored procedures. The database owner should create them in the lab, having first ensured that the procedure has SQL privileges for all the operations and objects it will touch and any users that are going to execute them have the required EXECUTE privileges. (SQL privilege rules apply also to objects touched by blocks of PSQL within an EXECUTE BLOCK statement.)

When you try to do illegal things in SQL, whether violations of access rules or any other exception condition, the engine politely returns exceptions to the application. If you are seeing a "hang" instead of an exception message, I would guess that your exception handlers are swallowing exceptions. They shouldn't, they should *handle* them in such a way that the exception condition can be fixed.

But then, one cannot tell from this description whether in fact this SP ever gets compiled, i.e., survives a COMMIT call. And in Firebird, SPs can't be executed without being compiled and actually coming into existence. EXECUTE BLOCK is the nearest Firebird gets to running uncompiled PSQL.

>Also such query: UPDATE <sometable> SET BLOCK = (SELECT BLOCK FROM
><anothertable> WHERE <someUID> = 1) WHERE <someUID> = 1" hangs,
>where BLOCK is binary BLOB.
>
>Is there any solution or workaround for this?

Correct your syntax:

update sometable s
set s.block = (
select a.block from anothertable a
where a.someUID = s.someUID
)
where s.someUID = 1

>P.S. Also this problem can be reproduced with FB 2.0.0, FB 2.0.1 and
>2.0b ODBC FB driver.

That's not surprising. ;-)

./heLen