Thread
-
content-based access control (Re: views, access control, etc)
De Clarke <de@ucolick.org> — 1998-01-14T20:22:34Z
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. :
-
Re: [HACKERS] content-based access control (Re: views, access control, etc)
Vadim B. Mikheev <vadim@sable.krasnoyarsk.su> — 1998-01-15T04:11:51Z
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
-
CBAC (content based access control), OIDs, auto fields
De Clarke <de@ucolick.org> — 1998-01-15T20:53:00Z
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. :
-
Re: [HACKERS] CBAC (content based access control), OIDs, auto fields
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-01-15T21:03:19Z
> 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
-
Re: [HACKERS] CBAC (content based access control), OIDs, auto fields
Marc G. Fournier <scrappy@hub.org> — 1998-01-15T21:10:37Z
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...
-
Re: [HACKERS] CBAC (content based access control), OIDs, auto fields
D'Arcy Cain <darcy@druid.net> — 1998-01-16T04:21:30Z
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.