Repository: incubator-trafodion
Updated Branches:
  refs/heads/master 437108f4f -> 8805eb26c


[TRAFODION-1758]: A user has dbroot role can't grant component privilege

Privilege checks were not handling role checks correctly.
PrivMgrComponentPrivileges::hasWGO is now checking privileges against roles.
privs1/TEST137 was updated to test role privileges
priv1/TEST120  was added to test query invalidation with roles
               (forgot to add it for a previous check in)


Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: 
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/1c938575
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/1c938575
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/1c938575

Branch: refs/heads/master
Commit: 1c93857502acd9ef9e14154df6a6e397284acc81
Parents: b12b45e
Author: Roberta Marton <rmarton@edev07.esgyn.local>
Authored: Mon Oct 17 20:30:59 2016 +0000
Committer: Roberta Marton <rmarton@edev07.esgyn.local>
Committed: Mon Oct 17 20:30:59 2016 +0000

----------------------------------------------------------------------
 core/sql/parser/sqlparser.y                     |   11 -
 core/sql/regress/privs1/EXPECTED120             | 1222 ++++++++++++++++++
 core/sql/regress/privs1/EXPECTED137             |  216 +++-
 core/sql/regress/privs1/TEST120                 |  379 ++++++
 core/sql/regress/privs1/TEST137                 |   53 +-
 core/sql/sqlcomp/PrivMgrComponentPrivileges.cpp |   76 +-
 6 files changed, 1910 insertions(+), 47 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1c938575/core/sql/parser/sqlparser.y
