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.
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.