
Seems like the discussion on views and access control is
drifting in a direction that interests me.  At the risk of
once again bringing up an issue that's already been solved
and tabled, I offer the following wish item:

Content-based access control (CBAC).  In my experience,
when these words are uttered, DBAs and MIS designers groan.
I wish CBAC were never required.  Unfortunately sometimes
it is, and I wonder if the PG team is thinking about it.

Column protection is not CBAC, of course, though it sorta
feels like it.  Column protection can be useful, but I've
had less need for it than true CBAC.  I'd like to see
column AC in PG someday, but it's not very important to me
personally -- whereas  I have immediate requirements for CBAC.

In true CBAC, the whole record is confidential.  In table T, 
User X "owns" some records and User Y "owns" some records, and 
the two of them should not see each other's records.  You can 
address this problem with views (if your view mechanism allows 
RSE as well as FSE, and if views don't inherit AC from parent 
tables).  But this gets you into a maintenance headache, where 
you're creating new views every time a new user joins the 
crowd. 

What I'd like, when I really think about it, is a rule 
mechanism for selects.  Perhaps PG already has this, but my 
conceptual model is the Sybase trigger feature.  On Update and 
Delete (but not select) in the older Sybase engines, the DB 
designer can interrupt the transaction and abort or alter it 
according to rules coded in SQL.  This was *very* useful, but 
(at least back then) it didn't work on select. 

I wish that all I had to do was code the records with the 
owner UID, and slap a "select trigger" or rule on the table 
that said effectively:  on a select, if record N has a user 
code not matching the user code of the query connection, 
suppress that record from the output stream. [We could get 
more sophisticated than that, of course:  if (RSE) and 
(group|user code) in (list)...] 

Of course the privileged user (database owner or postgres) 
would have to be exempt.  I'd like to see this suppression 
mechanism work for COUNT and all other stats. In fact, to each 
user, the table should look like it contains only that user's 
data.  That would be truly cool.  Like a view, but rule-based.

I *could* write a canned UI that creates this kind of view 
dynamically as it starts up, but (imho) rules don't belong in 
the app, they belong in the engine.  I want my access control 
to be proof against C code using API lib, interactive sql 
sessions, and any other way users can query the db. 

So, is this "already in 6.3"?  Has anyone faced this problem
and solved it by various other clever means?  I haven't
thought it through as clearly as I would like, so I'd be
interested to hear from those who have.

de

.............................................................................
:De Clarke, Software Engineer                     UCO/Lick Observatory, UCSC:
:Mail: de@ucolick.org | "There is no problem in computer science that cannot: 
:Web: www.ucolick.org |  be solved by another level of indirection"  --J.O. :




Wed, 14 Jan 1998 12:22:34 -0800 (PST)
De Clarke <de@ucolick.org>
content-based access control (Re: views, access control, etc)
De Clarke wrote:
> 
> Seems like the discussion on views and access control is
> drifting in a direction that interests me.  At the risk of
> once again bringing up an issue that's already been solved
> and tabled, I offer the following wish item:
> 
> Content-based access control (CBAC).  In my experience,
> when these words are uttered, DBAs and MIS designers groan.
> I wish CBAC were never required.  Unfortunately sometimes
> it is, and I wonder if the PG team is thinking about it.
> 
> Column protection is not CBAC, of course, though it sorta
> feels like it.  Column protection can be useful, but I've
> had less need for it than true CBAC.  I'd like to see
> column AC in PG someday, but it's not very important to me
> personally -- whereas  I have immediate requirements for CBAC.
> 
> In true CBAC, the whole record is confidential.  In table T,
> User X "owns" some records and User Y "owns" some records, and
> the two of them should not see each other's records.  You can
> address this problem with views (if your view mechanism allows
> RSE as well as FSE, and if views don't inherit AC from parent
> tables).  But this gets you into a maintenance headache, where
> you're creating new views every time a new user joins the
> crowd.
> 
> What I'd like, when I really think about it, is a rule
> mechanism for selects.  Perhaps PG already has this, but my
> conceptual model is the Sybase trigger feature.  On Update and
> Delete (but not select) in the older Sybase engines, the DB
> designer can interrupt the transaction and abort or alter it
> according to rules coded in SQL.  This was *very* useful, but
> (at least back then) it didn't work on select.

You could use PG triggers on Updates, Deletes and Inserts (to insert
owner user name) and try to use RULEs to rewrite SELECT statement.
(I never played with RULEs...)

Vadim

Thu, 15 Jan 1998 11:11:51 +0700
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
Re: [HACKERS] content-based access control (Re: views,
 access control,
	etc)

Vadim said:

> You could use PG triggers on Updates, Deletes and Inserts (to insert
> owner user name) and try to use RULEs to rewrite SELECT statement.
> (I never played with RULEs...)

Perhaps if it can be done easily with rules, and then the 
method can be published -- but I have to wonder about using 
features that even the hardcore developers have never played 
with :-)   I guess I was hoping for some kind of specific
support in the core.

If CBAC were supported in a straightforward, easy way in PG it 
would be a major "selling" point imho.  We could say "and
PostgreSQL supports content-based access control..." 

		----------------------------

