--- In firebird-support@yahoogroups.com, "Chad Z. Hower aka Kudzu"
<chad-jm@h...> wrote:

> In the grant options, what does reference mean? Ive read the docs a

dozen

> times, but I don't undersand how it differs from select.

It's very different. AFAIU, Borland got it wrong, so what I'm going to
say doesn't apply to IB unless they fixed it.

During FB1 development, it was determined after conversations with a
person that has experience with the SQL standard that "references" is
a DDL-time right unlike the rest that applies at DML-time and thus
changes were made in the code.
When you grant "references" to Joe, it means Joe can reference your
table when creating a FK. Once the relationship is created, anyone can
use it implicitly when the FK is enforced. No checks are done at usage
time. If you want to be more specific, you can tell the system that
Joe can use a specific field(s) only as the lookup field for an FK.
Example:
sysdba creates table T1 with fields A and B. A is a PK. Sysdba then
grants references on T1(A) to Joe.
Joe creates table T2 with fields D, E and F, with F being a FK
referencing T1(A).
If the right didn't exist, Joe couldn't create the FK pointing to T1's
A. Of course, you can grant references on all the table (without
specifying a field) so if you have a PK and several Unique fields in
T1, Joe can use any of them to form the relationship.

Why it's not terribly useful:
- Typically, there's one user that creates the whole script and
probably the same user is the designated DBA or at least the
maintainer of that db, so there's no much to check. I don't remember
if we considered explicitly the case when the owner of both tables is
the same. In general, db owner and table owner have some freedom here
and there in the code.
- To avoid alienating users, when you grant SELECT, REFERENCES is
granted under the hood. But you can revoke it.

The right was silent in IB4.2 but it wasn't documented until IB5.
Apparently it was non-functional in v4 releases. You can test and
probably will find some issue that needs to be fixed some day, but I
consider it low priority until someone makes a compelling case.

C.