[ http://issues.apache.org/jira/browse/DERBY-1330?page=all ]
Mamta A. Satoor updated DERBY-1330:
-----------------------------------
Attachment: Derby1330PrivilegeCollectionV2diff.txt
Derby1330PrivilegeCollectionV2stat.txt
I am attaching an updated patch(Derby1330ViewPrivilegeCollectionV2diff.txt)
which includes privilege collection for views, constraints and triggers. The
earlier patch (Derby1330ViewPrivilegeCollectionV1diff.txt) had privilege
collection changes for views only. The new patch is a superset of the earlier
patch and hence, if anyone has spent time reviewing the earlier patch, that
time spent won't be a waste. I have
also changed comments around the method and field definition into java doc
comments. Additionally, I have changed the method name
isExecutingWithInvokerPrivileges to isPrivilegeCollectionRequired. Both these
changes are based on Dan's feedback on the first review package.
During view, trigger and constraint creation, in SQL Authorization mode, we
need to collect all the privileges that are required by those objects. Using
the Dependency Manager, these dependencies need to be saved in SYSDEPENDS
table. If any of those privileges are later revoked by a revoke statement, then
all the dependent objects(views, triggers and constraints) should get dropped.
The patch has 4 majors components to it.
1)If the query for create view/create trigger is accessing view/s, then Derby
engine flattens all those view/s. Once the view flattening is finished, Derby
engine goes through all the columns for the flattened parent sql and it starts
building the privilege requirement for those columns. But since views are
always accessed with definer's privileges (which means that as long as a user
has privilege to access
a view, the user can select from the view. This works even if the user does not
have direct privileges to the objects accessed by the view.), When collecting
privilege requirement for a given sql, Derby engine should not collect
privilege requirements for objects accessed by views. In order to implement
this behavior, I have added a flag to QueryTreeNode which will be set to false
if the object is going to be accessed with definer's privileges. During view
flattening, the objects accessed by view will be marked with definer
privileges. Later, when the Derby enging goes through all the columns to
collect the privilege requirements, it will check if a column is marked with
definer's privilege and if so, then do not look for privilege requirement for
that column.
2)Once the privileges have been collected for the create view/create
trigger/create constraint in the bind phase, they need to be recorded in
SYSDEPENDS table during the execution phase of the create view/create
trigger/create constraint sql. SYSDEPENDS will add one row for every privilege
requirement for the view/trigger/constraint. The DEPENDENTID will be
view/trigger/constraint descriptor and PROVIDERID has to be a unique id to
identify permission descriptor's uniquely. In order to do this,
PermissionsDescriptor needs to implement interface Provider and it needs to
have a UUID associated with it. This required addition of a UUID column in
SYSTABLEPERMS, SYSCOLPERMS and
SYSROUTINEPERMS. This UUID is required by the DependencyManager to uniquely
identify a row for a given PROVIDERID.
3)Privilege collection during trigger creation is handled in a similar fashion
as item 1. ie no privilege collection is required for objects that are accessed
by objects running with definer privileges. For all the other objects,
appropriate privilege is required by the trigger creator and those privilege
requirements will be saved in SYSDEPENDS using the dependency manager. In
addition, the trigger
creator will need TRIGGER privilege on the table on which the trigger is
getting defined. The dependency of the trigger on this TRIGGER privilege will
be saved in SYSDEPENDS too.
4)Among the different kinds of constraints, foreign key constraint is the only
constraint in Derby that can access other objects in the database. And,
anytime, an object accesses another object, appropriate privileges need to be
in place. For foreign key constraints, we need to record these privilege
dependencies in the system. This is so that when one of those privileges is
revoked, the constraint
should get dropped. The peculiar thing about constraint creation compared to
views and triggers is one can define multiple foreign key constraints for a
given table within a single sql statement. Derby collects all the privileges
required for such a sql statement but foreign key constraint does not
necessarily depend on all those privileges. eg
create table t31ConstraintTest (c311 int references mamta1.t11ConstraintTest,
c312 int references mamta2.t21ConstraintTest);
For the above sql, Derby will collect REFERENCES privilege requirements for
mamta1.t11ConstraintTest and mamta2.t21ConstraintTest. But the foreign key
constraint on c311 depends only on REFERENCES privilege requirement for
mamta1.t11ConstraintTest and foreign key constraint on c132 depenly only on
REFERENCES privilege requirements for mamta2.t21ConstraintTest. I have made
changes to CreateConstraintConstantAction.executeConstantAction such that only
the necessary privileges get recorded for each foreign key constraint in
SYSDEPENDS table.
Following classes are impacted by this change. Compared to the first patch,
there are 2 additional classes that got changed in Derby engine, when I added
privilege collection for triggers and constraints. In addition, I added more
tests to grantRevokeDDL.sql and to ProcedureTest.java The 2 additional classes
that changed in Derby enginge compared to first patch are
java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java
java/engine/org/apache/derby/impl/sql/execute/CreateConstraintConstantAction.java
I have put some comment for all the classes that got changed/added below in
this patch
java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
Has method to mark this node to run with definer privileges (this could
happen if the node is being accessed by an object(view/trigger/constraint)
running with definer's privileges. This method also marks the result column
list as running with definer privileges.
java/engine/org/apache/derby/impl/sql/compile/DDLStatementNode.java
Need to add a privilege requirement for the object access, only if the
object is running with invoker's privileges.
java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java
Has method to mark this node to run with definer privileges (this could
happen if the node is being accessed by an object (view/trigger/constraint)
running with definer's privileges. This method also marks the subquery
underneath it as running with definer privileges.
java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
Has method to mark this node to run with definer privileges (this could
happen if the node is being accessed by an object (view/trigger/constraint)
running with definer's privileges. This method also marks it's fromTableList as
running with definer privileges.
java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
This is the super class that keeps the flag which determines if the object
is getting accessed with invoker's or definer's privileges. It has methods to
setter and getter methods on the flag.
java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
Need to add a privilege requirement for the object access, only if the
object is running with invoker's privileges. In addition, it has method to mark
this node to run with definer privileges (this could happen if the node is
being accessed by an object(view/trigger/constraint) running with definer's
privileges. This method also marks the expression underneath it as running with
definer privileges.
java/engine/org/apache/derby/impl/sql/compile/PrivilegeNode.java
Grant was activated only on base tables in this class. I changed the code
to allow grant on views along with the base tables.
java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
Need to add a privilege requirement for the object access, only if the
object is running with invoker's privileges.
java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java
Need to add a privilege requirement for the object access, only if the
object is running with invoker's privileges.
java/engine/org/apache/derby/impl/sql/compile/CreateSchemaNode.java
Need to add a privilege requirement for the object access, only if the
object is running with invoker's privileges.
java/engine/org/apache/derby/impl/sql/compile/JavaToSQLValueNode.java
Has method to mark this node to run with definer privileges (this could
happen if the node is being accessed by an object (view/trigger/constraint)
running with definer's privileges. This method also marks it's javaNode as
running with definer privileges.
java/engine/org/apache/derby/impl/sql/compile/FromList.java
Need to add a privilege requirement for the object access, only if the
object is running with invoker's privileges.
java/engine/org/apache/derby/impl/sql/compile/FKConstraintDefinitionNode.java
Need to add a privilege requirement for the object access, only if the
object is running with invoker's privileges.
java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
If we are dealing with a view, we need to mark the query underneath it to
run with definer privileges. This will make sure that we do not collect
privilege requirements for that query.
java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
Need to add a privilege requirement for the object access, only if the
object is running with invoker's privileges.
java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
Has method to mark this node to run with definer privileges (this could
happen if the node is being accessed by an object (view/trigger/constraint)
running with definer's privileges. This method also marks all the result
columns in it as running with definer privileges.
java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java
Need to add a privilege requirement for the object access, only if the
object is running with invoker's privileges.
java/engine/org/apache/derby/impl/sql/execute/CreateViewConstantAction.java
Add rows in SYSDEPENDS to keep track of view's dependency on various
privileges. If any of those privileges are later revoked, the view will be
dropped automatically. If the view is getting created by the dba, then no
privilege dependency is created because dba has access to all the objects in
the database. If view is accessing an object that is owned by the view creator,
then no need to put any privilege dependencies on such objects. If object
accessed is not user owned, then check if there exists a privilege on that
object for this user. If yes, then save that privilege in SYSDEPENDS table as
one of the privileges required by the view. If user does not have the privilege
on the object, then there has to exist a PUBLIC level privilege on that
object. If that was not true, authorization checking for all the objects
accessed by the views at the beginning of the execution phase would have
failed. Column level privileges have a distinct behavior. For a given table, a
user can have access to some of the columns via the privileges granted to the
user explicitly. For the same table, the user may have access to some of the
volumns via the privileged granted to the PUBLIC. an eg
user1
create table t11(c11 int, c12 int);
grant select(c11) on t1 to user2;
grant select(c12) on t1 to PUBLIC;
user2
create view v1 as select c11 from user1.t11 where c12=2;
For the view above, there are 2 column level privilege depencies, one for
column c11 which exists directly for user2 and one for column c12 which exists
at PUBLIC level.
java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java
(explanation for this node is same as for CreateViewConstantAction)
java/engine/org/apache/derby/impl/sql/execute/CreateConstraintConstantAction.java
Derby allows multiple foreign key creation within a single sql statement. The
privileges collected for such a sql may end up having a superset of privileges
required for individual foreign key constraints. eg
create table t31ConstraintTest (c311 int references mamta1.t11ConstraintTest,
c312 int references mamta2.t21ConstraintTest);
For the above sql, Derby will collect REFERENCES privilege requirements for
mamta1.t11ConstraintTest and mamta2.t21ConstraintTest. But the foreign key
constraint on c311 depends only on REFERENCES privilege requirement for
mamta1.t11ConstraintTest and foreign key constraint on c132 depenly only on
REFERENCES privilege requirements for mamta2.t21ConstraintTest. The changes in
the class above makes sure that right REFERENCES privilege gets picked up for
each foreign key constraint. The logic for this went into executeConstantAction
method.
java/engine/org/apache/derby/impl/sql/catalog/SYSCOLPERMSRowFactory.java
Added a column, uuid, (similar to one in SYSCONSTRAINTS). This column will be
used as PROVIDERID in SYSDEPENDS table to keep track of
view/triiger/constraint's dependency on column level constraints.
java/engine/org/apache/derby/impl/sql/catalog/SYSROUTINEPERMSRowFactory.java
Added a column, uuid, (similar to one in SYSCONSTRAINTS). This column will be
used as PROVIDERID in SYSDEPENDS table to keep track of
view/triiger/constraint's dependency on routine level constraints.
java/engine/org/apache/derby/impl/sql/catalog/SYSTABLEPERMSRowFactory.java
Added a column, uuid, (similar to one in SYSCONSTRAINTS). This column will be
used as PROVIDERID in SYSDEPENDS table to keep track of
view/triiger/constraint's dependency on table level constraints.
java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
Add a new method getColumnPermissions which is similar to another method
by the same name in the class. But the new method accepts column privilege type
in String format. This new method is called by
ColPermsDescriptor.getDependableFinder and ColPermsDescriptor keeps privilege
type in String format.
java/engine/org/apache/derby/impl/sql/catalog/DDColumnPermissionsDependableFinder.java
Need to add a new class because a Column Permission Descriptor needs a
tableuuid and the column privilege type. The generic DDdependableFinder class
can only deal with uuid and hence need a new subclass of DDdependableFinder.
This new class is similar in nature to existing DDColumnDependableFinder
java/engine/org/apache/derby/impl/sql/catalog/DDdependableFinder.java
java/engine/org/apache/derby/impl/sql/catalog/CoreDDFinderClassInfo.java
java/engine/org/apache/derby/catalog/Dependable.java
java/engine/org/apache/derby/iapi/services/io/RegisteredFormatIds.java
java/engine/org/apache/derby/iapi/services/io/StoredFormatIds.java
Above few classes do the ground work for 3 permission descriptors so they
can be used by the existing dependency system(to populate SYSDEPENDS table) to
record view/triiger/constraint's dependency on them.
java/engine/org/apache/derby/iapi/sql/dictionary/PermissionsDescriptor.java
Permission Descriptor can now be the PROVIDER in SYSDEPENDS. ie a
view/triiger/constraint might be dependent on an object level privilege. In
order to allow this, PermissionsDescriptor implements Provider interface. Some
of the methods of the interface are implemented by subclasses on
PermissionsDescriptor. Also, each of the 3 Permission Descriptors now have a
unique uuid. Added that uuid to PermissionsDescriptor and provided
getter/setter on it. This uuid will be used as the PROVIDERID in SYSDEPENDS
table. In
addition, there is a method called checkOwner in PermissionsDescriptor which
checks if the passed authorization id is same as the owner of the object on
which the permission is defined. This gets called by create
view/triiger/constraint while storing view/triiger/constraint's dependency on
various permissions in SYSDEPENDS table. If the view/triiger/constraint and
object being accessed
is owned by the same authorization id, then no need to save
view/triiger/constraint's dependency on object privilege in SYSDEPENDS table.
java/engine/org/apache/derby/iapi/sql/dictionary/TablePermsDescriptor.java
This class is modified to implement some of the PROVIDER interface methods.
Another modification to this class was addition of checkOwner method (described
under PermissionDescriptor class)
java/engine/org/apache/derby/iapi/sql/dictionary/RoutinePermsDescriptor.java
This class is modified to implement some of the PROVIDER interface methods.
Another modification to this class was addition of checkOwner method (described
under PermissionDescriptor class)
java/engine/org/apache/derby/iapi/sql/dictionary/ColPermsDescriptor.java
This class is modified to implement some of the PROVIDER interface methods.
Another modification to this class was addition of checkOwner method (described
under PermissionDescriptor class)
java/engine/org/apache/derby/iapi/sql/dictionary/DataDescriptorGenerator.java
Three methods in this class are modified to throw StandardException in their
signature because they call constructor's of PermissionDescriptor subclasses
and those constructors can throw StandardException.
java/engine/org/apache/derby/iapi/sql/dictionary/StatementPermission.java
java/engine/org/apache/derby/iapi/sql/dictionary/StatementTablePermission.java
java/engine/org/apache/derby/iapi/sql/dictionary/StatementRoutinePermission.java
java/engine/org/apache/derby/iapi/sql/dictionary/StatementSchemaPermission.java
java/engine/org/apache/derby/iapi/sql/dictionary/StatementColumnPermission.java
In the classes above, added a method called getPermissionDescriptor which
will return the PermissionDescriptor for the passed authorization id for the
StatementPermission object. This method gets during the execution phase of
create view/trigger/constraint to track view/trigger/constraint's dependency on
various permissions. getPermissionDescriptor for StatementSchemaPermission
always returns null because views/triggers/constraints do not need to keep
track of schema level privileges. They only need table, column and routine
privileges as part of view/trigger/constraint dependency. They do need to have
create permission is the schema, but once the view/trigger/constraint is
created in that schema, there is no need to keep that schema permission in
SYSDEPENDS. Special code is required to track column level privileges. It is
possible that some column level privileges are available to the passed
authorizer id but the rest required column level privileges are available at
PUBLIC level. In order to record
view/trigger/constraint dependency on user level and public level column
privileges, I needed to add method getPUBLIClevelColPermsDescriptor to
StatementColumnPermission.
Test changes in following files
java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/syscat.out
java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/syscat.out
java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk14/syscat.out
java/testing/org/apache/derbyTesting/functionTests/master/syscat.out
java/testing/org/apache/derbyTesting/functionTests/util/ProcedureTest.java
I have attached the svn stat -q(Derby1330PrivilegeCollectionV2stat.txt) and svn
diff(Derby1330PrivilegeCollectionV2diff.txt) output to DERBY-1330.
As always, any feedback highly appreciated.
> Provide runtime privilege checking for grant/revoke functionality
> -----------------------------------------------------------------
>
> Key: DERBY-1330
> URL: http://issues.apache.org/jira/browse/DERBY-1330
> Project: Derby
> Type: Sub-task
> Components: SQL
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Mamta A. Satoor
> Attachments: AuthorizationModelForDerbySQLStandardAuthorization.html,
> AuthorizationModelForDerbySQLStandardAuthorizationV2.html,
> Derby1330PrivilegeCollectionV2diff.txt,
> Derby1330PrivilegeCollectionV2stat.txt,
> Derby1330ViewPrivilegeCollectionV1diff.txt,
> Derby1330ViewPrivilegeCollectionV1stat.txt
>
> Additional work needs to be done for grant/revoke to make sure that only
> users with required privileges can access various database objects. In order
> to do that, first we need to collect the privilege requirements for various
> database objects and store them in SYS.SYSREQUIREDPERM. Once we have this
> information then when a user tries to access an object, the required
> SYS.SYSREQUIREDPERM privileges for the object will be checked against the
> user privileges in SYS.SYSTABLEPERMS, SYS.SYSCOLPERMS and
> SYS.SYSROUTINEPERMS. The database object access will succeed only if the user
> has the necessary privileges.
> SYS.SYSTABLEPERMS, SYS.SYSCOLPERMS and SYS.SYSROUTINEPERMS are already
> populated by Satheesh's work on DERBY-464. But SYS.SYSREQUIREDPERM doesn't
> have any information in it at this point and hence no runtime privilege
> checking is getting done at this point.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
