[jira] [Commented] (TRAFODION-2600) Unable to create view (lack of SELECT privilege) but user has SELECT privilege

["ASF GitHub Bot (JIRA)" <ji...@apache.org>]

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

ASF GitHub Bot commented on TRAFODION-2600:
-------------------------------------------

GitHub user robertamarton opened a pull request:

    https://github.com/apache/trafodion/pull/1520

    [TRAFODION-2600] Unable to create view ... but user has SELECT privilege

    Query invalidation is not resetting the role list when a user is granted a role.
    For DML operations, we always retry the request once, and between retries, the
    role list is reset.  So DML works on a retry.
    However, DDL operations are not retried, so the role list is not reset and the
    create view fails.
    
    An analogous issue exists when the role is revoked from a user and the role
    list is not reset.  In this case, the user can still create views even though
    they no longer have the privilege.
    
    Changes:
    - Grant role: sends a new query invalidation key
    - Revoke role: forces a query invalidation check even if the key is not present
    - Displays query invalidation keys when debug option DBUSER_DEBUG is set, e.g:
       set envvar DBUSER_DEBUG 1;

You can merge this pull request into a Git repository by running:

    $ git pull https://github.com/robertamarton/incubator-trafodion jira-2600

Alternatively you can review and apply these changes as the patch at:

    https://github.com/apache/trafodion/pull/1520.patch

To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:

    This closes #1520
    
----
commit f9820b26144a45b7c7cbdedaeefc832f150f5d45
Author: Roberta Marton <ro...@...>
Date:   2018-04-16T22:26:07Z

    [TRAFODION-2600] Unable to create view ... but user has SELECT privilege
    
    Query invalidation is not resetting the role list when a user is granted a role.
    For DML operations, we always retry the request once, and between retries, the
    role list is reset.  So DML works on a retry.
    However, DDL operations are not retried, so the role list is not reset and the
    create view fails.
    
    An analogous issue exists when the role is revoked from a user and the role
    list is not reset.  In this case, the user can still create views even though
    they no longer have the privilege.
    
    Changes:
    - Grant role: sends a new query invalidation key
    - Revoke role: forces a query invalidation check even if the key is not present
    - Displays query invalidation keys when debug option DBUSER_DEBUG is set, e.g:
       set envvar DBUSER_DEBUG 1;

----


> Unable to create view (lack of SELECT privilege) but user has SELECT privilege
> ------------------------------------------------------------------------------
>
>                 Key: TRAFODION-2600
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-2600
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-security
>            Reporter: Roberta Marton
>            Assignee: Roberta Marton
>            Priority: Major
>
> User unable to create a view because of missing SELECT privilege but user has been granted privilege through a role.
> admin 
>  - creates role1;
>  - creates table1 - grant select on table1 to role1
> user1
>  - tries to create a view - unable because of no SELECT priv
> admin 
>  - grants role1 to user1
> User1
>  - still unable to create view
>  - if user1 selects from table1 - it succeeds and a subsequent create view works.
> When a user connects, its list of active roles are stored in cache. 
> If the user is subsequently granted new roles, the in memory list should be updated.  For DML requests, the in memory list is updated.  For DDL requests, the in memory list is not updated.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)