At 02:18 PM 17/04/2008, you wrote:

>Firebird 1.5.2
>
>In setting up some users and roles I accidentally discovered that I can
>connect specifying a role that has not been granted to the user. Is this
>normal behavior?

Nope. But you seem to have totally missed what a role is for.

>It seems to default to the role that IS granted to the user. What happens if
>there are multiple roles granted to the user? Does it default to the first?

It doesn't make sense to grant multiple roles to a user.

A role is a package of privileges. After creating the role, you first grant all the privileges to the ROLE and then you GRANT the role to the user.

>Example follows. Users in database are SYSDBA, GENUSER (grant to role
>GENROLE) and ADVUSER (grant to role ADVROLE).
>
>Show roles and grants
>---------------------
>SQL> connect 127.0.0.1:test user SYSDBA password xxxxxxxx;
>Database: 127.0.0.1:test, User: SYSDBA
>SQL> show role;
> ADVROLE GENROLE
>SQL> show grant;
>/* Grant permissions for this database */
>GRANT SELECT, REFERENCES ON CATEGORY TO USER ADVUSER
>GRANT SELECT, REFERENCES ON V_CATEGORYGEN TO USER ADVUSER
>GRANT SELECT, REFERENCES ON V_CATEGORYGEN TO USER GENUSER
>GRANT ADVROLE TO ADVUSER
>GRANT GENROLE TO GENUSER
>SQL> exit;
>
>Connect using non granted role
>------------------------------
>SQL> connect 127.0.0.1:test user GENUSER role ADVROLE password xxxxxxxx;
>Database: 127.0.0.1:test, User: GENUSER, Role: ADVROLE
>
>Test privileges
>---------------
>SQL> select id from category;
>Statement failed, SQLCODE = -551
>no permission for read/select access to TABLE CATEGORY
>SQL> select id from v_categorygen;
> ID
>============
> 0
> 1
> 2
> 3
> 4
> 5
>[The above is correct for the users GRANTed role, not the connect role]
>
>I would not have expected the connect to succeed. The privileges follow that
>of role GENROLE which IS granted to the user but was not specified in the
>connection. ie. No security breach but strange that the connection succeeded
>and role seems to default to users granted role.

Not strange. Even with this small amount of messing about you have made your database's ACL into a bird's nest.

Start again. Revoke everything except SYSDBA and OWNER privileges. Then login as SYSDBA or OWNER (the latter only if the database owner is also the owner of all the objects). Then create your role packages. After you are happy that you have the needs covered, start assigning privs by granting the appropriate role to each specific user.

Kicking out unwanted users is just as easy. Just revoke the role from the user. But don't going granting other privs to users or you'll soon be back in the bird's nest.

./heLen