[TRAFODION-1788] Grant and Revoke on table columns with referencing views does not work

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Component/s: sql-security
• Assignee: Roberta Marton
• Reporter: Roberta Marton
• Created: 28/Jan/16 00:04
• Updated: 25/Aug/16 22:14
• Resolved: 25/Aug/16 22:14

Description

This is a similar problem to TRAFODION-1761 but has to do with column privileges. TRAFODION-1761 fixes issues with object privileges.

Granting or revoking column privilege on a table/view that has referencing views requires the grant or revoke be propagated to said views. (Ansi standard <grant statement> and <revoke statement> general rules section). Take grant, for example. If granting column privileges to a table, the column privileges should be propagated to all referencing views where the grantor is the system user, the grantee is the view owner, and the view contains columns defined in the grant statement.

• If the privilege is update, or delete and the referencing view is updatable (you can't grant insert privilege to column).

• If the privilege is select and the WGO is being added (the select privilege is required to create the referencing view in the first place).

There are a few issues with grant and revoke that need to be fixed: