Good morning from Bergamo,

well, Vlad told me to post the topic to the support list.

>> 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.

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.

> 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.

> A role is a database-specific package of privileges to objects in
> that databases. If you are using privileges correctly then you
> should not have any grants to PUBLIC.

Yes, of course.
I'm aware that the user logging in with a "invalid" role still has his
privileges granted to his username and to pseudo user public.

>> Is the behavior of firebird according to SQL standard?

> SQL doesn't have a "standard" regarding the scope of users.

That's interesting.

> But
> privileges are a standard SQL implementation. Privileges apply to
> database objects. It is very important to design a privileges schema
> that ensures full control of access for any user. Were you aware
> that a user doesn't have to exist in the security database, in order to be granted privileges?

Yes, of course.

>> 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 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.

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.


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....




Björn

--
Björn Reimer, Datenbanken und Verfahren
Friedrich-Alexander-Universität Erlangen-Nürnberg
Regionales RechenZentrum Erlangen (RRZE)
Hugenottenplatz 1a, D-91054 Erlangen, Germany
Tel. +49 9131 85-26719, Fax +49 9131 85-25777
bjoern.reimer@...-erlangen.de
http://www.rrze.uni-erlangen.de/infrastruktur/datenbanken/