X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 22067D6D19 for ; Fri, 11 Nov 2005 10:08:48 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 58040-06 for ; Fri, 11 Nov 2005 14:08:42 +0000 (GMT) X-Greylist: delayed 17:50:49.385572 by SQLgrey- Received: from svctag-ckgvz71.mailserver.wazagua.com (unknown [209.34.245.244]) by svr1.postgresql.org (Postfix) with ESMTP id 47D51D6D17 for ; Fri, 11 Nov 2005 10:08:43 -0400 (AST) Received: from jfradkin ([66.15.127.235]) by svctag-ckgvz71.mailserver.wazagua.com with Microsoft SMTPSVC(6.0.3790.1830); Fri, 11 Nov 2005 09:09:31 -0500 From: "Joel Fradkin" To: Subject: Re: High level discussion for design of using ACL to retrieve Data Date: Fri, 11 Nov 2005 09:09:55 -0500 Message-ID: <000e01c5e6c9$985269c0$797ba8c0@jfradkin> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000F_01C5E69F.AF7C61C0" In-Reply-To: <00c901c5e633$fdb14160$797ba8c0@jfradkin> X-OriginalArrivalTime: 11 Nov 2005 14:09:31.0265 (UTC) FILETIME=[8915CF10:01C5E6C9] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=4.084 required=5 tests=[AWL=-1.320, HTML_MESSAGE=0.001, MSGID_DOLLARS=2.163, RATWARE_MS_HASH=1.379, RATWARE_OUTLOOK_NONAME=1.861] X-Spam-Score: 4.084 X-Spam-Level: **** X-Archive-Number: 200511/102 X-Sequence-Number: 23353 This is a multi-part message in MIME format. ------=_NextPart_000_000F_01C5E69F.AF7C61C0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable I had an offlist inquiry for more details so I thought I would post my response. This is a select using the union for auto xref. =20 SELECT distinct * FROM ( select * from viwcaselist where clientnum =3D 'SEA' AND DivisionID =3D = 100 And isdeleted=3Dfalse=20 union all=20 select * from viwcaselist where clientnum =3D 'SEA' And = isdeleted=3Dfalse and MainLocationID IN (SELECT AutoLocationID FROM tblSearsAutoXref WHERE (LocationID in ( select MainLocationID from viwLocationWDivRegDis where clientnum =3D 'SEA' AND DivisionID =3D 100))) ) DERIVEDTBL Order BY OpenDate Desc,CaseNum limit 51 =20 Normaly it is more like. =20 select * from viwcaselist where clientnum =3D 'WAZ' AND DivisionID =3D = 100 And isdeleted=3Dfalse Order BY OpenDate Desc,CaseNum limit 51 =20 We have location,district, region, and division tables. AND DivisionID =3D 100 is the key for getting the records (this changes = based on the user level can be district, region, division, or location, or all records fro company level). =20 What I would like to see is the ability to have more types of access to = the data then one level, but as you can see from the xref I implemented it = runs super slow compared to just a straight select. I made a view viwLocationWDivRegDis that has the four tables (div,reg,dist,loc) tied together with joins. =20 Our location table : CREATE TABLE tbllocation ( clientnum varchar(16) NOT NULL, locationid int4 NOT NULL, districtid int4 NOT NULL, regionid int4 NOT NULL, divisionid int4 NOT NULL, locationnum varchar(8), name varchar(50), clientlocnum varchar(50), address varchar(100), address2 varchar(100), city varchar(50), state varchar(2) NOT NULL DEFAULT 'zz'::character varying, zip varchar(10), countryid int4, phone varchar(15), fax varchar(15), payname varchar(40), contact char(36), active bool NOT NULL DEFAULT true, coiprogram text, coilimit text, coiuser varchar(255), coidatetime varchar(32), ec_note_field varchar(1050), locationtypeid int4, open_time timestamp, close_time timestamp, insurance_loc_id varchar(50), lpregionid int4, sic int4, exportentity varchar(16), CONSTRAINT pk_tbllocation PRIMARY KEY (clientnum, locationid), CONSTRAINT ix_tbllocation_1 UNIQUE (clientnum, locationnum, name), CONSTRAINT ix_tbllocation_unique_number UNIQUE (clientnum, divisionid, regionid, districtid, locationnum) )=20 WITH OIDS; =20 =20 CREATE OR REPLACE VIEW viwlocationwdivregdis AS=20 SELECT tbllocation.clientnum, tbllocation.locationid, tbllocation.locationnum, tbllocation.name, tbldivision.divisionnum, tbldivision.divisionname, tblregion.regionnum, tblregion.regionname, tbldistrict.districtnum, tbldistrict.districtname, = tbllocation.locationid AS mainlocationid, tbllocation.divisionid, tbllocation.regionid, tbllocation.districtid, tbllocation.phone, tbllocation.contact, tbllocation.active, tbldistrict.active AS distactive, tblregion.active = AS regactive, tbldivision.active AS divactive FROM tbllocation JOIN tbldivision ON tbllocation.clientnum::text =3D tbldivision.clientnum::text AND tbllocation.divisionid =3D tbldivision.divisionid JOIN tblregion ON tbllocation.clientnum::text =3D = tblregion.clientnum::text AND tbllocation.regionid =3D tblregion.regionid AND = tbllocation.divisionid =3D tblregion.divisionid JOIN tbldistrict ON tbllocation.clientnum::text =3D tbldistrict.clientnum::text AND tbllocation.regionid =3D = tbldistrict.regionid AND tbllocation.divisionid =3D tbldistrict.divisionid AND tbllocation.districtid =3D tbldistrict.districtid; =20 Any ideas would be great! =20 =20 =20 Joel Fradkin =20 =20 -----Original Message----- From: pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Joel Fradkin Sent: Thursday, November 10, 2005 3:19 PM To: pgsql-sql@postgresql.org Subject: [SQL] High level discussion for design of using ACL to retrieve Data =20 We currently use a system with 4 tables (division, region, district, location). The data for the most part has a field named location id. The users get a level and location id (if they are district it would represent a district id etc). =20 I have been asked to make this more flexible, for example if the user = needs data for two locations but they are not in the same district. =20 One thing I have had to add was the ability to have a user have access = to a location and then also have access to a related location (main and auto center have different location number, but if the user has access to the main location he can also see auto center data). =20 I did this with a xref table and a union, but it seem pretty slow. So adding even more flexibity like multiple districts, locs etc (was thinking of trying to do some kind of grouping that would encompass our current plan) has been a problem I have thought about a lot, but I have = not figured out a way that will give fast access. I could do groups of access rights and do unions with distinct to get = data, but I fear that would be really slow. =20 Any one have ideas on this subject? =20 Thanks in advance. =20 Joel Fradkin =20 =20 =20 ------=_NextPart_000_000F_01C5E69F.AF7C61C0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I had an offlist inquiry for more = details so I thought I would post my response.

