[
https://issues.apache.org/jira/browse/TRAFODION-2538?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15927124#comment-15927124
]
ASF GitHub Bot commented on TRAFODION-2538:
-------------------------------------------
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.
> Revoking privileges from role not invoking query invalidation
> -------------------------------------------------------------
>
> Key: TRAFODION-2538
> URL: https://issues.apache.org/jira/browse/TRAFODION-2538
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp, sql-security
> Reporter: Roberta Marton
> Assignee: Roberta Marton
>
> Privilege information is cached. When a revoke is performed, query
> invalidation occurs. Query invalidation sends the revoke operation to RMS
> and each executor process checks for keys. If the key affect cache, the
> cache entry is refreshed.
> Query invalidation keys are not be created for revoke privileges from roles.
> Create a table
> create a role
> grant select, insert on table to role;
> grant role to user1.
> as user1, select and insert into table
> in another session, revoke insert from role
> user1 should no longer be able to insert
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
