""Bill Oliver"" <bill.oliver@...> wrote in message
news:<dg47q0+6p4l@...>...

> Hello,
>
> A co-worker asks if there is a way to protect the firebird system
> tables, for example RDB$PAGES, from destructive users.

Yes, there's one.

> In the example below, I login as SYSDBA and can delete all rows from
> the RDB$PAGES table, corrupting the database. I also tried this with
> a non-sysdba user and was able to do the same thing.
>
> C:\temp>isql
> Use CONNECT or CREATE DATABASE to specify a database
> SQL> create database 'syst.fdb';
> SQL> show table rdb$pages;
> RDB$PAGE_NUMBER (RDB$PAGE_NUMBER) INTEGER Nullable
> RDB$RELATION_ID (RDB$RELATION_ID) SMALLINT Nullable
> RDB$PAGE_SEQUENCE (RDB$PAGE_SEQUENCE) INTEGER Nullable
> RDB$PAGE_TYPE (RDB$PAGE_TYPE) SMALLINT Nullable
> SQL> delete from rdb$pages;
> SQL> select * from rdb$pages;
> SQL> quit;

And when you reconnect, you get a message about corruption and I doubt you
will be able to fix it. I noticed and posted this obvious flaw much before
you came to this project, when we were at FB1.

> I tried revoking privileges, but could still delete from this table.
> Here is what I tried:
>
> SQL> revoke all on user1 from rdb$pages;
> SQL> commit;
> SQL> quit;
> C:\temp>isql -u user1 -p masterkey TT.FDB
> Database: TT.FDB, User: user1
> SQL> delete from rdb$pages;
> SQL> commit;

No, it doesn't work because the SQL model and underlying GDML model do not
interact very well, so you get strange results.

> Any suggestions? Please post back with a SQL snippet os specific
> recommendations if you have an idea as to how to protect these
> tables. Thanks!

This is all you want to do:

SQL> grant select on rdb$pages to public;
SQL> grant all on rdb$pages to sysdba;

After that, if you log in with your unprivileged user, you will get:

SQL> delete from rdb$pages;
Statement failed, SQLCODE = -551
no permission for delete/write access to TABLE RDB$PAGES

The problem is that the exact set of rights cannot be expressed in SQL. If
you want to know how to do it accurately, look at ini.epp below the comment:

Add security on RDB$ROLES system table

I think we should do the same call add_security_to_sys_rel for all critical
system tables, not only rdb$pages, but rdb$formats as well. If I delete
rdb$formats, disconnect, reconnect and try to insert into an user table, the
engine crashes.

C.