db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-64) Create a table with a query
Date Thu, 14 Jun 2007 20:50:26 GMT

    [ 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:
>         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.

View raw message