Gustavo wrote:

> Hello:
>
> I use FireBird 2, Delphi 5 and IBX.
>
> I have an application wich uses many databases. Let´s suppose they are 10 and each database has 100 tables. I need to create a user wich has read only privileges to every table in every database.
>
> The only way I know to do that is the following:
>
> 1. Create a user PEPE (using IB_SQL).
> 2. Connect to DATABASE1 using user SYSDBA
> 3. Execute GRANT SELECT ON TABLE001 TO PEPE
> Execute GRANT SELECT ON TABLE002 TO PEPE
> ....
> Execute GRANT SELECT ON TABLE100 TO PEPE
> .............
> Repeat steps 2 and 3 for the 10 databases.
>
> Is there another way to do this without using 1000 SQL statements?
>
> The problem is not only the number of SQL statements. In my application, the tables are created as needed. So, if I create the user now and execute every SQL statements for every table, perhaps tomorrow there will be a new table and the user PEPE won´t have access to it.
>
> Another thing is that the user PEPE will be allowed to create a new table (for example executing CREATE TABLE NEWTABLE1(NAME VARCHAR(10)) and I don´t want this. I need that the only thing the user PEPE can do is to see (SELECT) every register in every table in every database.
>
> Is this possible? How?
>
> Thanks in advance.
>
> Gustavo
>

Not what you are expecting to hear... but will solve your problem.

The quick answer is no.

But there are a lot of alternatives to acomplish this.

1.)
select 'grant select on ' || r.rdb$relation_name || ' to Pepe;' from
rdb$relations r where r.rdb$view_blr is null and r.rdb$system_flag = 0;

execute this, save the output to a file and run that "script" against
each database.

2.) if you use FB 2.0
execute block as

declare variable wTableName varchar(30);
declare variable wstm varchar(255);

begin
for
select
r.rdb$relation_name
from
rdb$relations r
where
rdb$view_blr is null and
rdb$system_flag = 0
into :wTableName
do begin
wstm = 'grant select on ' || wTableName || ' to PEPE;';
execute statement wstm;
end

end;

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br