TRAFODION-1856: Revoke - object and column privilege checks not integrated for constraints Today, when revoking the object REFERENCES privil…
TRAFODION-1856: Revoke - object and column privilege checks not integrated for constraintsToday, when revoking the object REFERENCES privilege, the revoke fails if thereare any RI constraints that require the privilege. However, there may be columnlevel privileges that exist that would still allow the constraint to be present.Conversely, when revoking column REFERENCES privilege, the revoke does notcheck to see if REFERENCES privilege has been granted at the object level.In fact, the revoke operation does not check for dependencies on constraintscorrectly.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" becauseuser2 still has the references privileges on column dept_no. Vice versa, user1should be able to "revoke references(dept_no) on dept from user2" because user2still 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 tomanage 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 thingsbetween objects and columns easier. There is still more work to do in thisarea.There is a method called dealWithConstraints that, among other things, checksto see if the revoke can occur. Changes were made to check for column levelprivileges if object level privileges were no longer available. Revokingcolumn level privilege now calls this method to make sure the revoke canproceed.The dealWithConstraints change required updates to the query that retrievedreferenced table information. In addition to returning the referencing table,the list of referenced table columns associated with each constraint wasneeded. The column information returned was transformed into a newColumnReference structure attached to the existing ObjectUsage andObjectReference classes. Changes were also required in getConstraintName to getthe RI constraint related to the column which no longer has the privilege.In addition, the code to remove column level privileges when object levelprivileges was removed. In SQL, each grant needs a separate revoke to remove.So this code did not follow ANSI standard.