This is a select using the union for auto = xref.

 

SELECT distinct * FROM (

 select * from viwcaselist where clientnum =3D 'SEA' AND = DivisionID =3D 100 And isdeleted=3Dfalse

union all

select * from viwcaselist where clientnum =3D 'SEA' And = isdeleted=3Dfalse and MainLocationID IN (SELECT AutoLocationID FROM tblSearsAutoXref WHERE = (LocationID in ( select MainLocationID from viwLocationWDivRegDis where clientnum = =3D 'SEA' AND DivisionID =3D 100)))

) DERIVEDTBL Order BY OpenDate Desc,CaseNum limit = 51

 

Normaly it is more like.

 

select * from viwcaselist where clientnum =3D 'WAZ' AND = DivisionID =3D 100 And isdeleted=3Dfalse Order BY OpenDate Desc,CaseNum limit = 51

 

We have location,district, region, and division = tables.

AND DivisionID =3D 100 is the key for getting the records (this = changes based on the user level can be district, region, division, or location, = or all records fro company level).

 

What I would like to see is the ability to have more types of = access to the data then one level, but as you can see from the xref I implemented = it runs super slow compared to just a straight select. I made a view  = viwLocationWDivRegDis that has the four tables (div,reg,dist,loc) tied together with = joins.

 

