Thread: "with grant option" for user groups.
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Hi all, </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">We are building security system for a project where the security is modeled based on the sql-92 GRANT/REVOKEstatements.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">I was going through the documentation of postgresql related to GRANT/REVOKE statements.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Following statement says that "with grant option" is not allowed to a user group. I would like to knowwhat the reasons behind not implementing</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">this kind of feature.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font color="black" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black">If</span></font><tt><font color="black" face="Courier New" size="2"><span style="font-size:10.0pt;color:black">WITHGRANT OPTION</span></font></tt><font color="black"><span style="color:black"> isspecified, the recipient of the privilege may in turn grant it to others. By default this is not allowed. Grant optionscan only be granted to individual users, not to groups or </span></font><tt><font color="black" face="Courier New"size="2"><span style="font-size:10.0pt;color:black">PUBLIC</span></font></tt><font color="black"><span style="color:black">.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"><a href="http://developer.postgresql.org/docs/postgres/sql-grant.html">http://developer.postgresql.org/docs/postgres/sql-grant.html</a></span></font><p class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">I looked into ORACLE database also they don't support user groups, but they support roles. But even forroles "with grant option" is not allowed.</span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><spanstyle="font-size: 12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12.0pt">Thanks & regards,</span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12.0pt">Ramu</span></font></div>
> Following statement says that "with grant option" is not allowed to a user > group. I would like to know what the reasons behind not implementing > this kind of feature. Consider the following sequence of steps: in database 1: user A grants privilege to group B with grant option user C who is in group B grants privilege to user D in database 2: superuser removes user C from group B --> user D still has the privilege, because superuser doesn't have access to database 1 from his session If you can live with this problem, then you can remove the check from the source code and it should work.
Theoretically same kind of problem should arise even if the privilege is granted to a user also. To be specific I would like know the answers for the following Q's Scenario 1: =========== User A grants privilege to group B with grant option. User C who is in group B grants privilege to user D If super user removes the user C from the group, then who is the grantee for the user D? And who can revoke revoke the privileges from user D? Scenario 2: =========== User A grants privilege to group 'B' and 'Z' with grant option. User C who is in group 'B' and 'Z' grants privilege to user D. If user C removed from the group 'B' then who will be the grantee for user 'D'? And who can revoke revoke the privileges from user D? If user C is removed from both the groups then who will be the grantee for the user? And who can revoke revoke the privileges from user D? Thanks & Regards, Ramu -----Original Message----- From: Peter Eisentraut [mailto:peter_e@gmx.net] Sent: Friday, January 09, 2004 8:11 PM To: Potuganti Ramu; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] "with grant option" for user groups. > Following statement says that "with grant option" is not allowed to a user > group. I would like to know what the reasons behind not implementing > this kind of feature. Consider the following sequence of steps: in database 1: user A grants privilege to group B with grant option user C who is in group B grants privilege to user D in database 2: superuser removes user C from group B --> user D still has the privilege, because superuser doesn't have access to database 1 from his session If you can live with this problem, then you can remove the check from the source code and it should work.
Theoretically same kind of problem should arise even if the privilege is granted to a user also. To be specific I would like know the answers for the following Q's Scenario 1: =========== User A grants privilege to group B with grant option. User C who is in group B grants privilege to user D If super user removes the user C from the group, then who is the grantee for the user D? And who can revoke revoke the privileges from user D? Scenario 2: =========== User A grants privilege to group 'B' and 'Z' with grant option. User C who is in group 'B' and 'Z' grants privilege to user D. If user C removed from the group 'B' then who will be the grantee for user 'D'? And who can revoke revoke the privileges from user D? If user C is removed from both the groups then who will be the grantee for the user? And who can revoke revoke the privileges from user D? Thanks & Regards, Ramu -----Original Message----- From: Peter Eisentraut [mailto:peter_e@gmx.net] Sent: Friday, January 09, 2004 8:11 PM To: Potuganti Ramu; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] "with grant option" for user groups. > Following statement says that "with grant option" is not allowed to a user > group. I would like to know what the reasons behind not implementing > this kind of feature. Consider the following sequence of steps: in database 1: user A grants privilege to group B with grant option user C who is in group B grants privilege to user D in database 2: superuser removes user C from group B --> user D still has the privilege, because superuser doesn't have access to database 1 from his session If you can live with this problem, then you can remove the check from the source code and it should work.
Potuganti Ramu wrote: > Scenario 1: > =========== > User A grants privilege to group B with grant option. > User C who is in group B grants privilege to user D > > If super user removes the user C from the group, then who is the > grantee for the user D? And who can revoke revoke the privileges from > user D? The privileges should be revoked from user D automatically in the same step that removes the user C from the group. > Scenario 2: > =========== > User A grants privilege to group 'B' and 'Z' with grant option. > User C who is in group 'B' and 'Z' grants privilege to user D. > > If user C removed from the group 'B' then who will be the grantee for > user 'D'? And who can revoke revoke the privileges from user D? In strict SQL only one role can be active at one time, so there is no problem. If we didn't want to use that restriction, we'd need to think of something else. > If user C is removed from both the groups then who will be the > grantee for the user? And who can revoke revoke the privileges from > user D? See your scenario 1 above.