From gtm@oracom.com Wed Jan 16 14:42:18 2002 Received: from degroens.oracom.com (degroens.oracom.com [63.238.138.68]) by postgresql.org (8.11.3/8.11.4) with SMTP id g0GEgIV06950 for ; Wed, 16 Jan 2002 09:42:18 -0500 (EST) (envelope-from gtm@oracom.com) Received: (qmail 21231 invoked from network); 16 Jan 2002 10:58:25 -0000 Received: from unknown (HELO catamount) (192.168.253.253) by 192.168.253.2 with SMTP; 16 Jan 2002 10:58:25 -0000 Message-ID: <013401c19e9c$b3d4f790$4d00a8c0@catamount> From: "Glenn MacGregor" To: Subject: limiting access to (through) views Date: Wed, 16 Jan 2002 09:47:21 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0131_01C19E72.CAF5C7D0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200201/180 X-Sequence-Number: 6266 This is a multi-part message in MIME format. ------=_NextPart_000_0131_01C19E72.CAF5C7D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi All, I need to limit access to certain rows of a table. It was suggested to do = this via a view, but it seems to me that I need the current username that i= s logged in to do this. Also I need to get info from another table inorder= to make this work. Can I have multiple selects in a single view using the= output of one in another? Example: table1: col1,col2,col3 table1 Data: test,here,max test1,here1,me test2,here2,one test3,here3,max test4,here4,one test5,here5,two table2: username,key table2 Data: user1,max user2,one user3,two user4,me So I have these two tables, I authenticate and gain access to the db. How = do I know who I am in a query? I need to query table2 for the key of the u= sername I am. Then I need to query table1 for rows that col3 matches the k= ey I got from the previous query. Is this possable with a view? Thanks=20 Glenn ------=_NextPart_000_0131_01C19E72.CAF5C7D0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi All,
 
I need to limit access to certain rows of&= nbsp;a=20 table.  It was suggested to do this via a view, but it seems to me tha= t I=20 need the current username that is logged in to do this.  Also I need t= o get=20 info from another table inorder to make this work.  Can I have multipl= e=20 selects in a single view using the output of one in another?
 
Example:
 
    table1:
       = =20 col1,col2,col3
 
    table1 Data:
       =20 test,here,max
       =20 test1,here1,me
       =20 test2,here2,one
       =20 test3,here3,max
       =20 test4,here4,one
       =20 test5,here5,two
 
    table2:
        username,key
 
    table2 Data:
        user1,max
        user2,one
        user3,two
        user4,me
 
So I have these two tables, I authenticate and gain access to the db.&= nbsp;=20 How do I know who I am in a query?  I need to query table2 for the key= of=20 the username I am.  Then I need to query table1 for rows that col3 mat= ches=20 the key I got from the previous query.
 
Is this possable with a view?
 
    Thanks
 
           =20 Glenn
