Commented by: @AlexPeshkoff

This may be done using WITH RECURSIVE sql query on system tables. Do not want to say that virtual table is not needed at all - but goal can be reached w/o it.

Commented by: @sim1984

Alex, one small question

GRANT DEFAULT R1 TO USER IVAN;
GRANT DEFAULT R2 TO USER IVAN;

What value will CURRENT_ROLE after connection?

Commented by: @dyemanov

Currently, it's NONE. And I see no better option.

Commented by: @sim1984

Thus in CURRENT_ROLE can only be the role specified when connected or setted using the SET ROLE?

However, in some cases, it is useful to know what of the roles a user has privileges to the example above.

Commented by: @dyemanov

Alex: select on systables is not ideal, some roles may be granted after the current connection has been established and in fact they won't be active.

Commented by: Bjoern Reimer (bnreimer)

> Currently, it's NONE. And I see no better option.

Please don't do this. It will break compatibility of older code whenever an admin grants a second role!

I'd suggest to let it be the first role a user is granted and add a boolean variable (e.g.) more_roles which should be true when there are other roles to check for.

Commented by: @AlexPeshkoff

Bjoern, this does not break anything. CURRENT_ROLE now is displayed when it's selected _explicitly_ by the user when connecting to DB (or using SET ROLE in FB3). Default roles never existed in firebird and therefore could not be displayed.

Commented by: @dyemanov

First of all, older code will continue to work as long as it specifies role at connect time. We're speaking about implicit (default) roles here. Second, we don't have a historical info what role was granted first, so "the first role" could be either old one or the newly granted one, unpredictably. So other mechanisms should be used to check for multiple implicit roles.

Commented by: Bjoern Reimer (bnreimer)

Ok, I missed that you where talking only about DEFAULT Roles and that the handling of explicait roles will not change:

Am I right here:

GRANT DEFAULT R1 TO USER IVAN;
GRANT R TO USER IVAN;

Ivan loggs in with R

SELECT current_user, current_role FROM RDB$DATABASE
-> 'Ivan', R

GRANT DEFAULT R1 TO USER IVAN;
REVOKE R FROM USER IVAN;

Ivan loggs in with R
SELECT current_user, current_role FROM RDB$DATABASE
-> 'Ivan', NONE

Commented by: @AlexPeshkoff

Yes, you are absolutely right (at least it's designed to be so).
BTW, you can experiment with snapshot build.