On the related subject, I like oids.  They are very useful.  
But I *also* want table-specific autonumbering (monotonic 
integer series in which gaps do indicate deleted records) and 
I'd really prefer that it be provided automatically as a table 
creation option, rather than having to write the same darn 
trigger N hundred times (as I have using the older 
Sybase engines).  

In fact, while I'm dreaming:  why not have a set of *three*
table creation options?  
	-recno 	causes an autonumber field to be prepended 
		to each record.  
	-user	causes the user ID of the inserting process 
		to be prepended to each record.  
	-stamp	causes the timestamp at insert time to be 
		prepended.  
The fields could have fixed names (like oid), say "recno",
"user", and "stamp" -- or as a luxury option the user could
supply their names as part of the option syntax:
	-user uid -stamp itime -recno seqno  

Like oid, these fields would *not appear* in result streams 
unless explicitly included in the FSE.  'select *' would not 
reveal them, but 'select oid,recno,user,stamp,*' would show 
all fields. (If these options are specified at table create 
time, then all subsequent inserts to that table automatically 
fill the special fields, without the user or developer having 
to know or care.) 

Of the couple of hundred tables I've designed and deployed in 
various apps over the last 5 years, perhaps 10 or 20 have 
*not* needed these fields.  I've cut-n-pasted the same darn 
boilerplate code a couple of hundred times to implement these 
fields on all the other tables.  If I could specify a few 
simple options on 'create table' instead, so much drudgery 
would be eliminated! And users would not see the "bookkeeping" 
fields by default.  Sounds heavenly to me.  

Once you imagine that automatic entry options of this sort 
exists, then CBAC seems like a logical accompaniment, 
available only on those tables where the UID auto-stamp has 
been selected at create time. It would be just one more 
option:  CBAC: Y/N. If Y, then the uid field is checked 
against the owner of the select query and the filtering done 
as described in my last post to HACKERS.

This seems (to me anyway) like a killer value-added feature,
something that would make PG so-o-o-o attractive for real,
practical, bread-n-butter app development.  How hard would 
it be?  (I know, that's the most annoying question anyone 
can ask a developer).

Am I alone in thinking this would be incredibly cool and
worth some effort?  I would love to see PG be demonstrably
BETTER than the commercial competition in several specific
areas (then maybe I could get approval to use it for
production work) and this looks like a good opportunity.

de

.............................................................................
:De Clarke, Software Engineer                     UCO/Lick Observatory, UCSC:
:Mail: de@ucolick.org | "There is no problem in computer science that cannot: 
:Web: www.ucolick.org |  be solved by another level of indirection"  --J.O. :




Thu, 15 Jan 1998 12:53:00 -0800 (PST)
De Clarke <de@ucolick.org>
CBAC (content based access control), OIDs, auto fields
> Am I alone in thinking this would be incredibly cool and
> worth some effort?  I would love to see PG be demonstrably
> BETTER than the commercial competition in several specific
> areas (then maybe I could get approval to use it for
> production work) and this looks like a good opportunity.
> 

Yes, these would all be nice, but we have to get the 'bread and butter'
features working perfectly first, like subselects.

-- 
Bruce Momjian
maillist@candle.pha.pa.us

Thu, 15 Jan 1998 16:03:19 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] CBAC (content based access control), OIDs, auto fields
On Thu, 15 Jan 1998, De Clarke wrote:

> Am I alone in thinking this would be incredibly cool and
> worth some effort?  I would love to see PG be demonstrably
> BETTER than the commercial competition in several specific
> areas (then maybe I could get approval to use it for
> production work) and this looks like a good opportunity.

	Look forward to seeing the patches? :)

	I think the concepts are cool (uid, recno, timestamp) for having
this directly in the tables, and the concept of being able to have record
based restrictions on a table doubly so.  

	An example of the usefulness of this is the radiusd accounting
package I've been working on.  Be nice to be able to let the salesman have
direct select access to the login records for *their* clients, and then
have full access to the personal information records to actually make
changes...without them having access to another clients records...



Thu, 15 Jan 1998 16:10:37 -0500 (EST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] CBAC (content based access control), OIDs, auto fields
Thus spake De Clarke
> In fact, while I'm dreaming:  why not have a set of *three*
> table creation options?  
> 	-recno 	causes an autonumber field to be prepended 
> 		to each record.  
> 	-user	causes the user ID of the inserting process 
> 		to be prepended to each record.  
> 	-stamp	causes the timestamp at insert time to be 
> 		prepended.  
> The fields could have fixed names (like oid), say "recno",
> "user", and "stamp" -- or as a luxury option the user could
> supply their names as part of the option syntax:
> 	-user uid -stamp itime -recno seqno  
> 
> Like oid, these fields would *not appear* in result streams 
> unless explicitly included in the FSE.  'select *' would not 
> reveal them, but 'select oid,recno,user,stamp,*' would show 
> all fields. (If these options are specified at table create 

I like the general idea but I think they should either show up
when '*' is selected or else automatically put them into each
table.  If you don't then generic functions will have problems.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Thu, 15 Jan 1998 23:21:30 -0500 (EST)
darcy@druid.net (D'Arcy J.M. Cain)
Re: [HACKERS] CBAC (content based access control), OIDs, auto fields