Thread: Label Security

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

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

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

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.