Abstract: Database originally created before InterBase 6.5 may have this error
Q. I'm getting the error no permission for read/select access to table RDB$XXXX by user SYSDBA with InterBase XE. How do I resolve this error?
A. InterBase XE enforces tighter meta data security and this error may result from doing meta data operations on databases originally created with versions of InterBase prior to version 6.5. Meta data operations involve requesting information about system objects such as listing system objects or updating them and using the Performance Monitor.
To resolve this error you need to perform four similar operations. The first operation grants rights to system tables. To do so, execute readmeta.sql from the examples\security folder in your InterBase install directory.You can execute readmeta.sql against your database using isql or IBConsole.
Example using isql:
isql "path to database" -user sysdba -password masterkey -i readmeta.sql
Executing readmeta.sql with IBConsole
1. Connect to your database with IBConsole.
2. Tools | Interactive SQL ...
3. Query | Load Script, select readmeta.sql, click OK.
Second, you need to grant rights to system temorary tables if you are going to do performance monitoring. Due to potential security concerns, most installations will want to grant rights for system temporary tables to only sysdba and the database owner only, which is what is presented below. If you wish for all users to be able to view system temporary tables, modify this example to GRANT TO PUBLIC. Some installations will want only specific users to have rights, in which case a more customized script may be needed.
To grant rights for system temporary tables, save the following as a text file, then execute it the same as readmeta.sql above.
create procedure granttmp as
declare variable stmt varchar(1024);
declare variable ownername varchar(66);
declare variable tablename varchar(66);
select rdb$owner_name from rdb$relations where rdb$relation_name = 'RDB$RELATIONS'
for select rdb$relation_name from rdb$relations where rdb$system_flag>0
and rdb$relation_name starts with 'TMP$' into :tablename do
stmt = 'grant all on ' || tablename || ' to sysdba';
execute statement stmt;
stmt = 'grant all on ' || tablename || ' to ' || ownername;
execute statement stmt;
execute procedure granttmp;
drop procedure granttmp;
Third, if your database has encryption you may need to grant rights to the RDB$ENCRYPTIONS system table. The Sysdso user must grant rights on the RDB$ENCRYPTIONS table. Execute the following when logged in as Sysds:
REVOKE ALL ON RDB$ENCRYPTIONS FROM PUBLIC;
GRANT SELECT ON RDB$ENCRYPTIONS TO PUBLIC;
Note that you may not want to all users to see what encryptions are available. In that case, switch PUBLIC to SYSDBA and whatever other users you want to be able to see encryptions.
Fourth and finally, if your database has End User Authentication (EUA) enabled you may need to grant rights to the RDB$USERS system table. Execute the following as the sysdba user:
REVOKE ALL ON RDB$USERS FROM PUBLIC;
GRANT SELECT ON RDB$USERS TO PUBLIC;
Note that just like RDB$ENCRYPTIONS above, you may not want to all users to see what all user names are. In that case, switch PUBLIC to SYSDBA and whatever other users you want to be able to see user names.