SQL Authorization for dblook: Progress Update

[Hiranya Jayathilaka <hi...@gmail.com>]

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?

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

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?

Some insight on these matters would be most appreciated.

Thanks,
Hiranya

-- 
Hiranya Jayathilaka
E-mail: hiranya@apache.org;  Mobile: +94 77 633 3491
Blog: http://techfeast-hiranya.blogspot.com

Re: SQL Authorization for dblook: Progress Update

[Rick Hillegas <Ri...@Sun.COM>]

Dag H. Wanvik wrote:
> Hiranya Jayathilaka <hi...@gmail.com> writes:
>
>   
>>> 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 should think so.. Can you answer this one, Rick? Also, a CHECK
> constraint may depend on functions, and this is recorded in
> SYSDEPENDS.
>
> Dag
>
>
>   
Right. These dependencies (tables and constraints depending on 
functions) are tracked by SYSDEPENDS. If you approach the problem by 
tackling one level at a time (first recreate functions, then tables, 
then constraints), you won't have to worry about the SYSDEPENDS tuples 
which link the levels together. What you will need from SYSDEPENDS are 
the dependencies within a given level. E.g., the dependency of a foreign 
key constraint on a primary key constraint.

Hope this helps,
-Rick

Re: SQL Authorization for dblook: Progress Update

["Dag H. Wanvik" <Da...@Sun.COM>]

Hiranya Jayathilaka <hi...@gmail.com> writes:

>> 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 should think so.. Can you answer this one, Rick? Also, a CHECK
constraint may depend on functions, and this is recorded in
SYSDEPENDS.

Dag

Re: SQL Authorization for dblook: Progress Update

[Hiranya Jayathilaka <hi...@gmail.com>]

Hi Rick,

On Tue, May 26, 2009 at 9:17 PM, Rick Hillegas <Ri...@sun.com>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: hiranya@apache.org <ma...@apache.org>;  Mobile: +94 77
>> 633 3491
>> Blog: http://techfeast-hiranya.blogspot.com
>>
>
>


-- 
Hiranya Jayathilaka
Software Engineer;
WSO2 Inc.;  http://wso2.org
E-mail: hiranya@wso2.com;  Mobile: +94 77 633 3491
Blog: http://techfeast-hiranya.blogspot.com

Re: SQL Authorization for dblook: Progress Update

[Hiranya Jayathilaka <hi...@gmail.com>]

Hi Knut,

On Sun, May 31, 2009 at 6:38 AM, Knut Anders Hatlen <Kn...@sun.com>wrote:

> Dag.Wanvik@Sun.COM (Dag H. Wanvik) writes:
>
> > Presumably the output will be an ij script, so the ij CONNECT command
> > would be used?
> >
> > As far as password for user is needed, we need to think carefully
> > about how to solve this issue. A approach would be to leave
> > passwords open in the script to be supplied (edited in) by the data
> > base owner as the script is run, or provide the dblook tool with an
> > interface to fetch passwords for a given user.
>
> If dblook starts outputting ij CONNECT commands, we introduce an
> incompatibility, as the current output can be used unmodified to copy
> the schema over to a new database with a different name, like this:
>
> java org.apache.derby.tools.dblook -d jdbc:derby:db | java
> -Dij.database="jdbc:derby:copy;create=true" org.apache.derby.tools.ij
>
> This would stop working if "CONNECT 'jdbc:derby:db';" had been added to
> the output.


Thanks for pointing this out early.


>
>
> I agree that we should not add the passwords to the output.


I too believe that it's not a good idea to mention user passwords in the
database scripts generated by dblook. I like the idea suggested by Dag to
leave them open in the scripts to be filled in. But if adding the CONNECT
command breaks the existing functionality then I guess that's out too :(


> To keep it
> simple in the first iteration, we could perhaps just print a comment
> stating that the statements below need to be executed as user X in order
> to get the exact same dependencies as in the original database. That's
> consistent with what dblook currently does for the initial connection,
> it prints the URL in a header comment instead of printing a CONNECT
> statement.


+1. We can make this the default behavior. We can also provide a switch to
make this configurable so when a certain property is turned on, CONNECT
statements would be generated instead of the comments with passwords left
open to be filled in.

