Re: Design Database, 3 degrees of Users.

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.
Best Regards,
André Lopes.

On Fri, Jul 31, 2009 at 12:38:30PM +0100, Andre Lopes 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?

Depends on how much this separation should be done inside the database,
and how much outside the database. Within PG, the things that will help
you are roles[1], views[2], and functions[3] with "security definer"
set.

--
Sam http://samason.me.uk/

[1] http://www.postgresql.org/docs/current/static/user-manag.html
[2] http://www.postgresql.org/docs/current/static/sql-createview.html
[3] http://www.postgresql.org/docs/current/static/sql-createfunction.html

Would Veil be useful to you?

http://veil.projects.postgresql.org/curdocs/index.html

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.
>
> Best Regards,
> André Lopes.

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.

SQL is a set-oriented language for manipulating data stored in tables.
Your application code can specify the creation and use of VIEWS that depend
on the priviledges associated with each user when logged into the system. It
appears to me that you ought to take a step back and learn a bit more about
application development which uses a DBMS as a data storage back end.

Rich

--
Richard B. Shepard, Ph.D. | Integrity Credibility
Applied Ecosystem Services, Inc. | Innovation
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

Andre Lopes 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.
>
A little more description of your application would be helpful.

Are these users of the database directly or are they users who login to
something like a web-app to retrieve information?

In research, we sometimes deal with organizational trees. For example we
might have employee-satisfaction that must be made available to
thousands of managers, each of which is allowed to see the aggregate
data of her department and of any department below her but nothing above
her. If your situation is similar, you might look into using an
adjacency-tree (every record except the top has a "manager-id" pointing
to that person's boss) along with the new "with recursive" queries in 8.4.

Cheers,
Steve

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