db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dag.Wan...@Sun.COM (Dag H. Wanvik)
Subject Re: Wiki Page for dblook SQL Authorization Support Project
Date Wed, 13 May 2009 16:29:27 GMT

Hi Hiranya,

Hiranya Jayathilaka <hiranya911@gmail.com> writes:

> Hi Folks,
>
> I'll be using the wiki page at [1] as the documentation space for dblook SQL
> Authorization support implementation, during the design phase of the
> project. I've already updated the wiki page with some information that I've
> discussed with Dag off-line. Feel free to add your own ideas to the wiki
> page regarding this project and also feel free to discuss the matters
> mentioned in the wiki on the mailing list.
>
> Your feedback and comments are most appreciated.

Thanks for starting a wiki page for this design, Hiranya! This is a
good way to go.  I think the approach looks good.

Here are some answers to your questions and comments.

> Design
> 
> We intend to use a directed graph as the means of tracking and
> representing the dependencies among various persistent objects in a
> database. Let's call it the dblook dependency graph. When dblook is
> fired off against a database, it would create the dependency graph in
> memory and 'walk' the graph step-by-step while producing DDL
> statements required to reconstruct those objects.

To do this, we have to use several connections, one for each
authorized user that owns database objects, so that they can be
recreated with the correct owner. It could be useful to keep them open
(or you could open them on demand; slower) since object creation for
one user could be interleaved with an other user's objects, if
dependencies go in both directions between users. But this is probably
not so common that it warrants optimizing of connection openings?

> 
> All persistent objects of a database would be vertices in the
> dependency graph. If A and B are two persistent objects in a database
> and if B is a dependent object of A then there will be a directed edge
> from A to B in the dependency graph (A --> B). Information required to
> construct the dependency graph will be fetched from the system tables
> (specially from the SYSDEPENDS table which effectively captures all
> such dependencies among persistent objects). The graph construction
> algorithm should also associate each vertex with a database user as
> the owner. (what is the right way to find the object owner?)

For example, for a view, the SYSVIEWS table contains a reference to
the owning schema in the column COMPILATIONSCHEMAID.  The system table
for schemas, SYSSCHEMAS, contains AUTHORIZATIONID for each
schema. This is the owning user user (since Derby does not yet support
a schema being owned by a role, although the SQL standard does allow
that). So joining COMPILATIONSCHEMAID with SYSSCHEMAS.SCHEMAID gives
the owning user of a view. The case is similar for triggers and
constraints.

> In addition it should capture all the permissions associated with
> each object and the users involved with those permissions.

More precisely, all permissions granted for an object, be it to a
user, to a role or to PUBLIC, should be recorded with the object, so
those permissions can be granted once the object has been recreated.

If a persistent object depends on a role, that should be recorded as
well, so that the needed role can be set before recreating the object.

> 
> Once we have the full dblook dependency graph in memory we can create
> all the roles required (should be done as the dbo).

And all the role grants should be performed in this step, that is,
grant to users, to other roles and to PUBLIC. Only dbo can do these
things presently. Note that in order to do this step successfully, you
need to create a GRANT dependency graph of the roles'
interrelationships as well.

> 
> When dblook finally walks the graph it will need to produce an
> authentication statement prior to producing the actual DDL statement
> required to create each object. 

I am not sure what you mean by authentication statement here; I think
we need to open a connection per user that owns a persistent object
and use that connection to create that object. So the walk would have
to switch between the different connections as needed? In addition, if
this persistent object ("soon-to-be-created") depends on a role, the
connection should SET that role first, too, so we can make sure we
have the required permissions to create the object.

> For an example if there is an object O associated with the user U in

I assume you mean "owned by" user U here.

> the graph, dblook will first output an authentication statement for
> user U before producing the DDL statement for O. After producing the
> creation statement necessary grant statements should be created
> related to the object.

Yes, this seems the right time!

> Basically all the permissions related to the object should be
> granted to the associated users. 

.. and roles and PUBLIC.


> Walking the graph should start from a node which does not have any
> in bound edges (i.e. it does not depend on any other object). As the
> graph walk continues objects are removed from the graph along with
> all edges incident on them. The walk continues until the graph's set
> of vertices is empty.

Dependency Graph Design
> 
> The datastructures used to store the elements of the dependency graph
> should be lightweight. This graph will be held in memory and its size
> will be proportional to the number of persistent objects in the
> database. Therefore larger the database, larger the dependency
> graph. We need to be conservative in memory usage to efficiently deal
> with large databases.
> 
> Each vertex in the graph should store a set of in bound edges, set of
> out bound edges, an associated user ID (might also need to store
> authentication credentials of the user) and a set of permissions.

Yes, and you will need to store if an object depends on a role, too.
The set of permissions is those that should be granted for that object
(vertex; I presume you intend for the vertex to represent a persistent
object here?)

Dag

Mime
View raw message