[jira] [Commented] (TRAFODION-2279) revoke all doesn't revoke column privileges

["Roberta Marton (JIRA)" <ji...@apache.org>]

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

Roberta Marton commented on TRAFODION-2279:
-------------------------------------------

According to ANSI SQL, when ALL is specified, all privileges that the grantor has WGO for should be granted.  So, alternatively, when revoking privileges, ALL privileges that the grantor has granted, should be revoked.

The code is implemented differently.  When ALL is specified at grant time, then all object level privileges that the grantor has WGO for are granted.  At revoke time, only object level privileges are then removed.  Therefore, the code is working as implemented but not as ANSI has defined.

We can either leave the code as is, or change it to meet ANSI standards.  Today, the SQL reference manual has not been updated to include column level privileges (TRAFODION-2156).  If we decide to leave it as it is implemented, then the documentation should reflect this behavior.

> revoke all doesn't revoke column privileges
> -------------------------------------------
>
>                 Key: TRAFODION-2279
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-2279
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-security
>         Environment: centos6.7
> cdh5.4.8
>            Reporter: Gao, Rui-Xian
>            Assignee: Roberta Marton
>
> 'revoke all' doesn't revoke column privileges.
> >>create table tabl1(a int, b int);
> --- SQL operation complete.
> >>grant select(a) on tabl1 to qauser1;
> --- SQL operation complete.
> >>grant delete on tabl1 to qauser1;
> --- SQL operation complete.
> >>showddl tabl1;
> CREATE TABLE TRAFODION.RACHEL_SCH.TABL1
>   (
>     A                                INT DEFAULT NULL NOT SERIALIZED
>   , B                                INT DEFAULT NULL NOT SERIALIZED
>   )
>  ATTRIBUTES ALIGNED FORMAT
> ;
> -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.RACHEL_SCH.TABL1 TO DB__ROOT WITH GRANT OPTION;
>   GRANT DELETE ON TRAFODION.RACHEL_SCH.TABL1 TO QAUSER1;
> GRANT SELECT(A) ON
>   TRAFODION.RACHEL_SCH.TABL1 TO QAUSER1;
> --- SQL operation complete.
> >>revoke all on tabl1 from qauser1;
> --- SQL operation complete.
> >>showddl tabl1;
> CREATE TABLE TRAFODION.RACHEL_SCH.TABL1
>   (
>     A                                INT DEFAULT NULL NOT SERIALIZED
>   , B                                INT DEFAULT NULL NOT SERIALIZED
>   )
>  ATTRIBUTES ALIGNED FORMAT
> ;
> -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.RACHEL_SCH.TABL1 TO DB__ROOT WITH GRANT OPTION;
>   GRANT SELECT(A) ON TRAFODION.RACHEL_SCH.TABL1 TO QAUSER1;
> --- SQL operation complete.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)