Thread: 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@gmail.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@gmail.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@appl-ecosys.com> wrote: > On Fri, Jul 31, 2009 at 4:38 AM, Andre Lopes<lopes80andre@gmail.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
On Fri, 2009-07-31 at 08:38 -0300, pgsql-general-owner@postgresql.org wrote: > Date: Fri, 31 Jul 2009 12:38:30 +0100 > From: Andre Lopes <lopes80andre@gmail.com> > To: pgsql-general@postgresql.org > Subject: Design Database, 3 degrees of Users. > Message-ID: > <18f98e680907310438o764e9bc7hbb6e245d8464792@mail.gmail.com> > > 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? If I understand your requirement, I think you are hoping to implement Virtual Private Databases for each of your users. With a VPD each user connects to the same database but can see different subsets of data. You can do this with veil: http://veil.projects.postgresql.org/curdocs/index.html Note that this is a hard problem and good solutions, even using veil, require a lot of work. My advice to anyone thinking that they want to do this is, consider very carefully whether the benefits of a VPD are worth the cost. It is generally much easier to place the sort of controls you need in your application than in the database. __ Marc Munro (developer of veil)