[
https://issues.apache.org/jira/browse/TRAFODION-1761?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15120460#comment-15120460
]
Roberta Marton commented on TRAFODION-1761:
-------------------------------------------
The fix for this JIRA has been delivered with fixes for granting and revoking
object privileges. There still are problems with column privileges. JIRA
TRAFODION-1788 has been written to address column level privilege problems.
> Grant and Revoke on table with referencing views does not work according to
> ANSI
> --------------------------------------------------------------------------------
>
> Key: TRAFODION-1761
> URL: https://issues.apache.org/jira/browse/TRAFODION-1761
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-security
> Reporter: Roberta Marton
> Assignee: Roberta Marton
>
> Granting or revoking a 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 a privilege to a table, that privilege should be
> propagated to all referencing views where the grantor is the system user and
> the grantee is the view owner as follows:
> If the privilege is insert, update, or delete and the referencing view is
> updatable.
> 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:
> Example:
> -- run as sql_user1
> drop schema if exists user1_sch cascade;
> create schema user1_sch;
> set schema user1_sch;
> create table t1 (a int);
> grant select on t1 to sql_user2;
> create table t2(b int);
> grant select on t2 to sql_user2;
> showddl t1; -- user1 owns table, user2 has select priv
> showddl t2; -- user1 owns table, user2 has select priv
> -- Run as sql_user2
> drop schema if exists user2_sch cascade;
> create schema user2_sch;
> set schema user2_sch;
> create view v1 as select a from user1_sch.t1;
> create view v2 as select a, b from user1_sch.t1, user1_sch.t2;
> showddl v1; -- user2 granted select by system
> showddl v2; -- user2 granted select by system
> -- Run as sql_user1
> grant insert on user1_sch.t1 to sql_user2;
> showddl user1_sch.t1; -- user2 has both select and insert
> -- run as sql_user2
> showddl user2_sch.v1; -- v1 does inherit the insert privilege
> insert into v1 values (1); --succeeds
> -- BUG - as a side affect of the propagation, we are not allowing multiple
> grants directly on a view:
> create table xxx;
> create view v3 as select .... from xxx;
> grant select on v3 to abc; -- works
> grant insert on v3 to abc; -- silently fails
> showddl user2_sch.v2:-- v2 should not inherit the insert privilege because v2
> is not updatable but it does, however
> insert into v2 values (1,1) -- fails even though the view has insert priv
> *** ERROR[4027] Table or view TRAFODION.USER2_SCH.V2 does not permit
> insertions.
> -- BUG: today v2 is being assigned the insert privilege for non updatable
> views
> Revoke has a slightly different problem.
> -- run as sql_user1
> revoke insert on t1 from sql_user1;
> showddl t1; -- user2 no long has insert priv
> -- run as sql_user2
> showddl v1; -- v1 still has insert priv
> insert into v1 values (2); -- works!
> BUG, the revoke on insert on t1 should be propagated to view v1.
> showddl v2; -- v2 still has insert priv
> insert into v2 values (1,1): -- still fails because it is non-updatable.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
