GRANT user INSERT, UPDATE for whole database

[Midsoft UK]

Is it possible to GRANT user INSERT, UPDATE for whole database and call it a ROLE?

It's ok done by user but a pain to have to do for all tables.

Thanks

Andy

[Lucas Schatz]

Yes, you can create a ROLE, and grant everything you cant to it

Then, you just need to grant the role to the user, and login the user specifying that role

You can read more here:

https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-access-sql.html

[Midsoft UK]

Hi

What I'm trying to achieve is creating a role that has read/write access to the whole database. I can the login with any user and use the role as needed.

I  can't see anything anywhere that explains that. I can only see it at table level so maybe it's not possible ?

[Mark Rotteveel]

On 10-02-2021 11:36, Midsoft UK wrote:
> Hi
> What I'm trying to achieve is creating a role that has read/write access
> to the whole database. I can the login with any user and use the role as
> needed.

From a security perspective it doesn't make much sense to give all
users all rights. If you want those rights to apply always (instead of
only when the role is specified), you can also grant privileges to USER
PUBLIC.

> I  can't see anything anywhere that explains that. I can only see it at
> table level so maybe it's not possible ?

You cannot grant all privileges on all tables, you need to grant
privileges on each table individually.

So if you have tables TABLE1, TABLE2, TABLE3, you need to execute

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE1 TO ROLE yourrole;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE2 TO ROLE yourrole;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE3 TO ROLE yourrole;
etc..

(If you also want to grant REFERENCES, then you can replace SELECT,
INSERT, UPDATE, DELETE with ALL).

Mark
--
Mark Rotteveel

[Kjell Rilbe]

If I understand Andy correctly, he wants to configure a role or the
database so that the role or every new user is automatically granted
specific rights to every object/table, including new ones that are
created at a later time.

Like setting a "default grant".

I assume this is not possible.

Regards,
Kjell

[liviuslivius]

For such case is role public

Regards,

Karol Bieniaszewski

-------- Oryginalna wiadomość --------

Od: Kjell Rilbe <kjell...@marknadsinformation.se>

Data: 11.02.2021 09:07 (GMT+01:00)

Do: firebird...@googlegroups.com

Temat: Re: [firebird-support] Re: GRANT user INSERT, UPDATE for whole database

If I understand Andy correctly, he wants to configure a role or the
database so that the role or every new user is automatically granted
specific rights to every object/table, including new ones that are
created at a later time.

Like setting a "default grant".

I assume this is not possible.

Regards,
Kjell

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/ea56ec32-4d3f-41b1-1a3a-e7de7dcfba42%40marknadsinformation.se.

[Mark Rotteveel]

On 11-02-2021 09:06, Kjell Rilbe wrote:
> If I understand Andy correctly, he wants to configure a role or the
> database so that the role or every new user is automatically granted
> specific rights to every object/table, including new ones that are
> created at a later time.
>
> Like setting a "default grant".
>
> I assume this is not possible.

You can grant roles and privileges to all users by granting it to user
PUBLIC.

But you cannot grant privileges on objects that will be created in the
future, because you must grants privileges on each object individually.

In contrast, PostgreSQL has a feature `GRANT <privileges> ON ALL TABLES
IN SCHEMA <schema_list>`, which will grant you privileges on all tables
and views in the specified schema, even if created at a later point.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

11.02.2021 17:27, Mark Rotteveel wrote:
> But you cannot grant privileges on objects that will be created in the future, because you
> must grants privileges on each object individually.

In older versions of Firebird if no grants was issued in a database at all it worked in
"no security" node and everybody had access to everything.
I don't know if it is still the case nowadays though.

--
WBR, SD.