----------------------------------------------------------------------
diff --git a/core/sql/parser/sqlparser.y b/core/sql/parser/sqlparser.y
index b498108..8bd9a0b 100755
--- a/core/sql/parser/sqlparser.y
+++ b/core/sql/parser/sqlparser.y
@@ -27691,13 +27691,6 @@ revoke_role_statement : TOK_REVOKE 
optional_with_admin_option
                                    optional_drop_behavior
                                    optional_granted_by
                         {
-                          // revoke_role_statement ::= TOK_REVOKE 
optional_with_admin_option TOK_ROLE authorization_identifier_list TOK_FROM 
grantee_list optional_granted_by
-
-                          //if ($2 /* optional_with_admin_option */ == TRUE)
-                          //{
-                          //  YYERROR;
-                          //}
-
                           $$ = new (PARSERHEAP())
                             StmtDDLRoleGrant(
                                 $4 , /* authorization_identifier_list - role 
list */
@@ -27855,10 +27848,6 @@ grant_role_statement : TOK_GRANT TOK_ROLE 
authorization_identifier_list TOK_TO g
                                  optional_with_admin_option
                                  optional_granted_by
                        {
-                          //if($6 /* optional_with_admin_option */ == TRUE) // 
"with admin option" specified
-                         //{
-                          //    YYERROR;
-                          //}
                           $$ = new (PARSERHEAP())
                               StmtDDLRoleGrant(
                                 $3 , /*role list*/ 

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1c938575/core/sql/regress/privs1/EXPECTED120
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs1/EXPECTED120 
b/core/sql/regress/privs1/EXPECTED120
new file mode 100644
index 0000000..d60f4f5
--- /dev/null
+++ b/core/sql/regress/privs1/EXPECTED120
@@ -0,0 +1,1222 @@
+>>obey TEST120(tests);
+>>-- =================================================================
+>>-- Design:
+>>--    t120role1 - owns schema t120sch 
+>>--    t120role2 - contains grants against teams and games
+>>--    t120role3 - contains grants against teams, games, and standings
+>>--    t120role4 - control, has no privs granted, make sure revoking
+>>--                role does not cause recompilations
+>>--
+>>--    sql_user3 - is schema administrator for schema t120sch
+>>--    sql_user6 - is granted and revoked privileges directly and
+>>--                through t120role2 and t120role3
+>>--    sql_user9 - control, makes sure revokes from roles does not
+>>--                affect sql_user9's compiled queries
+>>--
+>>--    games     - multiple roles giving same privileges
+>>--    teams     - multiple privileges through different roles
+>>--    players   - control, not roles involved in privileges
+>>--    standings - used to test sequence privileges and revoke role
+>>--    stats     - tests revoke PUBLIC authorization ID
+>>-- =================================================================
+>>obey TEST120(create_db);
+>>create role t120role1;
+
+--- SQL operation complete.
+>>create role t120role2;
+
+--- SQL operation complete.
+>>create role t120role3;
+
+--- SQL operation complete.
+>>create role t120role4;
+
+--- SQL operation complete.
+>>grant role t120role1, t120role2, t120role3, t120role4 to sql_user3 with 
admin option;
+
+--- SQL operation complete.
+>>
+>>create schema t120sch authorization t120role1;
+
+--- SQL operation complete.
+>>set schema t120sch;
+
+--- SQL operation complete.
+>>create table teams
++>  (team_number int not null primary key,
++>   team_name char(20) not null,
++>   team_contact varchar(50) not null,
++>   team_contact_number char (10) not null
++>   )
++>  ;
+
+--- SQL operation complete.
+>>
+>>alter table teams add constraint valid_team_no check (team_number > 0);
+
+--- SQL operation complete.
+>>insert into teams values
++>   (1, 'White Socks', 'Sam','4082282222'),
++>   (2, 'Giants', 'Joe', '5102839483'),
++>   (3, 'Cardinals', 'Stella', '9513849384'),
++>   (4, 'Indians', 'Matt', '5128383748'),
++>   (5, 'Tigers', 'Ronit', '6198273827');
+
+--- 5 row(s) inserted.
+>>
+>>
+>>create table games
++>   ( home_team_number int not null,
++>     visitor_team_number int not null,
++>     game_number int not null primary key,
++>     game_time timestamp not null,
++>     game_location varchar(50) not null)
++>  ;
+
+--- SQL operation complete.
+>>
+>>alter table games add constraint valid_game_number check (game_number > 0);
+
+--- SQL operation complete.
+>>insert into games values
++>   (1, 2, 1, timestamp '2009-04-23 19:30:00', 'California'),
++>   (1, 3, 2, timestamp '2009-04-24 19:30:00', 'California'),
++>   (1, 4, 3, timestamp '2009-04-25 10:00:00', 'Oklahoma'),
++>   (2, 3, 4, timestamp '2009-04-25 13:30:00', 'Michigan'),
++>   (1, 5, 5, timestamp '2009-04-25 15:00:00', 'Oklahoma'),
++>   (2, 5, 6, timestamp '2009-04-27 17:00:00', 'New York'),
++>   (3, 4, 7, timestamp '2009-04-28 17:00:00', 'Florida'),
++>   (4, 2, 8, current_timestamp, 'Missouri');
+
+--- 8 row(s) inserted.
+>>
+>>create table players
++>  (player_number int not null,
++>   player_name varchar (50) not null,
++>   player_team_number int not null,
++>   player_phone_number char (10) not null,
++>   player_details varchar(50),
++>   primary key (player_number, player_team_number))
++>  no partition;
+
+--- SQL operation complete.
+>>
+>>alter table players add constraint valid_player_number check(player_number > 
0);
+
+--- SQL operation complete.
+>>
+>>insert into players values
++>   (1, 'Tom', 1, '4083948394', null),
++>   (2, 'Bob', 1, '4089483948', null),
++>   (3, 'Toby',1, '4082938493', 'pitcher'),
++>   (3, 'Toby',2, '4082938493', null),
++>   (4, 'Julie', 2, '5108394839', 'catcher'),
++>   (5, 'Joanne', 2, '5103849384', null),
++>   (6, 'Pete', 2, '5102839483', null),
++>   (6, 'Pete', 3, '5102839483', 'third base'),
++>   (7, 'Jared',4, '9518293849', 'short stop'),
++>   (8, 'Zachary', 4, '9518293840', null),
++>   (9, 'Lynne', 5, '9518293892', 'pitcher'),
++>   (10, 'Omar', 5, '5128394893', null);
+
+--- 12 row(s) inserted.
+>>
+>>create table standings
++>    (team_number int not null primary key,
++>     wins int default 0,
++>     loses int default 0,
++>     last_updated timestamp default current_timestamp)
++>;
+
+--- SQL operation complete.
+>>
+>>insert into standings (team_number)
++>  select team_number from teams;
+
+--- 5 row(s) inserted.
+>>
+>>create sequence team_seq;
+
+--- SQL operation complete.
+>>
+>>create table stats
++>  (team_number int not null primary key,
++>   num_players int not null)
++>;
+
+--- SQL operation complete.
+>>
+>>insert into stats
++>  select team_number, count (player_number)
++>   from teams t, players p
++>   where t.team_number = p.player_team_number
++>   group by team_number;
+
+--- 5 row(s) inserted.
+>>
+>>obey TEST120(queries);
+>>select game_number from games;
+
+GAME_NUMBER
+-----------
+
+          1
+          2
+          3
+          4
+          5
+          6
+          7
+          8
+
+--- 8 row(s) selected.
+>>select team_number from teams;
+
+TEAM_NUMBER
+-----------
+
+          1
+          2
+          3
+          4
+          5
+
+--- 5 row(s) selected.
+>>select player_number from players;
+
+PLAYER_NUMBER
+-------------
+
+            1
+            2
+            3
+            3
+            4
+            5
+            6
+            6
+            7
+            8
+            9
+           10
+
+--- 12 row(s) selected.
+>>select team_number, seqnum(team_seq) from standings;
+
+TEAM_NUMBER  (EXPR)              
+-----------  --------------------
+
+          1                     1
+          2                     2
+          3                     3
+          4                     4
+          5                     5
+
+--- 5 row(s) selected.
+>>select team_number, num_players from stats;
+
+TEAM_NUMBER  NUM_PLAYERS
+-----------  -----------
+
+          1            3
+          2            4
+          3            1
+          4            2
+          5            2
+
+--- 5 row(s) selected.
+>>
+>>
+>>sh sqlci -i "TEST120(runqueries)" -u sql_user6;
+>>values (current_user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER6                                                                      
                                                  
+
+--- 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.
+>>
+>>-- At this time sql_user6 has no privileges
+>>-- Expect error 4481
+>>-- Prepare a bunch of queries, all fail with no privilege
+>>prepare select_games from select game_number from games;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view 
TRAFODION.T120SCH.GAMES.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare select_teams from select team_number, team_name from teams;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view 
TRAFODION.T120SCH.TEAMS.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare insert_teams from 
++>  insert into teams values (6, 'Braves', 'Jim', '8653845150');
+
+*** ERROR[4481] The user does not have INSERT privilege on table or view 
TRAFODION.T120SCH.TEAMS.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare update_teams from
++>  update teams set team_contact_number = '8653855150' where team_number = 6;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view 
TRAFODION.T120SCH.TEAMS.
+
+*** ERROR[4481] The user does not have UPDATE privilege on table or view 
TRAFODION.T120SCH.TEAMS.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare select_players from select count(*) from players;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view 
TRAFODION.T120SCH.PLAYERS.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare select_standings from select team_number, seqnum(team_seq) from 
standings;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view 
TRAFODION.T120SCH.STANDINGS.
+
+*** ERROR[4491] The user does not have USAGE privilege on sequence 
TRAFODION.T120SCH.TEAM_SEQ.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>prepare select_stats from select team_number, num_players from stats;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view 
TRAFODION.T120SCH.STATS.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>-- sql_user9 also has no privileges
+>>sh sqlci -i "TEST120(select_queries)" -u sql_user9;
+>>values (current_user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER9                                                                      
                                                  
+
+--- 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.
+>>select game_number from games;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view 
TRAFODION.T120SCH.GAMES.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- grant privileges
+>>sh sqlci -i "TEST120(grant_all_privs)" -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, update(team_contact, team_contact_number) on teams to 
t120role2;
+
+--- SQL operation complete.
+>>grant select on teams to sql_user6;
+
+--- SQL operation complete.
+>>grant select (game_number, game_time) on games to t120role2;
+
+--- SQL operation complete.
+>>grant select (game_number, game_time) on games to t120role3;
+
+--- SQL operation complete.
+>>grant all on players to sql_user6;
+
+--- SQL operation complete.
+>>grant role t120role2, t120role3, t120role4 to sql_user6;
+
+--- SQL operation complete.
+>>grant role t120role3 to sql_user9;
+
+--- SQL operation complete.
+>>grant select on standings to sql_user6;
+
+--- SQL operation complete.
+>>grant usage on sequence team_seq to t120role3;
+
+--- SQL operation complete.
+>>grant select (team_number, num_players) on stats to "PUBLIC";
+
+--- SQL operation complete.
+>>showddl games;
+
+CREATE TABLE TRAFODION.T120SCH.GAMES
+  (
+    HOME_TEAM_NUMBER                 INT NO DEFAULT NOT NULL NOT DROPPABLE
+  , VISITOR_TEAM_NUMBER              INT NO DEFAULT NOT NULL NOT DROPPABLE
+  , GAME_NUMBER                      INT NO DEFAULT NOT NULL NOT DROPPABLE
+  , GAME_TIME                        TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+      DROPPABLE
+  , GAME_LOCATION                    VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , PRIMARY KEY (GAME_NUMBER ASC)
+  )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+ALTER TABLE TRAFODION.T120SCH.GAMES ADD CONSTRAINT
+  TRAFODION.T120SCH.VALID_GAME_NUMBER CHECK
+  (TRAFODION.T120SCH.GAMES.GAME_NUMBER > 0)
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T120SCH.GAMES 
TO T120ROLE1 WITH GRANT OPTION;
+  GRANT SELECT(GAME_NUMBER, GAME_TIME) ON TRAFODION.T120SCH.GAMES TO T120ROLE2
+  GRANTED BY SQL_USER3;
+GRANT SELECT(GAME_NUMBER, GAME_TIME) ON
+  TRAFODION.T120SCH.GAMES TO T120ROLE3 GRANTED BY SQL_USER3;
+
+--- SQL operation complete.
+>>showddl teams;
+
+CREATE TABLE TRAFODION.T120SCH.TEAMS
+  (
+    TEAM_NUMBER                      INT NO DEFAULT NOT NULL NOT DROPPABLE
+  , TEAM_NAME                        CHAR(20) CHARACTER SET ISO88591 COLLATE
+      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , TEAM_CONTACT                     VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , TEAM_CONTACT_NUMBER              CHAR(10) CHARACTER SET ISO88591 COLLATE
+      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , PRIMARY KEY (TEAM_NUMBER ASC)
+  )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+ALTER TABLE TRAFODION.T120SCH.TEAMS ADD CONSTRAINT
+  TRAFODION.T120SCH.VALID_TEAM_NO CHECK (TRAFODION.T120SCH.TEAMS.TEAM_NUMBER >
+  0)
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T120SCH.TEAMS 
TO T120ROLE1 WITH GRANT OPTION;
+  GRANT SELECT ON TRAFODION.T120SCH.TEAMS TO SQL_USER6 GRANTED BY SQL_USER3;
+  GRANT INSERT, DELETE ON TRAFODION.T120SCH.TEAMS TO T120ROLE2 GRANTED BY
+  SQL_USER3;
+GRANT UPDATE(TEAM_CONTACT, TEAM_CONTACT_NUMBER) ON
+  TRAFODION.T120SCH.TEAMS TO T120ROLE2 GRANTED BY SQL_USER3;
+
+--- SQL operation complete.
+>>showddl players;
+
+CREATE TABLE TRAFODION.T120SCH.PLAYERS
+  (
+    PLAYER_NUMBER                    INT NO DEFAULT NOT NULL NOT DROPPABLE
+  , PLAYER_NAME                      VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , PLAYER_TEAM_NUMBER               INT NO DEFAULT NOT NULL NOT DROPPABLE
+  , PLAYER_PHONE_NUMBER              CHAR(10) CHARACTER SET ISO88591 COLLATE
+      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+  , PLAYER_DETAILS                   VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+      DEFAULT DEFAULT NULL
+  , PRIMARY KEY (PLAYER_NUMBER ASC, PLAYER_TEAM_NUMBER ASC)
+  )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+ALTER TABLE TRAFODION.T120SCH.PLAYERS ADD CONSTRAINT
+  TRAFODION.T120SCH.VALID_PLAYER_NUMBER CHECK
+  (TRAFODION.T120SCH.PLAYERS.PLAYER_NUMBER > 0)
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON 
TRAFODION.T120SCH.PLAYERS TO T120ROLE1 WITH GRANT OPTION;
+  GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T120SCH.PLAYERS
+  TO SQL_USER6 GRANTED BY SQL_USER3;
+
+--- SQL operation complete.
+>>showddl standings;
+
+CREATE TABLE TRAFODION.T120SCH.STANDINGS
+  (
+    TEAM_NUMBER                      INT NO DEFAULT NOT NULL NOT DROPPABLE
+  , WINS                             INT DEFAULT 0
+  , LOSES                            INT DEFAULT 0
+  , LAST_UPDATED                     TIMESTAMP(6) DEFAULT CURRENT
+  , PRIMARY KEY (TEAM_NUMBER ASC)
+  )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON 
TRAFODION.T120SCH.STANDINGS TO T120ROLE1 WITH GRANT OPTION;
+  GRANT SELECT ON TRAFODION.T120SCH.STANDINGS TO SQL_USER6 GRANTED BY
+  SQL_USER3;
+
+--- SQL operation complete.
+>>showddl sequence team_seq;
+
+CREATE SEQUENCE TRAFODION.T120SCH.TEAM_SEQ
+  START WITH 1 /* NEXT AVAILABLE VALUE 26 */
+  INCREMENT BY 1
+  MAXVALUE 9223372036854775806
+  MINVALUE 1
+  CACHE 25
+  NO CYCLE
+  LARGEINT
+;
+
+-- GRANT USAGE ON SEQUENCE TRAFODION.T120SCH.TEAM_SEQ TO T120ROLE1 WITH GRANT 
OPTION;
+  GRANT USAGE ON SEQUENCE TRAFODION.T120SCH.TEAM_SEQ TO T120ROLE3 GRANTED BY
+  SQL_USER3;
+
+--- SQL operation complete.
+>>showddl role t120role1;
+
+CREATE ROLE "T120ROLE1";
+  -- GRANT ROLE "T120ROLE1" TO "DB__ROOT" WITH ADMIN OPTION;
+GRANT ROLE
+  "T120ROLE1" TO "SQL_USER3" WITH ADMIN OPTION;
+
+--- SQL operation complete.
+>>showddl role t120role2;
+
+CREATE ROLE "T120ROLE2";
+  -- GRANT ROLE "T120ROLE2" TO "DB__ROOT" WITH ADMIN OPTION;
+GRANT ROLE
+  "T120ROLE2" TO "SQL_USER3" WITH ADMIN OPTION;
+GRANT ROLE "T120ROLE2" TO
+  "SQL_USER6" GRANTED BY "SQL_USER3";
+
+--- SQL operation complete.
+>>showddl role t120role3;
+
+CREATE ROLE "T120ROLE3";
+  -- GRANT ROLE "T120ROLE3" TO "DB__ROOT" WITH ADMIN OPTION;
+GRANT ROLE
+  "T120ROLE3" TO "SQL_USER3" WITH ADMIN OPTION;
+GRANT ROLE "T120ROLE3" TO
+  "SQL_USER6" GRANTED BY "SQL_USER3";
+GRANT ROLE "T120ROLE3" TO "SQL_USER9"
+  GRANTED BY "SQL_USER3";
+
+--- SQL operation complete.
+>>showddl role t120role4;
+
+CREATE ROLE "T120ROLE4";
+  -- GRANT ROLE "T120ROLE4" TO "DB__ROOT" WITH ADMIN OPTION;
+GRANT ROLE
+  "T120ROLE4" TO "SQL_USER3" WITH ADMIN OPTION;
+GRANT ROLE "T120ROLE4" TO
+  "SQL_USER6" GRANTED BY "SQL_USER3";
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- Prepare a bunch of queries, all successful
+>>prepare select_games from select game_number from games;
+
+--- SQL command prepared.
+>>prepare select_teams from select team_number, team_name from teams;
+
+--- SQL command prepared.
+>>prepare insert_teams from 
++>  insert into teams values (6, 'Braves', 'Jim', '8653845150');
+
+--- SQL command prepared.
+>>prepare update_teams from
++>  update teams set team_contact_number = '8653855150' where team_number = 6;
+
+--- SQL command prepared.
+>>prepare select_players from select count(*) from players;
+
+--- SQL command prepared.
+>>prepare select_standings from select team_number, seqnum(team_seq) from 
standings;
+
+--- SQL command prepared.
+>>
+>>-- Gather the query invalidation keys for each plan
+>>--   OI - object/column insert required
+>>--   OG - sequence usage required
+>>--   OS - object/column select required
+>>--   OU - object/column update required
+>>--   AR - role involved, check query plans that rely on roles during revoke
+>>log;
+Query_Invalidation_Keys explain output for select_games, select_teams, 
insert_teams, update_teams, select_players, select_standings: 
+Query_Invalidation_Keys{,,UR}
+Query_Invalidation_Keys{,,OS}
+Query_Invalidation_Keys{,,UR}
+Query_Invalidation_Keys{,,OS}{,,UR}
+Query_Invalidation_Keys{,,OS}
+Query_Invalidation_Keys{,,OS}{,,UR}
+>>
+>>-- Verify that sql_user9 can select from games
+>>sh sqlci -i "TEST120(select_queries)" -u sql_user9;
+>>values (current_user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER9                                                                      
                                                  
+
+--- 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.
+>>select game_number from games;
+
+GAME_NUMBER
+-----------
+
+          1
+          2
+          3
+          4
+          5
+          6
+          7
+          8
+
+--- 8 row(s) selected.
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- revoke t120role4 from sql_user6
+>>sh sqlci -i "TEST120(revoke_t120role4)" -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 role t120role4 from sql_user6;
+
+--- SQL operation complete.
+>>
+>>-- sql_user3 has privs on all tables, can execute and no recompile
+>>obey TEST120(queries);
+>>select game_number from games;
+
+GAME_NUMBER
+-----------
+
+          1
+          2
+          3
+          4
+          5
+          6
+          7
+          8
+
+--- 8 row(s) selected.
+>>select team_number from teams;
+
+TEAM_NUMBER
+-----------
+
+          1
+          2
+          3
+          4
+          5
+
+--- 5 row(s) selected.
+>>select player_number from players;
+
+PLAYER_NUMBER
+-------------
+
+            1
+            2
+            3
+            3
+            4
+            5
+            6
+            6
+            7
+            8
+            9
+           10
+
+--- 12 row(s) selected.
+>>select team_number, seqnum(team_seq) from standings;
+
+TEAM_NUMBER  (EXPR)              
+-----------  --------------------
+
+          1                    26
+          2                    27
+          3                    28
+          4                    29
+          5                    30
+
+--- 5 row(s) selected.
+>>select team_number, num_players from stats;
+
+TEAM_NUMBER  NUM_PLAYERS
+-----------  -----------
+
+          1            3
+          2            4
+          3            1
+          4            2
+          5            2
+
+--- 5 row(s) selected.
+>>
+>>
+>>exit;
+
+End of MXCI Session
+
+>>execute select_games;
+
+GAME_NUMBER
+-----------
+
+          1
+          2
+          3
+          4
+          5
+          6
+          7
+          8
+
+--- 8 row(s) selected.
+>>execute select_teams;
+
+TEAM_NUMBER  TEAM_NAME           
+-----------  --------------------
+
+          1  White Socks         
+          2  Giants              
+          3  Cardinals           
+          4  Indians             
+          5  Tigers              
+
+--- 5 row(s) selected.
+>>
+>>-- revoke t120role2 from sql_user6
+>>sh sqlci -i "TEST120(revoke_t120role2)" -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 role t120role2 from sql_user6;
+
+--- SQL operation complete.
+>>
+>>-- sql_user3 has privs on all tables, can execute and no recompile
+>>obey TEST120(queries);
+>>select game_number from games;
+
+GAME_NUMBER
+-----------
+
+          1
+          2
+          3
+          4
+          5
+          6
+          7
+          8
+
+--- 8 row(s) selected.
+>>select team_number from teams;
+
+TEAM_NUMBER
+-----------
+
+          1
+          2
+          3
+          4
+          5
+
+--- 5 row(s) selected.
+>>select player_number from players;
+
+PLAYER_NUMBER
+-------------
+
+            1
+            2
+            3
+            3
+            4
+            5
+            6
+            6
+            7
+            8
+            9
+           10
+
+--- 12 row(s) selected.
+>>select team_number, seqnum(team_seq) from standings;
+
+TEAM_NUMBER  (EXPR)              
+-----------  --------------------
+
+          1                    51
+          2                    52
+          3                    53
+          4                    54
+          5                    55
+
+--- 5 row(s) selected.
+>>select team_number, num_players from stats;
+
+TEAM_NUMBER  NUM_PLAYERS
+-----------  -----------
+
+          1            3
+          2            4
+          3            1
+          4            2
+          5            2
+
+--- 5 row(s) selected.
+>>
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- still have privs but query recompiled (8597)
+>>execute select_games;
+
+*** 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.
+
+GAME_NUMBER
+-----------
+
+          1
+          2
+          3
+          4
+          5
+          6
+          7
+          8
+
+--- 8 row(s) selected.
+>>execute select_teams;
+
+TEAM_NUMBER  TEAM_NAME           
+-----------  --------------------
+
+          1  White Socks         
+          2  Giants              
+          3  Cardinals           
+          4  Indians             
+          5  Tigers              
+
+--- 5 row(s) selected.
+>>execute select_standings;
+
+TEAM_NUMBER  (EXPR)              
+-----------  --------------------
+
+          1                    76
+          2                    77
+          3                    78
+          4                    79
+          5                    80
+
+--- 5 row(s) selected.
+>>
+>>-- still have privs, no query recompilation message because no invalidation
+>>-- key for roles exist
+>>execute select_players;
+
+(EXPR)              
+--------------------
+
+                  12
+
+--- 1 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.
+>>execute update_teams;
+
+*** ERROR[4481] The user does not have UPDATE 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) updated.
+>>
+>>-- revoke role t120role3 from sql_user6
+>>sh sqlci -i "TEST120(revoke_t120role3)" -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 role t120role3 from sql_user6;
+
+--- SQL operation complete.
+>>
+>>-- sql_user3 has privs on all tables, can execute and no recompile
+>>obey TEST120(queries);
+>>select game_number from games;
+
+GAME_NUMBER
+-----------
+
+          1
+          2
+          3
+          4
+          5
+          6
+          7
+          8
+
+--- 8 row(s) selected.
+>>select team_number from teams;
+
+TEAM_NUMBER
+-----------
+
+          1
+          2
+          3
+          4
+          5
+
+--- 5 row(s) selected.
+>>select player_number from players;
+
+PLAYER_NUMBER
+-------------
+
+            1
+            2
+            3
+            3
+            4
+            5
+            6
+            6
+            7
+            8
+            9
+           10
+
+--- 12 row(s) selected.
+>>select team_number, seqnum(team_seq) from standings;
+
+TEAM_NUMBER  (EXPR)              
+-----------  --------------------
+
+          1                   101
+          2                   102
+          3                   103
+          4                   104
+          5                   105
+
+--- 5 row(s) selected.
+>>select team_number, num_players from stats;
+
+TEAM_NUMBER  NUM_PLAYERS
+-----------  -----------
+
+          1            3
+          2            4
+          3            1
+          4            2
+          5            2
+
+--- 5 row(s) selected.
+>>
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- no longer has privilege (4481) on select_games, 
+>>-- but has privilege on select_teams and select_players
+>>execute select_games;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view 
TRAFODION.T120SCH.GAMES.
+
+*** 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) selected.
+>>execute select_teams;
+
+TEAM_NUMBER  TEAM_NAME           
+-----------  --------------------
+
+          1  White Socks         
+          2  Giants              
+          3  Cardinals           
+          4  Indians             
+          5  Tigers              
+
+--- 5 row(s) selected.
+>>execute select_players;
+
+(EXPR)              
+--------------------
+
+                  12
+
+--- 1 row(s) selected.
+>>
+>>-- user has select but not usage
+>>execute select_standings;
+
+*** ERROR[4491] The user does not have USAGE privilege on sequence 
TRAFODION.T120SCH.TEAM_SEQ.
+
+*** 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) selected.
+>>
+>>-- Verify sql_user9 can still select from games and no recompilation msg
+>>sh sqlci -i "TEST120(select_queries)" -u sql_user9;
+>>values (current_user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER9                                                                      
                                                  
+
+--- 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.
+>>select game_number from games;
+
+GAME_NUMBER
+-----------
+
+          1
+          2
+          3
+          4
+          5
+          6
+          7
+          8
+
+--- 8 row(s) selected.
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- tests for PUBLIC
+>>-- select priv on stats has been granted to public
+>>prepare select_stats from select team_number, num_players from stats;
+
+--- SQL command prepared.
+>>log;
+Query_Invalidation_Keys explain output for select_stats: 
+Query_Invalidation_Keys{,,UZ}
+>>shecho"Query_Invalidation_Keysexplainoutputforselect_stats:">>LOG;
+>>
+>>execute select_stats;
+
+TEAM_NUMBER  NUM_PLAYERS
+-----------  -----------
+
+          1            3
+          2            4
+          3            1
+          4            2
+          5            2
+
+--- 5 row(s) selected.
+>>
+>>-- revoke PUBLIC
+>>sh sqlci -i "TEST120(revoke_public)" -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 select (team_number, num_players) on stats from "PUBLIC";
+
+--- SQL operation complete.
+>>showddl stats;
+
+CREATE TABLE TRAFODION.T120SCH.STATS
+  (
+    TEAM_NUMBER                      INT NO DEFAULT NOT NULL NOT DROPPABLE
+  , NUM_PLAYERS                      INT NO DEFAULT NOT NULL NOT DROPPABLE
+  , PRIMARY KEY (TEAM_NUMBER ASC)
+  )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T120SCH.STATS 
TO T120ROLE1 WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>select team_number, num_players from stats;
+
+TEAM_NUMBER  NUM_PLAYERS
+-----------  -----------
+
+          1            3
+          2            4
+          3            1
+          4            2
+          5            2
+
+--- 5 row(s) selected.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>select * from stats;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view 
TRAFODION.T120SCH.STATS.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>execute select_stats;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view 
TRAFODION.T120SCH.STATS.
+
+*** 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) selected.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>log;

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1c938575/core/sql/regress/privs1/EXPECTED137
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs1/EXPECTED137 
b/core/sql/regress/privs1/EXPECTED137
index 5f9b471..03db49d 100755
--- a/core/sql/regress/privs1/EXPECTED137
+++ b/core/sql/regress/privs1/EXPECTED137
@@ -49,6 +49,25 @@
 --- SQL command prepared.
 >>
 >>obey TEST137(create_db);
+>>create role md_access;
+
+--- SQL operation complete.
+>>grant select on "_MD_".auths to md_access;
+
+--- SQL operation complete.
+>>grant select on "_PRIVMGR_MD_".components to md_access;
+
+--- SQL operation complete.
+>>grant select on "_PRIVMGR_MD_".component_operations to md_access;
+
+--- SQL operation complete.
+>>grant select on "_PRIVMGR_MD_".component_privileges to md_access;
+
+--- SQL operation complete.
+>>grant role md_access to sql_user1, sql_user4, sql_user5;
+
+--- SQL operation complete.
+>>
 >>create shared schema t137;
 
 --- SQL operation complete.
@@ -664,6 +683,14 @@ LIBRARY_BOOKS
 >>
 >>
 >>log;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER1                                                                      
                                                  
+
+--- 1 row(s) selected.
 >>-- These tests should succeed
 >>-- returns 2 rows
 >>showddl role db__rootrole;
@@ -674,8 +701,15 @@ CREATE ROLE "DB__ROOTROLE";
 --- SQL operation complete.
 >>execute get_component_operations;
 
-*** ERROR[15017] Statement GET_COMPONENT_OPERATIONS was not found.
+COMPONENT             OPERATION_NAME        OPERATION_CODE
+--------------------  --------------------  --------------
 
+LIBRARY_ACCOUNTS      LIB_MANAGE_OVERDUE    MO            
+LIBRARY_ACCOUNTS      LIB_VIEW_CHECKOUTS    VC            
+LIBRARY_BOOKS         LIB_MANAGE_CHECKOUTS  MC            
+LIBRARY_BOOKS         LIB_VIEW_CHECKOUTS    VC            
+
+--- 4 row(s) selected.
 >>grant component privilege lib_view_checkouts on library_books to sql_user4 
 +>   with grant option;
 
@@ -683,11 +717,30 @@ CREATE ROLE "DB__ROOTROLE";
 >>grant component privilege lib_manage_checkouts on library_books to sql_user5;
 
 --- SQL operation complete.
->>-- returns 17 rows
+>>-- returns 16 rows
 >>execute get_priv_desc;
 
-*** ERROR[15017] Statement GET_PRIV_DESC was not found.
+COMPONENT             OPERATION_NAME        GRANTEE_NAME                       
                                               GRANT_DEPTH
+--------------------  --------------------  
--------------------------------------------------------------------------------
  -----------
 
+LIBRARY_ACCOUNTS      LIB_MANAGE_OVERDUE    DB__ROOT                           
                                                        -1
+LIBRARY_ACCOUNTS      LIB_MANAGE_OVERDUE    LIBRARY_ADMIN                      
                                                         0
+LIBRARY_ACCOUNTS      LIB_MANAGE_OVERDUE    SQL_USER2                          
                                                        -1
+LIBRARY_ACCOUNTS      LIB_VIEW_CHECKOUTS    DB__ROOT                           
                                                        -1
+LIBRARY_ACCOUNTS      LIB_VIEW_CHECKOUTS    LIBRARY_ADMIN                      
                                                         0
+LIBRARY_ACCOUNTS      LIB_VIEW_CHECKOUTS    LIBRARY_CKOUT_CLERKS               
                                                         0
+LIBRARY_ACCOUNTS      LIB_VIEW_CHECKOUTS    SQL_USER2                          
                                                        -1
+LIBRARY_BOOKS         LIB_MANAGE_CHECKOUTS  DB__ROOT                           
                                                        -1
+LIBRARY_BOOKS         LIB_MANAGE_CHECKOUTS  LIBRARY_ADMIN                      
                                                         0
+LIBRARY_BOOKS         LIB_MANAGE_CHECKOUTS  SQL_USER1                          
                                                        -1
+LIBRARY_BOOKS         LIB_MANAGE_CHECKOUTS  SQL_USER5                          
                                                         0
+LIBRARY_BOOKS         LIB_VIEW_CHECKOUTS    DB__ROOT                           
                                                        -1
+LIBRARY_BOOKS         LIB_VIEW_CHECKOUTS    LIBRARY_ADMIN                      
                                                         0
+LIBRARY_BOOKS         LIB_VIEW_CHECKOUTS    LIBRARY_CKOUT_CLERKS               
                                                         0
+LIBRARY_BOOKS         LIB_VIEW_CHECKOUTS    SQL_USER1                          
                                                        -1
+LIBRARY_BOOKS         LIB_VIEW_CHECKOUTS    SQL_USER4                          
                                                        -1
+
+--- 16 row(s) selected.
 >>
 >>-- These tests should fail
 >>register component library_other;
@@ -705,14 +758,42 @@ CREATE ROLE "DB__ROOTROLE";
 >>--revoke component privilege lib_view_repository
 >>--   on library_books from sql_user6;
 >>log;
+>>values(user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER4                                                                      
                                                  
+
+--- 1 row(s) selected.
 >>-- these tests should succeed
 >>grant component privilege lib_view_checkouts on library_books to sql_user6;
 
 --- SQL operation complete.
 >>execute get_priv_desc;
 
-*** ERROR[15017] Statement GET_PRIV_DESC was not found.
+COMPONENT             OPERATION_NAME        GRANTEE_NAME                       
                                               GRANT_DEPTH
+--------------------  --------------------  
--------------------------------------------------------------------------------
  -----------
+
+LIBRARY_ACCOUNTS      LIB_MANAGE_OVERDUE    DB__ROOT                           
                                                        -1
+LIBRARY_ACCOUNTS      LIB_MANAGE_OVERDUE    LIBRARY_ADMIN                      
                                                         0
+LIBRARY_ACCOUNTS      LIB_MANAGE_OVERDUE    SQL_USER2                          
                                                        -1
+LIBRARY_ACCOUNTS      LIB_VIEW_CHECKOUTS    DB__ROOT                           
                                                        -1
+LIBRARY_ACCOUNTS      LIB_VIEW_CHECKOUTS    LIBRARY_ADMIN                      
                                                         0
+LIBRARY_ACCOUNTS      LIB_VIEW_CHECKOUTS    LIBRARY_CKOUT_CLERKS               
                                                         0
+LIBRARY_ACCOUNTS      LIB_VIEW_CHECKOUTS    SQL_USER2                          
                                                        -1
+LIBRARY_BOOKS         LIB_MANAGE_CHECKOUTS  DB__ROOT                           
                                                        -1
+LIBRARY_BOOKS         LIB_MANAGE_CHECKOUTS  LIBRARY_ADMIN                      
                                                         0
+LIBRARY_BOOKS         LIB_MANAGE_CHECKOUTS  SQL_USER1                          
                                                        -1
+LIBRARY_BOOKS         LIB_MANAGE_CHECKOUTS  SQL_USER5                          
                                                         0
+LIBRARY_BOOKS         LIB_VIEW_CHECKOUTS    DB__ROOT                           
                                                        -1
+LIBRARY_BOOKS         LIB_VIEW_CHECKOUTS    LIBRARY_ADMIN                      
                                                         0
+LIBRARY_BOOKS         LIB_VIEW_CHECKOUTS    LIBRARY_CKOUT_CLERKS               
                                                         0
+LIBRARY_BOOKS         LIB_VIEW_CHECKOUTS    SQL_USER1                          
                                                        -1
+LIBRARY_BOOKS         LIB_VIEW_CHECKOUTS    SQL_USER4                          
                                                        -1
+LIBRARY_BOOKS         LIB_VIEW_CHECKOUTS    SQL_USER6                          
                                                         0
 
+--- 17 row(s) selected.
 >>revoke component privilege lib_view_checkouts on library_books from 
 >>sql_user6;
 
 --- SQL operation complete.
@@ -724,13 +805,98 @@ CREATE ROLE "DB__ROOTROLE";
 
 --- SQL operation failed with errors.
 >>log;
->>-- these tests should fail
+>>-- test getting component privileges through roles
+>>obey TEST137(role_tests);
+>>-- tests grants and revoke on roles owned by user work correctly
+>>create role lib_role_test;
+
+--- SQL operation complete.
+>>
+>>-- user5_tests fail - user5 has no priv
+>>grant component privilege lib_view_checkouts on library_books to 
lib_role_test with grant option;
+
+--- SQL operation complete.
+>>sh sqlci -i "TEST137(user5_tests)" -u sql_user5;
+>>values(user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER5                                                                      
                                                  
+
+--- 1 row(s) selected.
 >>grant component privilege lib_view_repository on library_books to sql_user7;
 
 *** ERROR[1004] Object LIB_VIEW_REPOSITORY does not exist or object type is 
invalid for the current operation.
 
 --- SQL operation failed with errors.
+>>grant component privilege lib_view_checkouts on library_books to sql_user7;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
 >>log;
+>>
+>>-- user5_tests for priv lib_view_checkouts succeeds, have privs through role
+>>grant role lib_role_test to sql_user5;
+
+--- SQL operation complete.
+>>sh sqlci -i "TEST137(user5_tests)" -u sql_user5;
+>>values(user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER5                                                                      
                                                  
+
+--- 1 row(s) selected.
+>>grant component privilege lib_view_repository on library_books to sql_user7;
+
+*** ERROR[1004] Object LIB_VIEW_REPOSITORY does not exist or object type is 
invalid for the current operation.
+
+--- SQL operation failed with errors.
+>>grant component privilege lib_view_checkouts on library_books to sql_user7;
+
+--- SQL operation complete.
+>>log;
+>>revoke component privilege lib_view_checkouts on library_books from 
lib_role_test;
+
+--- SQL operation complete.
+>>revoke role lib_role_test from sql_user5;
+
+--- SQL operation complete.
+>>drop role lib_role_test;
+
+--- SQL operation complete.
+>>
+>>-- user5_tests for priv lib_view_checkouts succeeds, have privs through 
DB__ROOTROLE
+>>grant role db__rootrole to sql_user5;
+
+--- SQL operation complete.
+>>sh sqlci -i "TEST137(user5_tests)" -u sql_user5;
+>>values(user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER5                                                                      
                                                  
+
+--- 1 row(s) selected.
+>>grant component privilege lib_view_repository on library_books to sql_user7;
+
+*** ERROR[1004] Object LIB_VIEW_REPOSITORY does not exist or object type is 
invalid for the current operation.
+
+--- SQL operation failed with errors.
+>>grant component privilege lib_view_checkouts on library_books to sql_user7;
+
+--- SQL operation complete.
+>>log;
+>>revoke role db__rootrole from sql_user5;
+
+--- SQL operation complete.
+>>
+>>
+>>-- test the SQL_OPERATIONS privilege
 >>obey TEST137(operator_privs);
 >>-- This section tests operation privileges:
 >>-- Test environment
@@ -1787,6 +1953,46 @@ drop component privilege lib_view_benefactors cascade;
 
 --- SQL operation complete.
 >>
+>>revoke component privilege lib_view_checkouts on library_books from 
lib_role_test;
+
+*** ERROR[1008] Authorization identifier LIB_ROLE_TEST does not exist.
+
+--- SQL operation failed with errors.
+>>revoke role lib_role_test from sql_user5;
+
+*** ERROR[1338] Role LIB_ROLE_TEST is not defined in the database.
+
+--- SQL operation failed with errors.
+>>revoke role db__rootrole from sql_user5;
+
+*** ERROR[1018] Grant of role or privilege DB__ROOTROLE from DB__ROOT to 
SQL_USER5 not found, revoke request ignored.
+
+--- SQL operation failed with errors.
+>>drop role lib_role_test;
+
+*** ERROR[1338] Role LIB_ROLE_TEST is not defined in the database.
+
+--- SQL operation failed with errors.
+>>
+>>revoke role md_access from sql_user1, sql_user4, sql_user5;
+
+--- SQL operation complete.
+>>revoke select on "_MD_".auths from md_access;
+
+--- SQL operation complete.
+>>revoke select on "_PRIVMGR_MD_".components from md_access;
+
+--- SQL operation complete.
+>>revoke select on "_PRIVMGR_MD_".component_operations from md_access;
+
+--- SQL operation complete.
+>>revoke select on "_PRIVMGR_MD_".component_privileges from md_access;
+
+--- SQL operation complete.
+>>drop role md_access;
+
+--- SQL operation complete.
+>>
 >>
 >>-- run tests for revoke.
 >>obey TEST137(revoke_comp_privs_setup);

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1c938575/core/sql/regress/privs1/TEST120
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs1/TEST120 b/core/sql/regress/privs1/TEST120
new file mode 100755
index 0000000..45021eb
--- /dev/null
+++ b/core/sql/regress/privs1/TEST120
@@ -0,0 +1,379 @@
+-- ============================================================================
+-- TEST120 - tests revoke query invalidation
+--
+-- @@@ START COPYRIGHT @@@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements.  See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership.  The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License.  You may obtain a copy of the License at
+--
+--   http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied.  See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+--
+-- @@@ END COPYRIGHT @@@
+--
+-- Tests query invalidation during revoke role operations.
+-- Added in response to JIRA TRAFODION-2189
+-- ============================================================================
+
+cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+cqd AUTO_QUERY_RETRY_WARNINGS 'ON';
+
+obey TEST120(clean_up);
+log LOG120 clear;
+obey TEST120(tests);
+log;
+obey TEST120(clean_up);
+exit;
+
+?section clean_up
+drop sequence team_seq;
+drop schema t120sch cascade;
+revoke role t120role3 from sql_user9 by sql_user3;
+revoke role t120role2 from sql_user6 by sql_user3;
+revoke role t120role3 from sql_user6 by sql_user3;
+revoke role t120role4 from sql_user6 by sql_user3;
+revoke role t120role1 from sql_user3; 
+revoke role t120role2 from sql_user3; 
+revoke role t120role3 from sql_user3; 
+revoke role t120role4 from sql_user3; 
+drop role t120role1;
+drop role t120role2;
+drop role t120role3;
+drop role t120role4;
+
+?section create_db
+create role t120role1;
+create role t120role2;
+create role t120role3;
+create role t120role4;
+grant role t120role1, t120role2, t120role3, t120role4 to sql_user3 with admin 
option;
+
+create schema t120sch authorization t120role1;
+set schema t120sch;
+create table teams
+  (team_number int not null primary key,
+   team_name char(20) not null,
+   team_contact varchar(50) not null,
+   team_contact_number char (10) not null
+   )
+  ;
+
+alter table teams add constraint valid_team_no check (team_number > 0);
+insert into teams values
+   (1, 'White Socks', 'Sam','4082282222'),
+   (2, 'Giants', 'Joe', '5102839483'),
+   (3, 'Cardinals', 'Stella', '9513849384'),
+   (4, 'Indians', 'Matt', '5128383748'),
+   (5, 'Tigers', 'Ronit', '6198273827');
+
+
+create table games
+   ( home_team_number int not null,
+     visitor_team_number int not null,
+     game_number int not null primary key,
+     game_time timestamp not null,
+     game_location varchar(50) not null)
+  ;
+
+alter table games add constraint valid_game_number check (game_number > 0);
+insert into games values
+   (1, 2, 1, timestamp '2009-04-23 19:30:00', 'California'),
+   (1, 3, 2, timestamp '2009-04-24 19:30:00', 'California'),
+   (1, 4, 3, timestamp '2009-04-25 10:00:00', 'Oklahoma'),
+   (2, 3, 4, timestamp '2009-04-25 13:30:00', 'Michigan'),
+   (1, 5, 5, timestamp '2009-04-25 15:00:00', 'Oklahoma'),
+   (2, 5, 6, timestamp '2009-04-27 17:00:00', 'New York'),
+   (3, 4, 7, timestamp '2009-04-28 17:00:00', 'Florida'),
+   (4, 2, 8, current_timestamp, 'Missouri');
+
+create table players
+  (player_number int not null,
+   player_name varchar (50) not null,
+   player_team_number int not null,
+   player_phone_number char (10) not null,
+   player_details varchar(50),
+   primary key (player_number, player_team_number))
+  no partition;
+
+alter table players add constraint valid_player_number check(player_number > 
0);
+
+insert into players values
+   (1, 'Tom', 1, '4083948394', null),
+   (2, 'Bob', 1, '4089483948', null),
+   (3, 'Toby',1, '4082938493', 'pitcher'),
+   (3, 'Toby',2, '4082938493', null),
+   (4, 'Julie', 2, '5108394839', 'catcher'),
+   (5, 'Joanne', 2, '5103849384', null),
+   (6, 'Pete', 2, '5102839483', null),
+   (6, 'Pete', 3, '5102839483', 'third base'),
+   (7, 'Jared',4, '9518293849', 'short stop'),
+   (8, 'Zachary', 4, '9518293840', null),
+   (9, 'Lynne', 5, '9518293892', 'pitcher'),
+   (10, 'Omar', 5, '5128394893', null);
+
+create table standings
+    (team_number int not null primary key,
+     wins int default 0,
+     loses int default 0,
+     last_updated timestamp default current_timestamp)
+;
+
+insert into standings (team_number)
+  select team_number from teams;
+
+create sequence team_seq;
+
+create table stats
+  (team_number int not null primary key,
+   num_players int not null)
+;
+
+insert into stats
+  select team_number, count (player_number)
+   from teams t, players p
+   where t.team_number = p.player_team_number
+   group by team_number;
+
+obey TEST120(queries);
+
+?section tests
+-- =================================================================
+-- Design:
+--    t120role1 - owns schema t120sch 
+--    t120role2 - contains grants against teams and games
+--    t120role3 - contains grants against teams, games, and standings
+--    t120role4 - control, has no privs granted, make sure revoking
+--                role does not cause recompilations
+--
+--    sql_user3 - is schema administrator for schema t120sch
+--    sql_user6 - is granted and revoked privileges directly and
+--                through t120role2 and t120role3
+--    sql_user9 - control, makes sure revokes from roles does not
+--                affect sql_user9's compiled queries
+--
+--    games     - multiple roles giving same privileges
+--    teams     - multiple privileges through different roles
+--    players   - control, not roles involved in privileges
+--    standings - used to test sequence privileges and revoke role
+--    stats     - tests revoke PUBLIC authorization ID
+-- =================================================================
+obey TEST120(create_db);
+sh sqlci -i "TEST120(runqueries)" -u sql_user6;
+
+?section runqueries
+-- =================================================================
+-- This runs all the tests as sql_user6.  
+-- =================================================================
+log LOG120;
+values (current_user);
+cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+cqd AUTO_QUERY_RETRY_WARNINGS 'ON';
+set schema t120sch;
+
+-- At this time sql_user6 has no privileges
+-- Expect error 4481
+-- Prepare a bunch of queries, all fail with no privilege
+prepare select_games from select game_number from games;
+prepare select_teams from select team_number, team_name from teams;
+prepare insert_teams from 
+  insert into teams values (6, 'Braves', 'Jim', '8653845150');
+prepare update_teams from
+  update teams set team_contact_number = '8653855150' where team_number = 6;
+prepare select_players from select count(*) from players;
+prepare select_standings from select team_number, seqnum(team_seq) from 
standings;
+prepare select_stats from select team_number, num_players from stats;
+
+-- sql_user9 also has no privileges
+sh sqlci -i "TEST120(select_queries)" -u sql_user9;
+
+-- grant privileges
+sh sqlci -i "TEST120(grant_all_privs)" -u sql_user3;
+
+-- Prepare a bunch of queries, all successful
+prepare select_games from select game_number from games;
+prepare select_teams from select team_number, team_name from teams;
+prepare insert_teams from 
+  insert into teams values (6, 'Braves', 'Jim', '8653845150');
+prepare update_teams from
+  update teams set team_contact_number = '8653855150' where team_number = 6;
+prepare select_players from select count(*) from players;
+prepare select_standings from select team_number, seqnum(team_seq) from 
standings;
+
+-- Gather the query invalidation keys for each plan
+--   OI - object/column insert required
+--   OG - sequence usage required
+--   OS - object/column select required
+--   OU - object/column update required
+--   AR - role involved, check query plans that rely on roles during revoke
+log;
+log EXPLAIN120 clear;
+explain select_games;
+explain select_teams;
+explain insert_teams;
+explain update_teams;
+explain select_players;
+explain select_standings;
+
+log;
+sh echo "Query_Invalidation_Keys explain output for select_games, 
select_teams, insert_teams, update_teams, select_players, select_standings: "   
>> LOG120;
+sh sed '/Query_Invalidation_Keys/,/ObjectUIDs/!d;/ObjectUIDs/d' EXPLAIN120 | 
sed -e 's/[0-9 \t]*//g' >> LOG120;
+log;
+log LOG120;
+
+-- Verify that sql_user9 can select from games
+sh sqlci -i "TEST120(select_queries)" -u sql_user9;
+
+-- revoke t120role4 from sql_user6
+sh sqlci -i "TEST120(revoke_t120role4)" -u sql_user3;
+execute select_games;
+execute select_teams;
+
+-- revoke t120role2 from sql_user6
+sh sqlci -i "TEST120(revoke_t120role2)" -u sql_user3;
+
+-- still have privs but query recompiled (8597)
+execute select_games;
+execute select_teams;
+execute select_standings;
+
+-- still have privs, no query recompilation message because no invalidation
+-- key for roles exist
+execute select_players;
+
+-- no longer has privilege (4481) and query attempted recompilation
+execute insert_teams;
+execute update_teams;
+
+-- revoke role t120role3 from sql_user6
+sh sqlci -i "TEST120(revoke_t120role3)" -u sql_user3;
+
+-- no longer has privilege (4481) on select_games, 
+-- but has privilege on select_teams and select_players
+execute select_games;
+execute select_teams;
+execute select_players;
+
+-- user has select but not usage
+execute select_standings;
+
+-- Verify sql_user9 can still select from games and no recompilation msg
+sh sqlci -i "TEST120(select_queries)" -u sql_user9;
+
+-- tests for PUBLIC
+-- select priv on stats has been granted to public
+prepare select_stats from select team_number, num_players from stats;
+log;
+log EXPLAIN120 clear;
+explain select_stats;
+sh echo "Query_Invalidation_Keys explain output for select_stats: "   >> 
LOG120;
+sh sed '/Query_Invalidation_Keys/,/ObjectUIDs/!d;/ObjectUIDs/d' EXPLAIN120 | 
sed -e 's/[0-9 \t]*//g' >> LOG120;
+log;
+log LOG120;
+
+execute select_stats;
+
+-- revoke PUBLIC
+sh sqlci -i "TEST120(revoke_public)" -u sql_user3;
+
+select * from stats;
+execute select_stats;
+
+?section grant_all_privs
+log LOG120;
+values (current_user);
+cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+cqd AUTO_QUERY_RETRY_WARNINGS 'ON';
+set schema t120sch;
+
+grant insert, delete, update(team_contact, team_contact_number) on teams to 
t120role2;
+grant select on teams to sql_user6;
+grant select (game_number, game_time) on games to t120role2;
+grant select (game_number, game_time) on games to t120role3; 
+grant all on players to sql_user6;
+grant role t120role2, t120role3, t120role4 to sql_user6;
+grant role t120role3 to sql_user9;
+grant select on standings to sql_user6;
+grant usage on sequence team_seq to t120role3;
+grant select (team_number, num_players) on stats to "PUBLIC";
+showddl games;
+showddl teams;
+showddl players;
+showddl standings;
+showddl sequence team_seq;
+showddl role t120role1;
+showddl role t120role2;
+showddl role t120role3;
+showddl role t120role4;
+
+?section revoke_t120role2
+log LOG120;
+values (current_user);
+cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+cqd AUTO_QUERY_RETRY_WARNINGS 'ON';
+set schema t120sch;
+
+revoke role t120role2 from sql_user6;
+
+-- sql_user3 has privs on all tables, can execute and no recompile
+obey TEST120(queries);
+
+?section revoke_t120role3
+log LOG120;
+values (current_user);
+cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+cqd AUTO_QUERY_RETRY_WARNINGS 'ON';
+set schema t120sch;
+
+revoke role t120role3 from sql_user6;
+
+-- sql_user3 has privs on all tables, can execute and no recompile
+obey TEST120(queries);
+
+?section revoke_t120role4
+log LOG120;
+values (current_user);
+cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+cqd AUTO_QUERY_RETRY_WARNINGS 'ON';
+set schema t120sch;
+
+revoke role t120role4 from sql_user6;
+
+-- sql_user3 has privs on all tables, can execute and no recompile
+obey TEST120(queries);
+
+?section revoke_public
+log LOG120;
+values (current_user);
+cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+cqd AUTO_QUERY_RETRY_WARNINGS 'ON';
+set schema t120sch;
+
+revoke select (team_number, num_players) on stats from "PUBLIC";
+showddl stats;
+select team_number, num_players from stats;
+
+?section queries
+select game_number from games;
+select team_number from teams;
+select player_number from players;
+select team_number, seqnum(team_seq) from standings;
+select team_number, num_players from stats;
+
+?section select_queries
+log LOG120;
+values (current_user);
+cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+cqd AUTO_QUERY_RETRY_WARNINGS 'ON';
+set schema t120sch;
+select game_number from games;

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1c938575/core/sql/regress/privs1/TEST137
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs1/TEST137 b/core/sql/regress/privs1/TEST137
index 2080a44..9654fe4 100755
--- a/core/sql/regress/privs1/TEST137
+++ b/core/sql/regress/privs1/TEST137
@@ -56,10 +56,12 @@ log;
 -- run tests as different users
 sh sqlci -i "TEST137(user1_tests)" -u sql_user1;
 sh sqlci -i "TEST137(user4_tests)" -u sql_user4;;
-sh sqlci -i "TEST137(user5_tests)" -u sql_user5;
 
--- test the SQL_OPERATIONS privilege
 log LOG137;
+-- test getting component privileges through roles
+obey TEST137(role_tests);
+
+-- test the SQL_OPERATIONS privilege
 obey TEST137(operator_privs);
 
 obey TEST137(clean_up);
@@ -105,6 +107,18 @@ revoke role library_ckout_clerks from sql_user2;
 revoke role library_ckout_clerks from sql_user3, sql_user4, sql_user5;
 drop role library_ckout_clerks;
 
+revoke component privilege lib_view_checkouts on library_books from 
lib_role_test;
+revoke role lib_role_test from sql_user5;
+revoke role db__rootrole from sql_user5;
+drop role lib_role_test;
+     
+revoke role md_access from sql_user1, sql_user4, sql_user5;
+revoke select on "_MD_".auths from md_access;
+revoke select on "_PRIVMGR_MD_".components from md_access;
+revoke select on "_PRIVMGR_MD_".component_operations from md_access;
+revoke select on "_PRIVMGR_MD_".component_privileges from md_access;
+drop role md_access;
+
 ?section set_up
 prepare get_roles from
 select substring (auth_db_name,1,20) as role_name
@@ -148,6 +162,13 @@ order by 1, 2, d.grantor_ID, d.grantee_name, grant_depth
 for read uncommitted access;
 
 ?section create_db
+create role md_access;
+grant select on "_MD_".auths to md_access;
+grant select on "_PRIVMGR_MD_".components to md_access;
+grant select on "_PRIVMGR_MD_".component_operations to md_access;
+grant select on "_PRIVMGR_MD_".component_privileges to md_access;
+grant role md_access to sql_user1, sql_user4, sql_user5;
+
 create shared schema t137;
 set schema t137;
 
@@ -353,6 +374,26 @@ drop component privilege lib_view_repository on 
library_xxxx;
 -- error unknown privilege
 drop component privilege lib_view_xxxx on library_books;
 
+?section role_tests
+-- tests grants and revoke on roles owned by user work correctly
+create role lib_role_test;
+
+-- user5_tests fail - user5 has no priv
+grant component privilege lib_view_checkouts on library_books to lib_role_test 
with grant option;
+sh sqlci -i "TEST137(user5_tests)" -u sql_user5;
+
+-- user5_tests for priv lib_view_checkouts succeeds, have privs through role
+grant role lib_role_test to sql_user5;
+sh sqlci -i "TEST137(user5_tests)" -u sql_user5;
+revoke component privilege lib_view_checkouts on library_books from 
lib_role_test;
+revoke role lib_role_test from sql_user5;
+drop role lib_role_test;
+
+-- user5_tests for priv lib_view_checkouts succeeds, have privs through 
DB__ROOTROLE
+grant role db__rootrole to sql_user5;
+sh sqlci -i "TEST137(user5_tests)" -u sql_user5;
+revoke role db__rootrole from sql_user5;
+
 ?section operator_privs
 -- This section tests operation privileges:
 -- Test environment
@@ -420,7 +461,9 @@ showddl component sql_operations;
 ?section user1_tests
 obey TEST137 (set_up);
 cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+-- user1_tests
 log LOG137;
+values (user);
 -- These tests should succeed
 -- returns 2 rows
 showddl role db__rootrole;
@@ -428,7 +471,7 @@ execute get_component_operations;
 grant component privilege lib_view_checkouts on library_books to sql_user4 
    with grant option;
 grant component privilege lib_manage_checkouts on library_books to sql_user5;
--- returns 17 rows
+-- returns 16 rows
 execute get_priv_desc;
 
 -- These tests should fail
@@ -444,6 +487,7 @@ log;
 obey TEST137 (set_up);
 cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
 log LOG137;
+values(user);
 -- these tests should succeed
 grant component privilege lib_view_checkouts on library_books to sql_user6;
 execute get_priv_desc;
@@ -457,8 +501,9 @@ log;
 obey TEST137 (set_up);
 cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
 log LOG137;
--- these tests should fail
+values(user);
 grant component privilege lib_view_repository on library_books to sql_user7;
+grant component privilege lib_view_checkouts on library_books to sql_user7;
 log;
 
 -- below is the setup and testing for various scenarios for revoke component 
privileges

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1c938575/core/sql/sqlcomp/PrivMgrComponentPrivileges.cpp
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/PrivMgrComponentPrivileges.cpp 
b/core/sql/sqlcomp/PrivMgrComponentPrivileges.cpp
index 70dd089..1340d12 100644
--- a/core/sql/sqlcomp/PrivMgrComponentPrivileges.cpp
+++ b/core/sql/sqlcomp/PrivMgrComponentPrivileges.cpp
@@ -31,6 +31,7 @@
 
 #include <string>
 #include <cstdio>
+#include <algorithm>
 #include <vector>
 #include "ComSmallDefs.h"
 
@@ -659,9 +660,8 @@ PrivStatus PrivMgrComponentPrivileges::grantPrivilege(
   
 {
 
-// Determine if the component exists.
-
-PrivMgrComponents component(metadataLocation_,pDiags_);
+   // Determine if the component exists.
+   PrivMgrComponents component(metadataLocation_,pDiags_);
 
    if (!component.exists(componentName))
    {
@@ -670,11 +670,11 @@ PrivMgrComponents component(metadataLocation_,pDiags_);
       return STATUS_ERROR;
    }
  
-std::string componentUIDString;
-int64_t componentUID;
-bool isSystemComponent;
-std::string tempStr;
-PrivStatus privStatus = STATUS_GOOD;
+   std::string componentUIDString;
+   int64_t componentUID;
+   bool isSystemComponent;
+   std::string tempStr;
+   PrivStatus privStatus = STATUS_GOOD;
 
    component.fetchByName(componentName,
                          componentUIDString,
@@ -682,11 +682,11 @@ PrivStatus privStatus = STATUS_GOOD;
                          isSystemComponent,
                          tempStr);
                          
-// OK, the component is defined, what about the operations?
-MyTable &myTable = static_cast<MyTable &>(myTable_);
-PrivMgrComponentOperations componentOperations(metadataLocation_,pDiags_);
-std::vector<std::string> operationCodes;
-int32_t grantorID = grantorIDIn;
+   // OK, the component is defined, what about the operations?
+   MyTable &myTable = static_cast<MyTable &>(myTable_);
+   PrivMgrComponentOperations componentOperations(metadataLocation_,pDiags_);
+   std::vector<std::string> operationCodes;
+   int32_t grantorID = grantorIDIn;
 
    for (size_t i = 0; i < operations.size(); i ++)
    {
@@ -720,7 +720,6 @@ int32_t grantorID = grantorIDIn;
       // If grantorID is DB__ROOT, then we use the "owner" of the 
       // operation, which is the user granted the privilege by _SYSTEM.
       // Read COMPONENT_PRIVILEGES to get grantorID.
-      //TODO: Need vector of grantorIDs.
       if (grantorIDIn == ComUser::getRootUserID())
       {
          privStatus = myTable.fetchOwner(componentUID,operationCode,
@@ -745,11 +744,11 @@ int32_t grantorID = grantorIDIn;
       operationCodes.push_back(operationCode);
    }
    
-//
-// Operations are valid, add or update each entry.
-//
+   //
+   // Operations are valid, add or update each entry.
+   //
 
-MyRow row(fullTableName_);
+   MyRow row(fullTableName_);
 
    row.componentUID_ = componentUID;
    row.grantDepth_ = grantDepth;
@@ -758,7 +757,7 @@ MyRow row(fullTableName_);
    row.grantorID_ = grantorID;
    row.grantorName_ = grantorName;
    
-std::string whereClauseHeader(" WHERE COMPONENT_UID = ");
+   std::string whereClauseHeader(" WHERE COMPONENT_UID = ");
 
    whereClauseHeader += componentUIDString;
    whereClauseHeader += " AND GRANTEE_ID = ";          
@@ -1209,23 +1208,46 @@ bool PrivMgrComponentPrivileges::hasWGO(
    
 {
 
-MyTable &myTable = static_cast<MyTable &>(myTable_);
+   // get roles granted to authID
+   std::vector<std::string> roleNames;
+   std::vector<int32_t> roleIDs;
+   std::vector<int32_t> grantDepths;
 
-std::string whereClause (" WHERE GRANTEE_ID = ");
+   PrivMgrRoles roles(" ",metadataLocation_,pDiags_);
 
-   whereClause += authIDToString(authID); 
-   whereClause += " AND COMPONENT_UID = ";          
+   if (roles.fetchRolesForUser(authID,roleNames,
+                               roleIDs,grantDepths) == STATUS_ERROR)
+      return false;
+
+   MyTable &myTable = static_cast<MyTable &>(myTable_);
+
+   std::string granteeList;
+   granteeList += authIDToString(authID);
+   for (size_t i = 0; i < roleIDs.size(); i++)
+   {
+      granteeList += ", ";
+      granteeList += authIDToString(roleIDs[i]);
+   }
+
+   // DB__ROOTROLE is a special role.  If the authID has been granted this 
role 
+   // then they have WGO privileges. 
+   if (std::find(roleIDs.begin(), roleIDs.end(), ROOT_ROLE_ID) != 
roleIDs.end())
+      return true;
+
+   std::string whereClause (" WHERE GRANTEE_ID IN (");
+   whereClause += granteeList;
+   whereClause += ") AND COMPONENT_UID = ";          
    whereClause += componentUIDString;
    whereClause += " AND OPERATION_CODE = '";          
    whereClause += operationCode;
    whereClause += "' AND GRANT_DEPTH <> 0";
    
-int64_t rowCount = 0;
+   int64_t rowCount = 0;
 
-// set pointer in diags area
-int32_t diagsMark = pDiags_->mark();
+   // set pointer in diags area
+   int32_t diagsMark = pDiags_->mark();
 
-PrivStatus privStatus = myTable.selectCountWhere(whereClause,rowCount);
+   PrivStatus privStatus = myTable.selectCountWhere(whereClause,rowCount);
 
    if ((privStatus == STATUS_GOOD || privStatus == STATUS_WARNING) &&
         rowCount > 0)

Reply via email to