Search code examples
selectfirebirdsql-grantfirebird2.1

Granting SELECT on all tables to a user in Firebird 2.1

I've added a user to a Firebird 2.1 instance using gsec, but now I wanted to grant SELECT on all tables to this new user. I could find how to grant this permission on specific tables, but not to them all:

GRANT SELECT ON TABLE table TO USER user;

If I try to use the new user I get the following error on isql:

no permission for read/select access to TABLE table

Is there a way to do that on Firebird 2.1?

Solution

  • Something like this:

    EXECUTE BLOCK
AS
  DECLARE VARIABLE tablename VARCHAR(32);
BEGIN
  FOR SELECT rdb$relation_name
  FROM rdb$relations
  WHERE rdb$view_blr IS NULL
  AND (rdb$system_flag IS NULL OR rdb$system_flag = 0)
  INTO :tablename DO
  BEGIN
    EXECUTE STATEMENT ('GRANT SELECT ON TABLE ' || :tablename || ' TO USER TEST');
  END
END