Обсуждение: Priviliges on tables and views
Since PostgreSQL doesn't have column level permissions, I tried to do something with views like this. CREATE TABLE account ( uid int, # Unique UID for account login char8, # User login - must also be unique cdate date, # Creation date a_active bool, # true or false gedit bool, # edit privs for group bid int, # reference to billing group table password text, # Encrypted password gcos text, # Public information home text, # home directory shell char8); # which shell CREATE UNIQUE INDEX account_uid ON account (uid); CREATE UNIQUE INDEX account_login ON account (login char8_ops); REVOKE ALL ON account FROM PUBLIC; CREATE VIEW passwd AS SELECT uid, login, bid, gcos, home, shell FROM account WHERE a_active = 't'; REVOKE ALL ON passwd FROM PUBLIC; GRANT SELECT ON passwd TO PUBLIC; Unfortunately this doesn't work. The VIEW inherits the permissions from the table it is a view of. It seems to me that allowing a view to define permissions separately from its parent would be a useful thing. So, does anyone know if this behaviour is allowed by the SQL spec and if it is allowed, would this be difficult to do? -- 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.
D'Arcy J.M. Cain wrote: > > REVOKE ALL ON account FROM PUBLIC; > > CREATE VIEW passwd AS SELECT uid, login, bid, gcos, home, shell > FROM account WHERE a_active = 't'; > > REVOKE ALL ON passwd FROM PUBLIC; > GRANT SELECT ON passwd TO PUBLIC; > > Unfortunately this doesn't work. The VIEW inherits the permissions > from the table it is a view of. It seems to me that allowing a view > to define permissions separately from its parent would be a useful > thing. So, does anyone know if this behaviour is allowed by the > SQL spec and if it is allowed, would this be difficult to do? This is allowed by SQL and this is very useful thing. Not easy to implement: views are handled by RULES - after parsing and before planning, - but permissions are checked by executor (execMain.c:InitPlan()->ExecCheckPerms()). Vadim
Thus spake Vadim B. Mikheev > > CREATE VIEW passwd AS SELECT uid, login, bid, gcos, home, shell > > FROM account WHERE a_active = 't'; > > > > REVOKE ALL ON passwd FROM PUBLIC; > > GRANT SELECT ON passwd TO PUBLIC; > > > > Unfortunately this doesn't work. The VIEW inherits the permissions > > from the table it is a view of. It seems to me that allowing a view > > to define permissions separately from its parent would be a useful > > thing. So, does anyone know if this behaviour is allowed by the > > SQL spec and if it is allowed, would this be difficult to do? > > This is allowed by SQL and this is very useful thing. Not easy to implement: > views are handled by RULES - after parsing and before planning, - but > permissions are checked by executor (execMain.c:InitPlan()->ExecCheckPerms()). Oh well. Is it worth putting on the TODO list at least? Maybe someone will get to it eventually. In the meantime, how close are we to being able to update views? I can do what I want that way - just make two tables with public perms on one but not the other and make a view for the combined table instead of for a subset of a table. -- 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.
> > Thus spake Vadim B. Mikheev > > > CREATE VIEW passwd AS SELECT uid, login, bid, gcos, home, shell > > > FROM account WHERE a_active = 't'; > > > > > > REVOKE ALL ON passwd FROM PUBLIC; > > > GRANT SELECT ON passwd TO PUBLIC; > > > > > > Unfortunately this doesn't work. The VIEW inherits the permissions > > > from the table it is a view of. It seems to me that allowing a view > > > to define permissions separately from its parent would be a useful > > > thing. So, does anyone know if this behaviour is allowed by the > > > SQL spec and if it is allowed, would this be difficult to do? > > > > This is allowed by SQL and this is very useful thing. Not easy to implement: > > views are handled by RULES - after parsing and before planning, - but > > permissions are checked by executor (execMain.c:InitPlan()->ExecCheckPerms()). > > Oh well. Is it worth putting on the TODO list at least? Maybe someone > will get to it eventually. > > In the meantime, how close are we to being able to update views? I can > do what I want that way - just make two tables with public perms on > one but not the other and make a view for the combined table instead > of for a subset of a table. Certainly is a good item for the TODO list. Added: * Allow VIEW permissions to be set separately from the underlying tables -- Bruce Momjian maillist@candle.pha.pa.us