[jira] [Updated] (DERBY-6429) Privilege checks for UPDATE statements are wrong.

["Rick Hillegas (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-6429?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas updated DERBY-6429:
---------------------------------

    Attachment: derby-6429-01-ab-privilegeFilters.diff

Attaching derby-6429-01-ab-privilegeFilters.diff. This is a preliminary version of a fix to this bug. I expect that errors will pop up when I run the full regression tests. In addition, I plan to write additional tests. I'm attaching this preliminary version because I think it is a promising approach.

My first attempt to fix this bug failed. This is what I tried inside UpdateNode.bindStatement():

1) Analyze - Collect a list of all the nodes which should give rise to privilege checks.

2) Bind - Continue binding as usual.

3) Rebind - At the end of UpdateNode.bindStatement(), create a new CompilerContext and rebind just the nodes which were identified in step 1.

This approach failed because step 2 ended up changing the AST in a way which made it impossible to determine the correct permissions during step 3 for certain edge-cases. These cases involved views.

The current patch does something subtler:

1') Analyze - Mark all nodes which should give rise to privilege checks at run time.

2') Bind - Bind as usual. However, only add privilege checks for the nodes marked during step 1'.

3') Rebind - Rebind UDT references. These can occur on any ValueNode and so can't be picked up during step 1'.

The patch introduces the following abstractions:

VisitableFilter - This is a generic filter for qualifying QueryTreeNodes

QueryTreeNode tags - QueryTreeNodes can be tagged for various purposes now.

The patch works by tagging QueryTreeNodes during step 1' and then only compiling privilege checks for nodes which pass a TagFilter during step 2'.

I am hoping that VisitableFilters and QueryTreeNode tags are general tools which may be useful for other purposes.


Touches the following files:

-------------

A       java/engine/org/apache/derby/iapi/sql/compile/TagFilter.java
M       java/engine/org/apache/derby/iapi/sql/compile/Visitable.java
A       java/engine/org/apache/derby/iapi/sql/compile/VisitableFilter.java

Support for filtering QueryTreeNodes based on tags.

-------------

M       java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java
M       java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java
M       java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java

Adds the ability to declare filters for privilege checking.

-------------

M       java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java
M       java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
M       java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java

Changes to limit which QueryTreeNodes give rise to privilege checks for UPDATE statements.

-------------

M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
M       java/testing/org/apache/derbyTesting/functionTests/tests/store/Derby5234Test.java
M       java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java

Moves some helper methods which I've been using for years. I want to use these helper method in other test classes, so I'm moving the methods up to BaseJDBCTestCase.

-------------

M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java
M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java

New test for this patch.


> Privilege checks for UPDATE statements are wrong.
> -------------------------------------------------
>
>                 Key: DERBY-6429
>                 URL: https://issues.apache.org/jira/browse/DERBY-6429
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.11.0.0
>            Reporter: Rick Hillegas
>            Assignee: Rick Hillegas
>         Attachments: derby-6429-01-ab-privilegeFilters.diff
>
>
> UPDATE statements confuse SELECT and UPDATE privileges. Consider the following SET clause:
>    SET updateColumn = selectColumn
> According to part 2 of the 2011 edition of the SQL Standard, that SET clause requires the following privileges:
> 1) UPDATE privilege on updateColumn. Privileges for the left side of a SET clause are described by section 14.14 (update statement: searched), access rule 1b.
> 2) SELECT privilege on selectColumn. Privileges for the right side of a SET clause are described by section 14.15 (set clause list) and the various productions underneath value expression. In this case, we have a column reference, whose privileges are governed by section 6.7 (column reference), access rule 2.
> However, Derby requires the following:
> 1') UPDATE privilege on both updateColumn and selectColumn
> When we address this bug, we should make corresponding changes to the MERGE statement.
> The following script shows the current behavior:
> connect 'jdbc:derby:memory:db;user=test_dbo;create=true';
> call syscs_util.syscs_create_user( 'TEST_DBO', 'test_dbopassword' );
> call syscs_util.syscs_create_user( 'RUTH', 'ruthpassword' );
> connect 'jdbc:derby:memory:db;shutdown=true';
> connect 'jdbc:derby:memory:db;user=test_dbo;password=test_dbopassword' as dbo;
> create table t1_025
> (
>     a int primary key,
>     updateColumn int,
>     selectColumn int,
>     privateColumn int
> );
> grant update ( updateColumn ) on t1_025 to ruth;
> grant select ( selectColumn ) on t1_025 to ruth;
> insert into t1_025 values ( 1, 100, 1000, 10000 );
> connect 'jdbc:derby:memory:db;user=ruth;password=ruthpassword' as ruth;
> -- correctly succeeds because ruth has UPDATE privilege on updateColumn
> update test_dbo.t1_025 set updateColumn = 17;
> -- the error message incorrectly states that the missing privilege
> -- is UPDATE privilege on privateColumn
> update test_dbo.t1_025 set updateColumn = privateColumn;
> -- incorrectly fails.
> -- ruth does have UPDATE privilege on updateColumn
> -- and SELECT privilege on selectColumn, which should be good enough.
> -- however, the error message incorrectly states that the missing privilege
> -- is UPDATE privilege on selectColumn.
> update test_dbo.t1_025 set updateColumn = selectColumn;
> -- incorrectly succeeds even though ruth does not have SELECT privilege on updateColumn
> update test_dbo.t1_025 set updateColumn = 2 * updateColumn;
> set connection dbo;
> select * from t1_025 order by a;



--
This message was sent by Atlassian JIRA
(v6.1.4#6159)