New security check for reference privileges.Although not documented, the REFERENCES privileges were present in IB4, although I'm not sure if they are operational in IB4. Unlike delete, insert or update rights, REFERENCES resembles SELECT rights in the fact that it allows reading values on a table, but it's very specific.Contrary to the other permissions that can be the result of direct user commands, REFERENCES is done by the engine on behalf of the user that attempts to reference a unique key on a table as a result of a FK in a child table that points to such unique key. So, a security check for REFERENCES privileges allows the owner of a table to allow or disallow reference to one of its unique keys from a foreign index on another table. The manual is wrong when it says "reference to its PK", since it doesn't need to be the primary key, but only a unique key in the master table. Of course, such unique key could be the PK itself. Reading that, one realizes that only IB5 and newer releases check and enforce this right. Since this is a rather subtle and not very known privilege, an example is need: table "master" has a unique key on field "article" and table "orders" is a detail table whose FK is "article_num" that references master(article). The owner of master can grant REFERENCES on table "orders" to PUBLIC without giving PUBLIC any SELECT right on "master". Since PUBLIC represents any logged in user, any user that can create a record on "orders" will cause the engine to verify that orders(article_num) exists in master(article), otherwise a PK violation error would be reported. Also, this right doesn't imply that any user has gained explicit SELECT right over "master". Conversely, if no user has REFERENCES right on "master", no user can insert a record on "orders" even with full rights over this table, because the FK cannot be verified on "master". REFERENCES and UPDATE are the only rights that can be granted or denied on specific fields of a table. By default, in absence of a field name, those rights are granted or denied on all fields in a table. I always have wondered why SELECT cannot be controlled in a field-wise way and the only explanation I can guess is that you can produce a restricted SELECT by creating a VIEW that only exposes the desired columns and giving SELECT rights on the VIEW but not on its underlying table or tables. |
|
This page was last updated on 2001-01-16 01:40:28 |