eg:
java org.apache.derby.tools.dblook -d jdbc:derby:db --> Generates comments
indicating how connections should be created and closed

java org.apache.derby.tools.dblook -d jdbc:derby:db -connectStatements on
--> Generate CONNECT/DISCONNECT statements instead of the comments

WDYT?

Thanks,
Hiranya


>
>
> If we later find an elegant solution to this problem, it should be
> fairly easy to replace the comment with whatever we come up with. But I
> don't think the lack of an elegant solution at this point is a
> showstopper, as printing the DDL in sorted order would be a great
> improvement by itself.
>
> --
> Knut Anders
>



-- 
Hiranya Jayathilaka
Software Engineer;
WSO2 Inc.;  http://wso2.org
E-mail: hiranya@wso2.com;  Mobile: +94 77 633 3491
Blog: http://techfeast-hiranya.blogspot.com

Re: SQL Authorization for dblook: Progress Update

["Dag H. Wanvik" <Da...@Sun.COM>]

Knut Anders Hatlen <Kn...@Sun.COM> writes:

> java org.apache.derby.tools.dblook -d jdbc:derby:db | java -Dij.database="jdbc:derby:copy;create=true" org.apache.derby.tools.ij
>
> This would stop working if "CONNECT 'jdbc:derby:db';" had been added to
> the output.
> I agree that we should not add the passwords to the output. To keep it
> simple in the first iteration, we could perhaps just print a comment
> stating that the statements below need to be executed as user X in order
> to get the exact same dependencies as in the original database. That's
> consistent with what dblook currently does for the initial connection,
> it prints the URL in a header comment instead of printing a CONNECT
> statement.


Right, but sooner or later we do need to be able to establish connections for
different users somehow. Emitting these in comments for the time being is
fine, I think. Maybe we will need to tweak ij a bit.

>

>
> If we later find an elegant solution to this problem, it should be
> fairly easy to replace the comment with whatever we come up with. But I
> don't think the lack of an elegant solution at this point is a
> showstopper, as printing the DDL in sorted order would be a great
> improvement by itself.

+1

Dag

Re: SQL Authorization for dblook: Progress Update

[Knut Anders Hatlen <Kn...@Sun.COM>]

Dag.Wanvik@Sun.COM (Dag H. Wanvik) writes:

> Presumably the output will be an ij script, so the ij CONNECT command
> would be used?
>
> As far as password for user is needed, we need to think carefully
> about how to solve this issue. A approach would be to leave
> passwords open in the script to be supplied (edited in) by the data
> base owner as the script is run, or provide the dblook tool with an
> interface to fetch passwords for a given user.

If dblook starts outputting ij CONNECT commands, we introduce an
incompatibility, as the current output can be used unmodified to copy
the schema over to a new database with a different name, like this:

java org.apache.derby.tools.dblook -d jdbc:derby:db | java -Dij.database="jdbc:derby:copy;create=true" org.apache.derby.tools.ij

This would stop working if "CONNECT 'jdbc:derby:db';" had been added to
the output.

I agree that we should not add the passwords to the output. To keep it
simple in the first iteration, we could perhaps just print a comment
stating that the statements below need to be executed as user X in order
to get the exact same dependencies as in the original database. That's
consistent with what dblook currently does for the initial connection,
it prints the URL in a header comment instead of printing a CONNECT
statement.

If we later find an elegant solution to this problem, it should be
fairly easy to replace the comment with whatever we come up with. But I
don't think the lack of an elegant solution at this point is a
showstopper, as printing the DDL in sorted order would be a great
improvement by itself.

-- 
Knut Anders

Re: SQL Authorization for dblook: Progress Update

["Dag H. Wanvik" <Da...@Sun.COM>]

Presumably the output will be an ij script, so the ij CONNECT command
would be used?

As far as password for user is needed, we need to think carefully
about how to solve this issue. A approach would be to leave
passwords open in the script to be supplied (edited in) by the data
base owner as the script is run, or provide the dblook tool with an
interface to fetch passwords for a given user.

Dag

Re: SQL Authorization for dblook: Progress Update

[Rick Hillegas <Ri...@Sun.COM>]

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 <ma...@apache.org>;  Mobile: +94 
> 77 633 3491
> Blog: http://techfeast-hiranya.blogspot.com