db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject [PATCH]DERBY-1330 Collect privilege requirements for views
Date Tue, 27 Jun 2006 22:03:26 GMT
Hi,

I have been working on storing privilege requirements for view (in Derby SQL
Authorization mode only) during create view time in SYSDEPENDS table. That
patch for this work is attached as
Derby1330ViewPrivilegeCollectionV1diff.txt to DERBY-1330. This information
in SYSDEPEDNS will later be used at the revoke privilege time. All the
views that depend on the privilege being revoked will be dropped. The revoke
privilege work to drop affected views is not included in this patch.

The patch has 2 major components to it
1)If the query for create view 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 need direct privileges to the
objects accessed by the view.), 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, it
will check if the 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 in the bind
phase, they need to be recorded in SYSDEPENDS table during the execution
phase of the create view sql. SYSDEPENDS will add one row for every
privilege requirement for the view. The DEPENDENTID will be view 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.

Once this patch is approved, I will go ahead and change the functional spec
attached to DERBY-464 to add the UUID column to SYSTABLEPERMS, SYSCOLPERMS
and SYSROUTINEPERMS. In addition, I will also remove the requirement of
SYSREQUIREDPERM from the functional spec.

Following classes are impacted by this change. I have put some comment for
all the classes that got changed/added below
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/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'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'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'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'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
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 while storing view's dependency on various permissions in
SYSDEPENDS table. If the view and object being accessed is owned by the same
authorization id, then no need to save view'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 do not need to
keep track of schema level privileges. They only need table, column and
routine privileges as part of view dependency. They do need to have create
permission is the schema, but once the view 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

I have attached the svn stat -q(Derby1330ViewPrivilegeCollectionV1stat.txt)
and svn diff(Derby1330ViewPrivilegeCollectionV1diff.txt) output to
DERBY-1330.

Any feedback on the patch is greatly appreciated,
Mamta

Mime
View raw message