At 17:29 28/09/2008, Bjoern Reimer wrote:

>Good morning from Bergamo,
>
>well, Vlad told me to post the topic to the support list.

Considering that your posting now appears to be a feature request, it would have been better advice to suggest posting to firebird-devel or firebird-architect.

>>> It's a known "feaure" I think, but it's still not as it should be.
>>>
>>> If I log on to firebird server with username, password and role and
>>> I was not granted to the role or have misspelled the role, there is
>>> no visible reaction of firebird.
>>>
>>> I always have to check via
>>> SELECT current_role FROM RDB$DATABASE
>>> for example.
>>>
>>> I'd expect at least a warning.
>
>> What would the warning be about?
>That a the user does not get the role he requested.

To achieve this, there would have to be some structural changes in databases, i.e., an ODS change, as well as cross-database dependencies between the security database and all user databases. That does not exist yet, although it is in future plans to have "dedicated" security databases.

Maybe you are confused in thinking that USER is a child of a ROLE...? It is not. A USER has no dependency on a role. As I mentioned in the first reply, a role is nothing but a package of privileges that can be granted to a user.

>I'm not aware of a command to change role when I'm logged on to a
>database. I can only give a role to a user during connect to db. If I
>do so, I expect the database would grant me the privileges of the role
>or throws an error.

Privileges are on *database objects*, not databases. The exception will come when the logged-in user attempts to access objects. If the role passed in the DPB was valid for that user, then the role's privileges are available to that user. If not, it's simply a "no-go" and the user will get an exception when trying to access privs that don't exist for that role.

>> If the user and password are known to the server then the
>> authentication succeeds. Any authenticated user has access to any database.
>
>On my point of view the user authentificates with user and password
>AND role.
>If he gives a role, he is not granted to, the authentification infos
>are not correct also password and username might fit together.

No: authentication is USER and PASSWORD. Authentication gives a user NO privileges in databases (unless the user is SYSDBA or Owner).

>>> Are there planes for changing the described behavior?
>
>> On Firebird, currently, *user authentication* is done at server
>> level. In Fb 3, user authentication will be able to be more
>> granular, i.e., security databases for each database or group of databases, if required.
>
>But that won't change the behavior according to logon with non granted
>roles.

I think that, with dedicated security databases, there's a strong argument for elevating the power of a role; although I don't have a theory about how it would be implemented.

>I mean it's confusing and as far as I got the only situation where you
>can tell the server to do something and it decided to don't do it but
>there is no error message.

That's not the only loophole in that part of the database access logic. It is a mess at the API level that Alex has expressed a keenness to fix in V.2.5.

>May be it's possible to not break compatibility with a config setting
>to change the behavior so that login fails if user is not granted to
>the role he requested during logon. I think that would be more logical
>than the current behavior.

The current behaviour is legacy behaviour and it is too loose. Any authenticated user can query the system tables of any database, so a "stop-gap" using a config setting seems at least possible for V.2.5. It won't come in 2.1.x, given that it requires a change in the API. But you could expect resistance to a hack like this that would be deprecated in the next release version.

>There is another topic, which might be not expected by users since
>Firebird 2.5. When you drop a user, his privileges granted to his
>username are not dropped.
>
>Of course that's the same behavior as in the versions before, but now
>there are SQL commands to CREATE/ALTER/DROP users. They can only be
>send to the server when logged on to a database. (or traditionally via
>gsec or the service api) But they are the only commands i can send to
>a database which don't change the database itsself but the
>security2.fdb. That is confusing too - at least for me....

This really is off-topic as a support question. The issues are important, all the same, so I do strongly suggest taking them to firebird-devel or firebird-architect, while they are fresh in your mind, and have them discussed properly as the design issues they are.

^ heLen