pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLabel Security
4+ messages / 2 participants
[nested] [flat]
* Label Security
@ 2004-01-26 20:45 James Taylor <jtx@hatesville.com>
0 siblings, 1 reply; 4+ messages in thread
From: James Taylor @ 2004-01-26 20:45 UTC (permalink / raw)
To: pgsql-sql
I'm migrating an Oracle 9 database over to Postgres 7.3.4, and just ran
into something I've never seen before (honestly, due to my lack of
experience in Oracle) and was curious if
Postgres supported anything similar. The DBA that set up Oracle
appears to have enabled Oracle Label Security, which looks as though it
offers per-row security levels. So, say we have the table
'test', user 'Nancy' does a "select * from test" and only will be
shown rows she has permission to. Joe will get the same, and the
superuser can see everything. Does Postgres offer anything like this,
maybe even through third party software
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Label Security
@ 2004-01-26 21:06 Bruno Wolff III <bruno@wolff.to>
parent: James Taylor <jtx@hatesville.com>
0 siblings, 1 reply; 4+ messages in thread
From: Bruno Wolff III @ 2004-01-26 21:06 UTC (permalink / raw)
To: James Taylor <jtx@hatesville.com>; +Cc: pgsql-sql
On Mon, Jan 26, 2004 at 12:45:40 -0800,
James Taylor <jtx@hatesville.com> wrote:
> I'm migrating an Oracle 9 database over to Postgres 7.3.4, and just ran
> into something I've never seen before (honestly, due to my lack of
> experience in Oracle) and was curious if
> Postgres supported anything similar. The DBA that set up Oracle
> appears to have enabled Oracle Label Security, which looks as though it
> offers per-row security levels. So, say we have the table
> 'test', user 'Nancy' does a "select * from test" and only will be
> shown rows she has permission to. Joe will get the same, and the
> superuser can see everything. Does Postgres offer anything like this,
> maybe even through third party software
You can do this with views, but there isn't a turn key set up to do this.
You can give someone access to a view without giving them direct access
to underlying tables. A view can check the current username versus
some data in the table being displayed (perhaps joined with some other
tables that keep track of group membership).
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Label Security
@ 2004-01-27 00:50 James Taylor <jtx@hatesville.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 4+ messages in thread
From: James Taylor @ 2004-01-27 00:50 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
Ok, so for example, say I add another column to the tables I want to
have the row-level security on called 'security'. I would go ahead and
designate
different security levels for each user, (ex. Jane security 1000, Bill
2000, Joe 3000). Then, if I only want X user with security 1000 to
view Y row, I set Y.security to 1000.
Then, I give these users no access to the table, and create views for
EACH user saying something to the effect of "select * from z where
security=securitylevel", and grant access to the views only to the user
itself.
Couldn't a user then go into the console themselves and create a view
giving them full access to the table?
Or, maybe I'm way off on this whole thing
On Jan 26, 2004, at 1:06 PM, Bruno Wolff III wrote:
> On Mon, Jan 26, 2004 at 12:45:40 -0800,
> James Taylor <jtx@hatesville.com> wrote:
>> I'm migrating an Oracle 9 database over to Postgres 7.3.4, and just
>> ran
>> into something I've never seen before (honestly, due to my lack of
>> experience in Oracle) and was curious if
>> Postgres supported anything similar. The DBA that set up Oracle
>> appears to have enabled Oracle Label Security, which looks as though
>> it
>> offers per-row security levels. So, say we have the table
>> 'test', user 'Nancy' does a "select * from test" and only will be
>> shown rows she has permission to. Joe will get the same, and the
>> superuser can see everything. Does Postgres offer anything like this,
>> maybe even through third party software
>
> You can do this with views, but there isn't a turn key set up to do
> this.
> You can give someone access to a view without giving them direct access
> to underlying tables. A view can check the current username versus
> some data in the table being displayed (perhaps joined with some other
> tables that keep track of group membership).
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Label Security
@ 2004-01-27 02:24 Bruno Wolff III <bruno@wolff.to>
parent: James Taylor <jtx@hatesville.com>
0 siblings, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2004-01-27 02:24 UTC (permalink / raw)
To: James Taylor <jtx@hatesville.com>; +Cc: pgsql-sql
On Mon, Jan 26, 2004 at 16:50:06 -0800,
James Taylor <jtx@hatesville.com> wrote:
> Ok, so for example, say I add another column to the tables I want to
> have the row-level security on called 'security'. I would go ahead and
> designate
> different security levels for each user, (ex. Jane security 1000, Bill
> 2000, Joe 3000). Then, if I only want X user with security 1000 to
> view Y row, I set Y.security to 1000.
> Then, I give these users no access to the table, and create views for
> EACH user saying something to the effect of "select * from z where
> security=securitylevel", and grant access to the views only to the user
> itself.
You can refer to current_user in the view. This allows you to use one
view for all users. Though you still have to have some information
mapping users to rows they can access in the database.
> Couldn't a user then go into the console themselves and create a view
> giving them full access to the table?
You don't have to give them access to the table directly in order to
have the view work. When accessing a table through a view you have the
rights of the person who created the view.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-01-27 02:24 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-26 20:45 Label Security James Taylor <jtx@hatesville.com>
2004-01-26 21:06 ` Bruno Wolff III <bruno@wolff.to>
2004-01-27 00:50 ` James Taylor <jtx@hatesville.com>
2004-01-27 02:24 ` 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