Received: from me.tm.ee (ppp881.tele2.ee [212.107.37.181]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA32821 for ; Fri, 19 May 2000 12:19:37 -0400 (EDT) (envelope-from hannu@tm.ee) Received: from tm.ee (IDENT:hannu@localhost.localdomain [127.0.0.1]) by me.tm.ee (8.9.3/8.9.3) with ESMTP id SAA00927; Fri, 19 May 2000 18:15:53 +0300 Message-ID: <39255AA9.906AA621@tm.ee> Date: Fri, 19 May 2000 18:15:53 +0300 From: Hannu Krosing X-Mailer: Mozilla 4.72 [en] (X11; U; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: Hiroshi Inoue , Tom Lane , Stephan Szabo , pgsql-hackers@postgresql.org Subject: Re: Re: [SQL] Foreign keys breaks tables permissions References: <25918.958691826@sss.pgh.pa.us> <001b01bfc13e$1df17600$0c64010a@kick.com> <6189.958707499@sss.pgh.pa.us> <39252551.2C3B0B17@tpf.co.jp> <39254A1C.2A4D546F@tm.ee> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200005/265 Hannu Krosing wrote: > > Hiroshi Inoue wrote: > > > > Tom Lane wrote: > > > > > "Stephan Szabo" writes: > > > > I believe the reason that the trigger does a select for update was > > > > because otherwise there could exist a case that we select and see it > > > > and then have the row go away afterwards because nothing stops the > > > > delete. > > > > > > Probably the denial-of-service argument is the weakest of the three > > > points. Is anyone in favor of reducing SELECT FOR UPDATE to only > > > requiring "SELECT" rights, and living with the possible lock-that- > > > you-shouldn't-really-have-been-able-to-get issue? > > > > > > > But what about DELETE CASCADE cases for exmaple ? > > Maybe RI_trigger should be able to update/insert/delete > > the referenced table. > > However another kind of permission for foreign key > > seems to be needed. i.e only granted users could > > define foreign key of the referenced table in CREATE > > (ALTER) TABLE command. > > IIRC this is even in the SQL standard as a separate right (maybe REFERENCES ?) Here's from SQL92 draft: We should at least consider it when designing our GRANT system ......... 4.26 Privileges A privilege authorizes a given category of to be per- formed on a specified base table, view, column, domain, character set, collation, or translation by a specified . The mapping of s to operating system users is implementation-dependent. The s that can be specified are: - INSERT - INSERT () - UPDATE - UPDATE () - DELETE - SELECT - REFERENCES - REFERENCES () - USAGE ....... A privilege descriptor with an action of INSERT, UPDATE, DELETE, SELECT, or REFERENCES is called a table privilege descriptor and identifies the existence of a privilege on the table identified by the privilege descriptor. A privilege descriptor with an action of SELECT (), INSERT (), UPDATE (), or REFERENCES () is called a column privilege de- scriptor and identifies the existence of a privilege on the column in the table identified by the privilege descriptor. Note: In this International Standard, a SELECT column privilege cannot be explicitly granted or revoked. However, for the sake of compatibility with planned future language extensions, SELECT column privilege descriptors will appear in the Information Schema. A table privilege descriptor specifies that the privilege iden- tified by the action (unless the action is DELETE) is to be au- tomatically granted by the grantor to the grantee on all columns subsequently added to the table. A privilege descriptor with an action of USAGE is called a usage privilege descriptor and identifies the existence of a privilege on the domain, character set, collation, or translation identified by the privilege descriptor. A grantable privilege is a privilege associated with a schema that may be granted by a . The phrase applicable privileges refers to the privileges defined by the privilege descriptors that define privileges granted to the current . The set of applicable privileges for the current consists of the privileges defined by the privilege descriptors associated with that and the privileges defined by the privilege descriptors associated with PUBLIC. Privilege descriptors that represent privileges for the owner of an object have a special grantor value, "_SYSTEM". This value is reflected in the Information Schema for all privileges that apply to the owner of the object. ........ 11.36 Function Define privileges. Format ::= GRANT ON TO [ { }... ] [ WITH GRANT OPTION ] ::= [ TABLE ] | DOMAIN | COLLATION | CHARACTER SET | TRANSLATION ----------- Hannu