db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: SQL Authorization for dblook: Progress Update
Date Tue, 26 May 2009 15:47:05 GMT
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. I believe that the dependency ordering is this:

jarFiles < databaseProperties < functions+procedures < tables < 
constraints+views < triggers+indexes

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.
>
> 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.

Hope this helps,
-Rick
>
> Some insight on these matters would be most appreciated.
>
> Thanks,
> Hiranya
>
> -- 
> Hiranya Jayathilaka
> E-mail: hiranya@apache.org <mailto:hiranya@apache.org>;  Mobile: +94 
> 77 633 3491
> Blog: http://techfeast-hiranya.blogspot.com


Mime
View raw message