by Paul Reeves
One of the new features of Firebird 3.0 is GRANT/REVOKE USAGE on exceptions and sequences. If you only ever use the SYSDBA account for access to the database then you won't notice this feature, but as soon as you try to access the database with a different user account you will run into an error if the user has not been granted usage on a generator or an exception.
Essentially, the security model which worked up to Firebird 2.5 is sort of broken now. It is now not enough, for example, to grant insert on a table to a user. If that table uses a generator the insert will fail if the user has not been granted USAGE on the generator. Usage ought to be implicit and it is difficult to imagine why this is not the default.
We have a similar problem with exceptions. A user may have EXECUTE privileges on a stored procedure but if that SP throws an exception and they don't have USAGE on the exception then an exception is raised saying they don't have usage on the exception. I guess there may be cases when hiding exceptions might be useful but I so far haven't thought of a general case which justifies this as the default behaviour.
The simplest obvious work around to this is just to add a
GRANT USAGE ON object TO PUBLIC
after every
CREATE GENERATOR | EXCEPTION
statement. Except of course, if that becomes common practice then why isn't it the default behaviour in Firebird anyway? After all, if someone can come up with a reason for not granting usage automatically they can always execute
REVOKE USAGE ON object FROM PUBLIC
and then assign specific grants as required.
Ideally, a future version of Firebird will grant usage to public by default but until then here is a work around to simplify things
SET TERM ^;
CREATE OR ALTER TRIGGER grant_public_usage ACTIVE AFTER ANY DDL STATEMENT
AS
DECLARE sql VARCHAR(256);
BEGIN
-- First, check event type. For now, we are only interested in CREATE
-- but this could be modified to test for ALTER and DROP
IF (rdb$get_context('DDL_TRIGGER','EVENT_TYPE') = 'CREATE' ) THEN BEGIN
-- Now check the object type - we are only interested in EXCEPTIONS
-- and SEQUENCES. Note that GENERATOR does not exist as an object type
IF ( rdb$get_context('DDL_TRIGGER','OBJECT_TYPE') = 'EXCEPTION') THEN BEGIN
sql = 'grant usage on exception ' || rdb$get_context('DDL_TRIGGER', 'OBJECT_NAME' ) || ' to public';
END
IF ( rdb$get_context('DDL_TRIGGER','OBJECT_TYPE') = 'SEQUENCE') THEN BEGIN
sql = 'grant usage on sequence ' || rdb$get_context('DDL_TRIGGER', 'OBJECT_NAME' ) || ' to public';
END
END
-- Now make sure we only execute the sql if our statement is not null
-- An error here will cause a rollback and the CREATE statement will fail.
IF ( :sql IS NOT NULL ) THEN BEGIN
execute STATEMENT :sql;
END
END ^
SET TERM ;^
The above is a DDL trigger that will fire after every ddl statement. It could be refined to only fire when a CREATE SEQUENCE | EXCEPTION is executed but that is an exercise left for the reader. It will automatically create and execute a 'grant usage on object to public' statement so you don't have to.
A Note about Database Level Triggers
DDL Triggers, like triggers on transactions and attachments can have catastrophic effects if there is even a minor error of execution logic. Always test them carefully in a development environment before deployment. And remember - ISQL and GBAK both have options to disable execution of database triggers. If something bad happens be ready to use isql with the -NOD parameter to quickly access the database and execute something like
ALTER TRIGGER mytrigger INACTIVE
if something has gone wrong. Once a database trigger has been fully debugged it can be used with confidence in production but until then you should proceed with caution.
Debugging database level triggers is not easy. Here is a tip to help you see what value has been assigned to a variable. In Firebird 3 you can now declare exceptions with parameters. Declare:
CREATE EXCEPTION exception_trigger_var 'Var @1 has value @2';
and then raise an exception with the variable. For example, in the above trigger you could check the value of :sql like this...
IF ( :sql IS NOT NULL ) THEN BEGIN
EXCEPTION exception_trigger_var USING ('sql', sql);