Revoking a column level privilege from a user, a prepared statement relying on that privilege can still be executed

• Key: DERBY-3736
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: Dag H. Wanvik
• Reporter: Dag H. Wanvik
• Component/s: SQL
• Affects Version/s: 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3
• Fix Version/s: 10.4.2.0, 10.5.1.1

Description

When a table level SELECT privilege is revoked, a dependent prepared statement is
invalidated and can no longer be executed, but in the case of a column level privilege
SELECT privilege, the dependent prepared statement can still be executed.

This works as expected in 10.2, but does not work in all 10.3 and 10.4 releases.

Comments

[Dag H. Wanvik]

Attaching a repro, column-level.sql which shows the issue.
For contrast I attach table-level.sql which fails as expected.

I also attach a diff I made to GrantRevokeDDLTest with a test case
which can be used to reproduce.

I ran the scripts with this incantation
java -Dderby.user.dag=wanvik -Dderby.user.donald=duck -Dderby.connection.requireAuthentication=true -Dderby.database.sqlAuthorization=true -jar derbyrun.jar ij < script

[Dag H. Wanvik]

Enclosing a patch which fixes the issue by adding a
line for the column case in TablePrivilegeInfo:

> dd.getDependencyManager().invalidateFor
   (td, DependencyManager.INTERNAL_RECOMPILE_REQUEST, lcc);

There is already such a line for the case when a table level privilege is revoked
and there it carries the following comment:

// When revoking a privilege from a Table we need to
// invalidate all GPSs refering to it. But GPSs aren't
// Dependents of TablePermsDescr, but of the
// TableDescriptor itself, so we must send
// INTERNAL_RECOMPILE_REQUEST to the TableDescriptor's
// Dependents.

The patch adds the test case to GrantRevokeDDLTest uploaded earlier.
It fails without the above change to TablePrivilegeInfo, but works with it.

Running regressions now, ready for review.

[Rick Hillegas]

Thanks for the patch, Dag. It looks good. You might want to decorate your one line fix with the comment block which explains why INTERNAL_RECOMPILE_REQUEST is being requested:

// When revoking a privilege from a Table we need to
// invalidate all GPSs refering to it. But GPSs aren't
// Dependents of TablePermsDescr, but of the
// TableDescriptor itself, so we must send
// INTERNAL_RECOMPILE_REQUEST to the TableDescriptor's
// Dependents.

Thanks,
-Rick

[Dag H. Wanvik]

Thanks for looking at this, Rick. I will add the comment for the
column case as well. Regressions ran ok.

[Dag H. Wanvik]

Updating a new version, derby-3736b which replaces the first version.
This adds the comment Rick asked for plus fixes some whitespace issues.

I will commit this soon if no objects arise.

[Dag H. Wanvik]

committed derby-3736b as svn 674808 on trunk.

[Dag H. Wanvik]

I link this issue to DERBY-2594, which addressed the
analog issue for table level privileges.

[Dag H. Wanvik]

DERBY-2594 explains why this was not an issue in 10.2.
I will backport this fix to the 10.3 and 10.4 branches.

[Dag H. Wanvik]

Merged patch to 10.3 branch as svn 675121 and to 10.4 branch as svn 675116, closing.

The regressions ran OK on 10.3 branch, but I saw several errors and and one fault on the 10.4 branch which
I deem to be unrelated (JMX management beans tests).

[Dag H. Wanvik]

The errors I saw on the 10.4 branch running regressions were due to running with classes,
not the DERBY jar files in CLASSPATH.