Re: [Firebird-devel] What does SET TRUSTED ROLE do?
On 09-02-2021 17:48, Alex Peshkoff via Firebird-devel wrote: I do wonder about this case. Specifically, why am I not able to execute SET ROLE TESTROLE in this case? According to the mapping I'm allowed to use this role. According to SQL specification set role shall fail if user is not granted the role. And allowed to use due to mapping and granted are different things. That's the second reason to have such form of statement at all, first - to help not think about what particular role became available due to mapping. In such scenario: set role special_role; -- perform some action that require special_role -- need to restore originak trusted role set trusted role; The problem I see is that it requires knowledge how the original role was obtained. This isn't really problematic for interactive users, but might be more of an issue for programs that for example want to restore the original role after temporarily switching roles and generate a statement using, for example, SELECT 'set role "' || TRIM(CURRENT_ROLE) || '";' FROM RDB$DATABASE then changes the role, and then tries to revert to the original role using the generated statement. Then again, such scenario is probably not very common. Maybe that is something we need to consider if this actually happens though, but we could bend the interpretation of the standard requirement so that a role assigned through a mapping rule is also considered granted to the user. If I have to summarize this feature, it is "allows the user to assume the mapped role, even when they connected with a different role". Would that be accurate? May be more accurate 'set current_role = trusted(mapped) role', no matter why curent_role differs. Would SET MAPPED ROLE have been a better name for this feature? Trusted here means that it comes from plugin to which database trusts enough (via mappings) to make that role available even if it was not explicitly granted. As I understand it, trusted roles are a subset of mapped roles (e.g. a mapped role is any role assigned by a MAPPING rule, while a trusted role is a mapped role which has not been explicitly granted to the user). Never thought this way :-) Thanks, I think I now have sufficient understanding of SET TRUSTED ROLE to write the documentation. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] What does SET TRUSTED ROLE do?
On 2/9/21 6:23 PM, Mark Rotteveel wrote: CREATE MAPPING WIN_ADMINS USING PLUGIN WIN_SSPI FROM Predefined_Group DOMAIN_ANY_RID_ADMINS TO ROLE RDB$ADMIN ``` While SET AUTO ADMIN MAPPING will apply RDB$ADMIN automatically if no explicit role is specified on connect. In other words, does my confusion derive from the fact the Firebird 3 release notes say that CREATE MAPPING ... TO ROLE ... updates the CURRENT_ROLE, while in fact it does no such thing? That 2 commands behave ABSOLUTELY identical - current_role will become RDB$ADMIN after attach to database if no explicit role is specified on connect. SET AUTO ADMIN MAPPING is just syntax sugar in grammar parser needed for backward compatibility, internally absolutely sam things are done. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] What does SET TRUSTED ROLE do?
On 2/9/21 7:01 PM, Mark Rotteveel wrote: On 09-02-2021 16:23, Mark Rotteveel wrote: In other words, does my confusion derive from the fact the Firebird 3 release notes say that CREATE MAPPING ... TO ROLE ... updates the CURRENT_ROLE, while in fact it does no such thing? Having done some further tests, a mapping will actually set CURRENT_ROLE if no explicit role was specified, but you can then use SET TRUSTED ROLE to update the CURRENT_ROLE to that mapped role. In addition SET TRUSTED ROLE can even be used if the user has not been explicitly granted the role. For example create mapping test_mark1_testrole using plugin srp from user mark1 to role testrole; connect 'database' user mark1 password 'mark1' role none; select current_role from rdb$database; ROLE === NONE set role testrole; Statement failed, SQLSTATE = 0P000 Role TESTROLE is invalid or unavailable set trusted role; select current_role from rdb$database; ROLE === TESTROLE I do wonder about this case. Specifically, why am I not able to execute SET ROLE TESTROLE in this case? According to the mapping I'm allowed to use this role. According to SQL specification set role shall fail if user is not granted the role. And allowed to use due to mapping and granted are different things. That's the second reason to have such form of statement at all, first - to help not think about what particular role became available due to mapping. In such scenario: set role special_role; -- perform some action that require special_role -- need to restore originak trusted role set trusted role; If I have to summarize this feature, it is "allows the user to assume the mapped role, even when they connected with a different role". Would that be accurate? May be more accurate 'set current_role = trusted(mapped) role', no matter why curent_role differs. Would SET MAPPED ROLE have been a better name for this feature? Trusted here means that it comes from plugin to which database trusts enough (via mappings) to make that role available even if it was not explicitly granted. As I understand it, trusted roles are a subset of mapped roles (e.g. a mapped role is any role assigned by a MAPPING rule, while a trusted role is a mapped role which has not been explicitly granted to the user). Never thought this way :-) Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] What does SET TRUSTED ROLE do?
On 09-02-2021 16:23, Mark Rotteveel wrote: In other words, does my confusion derive from the fact the Firebird 3 release notes say that CREATE MAPPING ... TO ROLE ... updates the CURRENT_ROLE, while in fact it does no such thing? Having done some further tests, a mapping will actually set CURRENT_ROLE if no explicit role was specified, but you can then use SET TRUSTED ROLE to update the CURRENT_ROLE to that mapped role. In addition SET TRUSTED ROLE can even be used if the user has not been explicitly granted the role. For example create mapping test_mark1_testrole using plugin srp from user mark1 to role testrole; connect 'database' user mark1 password 'mark1' role none; select current_role from rdb$database; ROLE === NONE set role testrole; Statement failed, SQLSTATE = 0P000 Role TESTROLE is invalid or unavailable set trusted role; select current_role from rdb$database; ROLE === TESTROLE I do wonder about this case. Specifically, why am I not able to execute SET ROLE TESTROLE in this case? According to the mapping I'm allowed to use this role. If I have to summarize this feature, it is "allows the user to assume the mapped role, even when they connected with a different role". Would that be accurate? Would SET MAPPED ROLE have been a better name for this feature? As I understand it, trusted roles are a subset of mapped roles (e.g. a mapped role is any role assigned by a MAPPING rule, while a trusted role is a mapped role which has not been explicitly granted to the user). Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] What does SET TRUSTED ROLE do?
On 09-02-2021 15:52, Alex Peshkoff via Firebird-devel wrote: On 2/9/21 5:42 PM, Mark Rotteveel wrote: I'm working on the Firebird 3 Language Reference, and I'm currently writing documentation for SET TRUSTED ROLE. I have tried reading the Firebird 3 release notes, and a Google-translated version of the Russian Firebird 3 Language Reference, but the meaning and intent of this feature is unclear to me. Please first of all read (in doc/sql.extensions/) README.mapping.html & README.set_role. They contain answers to your questions, but if something remains unclear anyway, I'll be glad to answer. README.set_role.txt is not helpful. The README.mapping.html is also not really explicit about trusted roles, the only mention of trusted roles is this example: """ Map windows group to trusted firebird role: CREATE MAPPING WINGROUP1 USING PLUGIN WIN_SSPI FROM GROUP GROUP_NAME TO ROLE ROLE_NAME; """ Exactly what makes this a trusted role? If I read between the lines, this would indicate that mapping TO ROLE _role_name_ is what defines a "trusted role", which would mean it does not actually update CURRENT_ROLE, which would contradict what is documented in the Firebird 3 release notes: """ The single solution for all such cases is mapping the login information assigned to a user when it connects to a Firebird server to internal security objects in a database — CURRENT_USER and CURRENT_ROLE. """ It also seems to contradict documentation in the Russian Firebird 3 Language Reference (page 508), which indicates that ALTER ROLE RDB$ADMIN SET AUTO ADMIN MAPPING is a equivalent to: ``` CREATE MAPPING WIN_ADMINS USING PLUGIN WIN_SSPI FROM Predefined_Group DOMAIN_ANY_RID_ADMINS TO ROLE RDB$ADMIN ``` While SET AUTO ADMIN MAPPING will apply RDB$ADMIN automatically if no explicit role is specified on connect. In other words, does my confusion derive from the fact the Firebird 3 release notes say that CREATE MAPPING ... TO ROLE ... updates the CURRENT_ROLE, while in fact it does no such thing? Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] What does SET TRUSTED ROLE do?
On 2/9/21 5:42 PM, Mark Rotteveel wrote: I'm working on the Firebird 3 Language Reference, and I'm currently writing documentation for SET TRUSTED ROLE. I have tried reading the Firebird 3 release notes, and a Google-translated version of the Russian Firebird 3 Language Reference, but the meaning and intent of this feature is unclear to me. Please first of all read (in doc/sql.extensions/) README.mapping.html & README.set_role. They contain answers to your questions, but if something remains unclear anyway, I'll be glad to answer. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
