You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by rm...@apache.org on 2016/10/18 15:20:32 UTC
[1/2] incubator-trafodion git commit: [TRAFODION-1758]: A user has
dbroot role can't grant component privilege
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 <rm...@edev07.esgyn.local>
Authored: Mon Oct 17 20:30:59 2016 +0000
Committer: Roberta Marton <rm...@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)
[2/2] incubator-trafodion git commit: merge [TRAFODION-1758] PR 765 A
user has dbroot role can't grant component privilege
Posted by rm...@apache.org.
merge [TRAFODION-1758] PR 765 A user has dbroot role can't grant component privilege
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/8805eb26
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/8805eb26
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/8805eb26
Branch: refs/heads/master
Commit: 8805eb26ce2a9cda273a6f1d19a0e48ed6b2bb6e
Parents: 437108f 1c93857
Author: Roberta Marton <ro...@apache.org>
Authored: Tue Oct 18 15:20:07 2016 +0000
Committer: Roberta Marton <ro...@apache.org>
Committed: Tue Oct 18 15:20:07 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(-)
----------------------------------------------------------------------