[jira] Commented: (DERBY-64) Create a table with a query

["A B (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12504911 ] 

A B commented on DERBY-64:
--------------------------

Just curious: has anyone thought about whether or not this new feature creates any holes in the existing grant/revoke functionality?  That is, can a user use the CREATE TABLE AS ... syntax to select from tables that s/he otherwise does not have permission to access?  Sort of like it (briefly) allowed users to create columns that they weren't supposed to be allowed to create...(DERBY-2605, now resolved).

I did some quick tests by creating a table and a view and then using the CREATE TABLE AS ... statement to try to select from that table and view.  As far as I can tell things work correctly: if the user does not have permission to select from the table/view, the CREATE TABLE AS statement fails with the appropriate error.  Ex:

  ij(CONNECTION1)> create table t_oops as select * from app.v1 with no data;
  ERROR 42502: User 'INIGO' does not have select permission on column 'I' of table 'APP'.'V1'

So my guess is that everything is okay here.  But I thought I'd raise the issue anyways, just to see if anyone out there might know of any problematic scenarios...

If there does turn out to be an issue here, it's probably not *that* big of a deal since we don't support the "WITH DATA" option yet (DERBY-2288) and thus there's no way to use this new syntax to see data.  So all a user  would be able to do is see the column names and types of the table/view, which s/he can do via the ij "describe" command already.

But still, it'd be good to understand whether not such "holes" exist...

> Create a table with a query
> ---------------------------
>
>                 Key: DERBY-64
>                 URL: https://issues.apache.org/jira/browse/DERBY-64
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>            Reporter: Christian d'Heureuse
>            Assignee: James F. Adams
>             Fix For: 10.3.0.0
>
>         Attachments: Derby64Patch1.txt, Derby64Patch2.txt, Derby64Patch3.txt, Derby64Patch4.txt
>
>
> I suggest to implement a SQL statement to create and fill a table with a query, without having to write the columns definition.
> e.g.:
>  CREATE TABLE new_table AS SELECT ...;
> or:
>  SELECT ... INTO new_table FROM ...;

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.