Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
Description
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.