Subject Re: [firebird-support] USERS and ROLES
Author Jose Ostos
The ROLE has to be specified to establish the connection since the same
user can be assigned diferent roles.

If you only want 1 role per user and you don't want the user to specify
the role, you have to control this in your application by creating a
table that contains the user names and the role assigned to them.

In you application, the user connects to the DB without specifying the
role, reads the table that contains the roles by user, disconnect from
the DB and reconnects with the obtained role.


Anthonie wrote:
> Good morning,
> Another what may seem a silly question regarding roles and users but
> a topic that I am unable to resolve. My query regarding Firebird
> 2.0.1 is this, also giving an example of the current database
> (migrating from Paradox to Firebird).
> CREATE ROLE SupplierAdmin;
> CREATE ROLE CustomerAdmin;
> CREATE TABLE suppliers(
> supplier_code CHAR(10) NOT NULL,
> supplier_name VARCHAR(50) NOT NULL,
> ...
> PRIMARY KEY(supplier_code)
> );
> CREATE TABLE customers(
> customer_code CHAR(10) NOT NULL,
> customer_name VARCHAR(50) NOT NULL,
> ...
> PRIMARY KEY(customer_code)
> );
> GRANT insert, select, update, references ON TABLE suppliers TO ROLE
> SupplierAdmin;
> GRANT insert, select, update, references ON TABLE customers TO ROLE
> CustomerAdmin;
> This is easy and within the security database the following users
> are defined:
> SYSADMIN (the creator of the database)
> SYSDBA (administrator installed with Firebird)
> IISABO Anthony Booth
> IISNLO Naomi Louze
> IISMPR Marthin Prentz
> IISGBR Graham Bransby
> All is well and each person can log onto the database. What I
> understand is that the ROLES are specific to the database where they
> are created (which makes sense) and also the options as given by the
> GRANT command.
> Is is possible to assign a role to the user when the user logs onto
> the database, meaning, when IISABO logs onto the database he is
> immedialted assigned the role of SupplierAdmin instead of SYSADMIN
> connecting to the database and once connected assigns each user
> their roles with GRANT SupplierAdmin TO IISABO and so forth?
> Thank you in advance.
> Anhonie.

[Non-text portions of this message have been removed]