Wednesday, August 3, 2011

Revoke in Sybase

I learned something new today about how Sybase's  revoke SQL statement works. In Sybase a database user can be granted a privilege directly or indirectly (via role and/or group membership). In case of a direct grant, revoking the privilege will take the privilege out. But in case of an indirect grant its a little different.

If the indirect grant comes via a role, then revoke  doesn't do anything (actually throws an error), but if the grant comes from the group to which the user belongs, then the user would be denied  the privilege. Everyone else belonging to the group would continue to have the privilege except this user. A row is added to the sysprotects table to this effect.  Also note it should be noted that this applies to object privileges only - revoking an indirectly granted system privilege (either via role or group) does not have any effect.

Also, its worth nothing here that role privileges trump any privilege grants and revokes at the group or direct user level.

To explain this by an example, lets assume user user1 belongs to group group1 and is also granted a role role1.

grant select on table table1 to group1

This gives user1  a select privilege on table1 via group group1.

revoke select on table1 from user1

This denies user1 select on table1

grant select on table1 to role1

This gives the select privilege back to user1 because the role privilege trumps any group and user level grants/revokes

 

No comments:

Post a Comment