[
https://issues.apache.org/jira/browse/DERBY-4428?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12772492#action_12772492
]
Kristian Waagan commented on DERBY-4428:
----------------------------------------
Thanks for the comments, Dag and Rick.
See my replies below, which address comments from both of you.
(Dag)> SQL uses the keyword "DROP", is there a reason you prefer "delete" over
drop in the URL?
No, I wouldn't mind changing it to "drop" if that is more in line with the SQL
spec. Is there something like "drop database" in the SQL spec?
Note that I don't have any plans at this time to implement a way to delete
databases using SQL.
(Dag)> ... [authentication and authorization] ... But it would be slightly
asymmetrical. What do you think?
(Rick)> I wonder if we need to check whether authentication is on?
I think we need two different paths here. Assuming SQL authorization will be
used to control who can delete a database in the future, what about this?
if (authenticationOn && sqlAuthorizationOn) {
checkPrivileges(); // May also want to see if the user is the DBO here,
depending on implementation details.
} else {
checkIsDBOwner();
}
Since we don't have the required functionality to use system privileges through
GRANT/REVOKE yet, we can just check for DBO unconditionally as Rick suggests.
Note that this will allow only the DBO to delete the database. Another option
is to allow everyone to delete a database if no authentication/authorization is
enabled, but this is of course very liberal.
(Rick)> ... [authentication] ... In such a situation, I think it's enough to
require that there be no open connections to the database.
This doesn't happen on shutdown, does it? Why should it happen on drop/delete,
in which case the data goes away anyway?
The only reason I can think of, is to allow the existing connections to finish
their queries.
As an experiment, I implemented a simple mechanism to block database access.
The mechanism was activated by the delete request, but could also be made
available through the JDBC connection URL (would make it possible for the DBO
to block access for maintenance work). My test, which ran 3 - 20 concurrent
threads accessing the database to be deleted and then executing a simple query,
gave much better results when the block mechanism was used. There are levels of
consideration:
- wait for ongoing connection attempts to finish (with the block mechanism,
new connections won't be allowed)
- wait for a given time to allow [some] queries to complete (issue with long
running queries)
- wait for all connected clients to disconnect (with timeout?)
Especially ongoing connection attempts result in lots of different error
messages. I've seen the shutdown exception (which is fine, I think), NPEs,
conglomerate not found exceptions, and others. It is also a question of when
one should signal / perform shutdown, as this will cause the connections to be
dropped at some point.
(Dag)> I think enabling dropping of on-disk databases is interesting. What
would the cons be?
I'm thinking mostly about the security aspect. Are there other issues?
A possible add-on feature here would be a property disabling the JDBC delete
mechanism for production systems, but this could be problematic if deleting
some databases in the system should be allowed. Maybe SQL authorization could
be used instead, if the drop/delete privilege can't be granted again if it has
been revoked?
(Rick)> I think the feature should be designed so that it can be extended to
the on-disk back end. However, I would treat on-disk back ends as a separate
JIRA - ...
I agree. This will require some extra code right away, because the
functionality is already used internally (to re-create the service root). I
know about several ways deletion can be disabled for a specific storage
factory, but they all have drawbacks... Maybe there is a better way I haven't
found yet, but I'll get back to the details later.
(Rick)> ... [system privileges] ... That privilege includes the specification
of where you are allowed to create databases.
Even though "where" isn't quite as well-defined in the in-memory back end as in
the on-disk/directory back end, I think such a privilege may be enough.
However, the Java security manager, or a custom one, would have to be faulted
in since the in-memory back end doesn't access the virtual files through
java.io.File.
It is not clear to me if such a privilege would allow us to differentiate
between back ends, or rather subsubprotocols.
Would the system privileges also allow us to say who are allowed to create
databases?
I now have the following known tasks on my list:
- add in-memory specific code required for drop
- add generic code required for drop (includes URL handling)
- add database access block mechanism
- disable drop/delete for on-disk back end (the rest of the back ends are read
only)
- add drop tests (for in-memory back end primarily)
- add mechanism to control / limit database creation (we can already use the
Java security manager for on-disk databases, but not for in-memory databases)
(- improve handling of unsuccessful boots in the in-memory storage factory
(resource management issue))
Further comments are welcome.
> Add proper delete mechanism for in-memory databases
> ---------------------------------------------------
>
> Key: DERBY-4428
> URL: https://issues.apache.org/jira/browse/DERBY-4428
> Project: Derby
> Issue Type: Improvement
> Components: JDBC, Services, Store
> Affects Versions: 10.6.0.0
> Reporter: Kristian Waagan
> Assignee: Kristian Waagan
> Attachments: derby-4428-0a-preview_patch.diff
>
>
> The current mechanism for deleting in-memory databases isn't good enough, and
> a proper one must be added.
> It is also important to be able to delete in-memory databases, since they
> occupy valuable main memory that should be discarded when the database is no
> longer needed.
> I intend to implement the mechanism by using the JDBC connection URL:
> "jdbc:derby:memory:myDatabase;delete=true[;user=X;password=Y]
> The connection attempt will throw an exception in any case, either because
> the request failed or because it succeeded.
> Reasons for a failure can be invalid user and/or password, lacking encryption
> attributes, or conflicting attributes.
> For the time being, only the database owner will be allowed to delete
> databases (*note*: do we have a way to control/limit in-memory database
> creation?)
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
