db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "John C. Turnbull" <ozem...@ozemail.com.au>
Subject RE: Derby database design issues
Date Thu, 02 Aug 2007 15:34:42 GMT
Hi Thomas,

Thanks for your information.  Comments inline

> - separate the (biz)logic from the client(s).
> This gives greater flexibility wrt clients, resulting in less coding,
> and I dare say simpler bugfixing, and (a lot) less administration.
> There is one centralized location where a potential update/bugfix must
> be applied. No need to run around installing updated clients, so it
> eases administration. And it can be done "online", so it's
> instantaneous. Combined this probably makes it one of the more
> important
> advantages of stored procedures.

[JCT] Yes, I see this as a real advantage.
> - the database engine *may* be able to precompile the stored procedure,
> and reuse that precompiled block at a later invocation of the stored
> procedure for a (very slight) performance gain. Impact depends on the
> nastyness of the expression and the engine itself.
> This is no different from what you would get from a PreparedStatement
> in
> the same engine though.

[JCT] I guess then that if the procedure is being used heavily and multiple
times there might be a real performance gain from using procedures over
straight SQL.

> > 2.       Is it possible to disable direct access to tables and
> provide
> > the only access via procedures as it is in other RDBMS?
> Except using GRANT/REVOKE? I don't think so.
> But you might be able to do it anyway - grant access to the table(s)
> only to a "sqlproc" (or whatever) user, and then execute the stored
> procedure as that user?

[JCT] In Sybase what I would do would be to revoke access to all tables for
a particular group to which all users of the system would belong.  Then I
would grant access to the set of procedures to that same group.  The result
was that all data manipulations were of a pre-defined type and it was
impossible for a user to do anything undesirable.  I see that Derby doesn't
seem to have the user group concept so to achieve the same effect every user
would have to have their privileges to the tables and procedures set
individually.  No big problem I guess but having groups would be nice.



View raw message