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; > >