[DERBY-1330] Provide runtime privilege checking for grant/revoke functionality

• Type: Sub-task
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.2.1.6
• Fix Version/s: 10.2.1.6
• Component/s: SQL
• Urgency: Urgent
• Assignee: Mamta A. Satoor
• Reporter: Mamta A. Satoor
• Created: 18/May/06 11:44
• Updated: 30/Aug/06 06:39
• Resolved: 30/Aug/06 06:37

Description

Additional work needs to be done for grant/revoke to make sure that only users with required privileges can access various database objects. In order to do that, first we need to collect the privilege requirements for various database objects and store them in SYS.SYSREQUIREDPERM. Once we have this information then when a user tries to access an object, the required SYS.SYSREQUIREDPERM privileges for the object will be checked against the user privileges in SYS.SYSTABLEPERMS, SYS.SYSCOLPERMS and SYS.SYSROUTINEPERMS. The database object access will succeed only if the user has the necessary privileges.

SYS.SYSTABLEPERMS, SYS.SYSCOLPERMS and SYS.SYSROUTINEPERMS are already populated by Satheesh's work on DERBY-464. But SYS.SYSREQUIREDPERM doesn't have any information in it at this point and hence no runtime privilege checking is getting done at this point.

Issue Links

• incorporates: DERBY-1539 A trigger should be dropped when a privilege required by the trigger is revoked.
• incorporates: DERBY-1611 A view should be dropped when a privilege required by the view is revoked.
• incorporates: DERBY-1612 A constraint should be dropped when a privilege required by the constraint is revoked.
• incorporates: DERBY-1643 A "revoke execute ... restrict" should fail if there are dependent objects on the execute privilege
• incorporates: DERBY-1579 Remove SYS.SYSREQUIREDPERM from Derby 10.2. This was added for Grant Revoke functionality
• is blocked by: DERBY-1646 Documentation to address Grant/Revoke Authorization for views/triggers/constraints/routines(DERBY-1330)
• relates to: DERBY-1632 During revoke privilege, Derby does not look for replacement privilege for the dependent objects and simply drops the dependent objects. This is not SQL compliant and should be fixed.
• relates to: DERBY-1782 When a privilege is revoked at table level, Derby should only drop objects that require that particular privilege and not all the objects that require some form of privilege on that table.
• relates to: DERBY-1686 Grant/Revoke: Attempt to GRANT access to another user on a VIEW, created by the current user with only SELECT privilege on the base table does not fail
• relates to: DERBY-1631 Derby needs to support drop view cascade in order for revoke privilege to function correctly