On 10-02-2021 15:03, Midsoft UK wrote:
> if you have to assign a user to a role, whats the purpose of connecting
> to the db using a role ?
Specifying the role on connect will allow the user to assume the
privileges associated with that role. The idea of roles is that a user
fulfills a functional role, and while they fulfill that role, they need
a specific set of privileges. Those privileges are assigned to a ROLE
object. If a user fulfills a different role, then they need another set
of privileges, etc. If a user connects without a role, they only have
the privileges assigned directly to their user, or to the user PUBLIC.
For example, an administrator has defined roles like "DATAENTRY" (which
allows you to enter data, e.g. INSERT privileges only) and "DATACHECKER"
(which allows you to see and update data, e.g. SELECT and UPDATE
A single user could have both roles. If they have no other privileges
(directly to the user or on PUBLIC), when they are logged in with role
DATAENTRY, they can only insert data, and when they are logged in with
role DATACHECKER, they can view the data and update it if necessary.
Firebird 4 will introduce a new feature on roles that will automatically
apply their privileges to a user, without the user needing to explicitly
specify that role on connect.
If a user wouldn't need to be granted a role, every user could be an
administrator, or at least gain a broad range of privileges that they
aren't actually entitled to, which would be a gigantic security hole.