Clone
Roberta Marton <roberta.marton@apache.org>
committed
on 29 Feb 16
TRAFODION-1856: Revoke - object and column privilege checks not integrated for constraints
Today, when revoking the object REFERENCES privil… Show more
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.

Show less

default + 7 more