Granting the Right to Grant Privileges - Firebird

Initially, only the owner of a table or view, or SYSDBA, can grant permission on the object to other users. Add WITH GRANT OPTION to the end of the GRANT statement to transfer the right to grant privileges on to the user, along with the privilege itself.

The following statement assigns a SELECT permission to user HPOTTER and allows HPOTTER to grant the SELECT permission to others:

GRANT SELECT ON DEPARTMENT TO HPOTTER WITH GRANT OPTION;

WITH GRANT OPTION cannot be assigned to a trigger or procedure. WITH GRANT OPTION rights are cumulative, even if issued by different users. For example, HPOTTER can get the grant authority for SELECT on DEPARTMENT from one user and for INSERT on DEPARTMENT from another.

In the example, HPOTTER was granted SELECT access to the DEPARTMENT table with grant authority. HPOTTER can grant this SELECT permission to other users. Suppose HPOTTER is now granted INSERT permission on the table as well, but without the grant authority:

GRANT INSERT ON DEPARTMENT TO HPOTTER;

HPOTTER can select from and insert to DEPARTMENT. He can grant SELECT permissions on DEPARTMENT to other users, but he cannot assign INSERT permissions because he does not have grant authority for that privilege.

The user’s existing privileges can be extended to include grant authority, by issuing a second GRANT statement for the same privilege that includes WITH GRANT OPTION.

To give HPOTTER authority to grant INSERT permission on DEPARTMENT, just issue a new statement:

GRANT INSERT ON DEPARTMENT TO HPOTTER WITH GRANT OPTION;

In summary, a user can grant an access privilege (SELECT, INSERT, UPDATE, DELETE, and REFERENCES) on an object to other users or objects, if the user either

  • Owns the object
  • Has been granted that privilege on that object WITH GRANT OPTION
  • Has acquired the privilege by being granted a role containing that privilege WITH ADMIN OPTION

SQL does not reject GRANT statements that cause a user’s permissions to be duplicated.

Unintended Effects

SQL allows the same grantee to get the same permissions from different grantors, even if it would duplicate a permission the grantee already has. Every time one user extends grant authority to another user, it opens one more source through which any user could receive permissions. The permissions structure has the potential to become the proverbial bird’s nest, from which it is very difficult to extricate the actual state of permissions for an individual user or object.

Suppose two users to whom the appropriate privileges and grant authority have been extended, SERENA and HPOTTER, both issue the following statement:

GRANT INSERT
ON DEPARTMENT
TO BRUNHILDE
WITH GRANT OPTION;

Later, SERENA revokes the privilege and grant authority for BRUNHILDE:

REVOKE INSERT
ON DEPARTMENT
FROM BRUNHILDE;

SERENA thinks BRUNHILDE no longer has INSERT permission or grant authority for the DEPARTMENT table. However, the REVOKE appears to have no effect, since BRUNHILDE still has the INSERT permission and grant authority assigned by HPOTTER.

As the number of users with privileges and grant authority for a table proliferates, the likelihood that different users can grant the same privileges and grant authority to any single user also expands. Quite simply, like nuclear fission, it can go out of control. It can become a big job just to revoke a specific permission. Revoking all (or many) permissions for a particular user can become an astronomical challenge.

If it is possible that the user might have received rights from several grantors, there are two possible solutions, both messy:

  • Find each and every permission granted to that user, along with the grantor in each case, and have each grantor revoke each permission it granted. This gets complicated when ALL and PUBLIC are involved, since revoking a more targeted permission doesn’t revoke rights acquired through ALL, PUBLIC, roles, or groups.
  • The owner of each table or object (or SYSDBA) issues REVOKE statements affecting all users of the table, and then issues GRANT statements to re-establish access privileges for the users who need to keep their rights.

The server gives no feedback about any REVOKE command, regardless of whether it succeeds or fails. It’s around about this point in your first misadventure with SQL permissions that you roll your eyes to the heavens and ponder the real mission of standards committees on Earth. A well-designed graphic rights manager utility can save your sanity. Fortunately, many of the desktop admin programs do provide this support and a number of custom grant manager tools are available.


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

Firebird Topics