You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by sa...@apache.org on 2016/03/02 07:36:46 UTC
[2/5] incubator-trafodion git commit: TRAFODION-1856: Revoke - object
and column privilege checks not integrated for constraints
TRAFODION-1856: Revoke - object and column privilege checks not integrated for constraints
Today, when revoking the object REFERENCES privilege, the revoke fails if there
are any RI constraints that require the privilege. However, there may be column
level privileges that exist that would still allow the constraint to be present.
Conversely, when revoking column REFERENCES privilege, the revoke does not
check to see if REFERENCES privilege has been granted at the object level.
In fact, the revoke operation does not check for dependencies on constraints
correctly.
For example:
user1:
create table dept( dept_no int not null primary key, dept_name char(50));
grant references on table dept to user2;
grant references(dept_no) to user2;
user2:
create table empl(empl_no int not null primary key, dept_no int not null);
alter table empl add constraint empl_dept
foreign key (dept_no) references dept;
user1 should be able to "revoke references on table dept from user2" because
user2 still has the references privileges on column dept_no. Vice versa, user1
should be able to "revoke references(dept_no) on dept from user2" because user2
still has the references privilege on table dept.
To make this work, several changes were implemented:
In the existing code, object level privileges use one set of structures to
manage privileges (PrivMgrCoreDesc) and column level privileges use another
(ColPrivEntry). The ColPrivEntry class was changed to use the same base
"PrivMgrCoreDesc" structure as object privileges. This makes comparing things
between objects and columns easier. There is still more work to do in this
area.
There is a method called dealWithConstraints that, among other things, checks
to see if the revoke can occur. Changes were made to check for column level
privileges if object level privileges were no longer available. Revoking
column level privilege now calls this method to make sure the revoke can
proceed.
The dealWithConstraints change required updates to the query that retrieved
referenced table information. In addition to returning the referencing table,
the list of referenced table columns associated with each constraint was
needed. The column information returned was transformed into a new
ColumnReference structure attached to the existing ObjectUsage and
ObjectReference classes. Changes were also required in getConstraintName to get
the RI constraint related to the column which no longer has the privilege.
In addition, the code to remove column level privileges when object level
privileges was removed. In SQL, each grant needs a separate revoke to remove.
So this code did not follow ANSI standard.
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/392031a3
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/392031a3
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/392031a3
Branch: refs/heads/master
Commit: 392031a304b52348aa4bbb8fe3a4e6399c70838f
Parents: 0918828
Author: Roberta Marton <ro...@apache.org>
Authored: Mon Feb 29 20:14:15 2016 +0000
Committer: Roberta Marton <ro...@apache.org>
Committed: Mon Feb 29 20:14:15 2016 +0000
----------------------------------------------------------------------
core/sql/regress/privs2/EXPECTED135 | 329 -----------
core/sql/regress/privs2/EXPECTED142 | 760 +++++++++++++++++++++++++
core/sql/regress/privs2/TEST135 | 43 --
core/sql/regress/privs2/TEST142 | 280 ++++++++++
core/sql/sqlcomp/CmpSeabaseDDLtable.cpp | 30 +-
core/sql/sqlcomp/PrivMgrDefs.h | 4 +-
core/sql/sqlcomp/PrivMgrMD.cpp | 171 ++++--
core/sql/sqlcomp/PrivMgrMD.h | 129 ++++-
core/sql/sqlcomp/PrivMgrPrivileges.cpp | 793 ++++++++++++++++-----------
core/sql/sqlcomp/PrivMgrPrivileges.h | 48 +-
10 files changed, 1811 insertions(+), 776 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/392031a3/core/sql/regress/privs2/EXPECTED135
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs2/EXPECTED135 b/core/sql/regress/privs2/EXPECTED135
index 0d8b0f6..9fbc688 100644
--- a/core/sql/regress/privs2/EXPECTED135
+++ b/core/sql/regress/privs2/EXPECTED135
@@ -648,335 +648,6 @@ End of MXCI Session
--- SQL operation complete.
>>
->>obey TEST135(constraint_tests);
->>-- ============================================================================
->>set schema t135sch;
-
---- SQL operation complete.
->>create table t135_t1 (c1 int not null primary key, c2 int);
-
---- SQL operation complete.
->>
->>-- have user3 create some objects
->>sh sqlci -i "TEST135(user3_objects)" -u sql_user3;
->>create table t135_t3 (c1 int not null primary key, c2 int);
-
---- SQL operation complete.
->>create table t135_t4 (c1 int not null, c2 largeint not null primary key, c3 int);
-
---- SQL operation complete.
->>showddl t135_t3;
-
-CREATE TABLE TRAFODION.T135SCH_USER3.T135_T3
- (
- C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
- , C2 INT DEFAULT NULL
- , PRIMARY KEY (C1 ASC)
- )
-;
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH_USER3.T135_T3 TO SQL_USER3 WITH GRANT OPTION;
-
---- SQL operation complete.
->>showddl t135_t4;
-
-CREATE TABLE TRAFODION.T135SCH_USER3.T135_T4
- (
- C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
- , C2 LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
- , C3 INT DEFAULT NULL
- , PRIMARY KEY (C2 ASC)
- )
-;
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH_USER3.T135_T4 TO SQL_USER3 WITH GRANT OPTION;
-
---- SQL operation complete.
->>
->>exit;
-
-End of MXCI Session
-
->>
->>-- now have user3 create an RI constraint
->>-- fails because user3 has no privs
->>sh sqlci -i "TEST135(user3_constraint)" -u sql_user3;
->>alter table t135_t3 add constraint t1_t3 foreign key (c1) references t135sch.t135_t1;
-
-*** ERROR[1017] You are not authorized to perform this operation.
-
---- SQL operation failed with errors.
->>showddl t135_t3;
-
-CREATE TABLE TRAFODION.T135SCH_USER3.T135_T3
- (
- C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
- , C2 INT DEFAULT NULL
- , PRIMARY KEY (C1 ASC)
- )
-;
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH_USER3.T135_T3 TO SQL_USER3 WITH GRANT OPTION;
-
---- SQL operation complete.
->>
->>-- ============================================================================
->>exit;
-
-End of MXCI Session
-
->>grant references on t135_t1 to sql_user3;
-
---- SQL operation complete.
->>showddl t135_t1;
-
-CREATE TABLE TRAFODION.T135SCH.T135_T1
- (
- C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
- , C2 INT DEFAULT NULL
- , PRIMARY KEY (C1 ASC)
- )
-;
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH.T135_T1 TO DB__ROOT WITH GRANT OPTION;
- GRANT REFERENCES ON TRAFODION.T135SCH.T135_T1 TO SQL_USER3;
-
---- SQL operation complete.
->>-- operation should succeed
->>sh sqlci -i "TEST135(user3_constraint)" -u sql_user3;
->>alter table t135_t3 add constraint t1_t3 foreign key (c1) references t135sch.t135_t1;
-
---- SQL operation complete.
->>showddl t135_t3;
-
-CREATE TABLE TRAFODION.T135SCH_USER3.T135_T3
- (
- C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
- , C2 INT DEFAULT NULL
- , PRIMARY KEY (C1 ASC)
- )
-;
-
-ALTER TABLE TRAFODION.T135SCH_USER3.T135_T3 ADD CONSTRAINT
- TRAFODION.T135SCH_USER3.T1_T3 FOREIGN KEY
- (
- C1
- )
- REFERENCES TRAFODION.T135SCH.T135_T1
- (
- C1
- )
-;
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH_USER3.T135_T3 TO SQL_USER3 WITH GRANT OPTION;
-
---- SQL operation complete.
->>
->>-- ============================================================================
->>exit;
-
-End of MXCI Session
-
->>
->>-- now try to revoke references privileges
->>revoke references on t135_t1 from sql_user3;
-
-*** ERROR[1025] Request failed. Dependent object TRAFODION."T135SCH_USER3"."T1_T3" exists.
-
---- SQL operation failed with errors.
->>sh sqlci -i "TEST135(user3_drops)" -u sql_user3;
->>drop table t135_t3 cascade;
-
---- SQL operation complete.
->>drop table t135_t4 cascade;
-
---- SQL operation complete.
->>
->>exit;
-
-End of MXCI Session
-
->>revoke references on t135_t1 from sql_user3;
-
---- SQL operation complete.
->>showddl t135_t1;
-
-CREATE TABLE TRAFODION.T135SCH.T135_T1
- (
- C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
- , C2 INT DEFAULT NULL
- , PRIMARY KEY (C1 ASC)
- )
-;
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH.T135_T1 TO DB__ROOT WITH GRANT OPTION;
-
---- SQL operation complete.
->>
->>-- repeat, but grant all privileges instead.
->>-- have user3 create some objects
->>sh sqlci -i "TEST135(user3_objects)" -u sql_user3;
->>create table t135_t3 (c1 int not null primary key, c2 int);
-
---- SQL operation complete.
->>create table t135_t4 (c1 int not null, c2 largeint not null primary key, c3 int);
-
---- SQL operation complete.
->>showddl t135_t3;
-
-CREATE TABLE TRAFODION.T135SCH_USER3.T135_T3
- (
- C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
- , C2 INT DEFAULT NULL
- , PRIMARY KEY (C1 ASC)
- )
-;
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH_USER3.T135_T3 TO SQL_USER3 WITH GRANT OPTION;
-
---- SQL operation complete.
->>showddl t135_t4;
-
-CREATE TABLE TRAFODION.T135SCH_USER3.T135_T4
- (
- C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
- , C2 LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
- , C3 INT DEFAULT NULL
- , PRIMARY KEY (C2 ASC)
- )
-;
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH_USER3.T135_T4 TO SQL_USER3 WITH GRANT OPTION;
-
---- SQL operation complete.
->>
->>exit;
-
-End of MXCI Session
-
->>
->>-- now have user3 create an RI constraint
->>-- fails because user3 has no privs
->>sh sqlci -i "TEST135(user3_constraint)" -u sql_user3;
->>alter table t135_t3 add constraint t1_t3 foreign key (c1) references t135sch.t135_t1;
-
-*** ERROR[1017] You are not authorized to perform this operation.
-
---- SQL operation failed with errors.
->>showddl t135_t3;
-
-CREATE TABLE TRAFODION.T135SCH_USER3.T135_T3
- (
- C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
- , C2 INT DEFAULT NULL
- , PRIMARY KEY (C1 ASC)
- )
-;
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH_USER3.T135_T3 TO SQL_USER3 WITH GRANT OPTION;
-
---- SQL operation complete.
->>
->>-- ============================================================================
->>exit;
-
-End of MXCI Session
-
->>grant all on t135_t1 to sql_user3;
-
---- SQL operation complete.
->>showddl t135_t1;
-
-CREATE TABLE TRAFODION.T135SCH.T135_T1
- (
- C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
- , C2 INT DEFAULT NULL
- , PRIMARY KEY (C1 ASC)
- )
-;
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH.T135_T1 TO DB__ROOT WITH GRANT OPTION;
- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH.T135_T1
- TO SQL_USER3;
-
---- SQL operation complete.
->>-- operation should succeed
->>sh sqlci -i "TEST135(user3_constraint)" -u sql_user3;
->>alter table t135_t3 add constraint t1_t3 foreign key (c1) references t135sch.t135_t1;
-
---- SQL operation complete.
->>showddl t135_t3;
-
-CREATE TABLE TRAFODION.T135SCH_USER3.T135_T3
- (
- C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
- , C2 INT DEFAULT NULL
- , PRIMARY KEY (C1 ASC)
- )
-;
-
-ALTER TABLE TRAFODION.T135SCH_USER3.T135_T3 ADD CONSTRAINT
- TRAFODION.T135SCH_USER3.T1_T3 FOREIGN KEY
- (
- C1
- )
- REFERENCES TRAFODION.T135SCH.T135_T1
- (
- C1
- )
-;
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH_USER3.T135_T3 TO SQL_USER3 WITH GRANT OPTION;
-
---- SQL operation complete.
->>
->>-- ============================================================================
->>exit;
-
-End of MXCI Session
-
->>
->>-- now try to revoke references privileges
->>revoke references on t135_t1 from sql_user3;
-
-*** ERROR[1025] Request failed. Dependent object TRAFODION."T135SCH_USER3"."T1_T3" exists.
-
---- SQL operation failed with errors.
->>sh sqlci -i "TEST135(user3_drops)" -u sql_user3;
->>drop table t135_t3 cascade;
-
---- SQL operation complete.
->>drop table t135_t4 cascade;
-
---- SQL operation complete.
->>
->>exit;
-
-End of MXCI Session
-
->>revoke references on t135_t1 from sql_user3;
-
---- SQL operation complete.
->>showddl t135_t1;
-
-CREATE TABLE TRAFODION.T135SCH.T135_T1
- (
- C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
- , C2 INT DEFAULT NULL
- , PRIMARY KEY (C1 ASC)
- )
-;
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH.T135_T1 TO DB__ROOT WITH GRANT OPTION;
- GRANT SELECT, INSERT, DELETE, UPDATE ON TRAFODION.T135SCH.T135_T1 TO
- SQL_USER3;
-
---- SQL operation complete.
->>
->>drop table t135_t1 cascade;
-
---- SQL operation complete.
->>
>>obey TEST135(udr_tests);
>>-- ============================================================================
>>-- To create a function/table_mapping function, or SPJ, you need
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/392031a3/core/sql/regress/privs2/EXPECTED142
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs2/EXPECTED142 b/core/sql/regress/privs2/EXPECTED142
new file mode 100644
index 0000000..2e9fc77
--- /dev/null
+++ b/core/sql/regress/privs2/EXPECTED142
@@ -0,0 +1,760 @@
+>>obey TEST142(create_db);
+>>create schema t142user1 authorization sql_user1;
+
+--- SQL operation complete.
+>>create schema t142user2 authorization sql_user2;
+
+--- SQL operation complete.
+>>create schema t142user3 authorization sql_user3;
+
+--- SQL operation complete.
+>>create schema t142user4 authorization sql_user4;
+
+--- SQL operation complete.
+>>create schema t142user5 authorization sql_user5;
+
+--- SQL operation complete.
+>>create schema t142user6 authorization sql_user6;
+
+--- SQL operation complete.
+>>
+>>set schema t142user4;
+
+--- SQL operation complete.
+>>create table referencedTable
++>( c1 int not null primary key,
++> c2 int not null,
++> c3 char(10) not null,
++> c4 char(10) not null,
++> c5 largeint not null );
+
+--- SQL operation complete.
+>>alter table referencedTable add constraint u1 unique (c3, c2);
+
+--- SQL operation complete.
+>>alter table referencedTable add constraint u2 unique (c4);
+
+--- SQL operation complete.
+>>alter table referencedTable add constraint u3 unique(c5);
+
+--- SQL operation complete.
+>>showddl referencedTable;
+
+CREATE TABLE TRAFODION.T142USER4.REFERENCEDTABLE
+ (
+ C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , C2 INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , C3 CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , C4 CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , C5 LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
+ , PRIMARY KEY (C1 ASC)
+ )
+;
+
+-- The following index is a system created index --
+CREATE UNIQUE INDEX U1 ON TRAFODION.T142USER4.REFERENCEDTABLE
+ (
+ C3 ASC
+ , C2 ASC
+ )
+;
+
+-- The following index is a system created index --
+CREATE UNIQUE INDEX U2 ON TRAFODION.T142USER4.REFERENCEDTABLE
+ (
+ C4 ASC
+ )
+;
+
+-- The following index is a system created index --
+CREATE UNIQUE INDEX U3 ON TRAFODION.T142USER4.REFERENCEDTABLE
+ (
+ C5 ASC
+ )
+;
+
+ALTER TABLE TRAFODION.T142USER4.REFERENCEDTABLE ADD CONSTRAINT
+ TRAFODION.T142USER4.U1 UNIQUE
+ (
+ C3
+ , C2
+ )
+;
+
+ALTER TABLE TRAFODION.T142USER4.REFERENCEDTABLE ADD CONSTRAINT
+ TRAFODION.T142USER4.U2 UNIQUE
+ (
+ C4
+ )
+;
+
+ALTER TABLE TRAFODION.T142USER4.REFERENCEDTABLE ADD CONSTRAINT
+ TRAFODION.T142USER4.U3 UNIQUE
+ (
+ C5
+ )
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T142USER4.REFERENCEDTABLE TO SQL_USER4 WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>
+>>-- compile cpp program for function
+>>sh rm -f ./etest140.dll;
+>>sh sh $$scriptsdir$$/tools/dll-compile.ksh etest140.cpp
++> 2>&1 | tee LOG140-SECONDARY;
+>>set pattern $$DLL$$ etest140.dll;
+>>set pattern $$QUOTE$$ '''';
+>>
+>>-- create the library and udf
+>>create library t142_l1 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$ ;
+
+--- SQL operation complete.
+>>create function t142_translatePrivsBitmap(bitmap largeint) returns (bitmap_string char (20))
++>language c parameter style sql external name 'translateBitmap'
++>library t142_l1
++>deterministic no sql final call allow any parallelism state area size 1024 ;
+
+--- SQL operation complete.
+>>grant execute on function t142_translatePrivsBitmap to "PUBLIC";
+
+--- SQL operation complete.
+>>
+>>set schema t142user1;
+
+--- SQL operation complete.
+>>create table user1
++>(u1_c1 int not null,
++> u1_c2 int not null primary key,
++> u1_c3 char(10) not null,
++> u1_c4 char(10));
+
+--- SQL operation complete.
+>>showddl user1;
+
+CREATE TABLE TRAFODION.T142USER1.USER1
+ (
+ U1_C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , U1_C2 INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , U1_C3 CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , U1_C4 CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT DEFAULT NULL
+ , PRIMARY KEY (U1_C2 ASC)
+ )
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T142USER1.USER1 TO SQL_USER1 WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>
+>>set schema t142user2;
+
+--- SQL operation complete.
+>>create table user2
++>(u2_c1 int not null,
++> u2_c2 int not null primary key,
++> u2_c3 char(10) not null,
++> u2_c4 largeint);
+
+--- SQL operation complete.
+>>showddl user2;
+
+CREATE TABLE TRAFODION.T142USER2.USER2
+ (
+ U2_C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , U2_C2 INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , U2_C3 CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , U2_C4 LARGEINT DEFAULT NULL
+ , PRIMARY KEY (U2_C2 ASC)
+ )
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T142USER2.USER2 TO SQL_USER2 WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>
+>>set schema t142user3;
+
+--- SQL operation complete.
+>>create table user3
++>(u3_c1 int not null,
++> u3_c2 int not null primary key,
++> u3_c3 char(10) not null,
++> u3_c4 char(10));
+
+--- SQL operation complete.
+>>showddl user3;
+
+CREATE TABLE TRAFODION.T142USER3.USER3
+ (
+ U3_C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , U3_C2 INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , U3_C3 CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , U3_C4 CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT DEFAULT NULL
+ , PRIMARY KEY (U3_C2 ASC)
+ )
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T142USER3.USER3 TO SQL_USER3 WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>
+>>set schema t142user5;
+
+--- SQL operation complete.
+>>create table user5
++>(u5_c1 int not null,
++> u5_c2 int not null primary key,
++> u5_c3 char(10) not null,
++> u5_c4 char(10));
+
+--- SQL operation complete.
+>>showddl user5;
+
+CREATE TABLE TRAFODION.T142USER5.USER5
+ (
+ U5_C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , U5_C2 INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , U5_C3 CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , U5_C4 CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT DEFAULT NULL
+ , PRIMARY KEY (U5_C2 ASC)
+ )
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T142USER5.USER5 TO SQL_USER5 WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>
+>>set schema t142user6;
+
+--- SQL operation complete.
+>>create table user6
++>(u6_c1 int not null,
++> u6_c2 int not null primary key,
++> u6_c3 char(10) not null,
++> u6_c4 char(10));
+
+--- SQL operation complete.
+>>showddl user6;
+
+CREATE TABLE TRAFODION.T142USER6.USER6
+ (
+ U6_C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , U6_C2 INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , U6_C3 CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , U6_C4 CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT DEFAULT NULL
+ , PRIMARY KEY (U6_C2 ASC)
+ )
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T142USER6.USER6 TO SQL_USER6 WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>
+>>obey TEST142(set_up);
+>>set schema "_PRIVMGR_MD_";
+
+--- SQL operation complete.
+>>prepare get_privs from
++>select distinct
++> trim(substring (o.object_name,1,20)) as object_name,
++> grantor_id, grantee_id, 'all',
++> t142user4.t142_translatePrivsBitmap(privileges_bitmap) as granted_privs,
++> t142user4.t142_translatePrivsBitmap(grantable_bitmap) as grantable_privs
++>from object_privileges p, "_MD_".objects o
++>where p.object_uid in
++> (select object_uid
++> from "_MD_".objects
++> where schema_name like 'T142USER%'
++> and object_name not like 'SB_%')
++> and p.object_uid = o.object_uid
++>union
++> (select distinct
++> trim(substring (o.object_name,1,20)) as object_name,
++> grantor_id, grantee_id, cast (column_number as char(3)),
++> t142user4.t142_translatePrivsBitmap(privileges_bitmap) as granted_privs,
++> t142user4.t142_translatePrivsBitmap(grantable_bitmap) as grantable_privs
++> from column_privileges p, "_MD_".objects o
++> where p.object_uid in
++> (select object_uid
++> from "_MD_".objects
++> where schema_name like 'T142USER%'
++> and object_name not like 'SB_%')
++> and p.object_uid = o.object_uid)
++>order by 1, 2, 3, 4
++>;
+
+--- SQL command prepared.
+>>
+>>obey TEST142(test_grants);
+>>-- =================================================================
+>>-- this set of tests run basic grant tests for constraints
+>>-- schema t142user4 contains the referenced table
+>>-- =================================================================
+>>set schema t142user4;
+
+--- SQL operation complete.
+>>
+>>-- all should fail, no one has permissions
+>>execute get_privs;
+
+OBJECT_NAME GRANTOR_ID GRANTEE_ID (EXPR) GRANTED_PRIVS GRANTABLE_PRIVS
+-------------------------------------------------------------------------------- -------------------- -------------------- ------ -------------------- --------------------
+
+REFERENCEDTABLE -2 33337 all SIDU-R- SIDU-R-
+T142_L1 -2 33337 all ---UG-- ---UG--
+T142_TRANSLATEPRIVSB -2 33337 all ------E ------E
+T142_TRANSLATEPRIVSB 33337 -1 all ------E NONE
+USER1 -2 33334 all SIDU-R- SIDU-R-
+USER2 -2 33335 all SIDU-R- SIDU-R-
+USER3 -2 33336 all SIDU-R- SIDU-R-
+USER5 -2 33338 all SIDU-R- SIDU-R-
+USER6 -2 33339 all SIDU-R- SIDU-R-
+
+--- 9 row(s) selected.
+>>sh sqlci -i "TEST142(user1_cmds)" -u sql_user1;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER1
+
+--- 1 row(s) selected.
+>>set schema t142user1;
+
+--- SQL operation complete.
+>>alter table user1 add constraint u1_fk1 foreign key (u1_c4, u1_c1)
++> references t142user4.referencedTable (c3, c2);
+
+*** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE.
+
+--- SQL operation failed with errors.
+>>alter table user1 add constraint u1_fk2 foreign key (u1_c2)
++> references t142user4.referencedTable(c1);
+
+*** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE.
+
+--- SQL operation failed with errors.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>sh sqlci -i "TEST142(user2_cmds)" -u sql_user2;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER2
+
+--- 1 row(s) selected.
+>>set schema t142user2;
+
+--- SQL operation complete.
+>>alter table user2 add constraint u2_fk1 foreign key (u2_c3)
++> references t142user4.referencedTable (c4);
+
+*** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE.
+
+--- SQL operation failed with errors.
+>>alter table user2 add constraint u2_fk2 foreign key (u2_c4)
++> references t142user4.referencedTable(c5);
+
+*** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE.
+
+--- SQL operation failed with errors.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>sh sqlci -i "TEST142(user3_cmds)" -u sql_user3;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER3
+
+--- 1 row(s) selected.
+>>set schema t142user3;
+
+--- SQL operation complete.
+>>alter table user3 add constraint u3_fk1 foreign key (u3_c1)
++> references t142user4.referencedTable;
+
+*** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE.
+
+--- SQL operation failed with errors.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>sh sqlci -i "TEST142(user5_cmds)" -u sql_user5;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER5
+
+--- 1 row(s) selected.
+>>set schema t142user5;
+
+--- SQL operation complete.
+>>alter table user5 add constraint u5_fk1 foreign key (u5_c1)
++> references t142user4.referencedTable;
+
+*** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE.
+
+--- SQL operation failed with errors.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>sh sqlci -i "TEST142(user6_cmds)" -u sql_user6;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER6
+
+--- 1 row(s) selected.
+>>set schema t142user6;
+
+--- SQL operation complete.
+>>alter table user6 add constraint u6_fk1 foreign key (u6_c3)
++> references t142user4.referencedTable(c4);
+
+*** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE.
+
+--- SQL operation failed with errors.
+>>-- this case always fails
+>>alter table user6 add constraint u6_fk2 foreign key (u6_c1)
++> references t142user4.referencedTable(c2);
+
+*** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE.
+
+--- SQL operation failed with errors.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- user1, user3, & user5 can create constraints
+>>grant references on referencedTable to sql_user1;
+
+--- SQL operation complete.
+>>grant references(c2, c3) on referencedTable to sql_user1;
+
+--- SQL operation complete.
+>>grant all_dml on referencedTable to sql_user3 with grant option;
+
+--- SQL operation complete.
+>>grant references on referencedTable to sql_user5 by sql_user3;
+
+--- SQL operation complete.
+>>execute get_privs;
+
+OBJECT_NAME GRANTOR_ID GRANTEE_ID (EXPR) GRANTED_PRIVS GRANTABLE_PRIVS
+-------------------------------------------------------------------------------- -------------------- -------------------- ------ -------------------- --------------------
+
+REFERENCEDTABLE -2 33337 all SIDU-R- SIDU-R-
+REFERENCEDTABLE 33336 33338 all -----R- NONE
+REFERENCEDTABLE 33337 33334 1 -----R- NONE
+REFERENCEDTABLE 33337 33334 2 -----R- NONE
+REFERENCEDTABLE 33337 33334 all -----R- NONE
+REFERENCEDTABLE 33337 33336 all SIDU-R- SIDU-R-
+T142_L1 -2 33337 all ---UG-- ---UG--
+T142_TRANSLATEPRIVSB -2 33337 all ------E ------E
+T142_TRANSLATEPRIVSB 33337 -1 all ------E NONE
+USER1 -2 33334 all SIDU-R- SIDU-R-
+USER2 -2 33335 all SIDU-R- SIDU-R-
+USER3 -2 33336 all SIDU-R- SIDU-R-
+USER5 -2 33338 all SIDU-R- SIDU-R-
+USER6 -2 33339 all SIDU-R- SIDU-R-
+
+--- 14 row(s) selected.
+>>
+>>sh sqlci -i "TEST142(user1_cmds)" -u sql_user1;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER1
+
+--- 1 row(s) selected.
+>>set schema t142user1;
+
+--- SQL operation complete.
+>>alter table user1 add constraint u1_fk1 foreign key (u1_c4, u1_c1)
++> references t142user4.referencedTable (c3, c2);
+
+--- SQL operation complete.
+>>alter table user1 add constraint u1_fk2 foreign key (u1_c2)
++> references t142user4.referencedTable(c1);
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>sh sqlci -i "TEST142(user3_cmds)" -u sql_user3;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER3
+
+--- 1 row(s) selected.
+>>set schema t142user3;
+
+--- SQL operation complete.
+>>alter table user3 add constraint u3_fk1 foreign key (u3_c1)
++> references t142user4.referencedTable;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>sh sqlci -i "TEST142(user5_cmds)" -u sql_user5;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER5
+
+--- 1 row(s) selected.
+>>set schema t142user5;
+
+--- SQL operation complete.
+>>alter table user5 add constraint u5_fk1 foreign key (u5_c1)
++> references t142user4.referencedTable;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- first time, user2 can only create one, second time it works
+>>grant references (c5) on referencedTable to sql_user2;
+
+--- SQL operation complete.
+>>sh sqlci -i "TEST142(user2_cmds)" -u sql_user2;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER2
+
+--- 1 row(s) selected.
+>>set schema t142user2;
+
+--- SQL operation complete.
+>>alter table user2 add constraint u2_fk1 foreign key (u2_c3)
++> references t142user4.referencedTable (c4);
+
+*** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE.
+
+--- SQL operation failed with errors.
+>>alter table user2 add constraint u2_fk2 foreign key (u2_c4)
++> references t142user4.referencedTable(c5);
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>grant references (c4) on referencedTable to sql_user2 with grant option;
+
+--- SQL operation complete.
+>>sh sqlci -i "TEST142(user2_cmds)" -u sql_user2;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER2
+
+--- 1 row(s) selected.
+>>set schema t142user2;
+
+--- SQL operation complete.
+>>alter table user2 add constraint u2_fk1 foreign key (u2_c3)
++> references t142user4.referencedTable (c4);
+
+--- SQL operation complete.
+>>alter table user2 add constraint u2_fk2 foreign key (u2_c4)
++> references t142user4.referencedTable(c5);
+
+*** ERROR[1043] Constraint TRAFODION.T142USER2.U2_FK2 already exists.
+
+--- SQL operation failed with errors.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- user6 can create first but not second constraint
+>>grant references (c4) on referencedTable to sql_user6 by sql_user2;
+
+--- SQL operation complete.
+>>sh sqlci -i "TEST142(user6_cmds)" -u sql_user6;
+>>values (user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER6
+
+--- 1 row(s) selected.
+>>set schema t142user6;
+
+--- SQL operation complete.
+>>alter table user6 add constraint u6_fk1 foreign key (u6_c3)
++> references t142user4.referencedTable(c4);
+
+--- SQL operation complete.
+>>-- this case always fails
+>>alter table user6 add constraint u6_fk2 foreign key (u6_c1)
++> references t142user4.referencedTable(c2);
+
+*** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE.
+
+--- SQL operation failed with errors.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>obey TEST142(test_revokes);
+>>-- ============================================================================
+>>-- verify that revoking privileges handle REFERENCES privilege correctly
+>>-- ============================================================================
+>>set schema t142user4;
+
+--- SQL operation complete.
+>>execute get_privs;
+
+OBJECT_NAME GRANTOR_ID GRANTEE_ID (EXPR) GRANTED_PRIVS GRANTABLE_PRIVS
+-------------------------------------------------------------------------------- -------------------- -------------------- ------ -------------------- --------------------
+
+REFERENCEDTABLE -2 33337 all SIDU-R- SIDU-R-
+REFERENCEDTABLE 33335 33339 3 -----R- NONE
+REFERENCEDTABLE 33336 33338 all -----R- NONE
+REFERENCEDTABLE 33337 33334 1 -----R- NONE
+REFERENCEDTABLE 33337 33334 2 -----R- NONE
+REFERENCEDTABLE 33337 33334 all -----R- NONE
+REFERENCEDTABLE 33337 33335 3 -----R- -----R-
+REFERENCEDTABLE 33337 33335 4 -----R- NONE
+REFERENCEDTABLE 33337 33336 all SIDU-R- SIDU-R-
+T142_L1 -2 33337 all ---UG-- ---UG--
+T142_TRANSLATEPRIVSB -2 33337 all ------E ------E
+T142_TRANSLATEPRIVSB 33337 -1 all ------E NONE
+USER1 -2 33334 all SIDU-R- SIDU-R-
+USER2 -2 33335 all SIDU-R- SIDU-R-
+USER3 -2 33336 all SIDU-R- SIDU-R-
+USER5 -2 33338 all SIDU-R- SIDU-R-
+USER6 -2 33339 all SIDU-R- SIDU-R-
+
+--- 17 row(s) selected.
+>>
+>>-- unable to revoke because of u1_fk2
+>>revoke references on referencedTable from sql_user1;
+
+*** ERROR[1025] Request failed. Dependent object TRAFODION."T142USER1"."U1_FK2" exists.
+
+--- SQL operation failed with errors.
+>>-- revoke succeeds because user2 has references at the object level
+>>revoke references (c2, c3) on referencedTable from sql_user1;
+
+--- SQL operation complete.
+>>
+>>-- remove u1_fk2 and retry
+>>alter table t142user1.user1 drop constraint u1_fk2;
+
+--- SQL operation complete.
+>>grant references (c2, c3) on referencedTable to sql_user1;
+
+--- SQL operation complete.
+>>
+>>-- now able to revoke references privilege
+>>revoke references on referencedTable from sql_user1;
+
+--- SQL operation complete.
+>>-- but not able to remove column privileges
+>>revoke references (c2) on referencedTable from sql_user1;
+
+*** ERROR[1025] Request failed. Dependent object TRAFODION."T142USER1"."U1_FK1" exists.
+
+--- SQL operation failed with errors.
+>>revoke references (c3) on referencedTable from sql_user1;
+
+*** ERROR[1025] Request failed. Dependent object TRAFODION."T142USER1"."U1_FK1" exists.
+
+--- SQL operation failed with errors.
+>>revoke references (c2, c3) on referencedTable from sql_user1;
+
+*** ERROR[1025] Request failed. Dependent object TRAFODION."T142USER1"."U1_FK1" exists.
+
+*** ERROR[1025] Request failed. Dependent object TRAFODION."T142USER1"."U1_FK1" exists.
+
+--- SQL operation failed with errors.
+>>
+>>-- and vice versa
+>>grant references on referencedTable to sql_user1;
+
+--- SQL operation complete.
+>>-- can revoke
+>>revoke references (c2) on referencedTable from sql_user1;
+
+--- SQL operation complete.
+>>revoke references (c3) on referencedTable from sql_user1;
+
+--- SQL operation complete.
+>>-- cannot revoke
+>>revoke references on referencedTable from sql_user1;
+
+*** ERROR[1025] Request failed. Dependent object TRAFODION."T142USER1"."U1_FK1" exists.
+
+*** ERROR[1025] Request failed. Dependent object TRAFODION."T142USER1"."U1_FK1" exists.
+
+--- SQL operation failed with errors.
+>>
+>>-- drop constraint and revoke succeeds
+>>alter table t142user1.user1 drop constraint u1_fk1;
+
+--- SQL operation complete.
+>>revoke references on referencedTable from sql_user1;
+
+--- SQL operation complete.
+>>
+>>log;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/392031a3/core/sql/regress/privs2/TEST135
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs2/TEST135 b/core/sql/regress/privs2/TEST135
index fb71235..1c15345 100755
--- a/core/sql/regress/privs2/TEST135
+++ b/core/sql/regress/privs2/TEST135
@@ -40,7 +40,6 @@ sh rm -f LOG135-SECONDARY;
obey TEST135(set_up);
obey TEST135(tbl_tests);
obey TEST135(view_tests);
-obey TEST135(constraint_tests);
obey TEST135(udr_tests);
obey TEST135(negative_tests);
log;
@@ -238,48 +237,6 @@ drop table t135_t2 cascade;
drop role t135_role1;
drop role t135_role2;
-?section constraint_tests
--- ============================================================================
-set schema t135sch;
-create table t135_t1 (c1 int not null primary key, c2 int);
-
--- have user3 create some objects
-sh sqlci -i "TEST135(user3_objects)" -u sql_user3;
-
--- now have user3 create an RI constraint
--- fails because user3 has no privs
-sh sqlci -i "TEST135(user3_constraint)" -u sql_user3;
-grant references on t135_t1 to sql_user3;
-showddl t135_t1;
--- operation should succeed
-sh sqlci -i "TEST135(user3_constraint)" -u sql_user3;
-
--- now try to revoke references privileges
-revoke references on t135_t1 from sql_user3;
-sh sqlci -i "TEST135(user3_drops)" -u sql_user3;
-revoke references on t135_t1 from sql_user3;
-showddl t135_t1;
-
--- repeat, but grant all privileges instead.
--- have user3 create some objects
-sh sqlci -i "TEST135(user3_objects)" -u sql_user3;
-
--- now have user3 create an RI constraint
--- fails because user3 has no privs
-sh sqlci -i "TEST135(user3_constraint)" -u sql_user3;
-grant all on t135_t1 to sql_user3;
-showddl t135_t1;
--- operation should succeed
-sh sqlci -i "TEST135(user3_constraint)" -u sql_user3;
-
--- now try to revoke references privileges
-revoke references on t135_t1 from sql_user3;
-sh sqlci -i "TEST135(user3_drops)" -u sql_user3;
-revoke references on t135_t1 from sql_user3;
-showddl t135_t1;
-
-drop table t135_t1 cascade;
-
?section udr_tests
-- ============================================================================
-- To create a function/table_mapping function, or SPJ, you need
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/392031a3/core/sql/regress/privs2/TEST142
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs2/TEST142 b/core/sql/regress/privs2/TEST142
new file mode 100755
index 0000000..8cf7f3c
--- /dev/null
+++ b/core/sql/regress/privs2/TEST142
@@ -0,0 +1,280 @@
+-- ============================================================================
+-- TEST142 - tests grant and revoke privileges for RI constraints
+--
+-- @@@ 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 @@@
+--
+-- ============================================================================
+
+cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+obey TEST142(clean_up);
+log LOG142 clear;
+obey TEST142(create_db);
+obey TEST142(set_up);
+obey TEST142(test_grants);
+obey TEST142(test_revokes);
+log;
+obey TEST142(clean_up);
+exit;
+
+?section clean_up
+-- drop database
+drop schema if exists t142user1 cascade;
+drop schema if exists t142user2 cascade;
+drop schema if exists t142user3 cascade;
+drop schema if exists t142user4 cascade;
+drop schema if exists t142user5 cascade;
+drop schema if exists t142user6 cascade;
+
+?section create_db
+create schema t142user1 authorization sql_user1;
+create schema t142user2 authorization sql_user2;
+create schema t142user3 authorization sql_user3;
+create schema t142user4 authorization sql_user4;
+create schema t142user5 authorization sql_user5;
+create schema t142user6 authorization sql_user6;
+
+set schema t142user4;
+create table referencedTable
+( c1 int not null primary key,
+ c2 int not null,
+ c3 char(10) not null,
+ c4 char(10) not null,
+ c5 largeint not null );
+alter table referencedTable add constraint u1 unique (c3, c2);
+alter table referencedTable add constraint u2 unique (c4);
+alter table referencedTable add constraint u3 unique(c5);
+showddl referencedTable;
+
+-- compile cpp program for function
+sh rm -f ./etest140.dll;
+sh sh $$scriptsdir$$/tools/dll-compile.ksh etest140.cpp
+ 2>&1 | tee LOG140-SECONDARY;
+set pattern $$DLL$$ etest140.dll;
+set pattern $$QUOTE$$ '''';
+
+-- create the library and udf
+create library t142_l1 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$ ;
+create function t142_translatePrivsBitmap(bitmap largeint) returns (bitmap_string char (20))
+language c parameter style sql external name 'translateBitmap'
+library t142_l1
+deterministic no sql final call allow any parallelism state area size 1024 ;
+grant execute on function t142_translatePrivsBitmap to "PUBLIC";
+
+set schema t142user1;
+create table user1
+(u1_c1 int not null,
+ u1_c2 int not null primary key,
+ u1_c3 char(10) not null,
+ u1_c4 char(10));
+showddl user1;
+
+set schema t142user2;
+create table user2
+(u2_c1 int not null,
+ u2_c2 int not null primary key,
+ u2_c3 char(10) not null,
+ u2_c4 largeint);
+showddl user2;
+
+set schema t142user3;
+create table user3
+(u3_c1 int not null,
+ u3_c2 int not null primary key,
+ u3_c3 char(10) not null,
+ u3_c4 char(10));
+showddl user3;
+
+set schema t142user5;
+create table user5
+(u5_c1 int not null,
+ u5_c2 int not null primary key,
+ u5_c3 char(10) not null,
+ u5_c4 char(10));
+showddl user5;
+
+set schema t142user6;
+create table user6
+(u6_c1 int not null,
+ u6_c2 int not null primary key,
+ u6_c3 char(10) not null,
+ u6_c4 char(10));
+showddl user6;
+
+?section set_up
+set schema "_PRIVMGR_MD_";
+prepare get_privs from
+select distinct
+ trim(substring (o.object_name,1,20)) as object_name,
+ grantor_id, grantee_id, 'all',
+ t142user4.t142_translatePrivsBitmap(privileges_bitmap) as granted_privs,
+ t142user4.t142_translatePrivsBitmap(grantable_bitmap) as grantable_privs
+from object_privileges p, "_MD_".objects o
+where p.object_uid in
+ (select object_uid
+ from "_MD_".objects
+ where schema_name like 'T142USER%'
+ and object_name not like 'SB_%')
+ and p.object_uid = o.object_uid
+union
+ (select distinct
+ trim(substring (o.object_name,1,20)) as object_name,
+ grantor_id, grantee_id, cast (column_number as char(3)),
+ t142user4.t142_translatePrivsBitmap(privileges_bitmap) as granted_privs,
+ t142user4.t142_translatePrivsBitmap(grantable_bitmap) as grantable_privs
+ from column_privileges p, "_MD_".objects o
+ where p.object_uid in
+ (select object_uid
+ from "_MD_".objects
+ where schema_name like 'T142USER%'
+ and object_name not like 'SB_%')
+ and p.object_uid = o.object_uid)
+order by 1, 2, 3, 4
+;
+
+?section test_grants
+-- =================================================================
+-- this set of tests run basic grant tests for constraints
+-- schema t142user4 contains the referenced table
+-- =================================================================
+set schema t142user4;
+
+-- all should fail, no one has permissions
+execute get_privs;
+sh sqlci -i "TEST142(user1_cmds)" -u sql_user1;
+sh sqlci -i "TEST142(user2_cmds)" -u sql_user2;
+sh sqlci -i "TEST142(user3_cmds)" -u sql_user3;
+sh sqlci -i "TEST142(user5_cmds)" -u sql_user5;
+sh sqlci -i "TEST142(user6_cmds)" -u sql_user6;
+
+-- user1, user3, & user5 can create constraints
+grant references on referencedTable to sql_user1;
+grant references(c2, c3) on referencedTable to sql_user1;
+grant all_dml on referencedTable to sql_user3 with grant option;
+grant references on referencedTable to sql_user5 by sql_user3;
+execute get_privs;
+
+sh sqlci -i "TEST142(user1_cmds)" -u sql_user1;
+sh sqlci -i "TEST142(user3_cmds)" -u sql_user3;
+sh sqlci -i "TEST142(user5_cmds)" -u sql_user5;
+
+-- first time, user2 can only create one, second time it works
+grant references (c5) on referencedTable to sql_user2;
+sh sqlci -i "TEST142(user2_cmds)" -u sql_user2;
+grant references (c4) on referencedTable to sql_user2 with grant option;
+sh sqlci -i "TEST142(user2_cmds)" -u sql_user2;
+
+-- user6 can create first but not second constraint
+grant references (c4) on referencedTable to sql_user6 by sql_user2;
+sh sqlci -i "TEST142(user6_cmds)" -u sql_user6;
+
+?section test_revokes
+-- ============================================================================
+-- verify that revoking privileges handle REFERENCES privilege correctly
+-- ============================================================================
+set schema t142user4;
+execute get_privs;
+
+-- unable to revoke because of u1_fk2
+revoke references on referencedTable from sql_user1;
+-- revoke succeeds because user2 has references at the object level
+revoke references (c2, c3) on referencedTable from sql_user1;
+
+-- remove u1_fk2 and retry
+alter table t142user1.user1 drop constraint u1_fk2;
+grant references (c2, c3) on referencedTable to sql_user1;
+
+-- now able to revoke references privilege
+revoke references on referencedTable from sql_user1;
+-- but not able to remove column privileges
+revoke references (c2) on referencedTable from sql_user1;
+revoke references (c3) on referencedTable from sql_user1;
+revoke references (c2, c3) on referencedTable from sql_user1;
+
+-- and vice versa
+grant references on referencedTable to sql_user1;
+-- can revoke
+revoke references (c2) on referencedTable from sql_user1;
+revoke references (c3) on referencedTable from sql_user1;
+-- cannot revoke
+revoke references on referencedTable from sql_user1;
+
+-- drop constraint and revoke succeeds
+alter table t142user1.user1 drop constraint u1_fk1;
+revoke references on referencedTable from sql_user1;
+
+?section user1_cmds
+-- ============================================================================
+-- creates RI constraints on referenced tables
+-- ============================================================================
+log LOG142;
+values (user);
+set schema t142user1;
+alter table user1 add constraint u1_fk1 foreign key (u1_c4, u1_c1)
+ references t142user4.referencedTable (c3, c2);
+alter table user1 add constraint u1_fk2 foreign key (u1_c2)
+ references t142user4.referencedTable(c1);
+
+?section user2_cmds
+-- ============================================================================
+-- creates RI constraints on referenced tables
+-- ============================================================================
+log LOG142;
+values (user);
+set schema t142user2;
+alter table user2 add constraint u2_fk1 foreign key (u2_c3)
+ references t142user4.referencedTable (c4);
+alter table user2 add constraint u2_fk2 foreign key (u2_c4)
+ references t142user4.referencedTable(c5);
+
+?section user3_cmds
+-- ============================================================================
+-- creates RI constraints on referenced tables
+-- ============================================================================
+log LOG142;
+values (user);
+set schema t142user3;
+alter table user3 add constraint u3_fk1 foreign key (u3_c1)
+ references t142user4.referencedTable;
+
+?section user5_cmds
+-- ============================================================================
+-- creates RI constraints on referenced tables
+-- ============================================================================
+log LOG142;
+values (user);
+set schema t142user5;
+alter table user5 add constraint u5_fk1 foreign key (u5_c1)
+ references t142user4.referencedTable;
+
+?section user6_cmds
+-- ============================================================================
+-- creates RI constraints on referenced tables
+-- ============================================================================
+log LOG142;
+values (user);
+set schema t142user6;
+alter table user6 add constraint u6_fk1 foreign key (u6_c3)
+ references t142user4.referencedTable(c4);
+-- this case always fails
+alter table user6 add constraint u6_fk2 foreign key (u6_c1)
+ references t142user4.referencedTable(c2);
+
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/392031a3/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp b/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp
index 4f8d7a7..6def978 100644
--- a/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp
+++ b/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp
@@ -6160,6 +6160,9 @@ void CmpSeabaseDDL::alterSeabaseTableAddRIConstraint(
}
// User must have REFERENCES privilege on the referenced table
+ // First check for REFERENCES at the object level (column checks happen
+ // later)
+ NABoolean noObjPriv = FALSE;
if (isAuthorizationEnabled())
{
PrivMgrUserPrivs* privs = refdNaTable->getPrivInfo();
@@ -6175,15 +6178,7 @@ void CmpSeabaseDDL::alterSeabaseTableAddRIConstraint(
}
if (!ComUser::isRootUserID() && !privs->hasReferencePriv())
- {
- *CmpCommon::diags() << DgSqlCode(-CAT_NOT_AUTHORIZED);
-
- deallocEHI(ehi);
-
- processReturn();
-
- return;
- }
+ noObjPriv = TRUE;
}
ElemDDLColNameArray &ringCols = alterAddConstraint->getConstraint()->castToElemDDLConstraintRI()->getReferencingColumns();
@@ -6309,6 +6304,23 @@ void CmpSeabaseDDL::alterSeabaseTableAddRIConstraint(
return;
}
+
+ // If the user/role does not have REFERENCES privilege at the object
+ // level, check to see if the user/role has been granted the privilege
+ // on all affected columns
+ if (noObjPriv)
+ {
+ PrivMgrUserPrivs* privs = refdNaTable->getPrivInfo();
+ if (!privs->hasColReferencePriv(refdNAC->getPosition()))
+ {
+ *CmpCommon::diags() << DgSqlCode(-4481)
+ << DgString0("REFERENCES")
+ << DgString1(referencedTableName.getObjectNamePart().getExternalName().data());
+
+ processReturn();
+ return;
+ }
+ }
}
// method getCorrespondingConstraint expects an empty input list if there are no
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/392031a3/core/sql/sqlcomp/PrivMgrDefs.h
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/PrivMgrDefs.h b/core/sql/sqlcomp/PrivMgrDefs.h
index 486c1f1..552f7f3 100644
--- a/core/sql/sqlcomp/PrivMgrDefs.h
+++ b/core/sql/sqlcomp/PrivMgrDefs.h
@@ -266,10 +266,10 @@ const static int32_t NBR_OF_PRIVS = NBR_DML_PRIVS+NBR_DDL_PRIVS;
//using PrivMgrBitmap = std::bitset<NBR_OF_PRIVS>;
#define PrivMgrBitmap std::bitset<NBR_OF_PRIVS>
typedef std::bitset<NBR_OF_PRIVS> PrivObjectBitmap;
-typedef std::bitset<NBR_DML_COL_PRIVS> PrivColumnBitmap;
+typedef std::bitset<NBR_OF_PRIVS> PrivColumnBitmap;
typedef std::bitset<NBR_OF_PRIVS> PrivSchemaBitmap;
typedef std::map<size_t,PrivColumnBitmap> PrivColList;
-typedef std::map<size_t,std::bitset<NBR_DML_COL_PRIVS> >::const_iterator PrivColIterator;
+typedef std::map<size_t,std::bitset<NBR_OF_PRIVS> >::const_iterator PrivColIterator;
inline bool isDMLPrivType(PrivType privType)
{
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/392031a3/core/sql/sqlcomp/PrivMgrMD.cpp
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/PrivMgrMD.cpp b/core/sql/sqlcomp/PrivMgrMD.cpp
index 250b5ff..811a71b 100644
--- a/core/sql/sqlcomp/PrivMgrMD.cpp
+++ b/core/sql/sqlcomp/PrivMgrMD.cpp
@@ -911,12 +911,13 @@ PrivStatus PrivMgrMDAdmin::getUdrsThatReferenceLibrary(
//
// method: getReferencingTablesForConstraints
//
-// This method returns the list of underlying tables that are associated with
-// RI constraints referencing an ObjectUsage.
+// This method returns the list of underlying tables and their columns that are
+// associated with RI constraints referencing an ObjectUsage.
//
// An RI constraint is a relationship between a set of columns on one table
-// with a set of columns on another table. Each set of columns must be
-// defined as an unique constraint (which include primary key constraints.
+// (the referencing table) with a set of columns on another table (the
+// referenced table). The set of columns must be defined as an unique
+// constraint (which include primary key constraints) on the referenced table.
// Relationships are stored through the constraints not their underlying tables.
//
// for example:
@@ -926,25 +927,41 @@ PrivStatus PrivMgrMDAdmin::getUdrsThatReferenceLibrary(
//
// user2:
// create table empl (empl_no int not null primary key, dept_no int, ... );
-// alter table empl add constraint empl_dept foreign key references dept;
+// alter table empl
+// add constraint empl_dept foreign key(dept_no) references dept;
//
// empl_dept is the name of the RI constraint
// The empl table references dept
// The dept table is being referenced by empl
//
-// The following query is called to get the list of tables:
-// <Gets underlying table for the foreign key's constraint>
-// select distinct o.object_uid, o.object_owner, o.create_time
-// from table_constraints t, objects o
-// where o.object_uid = t.table_uid;
-// and t.constraint_uid in
-//
-// <Gets foreign key's constraints referencing table>
-// (select foreign_constraint_uid
-// from table_constraints t, unique_ref_constr_usage u
-// where t.table_uid = objectUsage.objectUID
-// and t.constraint_uid = u.unique_constraint_uid)
-// order by o.create_time
+// The following query is called to get the list of tables and their columns:
+// <Gets underlying table and columns for the foreign key's constraint>
+// select (for referenced table)
+// objects (o): object_uid, object_owner, object_type, create_time, name,
+// list of columns (c): column_number
+// from table_constraints t, objects o, unique_ref_constr_usage u
+// (list of foreign key/unique constraint uids on referenced table) r,
+// (list of column numbers on referenced table's unique constraints) c
+// where o.object_uid = t.table_uid
+// and t.constraint_uid = r.foreign_constraint_uid
+// and r.unique_constraint_uid = c.object_uid
+// order by object_owner & create time
+//
+// <Get list of foreign key/unique constraint uids on reference table>
+// (select foreign_constraint_uid, unique_constraint_uid
+// from table_constraints t, unique_ref_constr_usage u
+// where t.table_uid = objectUsage.objectUID
+// and t.constraint_uid = u.unique_constraint_uid)
+// order by o.create_time
+//
+// <Get list of column numbers on referenced table's unique constraints>
+// (select object_uid, column_number, column_name
+// from TRAFODION."_MD_".KEYS
+// where object_uid in
+// (select unique_constraint_uid
+// from TABLE_CONSTRAINTS t,UNIQUE_REF_CONSTR_USAGE u
+// where t.table_uid = objectUsage.objectUID
+// and t.constraint_uid = u.unique_constraint_uid))
//
// input: ObjectUsage - object desiring list of referencing tables
// In the example above, this would be the DEPT table
@@ -962,29 +979,41 @@ PrivStatus PrivMgrMDAdmin::getReferencingTablesForConstraints (
trafMetadataLocation_ + ".OBJECTS o";
std::string tblConstraintsMDTable =
trafMetadataLocation_ + ".TABLE_CONSTRAINTS t";
+ std::string keysMDTable =
+ trafMetadataLocation_ + ".KEYS k";
std::string uniqueRefConstraintsMDTable =
trafMetadataLocation_ + ".UNIQUE_REF_CONSTR_USAGE u";
// Select all the constraints that are referenced by the table
- // create_time is included to order by the oldest to newest
std::string selectStmt = "select distinct o.object_uid, o.object_owner, o.object_type, o.create_time, ";
selectStmt += "trim(o.catalog_name) || '.\"' || ";
- selectStmt += "trim (o.schema_name) || '\".\"' ||";
- selectStmt += "trim (o.object_name)|| '\"' from ";
+ selectStmt += "trim (o.schema_name) || '\".\"' ||";
+ selectStmt += "trim (o.object_name)|| '\"' ";
+ selectStmt += ", c.column_number ";
+ selectStmt += "from " + uniqueRefConstraintsMDTable + std::string(", ");
selectStmt += tblConstraintsMDTable + std::string(", ") + objectsMDTable;
- selectStmt += " where o.object_uid = t.table_uid and t.constraint_uid in ";
- selectStmt += "(select foreign_constraint_uid from ";
+ selectStmt += " , (select foreign_constraint_uid, unique_constraint_uid from ";
selectStmt += tblConstraintsMDTable + std::string(", ") + uniqueRefConstraintsMDTable;
selectStmt += " where t.table_uid = " + UIDToString(objectUsage.objectUID);
- selectStmt += " and t.constraint_uid = u.unique_constraint_uid)";
- selectStmt += " order by o.create_time ";
+ selectStmt += " and t.constraint_uid = u.unique_constraint_uid) r ";
+ selectStmt += " , (select object_uid, column_number, column_name from ";
+ selectStmt += keysMDTable;
+ selectStmt += " where object_uid in (select unique_constraint_uid from ";
+ selectStmt += tblConstraintsMDTable + std::string(", ") ;
+ selectStmt += uniqueRefConstraintsMDTable + std::string(" where t.table_uid = ");
+ selectStmt += UIDToString(objectUsage.objectUID);
+ selectStmt += " and t.constraint_uid = u.unique_constraint_uid)) c ";
+ selectStmt += "where o.object_uid = t.table_uid ";
+ selectStmt += " and t.constraint_uid = r.foreign_constraint_uid ";
+ selectStmt += " and r.unique_constraint_uid = c.object_uid ";
+ selectStmt += " order by o.object_owner, o.create_time ";
ExeCliInterface cliInterface(STMTHEAP, NULL, NULL,
CmpCommon::context()->sqlSession()->getParentQid());
Queue * objectsQueue = NULL;
-// set pointer in diags area
-int32_t diagsMark = pDiags_->mark();
+ // set pointer in diags area
+ int32_t diagsMark = pDiags_->mark();
int32_t cliRC = cliInterface.fetchAllRows(objectsQueue, (char *)selectStmt.c_str(), 0, FALSE, FALSE, TRUE);
if (cliRC < 0)
@@ -1004,35 +1033,74 @@ int32_t diagsMark = pDiags_->mark();
char value[MAX_SQL_IDENTIFIER_NAME_LEN + 1];
objectsQueue->position();
+ std::vector<ColumnReference *> *columnReferences = new std::vector<ColumnReference *>;
+ ObjectReference *pObjectReference(NULL);
+ ColumnReference *pColumnReference(NULL);
+ int64_t currentObjectUID = 0;
+
// Set up an objectReference for any objects found, the caller manages
// space for this list
for (int idx = 0; idx < objectsQueue->numEntries(); idx++)
{
OutputInfo * pCliRow = (OutputInfo*)objectsQueue->getNext();
- ObjectReference *pObjectReference = new ObjectReference;
// column 0: object uid
pCliRow->get(0,ptr,len);
- pObjectReference->objectUID = *(reinterpret_cast<int64_t*>(ptr));
+ int64_t nextObjectUID = *(reinterpret_cast<int64_t*>(ptr));
- // column 1: object owner
- pCliRow->get(1,ptr,len);
- pObjectReference->objectOwner = *(reinterpret_cast<int32_t*>(ptr));
+ // If a new object uid then save previous object reference
+ // and prepare for new object reference
+ if (nextObjectUID != currentObjectUID)
+ {
+ // Save off previous reference, if previous reference exists
+ if (currentObjectUID > 0)
+ {
+ pObjectReference->columnReferences = columnReferences;
+ objectReferences.push_back(pObjectReference);
+ }
+ currentObjectUID = nextObjectUID;
- // column 2: object type
- pCliRow->get(2,ptr,len);
- strncpy(value, ptr, len);
- value[len] = 0;
- pObjectReference->objectType = ObjectLitToEnum(value);
+ // prepare for new object reference
+ pObjectReference = new ObjectReference;
+ columnReferences = new std::vector<ColumnReference *>;
- // column 3: object name
- pCliRow->get(3,ptr,len);
- strncpy(value, ptr, len);
- value[len] = 0;
- pObjectReference->objectName = value;
+ // object UID
+ pObjectReference->objectUID = nextObjectUID;
- objectReferences.push_back(pObjectReference);
+ // column 1: object owner
+ pCliRow->get(1,ptr,len);
+ pObjectReference->objectOwner = *(reinterpret_cast<int32_t*>(ptr));
+
+ // column 2: object type
+ pCliRow->get(2,ptr,len);
+ strncpy(value, ptr, len);
+ value[len] = 0;
+ pObjectReference->objectType = ObjectLitToEnum(value);
+
+ // skip create_time (column 3)
+
+ // column 4: object name
+ pCliRow->get(4,ptr,len);
+ strncpy(value, ptr, len);
+ value[len] = 0;
+ pObjectReference->objectName = value;
+
+ }
+
+ // set up the column reference
+ // column 5: column number
+ ColumnReference *pColumnReference = new ColumnReference;
+ pCliRow->get(5,ptr,len);
+ pColumnReference->columnOrdinal = *(reinterpret_cast<int32_t*>(ptr));
+
+ // add to column list
+ columnReferences->push_back(pColumnReference);
}
+
+ // Add the final object reference to list
+ pObjectReference->columnReferences = columnReferences;
+ objectReferences.push_back(pObjectReference);
+
return STATUS_GOOD;
}
@@ -1053,21 +1121,23 @@ int32_t diagsMark = pDiags_->mark();
//
// user2:
// create table empl (empl_no int not null primary key, dept_no int, ... );
-// alter table empl add constraint empl_dept foreign key references dept;
+// alter table empl add constraint empl_dept foreign key(dept_no) references dept;
//
// This method returns the constraint named empl_dept
//
// The following query is called to get the constraint name:
//
// select [first 1] object_name as referenced_constraint_name
-// from objects o, table_constraints t
+// from objects o, table_constraints t, keys k
// where o.object_uid = t.constraint_uid
// and t.table_uid = <referencing table UID>
// and t.constraint_uid in
// (select ref_constraint_uid from ref_constraints
// where unique_constraint_uid in
// (select constraint_uid from table_constraints
-// where t.table_uid = <referenced table uid>))
+// where t.table_uid = <referenced table uid>
+// and constraint_uid = k.object_uid
+// and k.column_number = <column number>))
//
// input: referencedTableUID - the referenced table
// in the example above, the is the DEPT table UID
@@ -1085,6 +1155,7 @@ int32_t diagsMark = pDiags_->mark();
bool PrivMgrMDAdmin::getConstraintName(
const int64_t referencedTableUID,
const int64_t referencingTableUID,
+ const int32_t columnNumber,
std::string &constraintName)
{
std::string objectsMDTable = trafMetadataLocation_ + ".OBJECTS o";
@@ -1092,6 +1163,8 @@ bool PrivMgrMDAdmin::getConstraintName(
trafMetadataLocation_ + ".TABLE_CONSTRAINTS t";
std::string refConstraintsMDTable =
trafMetadataLocation_ + ".REF_CONSTRAINTS u";
+ std::string keysMDTable =
+ trafMetadataLocation_ + ".KEYS k";
// select object_name based on passed in object UID
std::string quote("\"");
@@ -1103,11 +1176,12 @@ bool PrivMgrMDAdmin::getConstraintName(
selectStmt += " where o.object_uid = t.constraint_uid";
selectStmt += " and t.table_uid = " + UIDToString(referencingTableUID);
selectStmt += " and t.constraint_uid in (select ref_constraint_uid from ";
- selectStmt += refConstraintsMDTable;
+ selectStmt += refConstraintsMDTable + ", " + keysMDTable;
selectStmt += " where unique_constraint_uid in ";
selectStmt += " (select constraint_uid from " + tblConstraintsMDTable;
selectStmt += " where t.table_uid = " + UIDToString(referencedTableUID);
- selectStmt += "))";
+ selectStmt += ") and k.column_number = " + UIDToString(columnNumber);
+ selectStmt += " and unique_constraint_uid = k.object_uid) ";
ExeCliInterface cliInterface(STMTHEAP, NULL, NULL,
CmpCommon::context()->sqlSession()->getParentQid());
@@ -1146,6 +1220,7 @@ int32_t diagsMark = pDiags_->mark();
return true;
}
+
// ----------------------------------------------------------------------------
// method: compareTableDefs
//
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/392031a3/core/sql/sqlcomp/PrivMgrMD.h
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/PrivMgrMD.h b/core/sql/sqlcomp/PrivMgrMD.h
index 97bf5a7..c61d49a 100644
--- a/core/sql/sqlcomp/PrivMgrMD.h
+++ b/core/sql/sqlcomp/PrivMgrMD.h
@@ -29,6 +29,7 @@
#include "PrivMgrDefs.h"
#include "PrivMgr.h"
#include "PrivMgrDesc.h"
+#include "PrivMgrMDTable.h"
#include "ComSmallDefs.h"
#include "CmpSeabaseDDLauth.h"
@@ -36,6 +37,7 @@
class Queue;
class ExeCliInterface;
class OutputInfo;
+
#ifndef Lng32
typedef int Lng32;
#endif
@@ -56,35 +58,29 @@ typedef int Lng32;
// -----------------------------------------------------------------------
class ObjectPrivsRow;
class PrivMgrMDAdmin;
+class ObjectReference;
+class ObjectUsage;
// -----------------------------------------------------------------------
// Struct definitions
// -----------------------------------------------------------------------
-typedef struct {
- int64_t objectUID;
- int32_t granteeID;
- bool grantorIsSystem;
- std::string objectName;
- ComObjectType objectType;
- PrivMgrDesc originalPrivs;
- PrivMgrDesc updatedPrivs;
+struct ColumnReference {
+ int32_t columnOrdinal;
+ PrivMgrCoreDesc originalPrivs;
+ PrivMgrCoreDesc updatedPrivs;
+ ColumnReference()
+ : columnOrdinal(-1),
+ originalPrivs(),
+ updatedPrivs(){};
void describe (std::string &details) const
{
- details = "object usage - type is ";
- char objectTypeLit[3] = {0};
- strncpy(objectTypeLit,PrivMgr::ObjectEnumToLit(objectType),2);
- details += objectTypeLit;
- details += ", UID is ";
- details += to_string((long long int) objectUID);
- details += ", name is ";
- details += objectName;
- details += ", grantee is ";
- details += to_string((long long int) granteeID);
- details += ", is owner ";
- details += (grantorIsSystem) ? "true " : "false ";
+ details = "column usage ";
+ details += " column number is ";
+ details += to_string((long long int) columnOrdinal);
}
-} ObjectUsage;
+
+};
typedef struct {
int64_t viewUID;
@@ -110,12 +106,35 @@ typedef struct {
} ViewUsage;
-typedef struct {
+// -----------------------------------------------------------------------
+// Class definitions
+// -----------------------------------------------------------------------
+class ObjectReference
+{
+ public:
+
+ ObjectReference()
+ : objectUID(0),
+ objectOwner(NA_UserIdDefault),
+ objectType(COM_UNKNOWN_OBJECT),
+ columnReferences(NULL),
+ updatedPrivs()
+ {}
+
+ virtual ~ObjectReference ( void )
+ {
+ while(!columnReferences->empty())
+ delete columnReferences->back(), columnReferences->pop_back();
+ delete columnReferences;
+ }
+
int64_t objectUID;
int32_t objectOwner;
ComObjectType objectType;
std::string objectName;
+ std::vector<ColumnReference *> *columnReferences;
PrivMgrDesc updatedPrivs;
+
void describe (std::string &details) const
{
details = "object reference - type is ";
@@ -130,7 +149,70 @@ typedef struct {
details += to_string((long long int) objectOwner);
}
-} ObjectReference;
+};
+
+class ObjectUsage
+{
+ public:
+
+ ObjectUsage()
+ : objectUID (0),
+ granteeID (NA_UserIdDefault),
+ grantorIsSystem(false),
+ objectType (COM_UNKNOWN_OBJECT),
+ columnReferences(NULL),
+ originalPrivs(),
+ updatedPrivs()
+ {}
+
+ virtual ~ObjectUsage ( void )
+ {
+ if (columnReferences)
+ {
+ while(!columnReferences->empty())
+ delete columnReferences->back(), columnReferences->pop_back();
+ delete columnReferences;
+ }
+ }
+
+ int64_t objectUID;
+ int32_t granteeID;
+ bool grantorIsSystem;
+ std::string objectName;
+ ComObjectType objectType;
+ std::vector<ColumnReference *> *columnReferences;
+ PrivMgrDesc originalPrivs;
+ PrivMgrDesc updatedPrivs;
+
+ ColumnReference * findColumn (int32_t columnOrdinal)
+ {
+ if (columnReferences == NULL)
+ return NULL;
+ for (int i = 0; i < columnReferences->size(); i++)
+ {
+ ColumnReference *pRef = (*columnReferences)[i];
+ if (pRef->columnOrdinal == columnOrdinal)
+ return pRef;
+ }
+ return NULL;
+ }
+
+ void describe (std::string &details) const
+ {
+ details = "object usage - type is ";
+ char objectTypeLit[3] = {0};
+ strncpy(objectTypeLit,PrivMgr::ObjectEnumToLit(objectType),2);
+ details += objectTypeLit;
+ details += ", UID is ";
+ details += to_string((long long int) objectUID);
+ details += ", name is ";
+ details += objectName;
+ details += ", grantee is ";
+ details += to_string((long long int) granteeID);
+ details += ", is owner ";
+ details += (grantorIsSystem) ? "true " : "false ";
+ }
+};
// ****************************************************************************
@@ -178,6 +260,7 @@ class PrivMgrMDAdmin : public PrivMgr
bool getConstraintName(
const int64_t referencedTableUID,
const int64_t referencingTableUID,
+ const int32_t columnNumber,
std::string &referencingTable);
PrivStatus getObjectsThatViewReferences (