[DERBY-464] Enhance Derby by adding grant/revoke support. Grant/Revoke provide finner level of privileges than currently provided by Derby that is especially useful in network configurations.

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.0.2.1, 10.1.1.0, 10.2.1.6
• Fix Version/s: 10.2.1.6
• Component/s: SQL
• Environment: generic
• Urgency: Normal
• Assignee: Unassigned
• Reporter: Satheesh E. Bandaram
• Created: 16/Jul/05 09:43
• Updated: 01/Jul/09 00:34
• Resolved: 18/Sep/06 15:44

Description

Derby currently provides a very simple permissions scheme, which is quite suitable for an embedded database system. End users of embedded Derby do not see Derby directly; they talk to a application that embeds Derby. So Derby left most of the access control work to the application. Under this scheme, Derby limits access on a per database or per system basis. A user can be granted full, read-only, or no access.

This is less suitable in a general purpose SQL server. When end users or diverse applications can issue SQL commands directly against the database, Derby must provide more precise mechanisms to limit who can do what with the database.

I propose to enhance Derby by implementing a subset of grant/revoke capabilities as specified by the SQL standard. I envision this work to involve the following tasks, at least:

1) Develop a specification of what capabilities I would like to add to Derby.
2) Provide a high level implementation scheme.
3) Pursue a staged development plan, with support for DDL added to Derby first.
4) Add support for runtime checking of these privileges.
5) Address migration and upgrade issues from previous releases and from old scheme to newer database.

Since I think this is a large task, I would like to invite any interested people to work with me on this large and important enhancement to Derby.

Issue Links

• incorporates: DERBY-1523 Statements in cache need to depend on privileges and the appropriate statements in cache should get invalidated if those privileges change.
• incorporates: DERBY-1521 Upgrade test needs to be enhanced to test grant revoke
• incorporates: DERBY-1367 add lang/grantrevoke.java to derbynetclientmats
• incorporates: DERBY-1579 Remove SYS.SYSREQUIREDPERM from Derby 10.2. This was added for Grant Revoke functionality
• incorporates: DERBY-1522 Switch(if supported) from Derby Authorization to Derby SQL Standard Authorization needs to be tested
• is related to: DERBY-1523 Statements in cache need to depend on privileges and the appropriate statements in cache should get invalidated if those privileges change.
• is related to: DERBY-1589 CREATE TABLE throws NullPointerException in Derby SQL Standard Authorization after DROPs and REVOKES
• relates to: DERBY-4268 "SECURITY" is reserved as SQL keyword.
• relates to: DERBY-1538 Unexpected behavior on self privilege revocation
• relates to: DERBY-1542 Implicit revoke via drop table does not clean up its associated row(s) in SYS.SYSTABLEPERMS
• relates to: DERBY-1582 REVOKE statement does not generate a warning when no privileges are revoked.
• relates to: DERBY-1583 With grant revoke enabled, defining a trigger whose actions updates a table (from different schema) results in NPE
• relates to: DERBY-1592 Update statement is allowed to execute even though the column that the statement access has been revoked.
• relates to: DERBY-1708 Unprivileged user can perform lock table statement on a table which he/she does not have any access rights
• relates to: DERBY-1715 Revoke fails when grantee declares additional view that is on top of the view to be dropped
• relates to: DERBY-1716 Revoking select privilege from a user times out when that user still have a cursor open.
• relates to: DERBY-1723 Database owner revokes select privilege from a schema owner but owner is still able to select
• relates to: DERBY-1724 Executing grant statement within a transaction leads to a NPE later when the db owner attempts to update a table
• relates to: DERBY-1729 Invoking Java stored procedure that contains GRANT or REVOKE statement with CONTAINS SQL should fail.
• relates to: DERBY-1738 An user is able to grant select privilege on a view but the underlying object is not own by the user.
• relates to: DERBY-1847 SELECT statement asserts with XJ001 when attempted to select a newly added column in SQL authorization mode

Sub-Tasks

• Add EXTERNAL SECURITY DEFINER and EXTERNAL SECURITY INVOKER support for routines(functions or procedures): Closed
• documentation to address Grant/Revoke (Derby-464): Closed
• Provide runtime privilege checking for grant/revoke functionality: Closed
• Add GRANTOR column to primary key for SYSTABLEPERMS, SYSCOLPERMS and SYSROUTINEPERMS: Closed
• Address two remaining issues with GRANT/REVOKE functionality: 1) Add warning when sqlAuthorization is on with authentication off 2) Drop permission descriptors when objects they cover are dropped.: Closed
• Address remaining upgrade task(s) to complete full upgrade mechanism for GRANT/REVOKE, specifically with changing database owner name from 'DBA' to authorizationId of user invoking upgrade.: Closed