[jira] [Commented] (TRAFODION-1100) LP Bug: 1437470 - Creator of view in private schema unable to select from view

["Roberta Marton (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-1100?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15011858#comment-15011858 ] 

Roberta Marton commented on TRAFODION-1100:
-------------------------------------------

For private schemas, all objects are owned by the schema owner. If an authID has been granted the  "CREATE" component privilege, they can create objects in other schemas but the owner of these new objects are still the schema owner.
In this case, when the object creator is not the schema owner, then the schema owner automatically becomes the owner and the object creator is granted all relevant privileges on the object with grant option.

For example:

create schema abc authorization user1;
connect as user2 (who has been granted the "CREATE" privilege)
create table abc.t1 (...);
Two privilege records exist:
- the system grants to user1 (as the owner)
- user1 grants to user2 (as the table creator).

For views, this is not working correctly.
Given the example above, when a view is created by user2, only one privilege record is created:
- the system grantor to user2 with grant option

There should actually be two privilege records:
- the system granting all relevant privileges to user1
- user1 granting all relevant privileges to user2.

When testing, a second issue occurred.  the code that gathers the privilege record does not always consider column privileges.

For example:

user1 grants:
   select at the object level to user3
   updated for a list of column to user3

If a view  is created by users, it should show that user3 has update privilege if the view only contains columns that have update. For example:

user1:

create table t2 (a int, b int, c int);
grant select on t2 to user3;
grant update (a,b) to user3;

user3:

create view v2 as select a,b from t2;
Privilege record should show system granted select, update to v2 however, it is erroneously showing only select privilege.

alternatively, user3:

create view v3 as select a,c from t2;
Then the privilege record should only show select privilege.  user3 does not have update privilege on column c.


> LP Bug: 1437470 - Creator of view in private schema unable to select from view
> ------------------------------------------------------------------------------
>
>                 Key: TRAFODION-1100
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-1100
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-security
>            Reporter: Paul Low
>            Assignee: Roberta Marton
>            Priority: Critical
>             Fix For: 2.0-incubating
>
>
> Security is enabled on the instance.
> Table and view are created by user on a private schema where he is granted CREATE privilege.
> User is able to select from table, but unable to select from the view that he created.
> According to documentation, the creator of the object is granted full DML privileges on the object they create.
> SQL>select * from myview2;
> *** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.SCH001.MYVIEW2.
> See log of sequence below:
> Connected to Trafodion 
> SQL>create schema sch001;
> --- SQL operation complete.
> SQL>grant COMPONENT privilege "CREATE" on sql_operations to username1;
> --- SQL operation complete.
> SQL>connect username1/password1
> Connected to Trafodion 
> SQL>set schema sch001;
> --- SQL operation complete.
> SQL>create table mytable (a int, b int);
> --- SQL operation complete.
> SQL>insert into mytable values (1,1);
> --- 1 row(s) inserted.
> SQL>create view myview2 as select * from mytable;
> --- SQL operation complete.
> SQL>select * from mytable;
> A           B          
> ----------- -----------
>           1           1
> --- 1 row(s) selected.
> SQL>select * from myview2;
> *** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.SCH001.MYVIEW2.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)