| Subject | Re: [Firebird-Architect] FB security - Roles vs Groups |
|---|---|
| Author | Geoff Worboys |
| Post date | 2005-08-04T23:28:30Z |
Hi Sean,
accumulation point is different.
In NT domains each user can get multiple group associations,
giving very good flexibility at the user level. But this in
itself can get quite confusing, suddenly you have to study
each user in detail to understand what privileges have been
given out.
To get the desired effect with SQL roles it is just a matter
of adjusting your technique a little.
A theoretical example would be:
A set of roles named DETAIL_* would be created to assign the
detail privileges against tables and procedures etc.
A set of roles named GROUP_* would be created that assemble
detail roles into easily identifiable groupings.
For users that need special treatment (need something not
covered by GROUP_* definitions) you would create
USER_<username> roles and give the user their special access.
That last item could be done differently. Every time you
create a user you could create a USER_[username] role in the
database. Now you have exactly NT group situation, but applied
at the USER_[username] role level.
Note that the requirement of create USER_[username] roles in
your application database is necessary to get this level of
control because, unlike NT networks, there is no "server". You
have only the database and so you start to need to represent
the user in the database.
We could choose to create "user" tables in each database to
maintain this sort of role facility, but there is no actual
need. As demonstrated above, those that need it could create
the similar facility, and we can stay within the SQL standard.
--
Geoff Worboys
Telesis Computing
>> However Claudio did say:This is really no different to groups, it is just that the
>> > It's possible to create more complex schemas by granting
>> > roles to roles (and it's in the standard), but we don't
>> > support that capability.
>>
>> This aspect has been mentioned before and I believe it is very
>> important. If we had this ability then suddenly the whole
>> issue of privilege management becomes much easier.
> I agree, that can help.
> However, without allowing for a user to have multiple roles
> active-simultaneously, the number of roles which must be
> defined to cover all possible combinations works out to be
> n^2-1.
accumulation point is different.
In NT domains each user can get multiple group associations,
giving very good flexibility at the user level. But this in
itself can get quite confusing, suddenly you have to study
each user in detail to understand what privileges have been
given out.
To get the desired effect with SQL roles it is just a matter
of adjusting your technique a little.
A theoretical example would be:
A set of roles named DETAIL_* would be created to assign the
detail privileges against tables and procedures etc.
A set of roles named GROUP_* would be created that assemble
detail roles into easily identifiable groupings.
For users that need special treatment (need something not
covered by GROUP_* definitions) you would create
USER_<username> roles and give the user their special access.
That last item could be done differently. Every time you
create a user you could create a USER_[username] role in the
database. Now you have exactly NT group situation, but applied
at the USER_[username] role level.
Note that the requirement of create USER_[username] roles in
your application database is necessary to get this level of
control because, unlike NT networks, there is no "server". You
have only the database and so you start to need to represent
the user in the database.
We could choose to create "user" tables in each database to
maintain this sort of role facility, but there is no actual
need. As demonstrated above, those that need it could create
the similar facility, and we can stay within the SQL standard.
--
Geoff Worboys
Telesis Computing