Hi Rick, On Tue, May 26, 2009 at 9:17 PM, Rick Hillegas <[email protected]>wrote:
> Hi Hiranya, > > Thanks for tackling this project. Some comments inline... > > Hiranya Jayathilaka wrote: > >> Hi Devs, >> >> I have started writing code for my GSoC project (SQL authorization support >> for dblook). To start with I developed some basic data structures to store >> the information (permissions, authorization Ids etc) regarding persistent >> database objects. Using these basic structures I've developed a class which >> will act as the blueprint of the dblook dependency graph as well as the role >> dependency graph. >> >> I've already done some coding related to the construction of the >> dependency graph. Before I go any further I would like to get a couple of >> things clarified. >> >> 1.The documentation on system tables implies that only views, constraints >> and triggers can directly depend on another persistent object. (I'm >> referring to the SYSDEPENDS table here) Have I understood that correctly? >> Won't there be any situations where, say a table, is dependent on another >> object? >> > A table can depend on a function. This is because the table could have a > generated column whose generation clause invokes a user-defined function. Do we capture these dependencies in the system tables? Can I get the information regarding these dependencies by referring the SYSDEPENDS table or some other system table? > I believe that the dependency ordering is this: > > jarFiles < databaseProperties < functions+procedures < tables < > constraints+views < triggers+indexes Thanks for mentioning this. It is indeed very helpful and answers lot of my future questions :) > > Constraints can reference other constraints and views can reference other > views. So those objects need further sub-sorting. > > If you also created relevant permissions at each level, I think that you > would end up with a script which would run correctly. +1. According to our current design, after creating each database object, all the permission grants related to that object will be carried out. So that takes care of that. > > >> 2.What is the DDL statement we should generate to create a database >> connection as a particular user and what is the statement to close it? (I'm >> currently saving authentication Ids associated with each object in the graph >> vertices and I can use that information to generate a connection >> establishment statement prior to actually creating the object.) >> > Derby doesn't have a SQL statement for creating a database connection. > Connections are JDBC objects and they are obtained from the > java.sql.DriverManager or from a javax.sql.DataSource or a > javax.sql.ConnectionPoolDataSource. Information on how to get a Connection > bound to a particular user can be found at > http://db.apache.org/derby/docs/10.5/ref/ref-single.html#rrefjdbc27433 > > The ij tool, however, does have a CONNECT statement for obtaining a > connection and giving it a name which you can reference later on: > http://db.apache.org/derby/docs/10.5/tools/tools-single.html#rtoolsijcomref22318 > > To close a java.sql.Connection, call its close() method. If you are using > the ij tool, you can use the DISCONNECT command: > http://db.apache.org/derby/docs/10.5/tools/tools-single.html#rtoolsijcomref20382 > > Bear in mind that creating a connection owned by a user implies that you > know the user's password. In general your tool will not have this > information. If you need to masquerade as a particular user, you will need > to override the authentication scheme. > > >> 3.It appears after creating a table we have to do two types of permission >> grants - Table permission grants (from SYSTABLEPERMS table) and column >> permission grants (from SYSCOLPERMS table). Is that correct? >> > Yes. Thanks for verifying this. I've already implemented this functionality for tables. After creating a particular table all the table permissions and column permissions on the table will be granted to respective authorization IDs. Thanks, Hiranya > > Hope this helps, > -Rick > >> >> Some insight on these matters would be most appreciated. >> >> Thanks, >> Hiranya >> >> -- >> Hiranya Jayathilaka >> E-mail: [email protected] <mailto:[email protected]>; Mobile: +94 77 >> 633 3491 >> Blog: http://techfeast-hiranya.blogspot.com >> > > -- Hiranya Jayathilaka Software Engineer; WSO2 Inc.; http://wso2.org E-mail: [email protected]; Mobile: +94 77 633 3491 Blog: http://techfeast-hiranya.blogspot.com
