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
SQL does not reject GRANT statements that cause a user’s permissions to be duplicated.
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
Later, SERENA revokes the privilege and grant authority for BRUNHILDE:REVOKE INSERT
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:
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.
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.