Re: Design Database, 3 degrees of Users.

On Fri, Jul 31, 2009 at 9:47 AM, Rich Shepard<rshepard(at)appl-ecosys(dot)com> wrote:
> On Fri, Jul 31, 2009 at 4:38 AM, Andre Lopes<lopes80andre(at)gmail(dot)com> wrote:
>
>> I need to design a Database that will handle 3 degrees of users:
>>
>> Administrators - They can see all the information in the database.
>> Managers - They only can see the information of his dependants.
>> Dependants - Theirs action must be aprovet by the managers.
>>
>> Wich the best way to implement this in PostGreSQL? There is some database
>> examples doing this? Some OpenSource Project?
>>
>> I'am designing a Database for doing this, but I don't have sure I'am doing
>> well.
>>
>> Please give me some advice.
>
>  You don't want to implement this in SQL, regardless of what DBMS you use
> as the back end. Implementing different user classes (or roles as we call
> them) is done in your middleware and UI.
>

That's a fairly broad statement which I don't necessarily agree with
depending on how you define 'in SQL'. It's quite easy and common to
describe user roles and allowed activities within the database to
support an application, and to check user actions against those tables
at various places. This is 'in SQL' to me, because the logic
controlling what the users can and cannot do is handled inside the
database (either in tables + actual sql or a supporting language
hooked in to the database).

Strictly using SQL roles for security. however, can be clumsy if you
need fine grained control beyond reading or writing to a particular
set of tables. This is more of a data security thing, which is quite
different from controlling how a particular application is supposed to
behave.

merlin