Reference Privileges - Firebird

Firebird implements SQL security on all objects in the database. Every user except the owner of the database and users with SYSDBA or system root privileges must be GRANTed the necessary privileges to access an object.

However, one privilege may be of special significance in the design of your referential integrity infrastructure: the REFERENCES privilege. If the parent and child tables have different owners, a GRANT REFERENCES privilege may be needed to give users sufficient permission to enable referential constraint actions.

The REFERENCES privilege is granted on the referenced table in the relationship—that is, the table referenced by the foreign key—or, at least, on every column of the reference primary or unique key. The privilege needs to be granted to the owner of the referencing table (the child table) and also to any user who needs to write to the referencing table.

At runtime, REFERENCES kicks in whenever the database engine verifies that a value input to a foreign key is contained in the referenced table.

Because this privilege is also checked when a foreign key constraint is defined, it will be necessary for the appropriate permissions to be granted and committed before-hand. If you need to create a foreign key that refers to a table owned by someone else, that owner must first grant you REFERENCES privileges on that table. Alternatively, the owner can grant REFERENCES privileges to a role and then grant that role to you.

TIP

If you have these restrictions among your requirements, it may be necessary to maintain two separate permissions scripts: one for developers that is run following table creation and another for users that is run on an otherwise completed schema.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics