db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Hiranya Jayathilaka <hiranya...@gmail.com>
Subject Re: Wiki Page for dblook SQL Authorization Support Project
Date Thu, 14 May 2009 05:00:31 GMT
Hi Dag,

Thanks a lot for taking time to go through the wiki page and providing your
valuable insight. I'll soon update the wiki with the things you have
mentioned here. See my comments in-line.

On Wed, May 13, 2009 at 9:59 PM, Dag H. Wanvik <Dag.Wanvik@sun.com> wrote:

> 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
> > 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
> the owning user of a view. The case is similar for triggers and
> constraints.

Thanks. That answers it :)

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

Yes. Thanks for pointing that out. So I guess we need to add this as a step
of our overall execution (may be prior to creating the dependency graph?). I
think we can find all the necessary data by just looking at the SYSROLES
table to do this.

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

Yes. That's what I meant. Basically we need to generate a statement which
establishes the necessary database connection, when executed. I'll rephrase
this on the wiki so it's more clear to the reader.

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

Indeed. Each persistent object will be a vertex in the graph.


> Dag

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

View raw message