So, by the moment, the best thing I can do is what Alexandre
suggested but I still have the following problems:

1. The user will be allowed to create a table. Is there a way to
avoid this?

2. eMeL said that executing a SELECT with a SP, the read only user
can modify the database. Is this true even if I don't grant EXECUTE
to this user?

Any other suggestion of creating a read only user another way?

Gustavo

--- In firebird-support@yahoogroups.com, Alexandre Benson Smith
<iblist@...> wrote:

>
> 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
>