[jira] [Created] (TRAFODION-636) LP Bug: 1370739 - Revoke role restrict no working correctly

["Alice Chen (JIRA)" <ji...@apache.org>]

Alice Chen created TRAFODION-636:
------------------------------------

             Summary: LP Bug: 1370739 - Revoke role restrict no working correctly
                 Key: TRAFODION-636
                 URL: https://issues.apache.org/jira/browse/TRAFODION-636
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-security
            Reporter: Roberta Marton
            Assignee: Cliff Gray
            Priority: Critical


Couple of issues:

Roles are not being revoked correctly:

SQL>showddl role RER_role1;


CREATE ROLE "RER_ROLE1";
  -- GRANT ROLE "RER_ROLE1" TO "DB__ROOT" WITH ADMIN OPTION; 
GRANT ROLE  "RER_ROLE1" TO "QAUSER_SQLQAA";

--- SQL operation complete.

SQL>revoke role RER_role1 from qauser_sqlqaa;

--- SQL operation complete.

SQL>showddl role RER_role1;


CREATE ROLE "RER_ROLE1";
  -- GRANT ROLE "RER_ROLE1" TO "DB__ROOT" WITH ADMIN OPTION; 
GRANT ROLE   "RER_ROLE1" TO "QAUSER_SQLQAA"; <-- this should have been revoked.

--- SQL operation complete.

Roles are being revoked when dependent object exist:

role football_players has been granted to user1:

showddl role football_players:

CREATE ROLE "FOOTBALL_PLAYERS";
  -- GRANT ROLE "FOOTBALL_PLAYERS" TO "DB__ROOT" WITH ADMIN OPTION;
GRANT ROLE
  "FOOTBALL_PLAYERS" TO "SQL_USER1";

--- SQL operation complete.


Role football_players has been granted select privilege:

showddl employee;

CREATE TABLE TRAFODION.SCH.EMPLOYEE
  (
    EMPNUM                           DECIMAL(4, 0) UNSIGNED NO DEFAULT NOT NULL 
  , EMPNAME                          CHAR(18)  DEFAULT NO DEFAULT NOT NULL SERIALIZED
  , REGNUM                           DECIMAL(2, 0) UNSIGNED NO DEFAULT NOT NULL
  , BRANCHNUM                        DECIMAL(2, 0) UNSIGNED NO DEFAULT NOT NULL 
  , JOB                              VARCHAR(12)    DEFAULT NO DEFAULT NOT NULL SERIALIZED
  , AGE                              NUMERIC(2, 0) UNSIGNED NO DEFAULT NOT NULL SERIALIZED
  , SALARY                           NUMERIC(6, 0) UNSIGNED NO DEFAULT NOT NULL SERIALIZED
  , VACATION                         NUMERIC(2, 0) UNSIGNED NO DEFAULT NOT NULL SERIALIZED
  , PRIMARY KEY (EMPNUM ASC)
  )
;
-- GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON TRAFODION."SCH"."EMPLOYEE" TO DB__ROOT WITH GRANT OPTION;
  GRANT SELECT ON TRAFODION.SCH.EMPLOYEE TO FOOTBALL_PLAYERS;

--- SQL operation complete.

User1 creates a view on table because of the granted SELECT privilege:

CREATE VIEW TRAFODION.SCH.EMPLOYEE_VIEW AS
  SELECT TRAFODION.SCH.EMPLOYEE.EMPNUM, TRAFODION.SCH.EMPLOYEE.EMPNAME,
    TRAFODION.SCH.EMPLOYEE.REGNUM, TRAFODION.SCH.EMPLOYEE.BRANCHNUM,
    TRAFODION.SCH.EMPLOYEE.JOB, TRAFODION.SCH.EMPLOYEE.AGE,
    TRAFODION.SCH.EMPLOYEE.SALARY, TRAFODION.SCH.EMPLOYEE.VACATION FROM
    TRAFODION.SCH.EMPLOYEE ;

-- GRANT SELECT ON TRAFODION.SCH.EMPLOYEE_VIEW TO SQL_USER1;

Now the role admin revokes the role from football_players RESTRICT.  Should fail beause of sql_user1's employee_view.

>>revoke role football_players from sql_user1;

--- SQL operation complete.
>>showddl role football_players;
..

CREATE ROLE "FOOTBALL_PLAYERS";
  -- GRANT ROLE "FOOTBALL_PLAYERS" TO "DB__ROOT" WITH ADMIN OPTION;



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