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: [email protected] <mailto:[email protected]>; Mobile: +94
77 633 3491
Blog: http://techfeast-hiranya.blogspot.com
