On Mon, 2002-08-12 at 11:38, Mario Weilguni wrote:
> Am Montag, 12. August 2002 08:02 schrieb Don Baccus:
> > Curt Sampson wrote:
> > > On Sun, 11 Aug 2002, Don Baccus wrote:
> > >>I've been wanting to point out that SQL views are really, when
> > >>scrutinized, "just syntactic sugar" ...
> > >
> > > Oh? Ok, please translate the following into equivalant SQL that
> > > does not use a view:
> > >
> > > CREATE TABLE t1 (key serial, value1 text, value2 text);
> > > CREATE VIEW v1 AS SELECT key, value1 FROM t1;
> > > GRANT SELECT ON v1 TO sorin;
> >
> > Granulize GRANT to the table column level. Then GRANT "SELECT" perms
> > for the user on every column from the two tables that happen to be
> > included in the view.
> >
> > Yes, it's awkward. So are the VIEW-based replacements for PG's type
> > extensibility features.
>
> But this is not a replacement for a view, isn't it? With a view I can do this:
> create view v1 as select name, salary from workers where type <> 'MANAGEMENT';
>
> with column permissions I must give access to all workers salary including the management, but not with a view.
I guess that bare-bones replacement of CREATE VIEW with CREATE TABLE and
CREATE RULE ... ON SELECT DO INSTEAD ... would have exaclty the same
semantics as CREATE VIEW, including the ability to GRANT .
so the no-view-syntactic-sugar equivalent would be
CREATE TABLE v1 AS SELECT * FROM t1 WHERE false;
CREATE RULE v1ins AS ON SELECT TO tv1 DO INSTEAD SELECT t1."key", t1.value2 FROM t1 WHERE
(t1."type"<> 'MANAGEMENT'::text);
GRANT SELECT ON v1 TO sorin;
Actually it seems that GRANT is also syntactic sugar for rules and the
above could be replaced with
CREATE RULE v1ins AS ON SELECT TO tv1 DO INSTEAD SELECT t1."key", t1.value2 FROM t1 WHERE
(t1."type"<> 'MANAGEMENT'::text) AND CURRENT_USER IN ( SELECT username FROM grantees
WHERE tablename = 'v1' AND command = 'select' )
INSERT INTO GRANTEES(tablename,command,username) VALUES('v1','select','sorin');
----------------
Hannu