Github user DaveBirdsall commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/1010#discussion_r106301808
--- Diff: core/sql/regress/privs1/EXPECTED120 ---
@@ -746,6 +748,109 @@ TEAM_NUMBER TEAM_NAME
--- 5 row(s) selected.
>>
+>>-- revoke insert, delete privilege from t120role2
+>>sh sqlci -i "TEST120(revoke_t120role2p)" -u sql_user3;
+>>values (current_user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER3
+
+--- 1 row(s) selected.
+>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+
+--- SQL operation complete.
+>>cqd AUTO_QUERY_RETRY_WARNINGS 'ON';
+
+--- SQL operation complete.
+>>set schema t120sch;
+
+--- SQL operation complete.
+>>
+>>revoke insert, delete on teams from t120role2;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>-- still have privilege
+>>execute select_teams;
+
+TEAM_NUMBER TEAM_NAME
+----------- --------------------
+
+ 1 White Socks
+ 2 Giants
+ 3 Cardinals
+ 4 Indians
+ 5 Tigers
+
+--- 5 row(s) selected.
+>>-- no longer has privilege (4481) and query attempted recompilation
+>>execute insert_teams;
+
+*** ERROR[4481] The user does not have INSERT privilege on table or view
TRAFODION.T120SCH.TEAMS.
+
+*** ERROR[8822] The statement was not prepared.
+
+*** WARNING[8597] Statement was automatically retried 1 time(s). Delay
before each retry was 0 seconds. See next entry for the error that caused this
retry.
+
+*** WARNING[8734] Statement must be recompiled to allow privileges to be
re-evaluated.
+
+--- 0 row(s) inserted.
+>>
+>>-- grant privilege back
+>>sh sqlci -i "TEST120(grant_t120role2p)" -u sql_user3;
+>>values (current_user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER3
+
+--- 1 row(s) selected.
+>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+
+--- SQL operation complete.
+>>cqd AUTO_QUERY_RETRY_WARNINGS 'ON';
+
+--- SQL operation complete.
+>>set schema t120sch;
+
+--- SQL operation complete.
+>>
+>>grant insert, delete on teams to t120role2;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>execute select_teams;
+
+TEAM_NUMBER TEAM_NAME
+----------- --------------------
+
+ 1 White Socks
+ 2 Giants
+ 3 Cardinals
+ 4 Indians
+ 5 Tigers
+
+--- 5 row(s) selected.
+>>-- now works and query recompiled (8597)
+>>execute insert_teams;
+
+*** WARNING[8597] Statement was automatically retried 1 time(s). Delay
before each retry was 0 seconds. See next entry for the error that caused this
retry.
+
+*** WARNING[8583] This statement contains no generated plan to execute at
runtime. An error during query compilation caused this condition.
--- End diff --
A strange warning. Seems something like "plan was recompiled due to
authorization setting changes" might be more appropriate.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at [email protected] or file a JIRA ticket
with INFRA.
---
