Login and autodetect role

[baur...@gmail.com]

Hello,

I would like to solve following issue:

• Currently there is only one role setup in my DB. All tables etc. have permissions set in this role
• I need to add a new role for readonly access –> np, I set it up and configure the permissions
• The actual user of the system does not know about roles
• The ideal scenario would be that he logs in into my app (and from there into Firebird) without specifying the role

The best idea I had so far:

1. Login without a role
2. Check the assigned roles by:  SELECT u.RDB$USER,   u.RDB$RELATION_NAME FROM RDB$USER_PRIVILEGES u WHERE u.RDB$PRIVILEGE = 'M'  AND  u.RDB$USER = ‚mycurrentuser‘
3. Logout
4. Login with the previous identified role

Most of the users work with trusted authentication or Win_Sspi.

Please let me know what you think about it.

 Thanks

 Niko

[Mark Rotteveel]

On 06-08-2021 10:35, baur...@gmail.com wrote:
> I would like to solve following issue:
>

> * Currently there is only one role setup in my DB. All tables etc.

> have permissions set in this role

> * I need to add a new role for readonly access –> np, I set it up and
> configure the permissions
> * The actual user of the system does not know about roles
> * The ideal scenario would be that he logs in into my app (and from

> there into Firebird) without specifying the role
>
> The best idea I had so far:
>

> 1. Login without a role
> 2. Check the assigned roles by:  SELECT u.RDB$USER,

>   u.RDB$RELATION_NAME FROM RDB$USER_PRIVILEGES u WHERE
> u.RDB$PRIVILEGE = 'M'  AND  u.RDB$USER = ‚mycurrentuser‘

> 3. Logout
> 4. Login with the previous identified role

>
> Most of the users work with trusted authentication or Win_Sspi.
>
> Please let me know what you think about it.

In Firebird 3.0, you can use authentication mapping[1] to assign a role
on login (though I don't think it offers enough flexibility to do this
without creating a mapping rule per user). In Firebird 4.0, you can
assign a role as a default role[2],[3] which means it will be used
automatically.

Mark

[1]:
https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html#fblangref30-security-mapping
[2]:
https://www.firebirdsql.org/file/documentation/release_notes/html/en/4_0/rlsnotes40.html#rnfb4-grant-role-default
[3]:
https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-security-grant-role-default
--
Mark Rotteveel

[baur...@gmail.com]

Hello Mark,

thanks for the response. I tried the mapping on FB 3.0.7 und could get the needed result. 

With FB 3 it is one mapping per user: CREATE MAPPING READONLYUSER1 USING PLUGIN SRP IN "testdatabase" FROM USER READONLY1 TO ROLE READONLYUSERS;

Niko

[Jiří Činčura]

What are you trying to solve with it? Security? Limit users? Or?

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

On Fri, Aug 6, 2021, at 10:35, baur...@gmail.com wrote:
> Hello,
>
>
> I would like to solve following issue:
>

> * Currently there is only one role setup in my DB. All tables etc.

> have permissions set in this role

> * I need to add a new role for readonly access –> np, I set it up and
> configure the permissions
> * The actual user of the system does not know about roles
> * The ideal scenario would be that he logs in into my app (and from

> there into Firebird) without specifying the role
>
> The best idea I had so far:
>

> 1. Login without a role

> 2. Check the assigned roles by: SELECT u.RDB$USER,

> u.RDB$RELATION_NAME FROM RDB$USER_PRIVILEGES u WHERE u.RDB$PRIVILEGE =
> 'M' AND u.RDB$USER = ‚mycurrentuser‘

> 3. Logout
> 4. Login with the previous identified role

> Most of the users work with trusted authentication or Win_Sspi.
>
> Please let me know what you think about it.
>
> Thanks
>
> Niko
>
>

> --
> You received this message because you are subscribed to the Google
> Groups "firebird-net-provider" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to firebird-net-pro...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/firebird-net-provider/374041d6-a080-4012-8744-d39b774e31f3n%40googlegroups.com <https://groups.google.com/d/msgid/firebird-net-provider/374041d6-a080-4012-8744-d39b774e31f3n%40googlegroups.com?utm_medium=email&utm_source=footer>.

[baur...@gmail.com]

Hello Jiri,

the current desktop client is build for reading and writing all data. There are requests from customers, the have a readonly role as well.

On aspect is the security on database level via the Firebird role, another on to get the desktop program to handle readonly users: forbid and/or catch all writing attempts with a clear error message.

Niko

[Jiří Činčura]

On database level because access happens not only via desktop app? The reason I'm asking is whether it wouldn't be easier to handle this in the application.