------=_NextPart_000_0131_01C19E72.CAF5C7D0-- From sszabo@megazone23.bigpanda.com Wed Jan 16 15:17:00 2002 Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0GFH0V14348 for ; Wed, 16 Jan 2002 10:17:00 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 6C5ADD5F3; Wed, 16 Jan 2002 07:17:04 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 5E0405BF4; Wed, 16 Jan 2002 07:17:04 -0800 (PST) Date: Wed, 16 Jan 2002 07:17:04 -0800 (PST) From: Stephan Szabo To: Glenn MacGregor Cc: Subject: Re: limiting access to (through) views In-Reply-To: <013401c19e9c$b3d4f790$4d00a8c0@catamount> Message-ID: <20020116071522.X43837-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200201/181 X-Sequence-Number: 6267 On Wed, 16 Jan 2002, Glenn MacGregor wrote: > Hi All, > > I need to limit access to certain rows of a table. It was suggested > to do this via a view, but it seems to me that I need the current > username that is logged in to do this. Also I need to get info from > another table inorder to make this work. Can I have multiple selects > in a single view using the output of one in another? > > Example: > > table1: > col1,col2,col3 > > table1 Data: > test,here,max > test1,here1,me > test2,here2,one > test3,here3,max > test4,here4,one > test5,here5,two > > table2: > username,key > > table2 Data: > user1,max > user2,one > user3,two > user4,me > > So I have these two tables, I authenticate and gain access to the db. > How do I know who I am in a query? I need to query table2 for the key > of the username I am. Then I need to query table1 for rows that col3 > matches the key I got from the previous query. > > Is this possable with a view? CURRENT_USER should give the current user name. Hmm for the above maybe... select table1.* from table1, table2 where table1.col3=table2.key and table2.username=CURRENT_USER; From bruno@wolff.to Wed Jan 16 15:21:27 2002 Received: from wolff.to (wolff.to [66.92.219.49]) by postgresql.org (8.11.3/8.11.4) with SMTP id g0GFLRV16294 for ; Wed, 16 Jan 2002 10:21:27 -0500 (EST) (envelope-from bruno@wolff.to) Received: (qmail 27077 invoked by uid 500); 16 Jan 2002 15:23:14 -0000 Date: Wed, 16 Jan 2002 09:23:14 -0600 From: Bruno Wolff III To: Glenn MacGregor Cc: pgsql-sql@postgresql.org Subject: Re: limiting access to (through) views Message-ID: <20020116152314.GA26996@wolff.to> Mail-Followup-To: Glenn MacGregor , pgsql-sql@postgresql.org References: <013401c19e9c$b3d4f790$4d00a8c0@catamount> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <013401c19e9c$b3d4f790$4d00a8c0@catamount> User-Agent: Mutt/1.3.25i X-Archive-Number: 200201/184 X-Sequence-Number: 6270 On Wed, Jan 16, 2002 at 09:47:21AM -0500, Glenn MacGregor wrote: > Hi All, > > I need to limit access to certain rows of a table. It was suggested to do this via a view, but it seems to me that I need the current username that is logged in to do this. Also I need to get info from another table inorder to make this work. Can I have multiple selects in a single view using the output of one in another? You would want to use session_user or current_user. For now these are both the same, but eventually they may not be. From gtm@oracom.com Wed Jan 16 15:33:31 2002 Received: from degroens.oracom.com (degroens.oracom.com [63.238.138.68]) by postgresql.org (8.11.3/8.11.4) with SMTP id g0GFXVV20091 for ; Wed, 16 Jan 2002 10:33:31 -0500 (EST) (envelope-from gtm@oracom.com) Received: (qmail 22860 invoked from network); 16 Jan 2002 11:48:44 -0000 Received: from unknown (HELO catamount) (192.168.253.253) by 192.168.253.2 with SMTP; 16 Jan 2002 11:48:44 -0000 Message-ID: <01a001c19ea3$db6dab60$4d00a8c0@catamount> From: "Glenn MacGregor" To: "Stephan Szabo" Cc: References: <20020116071522.X43837-100000@megazone23.bigpanda.com> Subject: Re: limiting access to (through) views Date: Wed, 16 Jan 2002 10:38:31 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200201/183 X-Sequence-Number: 6269 Thanks, Now is it possible to do the following: select table1.* from table1, table2 where table1.col3=table2.key and table2.username like 'CURRENT_USER%' This is returning nothing when I know it should be returning something, so I think that since the CURRENT_USER is in the ' it is not executing the function. Is there a way to make that execute using the like with the regex matching (%)? Thanks ----- Original Message ----- From: "Stephan Szabo" To: "Glenn MacGregor" Cc: Sent: Wednesday, January 16, 2002 10:17 AM Subject: Re: [SQL] limiting access to (through) views > On Wed, 16 Jan 2002, Glenn MacGregor wrote: > > > Hi All, > > > > I need to limit access to certain rows of a table. It was suggested > > to do this via a view, but it seems to me that I need the current > > username that is logged in to do this. Also I need to get info from > > another table inorder to make this work. Can I have multiple selects > > in a single view using the output of one in another? > > > > Example: > > > > table1: > > col1,col2,col3 > > > > table1 Data: > > test,here,max > > test1,here1,me > > test2,here2,one > > test3,here3,max > > test4,here4,one > > test5,here5,two > > > > table2: > > username,key > > > > table2 Data: > > user1,max > > user2,one > > user3,two > > user4,me > > > > So I have these two tables, I authenticate and gain access to the db. > > How do I know who I am in a query? I need to query table2 for the key > > of the username I am. Then I need to query table1 for rows that col3 > > matches the key I got from the previous query. > > > > Is this possable with a view? > > CURRENT_USER should give the current user name. > Hmm for the above maybe... > > select table1.* from > table1, table2 where > table1.col3=table2.key and > table2.username=CURRENT_USER; > > From sszabo@megazone23.bigpanda.com Wed Jan 16 15:44:47 2002 Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0GFilV24816 for ; Wed, 16 Jan 2002 10:44:47 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 16F28D5F3; Wed, 16 Jan 2002 07:44:48 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 0CA465BF4; Wed, 16 Jan 2002 07:44:48 -0800 (PST) Date: Wed, 16 Jan 2002 07:44:48 -0800 (PST) From: Stephan Szabo To: Glenn MacGregor Cc: Subject: Re: limiting access to (through) views In-Reply-To: <01a001c19ea3$db6dab60$4d00a8c0@catamount> Message-ID: <20020116074331.G44027-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200201/186 X-Sequence-Number: 6272 On Wed, 16 Jan 2002, Glenn MacGregor wrote: > Thanks, > > Now is it possible to do the following: > > select table1.* from > table1, table2 where > table1.col3=table2.key and > table2.username like 'CURRENT_USER%' > > This is returning nothing when I know it should be returning something, so I > think that since the CURRENT_USER is in the ' it is not executing the > function. Is there a way to make that execute using the like with the regex > matching (%)? Do you really want a like search or are you trying to get around it not matching something? table2.username like CURRENT_USER || '%' would probably work. From gtm@oracom.com Wed Jan 16 15:53:52 2002 Received: from degroens.oracom.com (degroens.oracom.com [63.238.138.68]) by postgresql.org (8.11.3/8.11.4) with SMTP id g0GFrqV29356 for ; Wed, 16 Jan 2002 10:53:52 -0500 (EST) (envelope-from gtm@oracom.com) Received: (qmail 23599 invoked from network); 16 Jan 2002 12:08:46 -0000 Received: from unknown (HELO catamount) (192.168.253.253) by 192.168.253.2 with SMTP; 16 Jan 2002 12:08:46 -0000 Message-ID: <01c201c19ea6$b40ec0b0$4d00a8c0@catamount> From: "Glenn MacGregor" To: "Stephan Szabo" Cc: References: <20020116074331.G44027-100000@megazone23.bigpanda.com> Subject: Re: limiting access to (through) views Date: Wed, 16 Jan 2002 10:58:56 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200201/188 X-Sequence-Number: 6274 Stephan, Thanks again. The CURRENT_USER function returns just the username, my table actually has username@server. Glenn ----- Original Message ----- From: "Stephan Szabo" To: "Glenn MacGregor" Cc: Sent: Wednesday, January 16, 2002 10:44 AM Subject: Re: [SQL] limiting access to (through) views > > On Wed, 16 Jan 2002, Glenn MacGregor wrote: > > > Thanks, > > > > Now is it possible to do the following: > > > > select table1.* from > > table1, table2 where > > table1.col3=table2.key and > > table2.username like 'CURRENT_USER%' > > > > This is returning nothing when I know it should be returning something, so I > > think that since the CURRENT_USER is in the ' it is not executing the > > function. Is there a way to make that execute using the like with the regex > > matching (%)? > > Do you really want a like search or are you trying to get around it not > matching something? > table2.username like CURRENT_USER || '%' would probably work. > >