GitHub user robertamarton opened a pull request:
https://github.com/apache/incubator-trafodion/pull/349
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.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/robertamarton/incubator-trafodion col-privs
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-trafodion/pull/349.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #349
----
commit 392031a304b52348aa4bbb8fe3a4e6399c70838f
Author: Roberta Marton <[email protected]>
Date: 2016-02-29T20:14:15Z
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.
----
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at [email protected] or file a JIRA ticket
with INFRA.
---
