pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
limiting access to (through) views
6+ messages / 3 participants
[nested] [flat]

* limiting access to (through) views
@ 2002-01-16 14:47 Glenn MacGregor <gtm@oracom.com>
  2002-01-16 15:17 ` Re: limiting access to (through) views Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2002-01-16 15:23 ` Re: limiting access to (through) views Bruno Wolff III <bruno@wolff.to>
  0 siblings, 2 replies; 6+ messages in thread

From: Glenn MacGregor @ 2002-01-16 14:47 UTC (permalink / raw)
  To: pgsql-sql

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?

    Thanks 

            Glenn

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: limiting access to (through) views
  2002-01-16 14:47 limiting access to (through) views Glenn MacGregor <gtm@oracom.com>
@ 2002-01-16 15:17 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2002-01-16 15:38   ` Re: limiting access to (through) views Glenn MacGregor <gtm@oracom.com>
  1 sibling, 1 reply; 6+ messages in thread

From: Stephan Szabo @ 2002-01-16 15:17 UTC (permalink / raw)
  To: Glenn MacGregor <gtm@oracom.com>; +Cc: pgsql-sql

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;




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: limiting access to (through) views
  2002-01-16 14:47 limiting access to (through) views Glenn MacGregor <gtm@oracom.com>
  2002-01-16 15:17 ` Re: limiting access to (through) views Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-01-16 15:38   ` Glenn MacGregor <gtm@oracom.com>
  2002-01-16 15:44     ` Re: limiting access to (through) views Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Glenn MacGregor @ 2002-01-16 15:38 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: limiting access to (through) views
  2002-01-16 14:47 limiting access to (through) views Glenn MacGregor <gtm@oracom.com>
  2002-01-16 15:17 ` Re: limiting access to (through) views Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2002-01-16 15:38   ` Re: limiting access to (through) views Glenn MacGregor <gtm@oracom.com>
@ 2002-01-16 15:44     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2002-01-16 15:58       ` Re: limiting access to (through) views Glenn MacGregor <gtm@oracom.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Stephan Szabo @ 2002-01-16 15:44 UTC (permalink / raw)
  To: Glenn MacGregor <gtm@oracom.com>; +Cc: pgsql-sql


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.




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: limiting access to (through) views
  2002-01-16 14:47 limiting access to (through) views Glenn MacGregor <gtm@oracom.com>
  2002-01-16 15:17 ` Re: limiting access to (through) views Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2002-01-16 15:38   ` Re: limiting access to (through) views Glenn MacGregor <gtm@oracom.com>
  2002-01-16 15:44     ` Re: limiting access to (through) views Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-01-16 15:58       ` Glenn MacGregor <gtm@oracom.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Glenn MacGregor @ 2002-01-16 15:58 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql

Stephan,

Thanks again.
The CURRENT_USER function returns just the username, my table actually has
username@server.

    Glenn

----- 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: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.
>
>




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: limiting access to (through) views
  2002-01-16 14:47 limiting access to (through) views Glenn MacGregor <gtm@oracom.com>
@ 2002-01-16 15:23 ` Bruno Wolff III <bruno@wolff.to>
  1 sibling, 0 replies; 6+ messages in thread

From: Bruno Wolff III @ 2002-01-16 15:23 UTC (permalink / raw)
  To: Glenn MacGregor <gtm@oracom.com>; +Cc: pgsql-sql

On Wed, Jan 16, 2002 at 09:47:21AM -0500,
  Glenn MacGregor <gtm@oracom.com> 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.



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2002-01-16 15:58 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-16 14:47 limiting access to (through) views Glenn MacGregor <gtm@oracom.com>
2002-01-16 15:17 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-01-16 15:38   ` Glenn MacGregor <gtm@oracom.com>
2002-01-16 15:44     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-01-16 15:58       ` Glenn MacGregor <gtm@oracom.com>
2002-01-16 15:23 ` Bruno Wolff III <bruno@wolff.to>

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