db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <derby-...@db.apache.org>
Subject [jira] Updated: (DERBY-1330) Provide runtime privilege checking for grant/revoke functionality
Date Tue, 04 Jul 2006 16:07:32 GMT
     [ 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


Mime
View raw message