Our location table :

CREATE TABLE = tbllocation

(

  clientnum varchar(16) NOT = NULL,

  locationid int4 NOT = NULL,

  districtid int4 NOT = NULL,

  regionid int4 NOT = NULL,

  divisionid int4 NOT = NULL,

  locationnum = varchar(8),

  name = varchar(50),

  clientlocnum = varchar(50),

  address = varchar(100),

  address2 = varchar(100),

  city = varchar(50),

  state varchar(2) NOT NULL = DEFAULT 'zz'::character varying,

  zip = varchar(10),

  countryid = int4,

  phone = varchar(15),

  fax = varchar(15),

  payname = varchar(40),

  contact = char(36),

  active bool NOT NULL DEFAULT = true,

  coiprogram = text,

  coilimit = text,

  coiuser = varchar(255),

  coidatetime = varchar(32),

  ec_note_field = varchar(1050),

  locationtypeid = int4,

  open_time = timestamp,

  close_time = timestamp,

  insurance_loc_id = varchar(50),

  lpregionid = int4,

  sic int4,

  exportentity = varchar(16),

  CONSTRAINT pk_tbllocation = PRIMARY KEY (clientnum, locationid),

  CONSTRAINT ix_tbllocation_1 = UNIQUE (clientnum, locationnum, name),

  CONSTRAINT = ix_tbllocation_unique_number UNIQUE (clientnum, divisionid, regionid, districtid, = locationnum)

)

WITH OIDS;

 

 

CREATE OR REPLACE VIEW = viwlocationwdivregdis AS

 SELECT tbllocation.clientnum, = tbllocation.locationid, tbllocation.locationnum, tbllocation.name, tbldivision.divisionnum, = tbldivision.divisionname, tblregion.regionnum, tblregion.regionname, tbldistrict.districtnum, = tbldistrict.districtname, tbllocation.locationid AS mainlocationid, tbllocation.divisionid, = tbllocation.regionid, tbllocation.districtid, tbllocation.phone, tbllocation.contact, = tbllocation.active, tbldistrict.active AS distactive, tblregion.active AS regactive, = tbldivision.active AS divactive

   FROM = tbllocation

   JOIN tbldivision ON = tbllocation.clientnum::text =3D tbldivision.clientnum::text AND tbllocation.divisionid =3D = tbldivision.divisionid

   JOIN tblregion ON = tbllocation.clientnum::text =3D tblregion.clientnum::text AND tbllocation.regionid =3D = tblregion.regionid AND tbllocation.divisionid =3D tblregion.divisionid

   JOIN tbldistrict ON = tbllocation.clientnum::text =3D tbldistrict.clientnum::text AND tbllocation.regionid =3D = tbldistrict.regionid AND tbllocation.divisionid =3D tbldistrict.divisionid AND = tbllocation.districtid =3D tbldistrict.districtid;

 

Any ideas would be = great!

 

 

 

Joel Fradkin

 

 

-----Original = Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] = On Behalf Of Joel = Fradkin
Sent: Thursday, November = 10, 2005 3:19 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] High level discussion for design of using ACL to retrieve Data

 

We currently use a system = with 4 tables (division, region, district, location).

The data for the most part = has a field named location id.

The users get a level and = location id (if they are district it would represent a district id = etc).

 

I have been asked to make = this more flexible, for example if the user needs data for two locations but they = are not in the same district.

 

One thing I have had to add = was the ability to have a user have access to a location and then also have = access to a related location (main and auto center have different location number, = but if the user has access to the main location he can also see auto center = data).

 

I did this with a xref = table and a union, but it seem pretty slow.

So adding even more = flexibity like multiple districts, locs etc (was thinking of trying to do some kind of grouping that would encompass our current plan) has been a problem I = have thought about a lot, but I have not figured out a way that will give = fast access.

I could do groups of access = rights and do unions with distinct to get data, but I fear that would be really = slow.

 

Any one have ideas on this = subject?

 

Thanks in = advance.

 

Joel = Fradkin

 


 

 

------=_NextPart_000_000F_01C5E69F.AF7C61C0--