X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 678479FB352 for ; Mon, 31 Jul 2006 05:13:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 60968-06 for ; Mon, 31 Jul 2006 05:12:56 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.188]) by postgresql.org (Postfix) with ESMTP id D095E9FB34D for ; Mon, 31 Jul 2006 05:12:41 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id p77so412740nfc for ; Mon, 31 Jul 2006 01:12:30 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=MOwdhG9+0fF9KpGIuSgY5ylShwfAPb4lW+HWxfeIpddX3p4co79P9emmcMaXozjzvMzlwv3isK9G9SnOSxlmRbCHuHJzuFO3xQRnxao7pCpc1Ngk8Z6Qmn1514jJKy4iXP3epnU5o/58H+zCy13G0fbzW27u9XtMzs7TNuYFxGQ= Received: by 10.78.157.8 with SMTP id f8mr441963hue; Mon, 31 Jul 2006 01:12:29 -0700 (PDT) Received: by 10.78.32.10 with HTTP; Mon, 31 Jul 2006 01:12:29 -0700 (PDT) Message-ID: Date: Mon, 31 Jul 2006 03:12:29 -0500 From: "Aaron Bono" To: "John Tregea" Subject: Re: Joining a result set from four (4) tables Cc: pgsql-sql@postgresql.org In-Reply-To: <44CDB1EF.6000508@debraneys.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_41030_21514813.1154333549349" References: <44CDB1EF.6000508@debraneys.com> X-Google-Sender-Auth: 5ec3ed77c4ae228a X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.599 tagged_above=0 required=5 tests=AWL, HTML_30_40, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200607/373 X-Sequence-Number: 25947 ------=_Part_41030_21514813.1154333549349 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/31/06, John Tregea wrote: > > Hi, > > Can anyone help me with the following? > > I am setting up a series of permissions of my own making in pgSQL 8.1.4. > I have the following tables; > > resource -- a list of available resources > actions -- the actions available to the user > policies -- the actions that are allowed to be performed on individual > resources > permissions -- matches users with granted actions on any resource > users -- no surprises here > > > I have read the docs about joins but cannot get my head around the > correct syntax. The following SQL returns all actions for every resource > > SELECT > permission.serial_id, > resource.name, > actions.name, > actions.classification, > actions.display_group, > > FROM > permission, policies, resource, actions > > WHERE > permission.user_id = '11' AND > permission.related_id = policies.serial_id AND > policies.status = 'Active' AND > permission.status = 'Active'AND > actions.status = 'Active'AND > resource.status = 'Active' > > I need a list of permissions back for each resource that a user is > authorised to access (when they login to their GUI). > > I also need to check (at user login) if every record in the chain (e.g. > resource, action, policy and permission) is "Active" before the > permission record is considered valid. > > The list for a resource called 'Scenarios' would look something like: > > 11900;"Scenarios";"Publish";"Action";"B" > 11900;"Scenarios";"Authorise";"Action";"B" > 11900;"Scenarios";"Create";"Action";"C" > 11900;"Scenarios";"Update";"Action";"C" > > I am guessing it should be an inner join? but by reference book does not > show joins on this many tables. > > Thanks in advance for any help. Can you include the table create statements with primary and foreign keys? That would help a lot. ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com ================================================================== ------=_Part_41030_21514813.1154333549349 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/31/06, John Tregea <john@debraneys.com> wrote:
Hi,

Can anyone help me with the following?

I am setting up a series of permissions of my own making in pgSQL 8.1.4.
I have the following tables;

resource -- a list of available resources
actions -- the actions available to the user
policies -- the actions that are allowed to be performed on individual
resources
permissions -- matches users  with granted actions on any resource
users --  no surprises here


I have read the docs about joins but cannot get my head around the
correct syntax. The following SQL returns all actions for every resource

SELECT
   permission.serial_id,
   resource.name,
   actions.name ,
   actions.classification,
   actions.display_group,

FROM
   permission, policies, resource, actions

WHERE
   permission.user_id = '11' AND
   permission.related_id = policies.serial_id AND
   policies.status = 'Active' AND
   permission.status = 'Active'AND
   actions.status = 'Active'AND
   resource.status = 'Active'

I need a list of permissions back for each resource that a user is
authorised to access (when they login to their GUI).

I also need to check (at user login) if every record in the chain (e.g.
resource, action, policy and permission) is "Active" before the
permission record is considered valid.

The list for a resource called 'Scenarios' would look something like:

11900;"Scenarios";"Publish";"Action";"B"
11900;"Scenarios";"Authorise";"Action";"B"
11900;"Scenarios";"Create";"Action";"C"
11900;"Scenarios";"Update";"Action";"C"

I am guessing it should be an inner join? but by reference book does not
show joins on this many tables.

Thanks in advance for any help.


Can you include the table create statements with primary and foreign keys?  That would help a lot.

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
================================================================== ------=_Part_41030_21514813.1154333549349--