Session specific role

[livius...@poczta.onet.pl]

Hi

In Firebird, is it possible to connect using a user account without specifying a role, and then somehow assign or set a role later within the same session?

The scenario I have in mind is as follows:

a user connects through my application and has permissions (via some logic),

but if they tried to connect manually (e.g., using ISQL, FlameRobin, etc.), they would not have those permissions.

For example, suppose there's a stored procedure that accepts a token as a parameter — a token calculated based on some internal logic, such as the username and the current date.

The procedure calls a UDR (User Defined Routine) to validate the token, and if it's valid, it "assigns" a session-specific role to the user.

Of course, the actual user account doesn’t have that role permanently. Once the session ends, the role is gone. So if the user tries to connect using ISQL, they won’t have access.

Is something like this possible in Firebird? Or is the role assignment strictly limited to the point of connection?

Regards,

Karol Bieniaszewski

[Attila Molnár]

https://www.firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref40/fblangref40-management-role.html

[Mark Rotteveel]

On 16/05/2025 08:08, liviuslivius via firebird-support wrote:
> In Firebird, is it possible to connect using a user account /without/
> specifying a role, and then somehow assign or set a role later /within
> the same session/?

>
> The scenario I have in mind is as follows:
>
> a user connects through my application and has permissions (via some
> logic),
> but if they tried to connect manually (e.g., using ISQL, FlameRobin,
> etc.), they would not have those permissions.
>
> For example, suppose there's a stored procedure that accepts a token as
> a parameter — a token calculated based on some internal logic, such as
> the username and the current date.
>
> The procedure calls a UDR (User Defined Routine) to validate the token,
> and if it's valid, it "assigns" a session-specific role to the user.
>
> Of course, the actual user account doesn’t have that role permanently.
> Once the session ends, the role is gone. So if the user tries to connect
> using ISQL, they won’t have access.
>
> Is something like this possible in Firebird? Or is the role assignment
> strictly limited to the point of connection?

There is SET ROLE[1], available using Firebird 4, to change the role,
but you'd have to grant and revoke the role on the fly if you don't want
the user to use the role otherwise.

Depending on your needs, it might be simpler to write a procedure to do
the things you want, grant the necessary rights to the procedure, and
let the procedure decide if the user is allowed to do the thing or not,
instead.

Mark

[1]:
https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref50/fblangref50-management-role.html#fblangref50-management-role-set

--
Mark Rotteveel

[livius...@poczta.onet.pl]

Hi

SET ROLE XXX;  is quite helpfull but first i must grant this role to the user :-(

--

Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:

https://www.firebirdsql.org/donate

---

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, visit https://groups.google.com/d/msgid/firebird-support/2517aa85-a5e1-4a95-8cfe-1c79758d3667n%40googlegroups.com.