GRANT user INSERT, UPDATE for whole database

36 views
Skip to first unread message

Midsoft UK

unread,
Jan 22, 2021, 12:33:55 PMJan 22
to firebird-support
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

unread,
Jan 22, 2021, 12:43:17 PMJan 22
to firebird-support
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:

Midsoft UK

unread,
Feb 10, 2021, 8:03:03 AMFeb 10
to firebird-support
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

unread,
Feb 10, 2021, 9:44:02 AMFeb 10
to firebird...@googlegroups.com
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

unread,
Feb 11, 2021, 3:07:08 AMFeb 11
to firebird...@googlegroups.com
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

kjell_rilbe.vcf

liviuslivius

unread,
Feb 11, 2021, 6:51:29 AMFeb 11
to firebird...@googlegroups.com
For such case is role public


Regards,
Karol Bieniaszewski


-------- Oryginalna wiadomość --------
Data: 11.02.2021 09:07 (GMT+01:00)
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

unread,
Feb 11, 2021, 11:27:10 AMFeb 11
to firebird...@googlegroups.com
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

unread,
Feb 11, 2021, 11:31:13 AMFeb 11
to firebird...@googlegroups.com
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.
Reply all
Reply to author
Forward
0 new messages