Subject Re: [ib-support] Why can I login into a db with a role I have never been granted?
Author Ray Drew
I created a database with some roles and users. There is an
"administrator" role and a "katja" user never granted access to this
role. Why can I log into that database (isql/jaybird) specifying a)
the database I want to log into (URL), b) the username (katja) and
password, and c) the role (administrator)??? Is this a bug?

It bugs me, at least: In my java program I check the validity of the
db-connection details by "test-connecting" to the database at program
startup. In the above scenario my test falsely succeeds; no exception
is thrown at me when openening the connection. The exception occurs
when accessing db-objects I am not allowed to access (likewise isql
complains)... which happens where/when my program did not assume that
to happen.


I'm just working round this myself (python not java).

I don't know why this happens - no doubt you'd get an exception when the
user tries to action something he/she doesn't have privileges for.

I don't know if this is the best solution but you could create a view
something like:

create view administratorrole_p as select rdb$relation_name from
rdb$user_privileges where rdb$user = (select current_user from rdb$database)
and rdb$relation_name = ADMINISTRATORROLE"

give public access to the view and then check the connection with something

select * from administratorrole_p"

If the user has been granted the role you'll get a text string with the
rolename back - otherwise you should get an empty result set.

Is there a better way of doing this?