pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Glenn MacGregor <gtm@oracom.com>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: limiting access to (through) views
Date: Wed, 16 Jan 2002 10:38:31 -0500
Message-ID: <01a001c19ea3$db6dab60$4d00a8c0@catamount> (raw)
References: <20020116071522.X43837-100000@megazone23.bigpanda.com>

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" <sszabo@megazone23.bigpanda.com>
To: "Glenn MacGregor" <gtm@oracom.com>
Cc: <pgsql-sql@postgresql.org>
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;
>
>




view thread (6+ messages)  latest in thread

Message-ID: <01a001c19ea3$db6dab60$4d00a8c0@catamount>
Permalink:  ../01a001c19ea3$db6dab60$4d00a8c0@catamount/
Also on:    postgresql.org/message-id/01a001c19ea3$db6dab60$4d00a8c0@catamount

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: gtm@oracom.com, sszabo@megazone23.bigpanda.com
  Subject: Re: limiting access to (through) views
  In-Reply-To: <01a001c19ea3$db6dab60$4d00a8c0@catamount>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox